Sunday, October 19, 2008

Connection and transaction handling explained

In an application like Toad connection and transaction handling will invariant become a complicate affair. During the years we have been working on this we have tried to make it as intuitive as possible so that you usually don't have to think about it. However, in some cases you can still run into problems so this post will try to explain how we handle connections and transactions so that you can avoid the pitfalls.

All information in this article refer to the upcoming 4.1 (2.1 for TDA) release or later.

Connections

In Toad we distinguish between two different kinds of connections. The first on we refer to as a Toad connection which is our abstract representation of a user connecting to a database. Each toad connection can be represented by one or more database connections which are actual connections to a database. At a minimum Toad will usually use at least 2 database connections per Toad connection. The first one is used to fetch database meta data about the database. The second one is used for either the data tab or the editor you are looking at.

We need to have a separate connection to inspect the database because some databases (DB2 in particular) have an unnerving habit of creating locks even if you are just reading data so whenever we read data on these connections we always commit after we are done reading and if you were running Toad with auto commit off we would mess up transactions that were open.

By default each editor and data tab will use it's own database connection to keep the transactions apart. You can control this behavior through the option in the screen shot on the right. If you change this all data tabs and editors will share one connection. You will still have two connections though.

Depending on which database you are connecting to Toad does support to use command multiplexing by which I mean executing several statements as one on the same connection. Currently the only provider that uses this functionality right now is Oracle though. If you try to execute something in an editor while another statement is already running and the connection does not support multiplexing you will be presented with the dialog on the left.

Basically you have the choice of how you want to proceed. At the bottom you also see what it is that is already running on the connection. Most choices are pretty self explanatory except the one about opening up a new connection and execute your new statement on that connection. If you do choose this option the new connection will be the one that all the following executions in the editor will use.

When you close an editor the connection associated with it is also closed immediately unless there are open transactions on the connection. If there are transactions the connection will be closed when you commit or rollback the connection.

There are also a bunch of other places where Toad will allocate another database connection. For instance when you are applying changes from database object editors (This is platform specific) or debugging (Which has a tendency to need two database connections per debugging session).

If you want to see how many database connections are actually being used you can see that in the tooltip over the connection in the connection manager.

Current connection and document connection

Two things that many people have problems with is the difference between the current connection and the connection of the current document. The current connection is the connection that any new documents will have and also the connection currently being shown in the Object Explorer. You can also see which connection is current either by the connection being shown in the connection toolbar or through the icon in the connection manager.

The connection of the current document is shown in the lower right corner of the application (Shown to the right here). This button shows the connection of the current document. Also when you click it you can switch to other compatible (And open) connection depending on the document. Some documents don't have connections attached to them and in those cases this button will not be visible. You can also switch the connection of a document by right clicking on the document tab.

Normally this doesn't present any problems because when you switch the current connection we automatically select the first open document that has that connection and also when you switch document we also change the current connection to that of the new current document.

There is one case when the two can differ. If you have two open connections to two different database instances, let's call them A and B. Next you make sure you close all documents that have the database B as the document connection. Then while being on an editor with connection A as the document connection you use the connection toolbar or connection manager to switch to connection B. In this case since there is no document that we can switch to that has connection B as the document connection we will not change the active document. If you now try to execute something in the current document it will execute on connection A even though both the connection manager and the Object Explorer is showing connection B as the current connection. This is an unfortunate exception and there is really nothing we can do about it, we have tried a bunch of different solutions to this problem but they all turned up even more confusing to the end user.

Transactions

Transactions in Toad is really tricky. You control transactions through the controls in the lower right corner of the main window (This has changed for between 4.1 and 4.0). It now looks like on the right.

The two commit and rollback buttons are only visible if you are not in auto commit mode. You switch auto commit mode by clicking the button to the far right and that part should be fairly self explanatory. When you switch auto commit on or off that setting is persisted per connection between sessions so be aware that if you forget to switch auto commit back to on when you close Toad you will still be in auto commit when you start the application back up again and connect to the same connection.

Auto commit is global for the Toad connection and will affect all editors and data tabs for a specific connection when you switch it. As of version 4.1 there is also a new item in this menu which allows you to switch one specific editor to auto commit without affecting the browser or other editors. Also when you use this feature it is not persisted to the next session and you will still be in auto commit when you restart Toad. Normally when you click the commit or rollback buttons it will commit all database connections for the current Toad connection. However, when you are in the auto commit off for the current editor the commit and rollback buttons only affect that editor (Even if you have more than one editor with this mode enabled for the same Toad connection).

All connections start by default in auto commit mode except for the Oracle platform which is in auto commit off by default (This is the platform that has the least problem with unexpected locks of the ones we support). This default (Which only applies when you add new connections in the connection manager) can be changed in the options as shown here on the right.

Inside Auto Commit

Auto commit is implemented in two different ways depending on the platform you are connecting to. For SqlServer and MySQL we rely on the implicit auto commit available on the server. For all other platforms auto commit is implemented by simply issuing a commit after we are done. There is an option to indicate what "done" in this case means. It's in the lower edge of the red rectangle in the screen shot above. You can choose to either commit after each statement or only after you are done executing the entire batch of statements you are executing (If you are executing a script for instance).

Pitfalls of not using auto commit

Some databases (Specifically SqlServer and DB2) have a problem with dropping locks around when you are running with auto commit off. This is particularly acute when you are issuing DDL commands because these have a tendency to create locks on the data dictionary which causes all activity in the database to lock up for all other connections than the one used in the editor. Since the database browser uses a different connection than the editor to fetch meta data about objects using the database browser in this case can completely lock up Toad. There is unfortunately very little we can do about this issue (At least as we have figured out yet), but it is an issue we are thinking a lot about.

Regardless of the database you are connected against though auto commit off can by it's very definition create locks that can stop other users to access parts the database. Because of this there is a timer in Toad (By default it is set to 30 minutes, but you can change this in the Database|Timeout page in the options). After this timer runs out you will be asked to commit or rollback your changes and if you don't reply within a 30 second time period your changes will be rolled back automatically. This is just to make sure you don't forget to commit changes in Toad, go home from work and then have the DBA yelling at you in the morning because you caused the night time batch runs to fail because of your lock.

No comments: