Using a Single Shared Physical Connection

By default, DbVisualizer uses multiple physical connections to the database. Each SQL Commander tab is allocated its own connection. Other processes, which update the database, such as saving grid edits or importing data in a table, use their own connections. Finally, most read-only operations, such as navigating the database objects tree, use a separate shared connection.

This is normally the most efficient way to access the database, but in certain circumstances it is important to instead use a single shared physical connection for all operations. Some examples are:

• Only one session per account is allowed in the target database,
• Locking issues when modifying the same table in the Data tab and in an SQL Commander (when a pending transaction locks the whole table)
• When using one-time passwords, new physical connections cannot be established without prompting for a new password.

For situations like these, you can force DbVisualizer to use a single shared physical connection.

MFA/2FA and browser-based authentication

Turning on "Single Shared Physical Connection" is especially useful for database connections established with any of MFA/2FA, token or browser-based authentication. The reason is that you otherwise will need to authenticate whenever DbVisualizer opens a new physical connection with the server, which may be a bit overwhelming and annoying. The downside with a single shared physical connection is that running parallel actions in DbVisualizer may be limited.

Selecting the Single Shared Physical Connection Mode

To use a single shared physical connection:

1. Open the Object View tab for the database connection,
2. Select the Properties tab,
3. Select the Physical Connection category and enable Use a Single Shared Physical Connection.

Data Manipulation with a Single Shared Physical Connection

Executing a script in an SQL Commander tab, using an Action, editing a table in a grid and importing data to a table are all operations that (potentially) modify data in the database. When a single shared physical connection is used, only one such operation may be performed at a time. If you try to start an operation like this while another one is already being processed, a dialog will pop up asking you to try again later.

Transaction Handling with a Single Shared Physical Connection

If you have Auto-Commit disabled with Single Shared Physical Connection enabled, commits or rollbacks done in one part of the GUI affect changes done in any other part of the GUI. An example is if you have executed UPDATE or INSERT statements in an SQL Commander tab, and then edit a table in its Data tab and commit those changes, you are also committing the changes made by the UPDATE or INSERT statements. To make this clear, all GUI controls for transaction handling for shared physical connections are shown in a separate Single Physical Connections window.

This window pops up when you connect to a database with Single Shared Physical Connection enabled, or when clicking any of the transaction control buttons in an SQL Commander tab for such a database. You can also bring it up by clicking on the corresponding icon in the DbVisualizer status bar, or by choosing Help → Single Physical Connections in the application menu. From this window, you can enable or disable Auto-Commit and manually commit or rollback a pending transaction.

You also get prompted to commit, rollback or continue working within the same transaction every time an operation results in data changes. Before potentially making lots of changes, you get prompted to enable Auto-Commit, since making lots of changes (e.g., importing lots of data) may fill up redo logs if running with Auto-Commit disabled.