Using a Single Shared Physical Connection

By default, DbVisualizer uses multiple physical connections to a database. Each SQL Commander tab is allocated its own connection. Other processes that update the database, such as saving grid edits or importing data to a table, also 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 one 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.

Selecting the Single Shared Physical Connection Mode

To use a single shared physical connection:

1. Open the Object View tab for the connection node,
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. For instance, 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 click the corresponding button in the DbVisualizer status bar to bring it up. 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.