PRB: DAO Transactions to ODBC Database Can Hang Application

• Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 5.0
• Microsoft Visual Basic Professional and Enterprise Editions, 16-bit and 32-bit, for Windows, version 4.0

SYMPTOMS

When using transactions to an ODBC database, it is possible to cause a Visual Basic application to appear to hang until the ODBC query time-out interval is reached and error 3146 "ODBC Call Failed" is raised. The DBEngine errors collection will show an ODBC "Time-out Expired" error.

The Visual Basic application appears to hang and will eventually display and error with an ODBC Time-out message after the interval specified in the QueryTimeout property of the database object (default = 60 seconds). If the error is trapped and handled, the application can resume normally although the data may have been rolled back or partially committed.

CAUSE

When the Microsoft Jet Engine, used by Visual Basic, performs transactions on an ODBC database, it issues ODBC transactions down each connection that it opens between the BeginTrans and the CommitTrans/Rollback. It then commits or rolls back all of the transactions on all of the connections when a CommitTrans or Rollback is issued.

It must do this because the Jet engine offers global and nested transactions while the current ODBC specification does not; it only allows for a single transaction on a per connection basis.

If needed, the Jet engine will open multiple connections in order to perform the transactions. The server sees each connection as a separate process. It has no way of knowing that it is from the same application or that you may want the connections to get along with each other in terms of locking or available data. It simply treats each connection as a completely independent and distinct entity.

This raises a problem. If the Jet engine opens a connection, begins a transaction that affects a given page of data on a given table, then opens another connection and begins another transaction that affects the same page of data, the server will block (serialize) the second connection until the first one releases the locks (completes). This causes the Visual Basic application to hang on the line of code that is running the statement on the second connection until the transaction that it sent on the first connection completes. This can never happen because the transaction on the first connection is open until a CommitTrans or Rollback is encountered in the Visual Basic code, but that code is hung waiting for the transaction to complete. It is hung indefinitely unless a time-out occurs on one of the connections, which is what eventually happens.

The following example demonstrates the problem with most ODBC servers:

1. Create a datasource for your server named DSN_BLK_TEST using the ODBC Administrator applet.

2. Open Visual Basic 4.0 (16- or 32-bit) and start a new project.

3. Reference the appropriate DAO reference in the References dialog.

4. Add a CommandButton to the form.

5. Add the following code to the click event of the CommandButton:

         Private Sub Command1_Click()
   

            Dim db As Database
            Dim rs As Recordset
            Dim strSQL As String
            Dim intLoop As Integer
   
            'Open up the ODBC datasource
            Set db = DBEngine.Workspaces(0).OpenDatabase("", _
            False, False, "ODBC;DSN=HOOHAA;DATABASE=TestDB;UID=sa;PWD=;")
            'Create a table with two fields one a primary key
            strSQL = "IF EXISTS(SELECT * FROM sysobjects WHERE id = "
            strSQL = strSQL & "object_id('t_blocking')) DROP TABLE t_blocking"
            db.Execute strSQL, dbSQLPassThrough
            strSQL = "CREATE TABLE t_blocking (f_pkey int " & _
                     "NOT NULL PRIMARY KEY,"
            strSQL = strSQL & " f_dummy varchar(10) NULL)"
            db.Execute strSQL, dbSQLPassThrough
            Set rs = db.OpenRecordset("SELECT * FROM t_blocking")
            'Demonstrate Blocking
            BeginTrans
            'Add some records
            For intLoop = 1 To 25
               rs.AddNew
               rs.Fields("f_pkey") = intLoop
               rs.Fields("f_dummy") = "test" & Str$(intLoop)
               rs.Update
            Next intLoop
            'Try to update a few records. This should cause a second
            'connection' to be created by the JET engine
            'causing a blocking condition.
            strSQL = "UPDATE t_blocking SET f_dummy = 'updated' " & _
                     "WHERE f_pkey > 20"
            db.Execute strSQL
            CommitTrans
   
         End Sub
   
   

NOTE: Depending on the locking mechanisms of the server, this test may not cause a locking problem.

RESOLUTION

To work around the problem:

1. Keep transactions as small as possible. If this condition is encountered, check the locking on the server and limit the transaction to the point where blocking occurs.

2. Use the ODBC API. If you are using Visual Basic 4.0 32-bit Enterprise Edition, use the Remote Data Objects (RDO) or Remote Data Control (RDC). Because you have complete control over connections and transactions on the connections, you can avoid this problem.

3. Use SQLPassthrough queries that combine the blocking statements into one statement that does transactions within the SQL statement. This could be ad hoc SQL or precompiled into a stored procedure.

STATUS

This behavior is by design.

(c) Microsoft Corporation 1997, All Rights Reserved. Contributions by Troy Cambra, Microsoft Corporation

Keywords          : VB4ALL VB4WIN vb5all
Version           : 4.0 5.0
Platform          : WINDOWS
Issue type        : kbbug