INFO: Understanding ADO Transactions with MS SQL Server

ID: Q198024

The information in this article applies to:

Microsoft OLE DB Provider for SQL Server, version 7.0
ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2
Microsoft ODBC Driver for SQL Server, version 3.7

SUMMARY

The ActiveX Data Objects (ADO) Connection method BeginTrans states that it begins a new Transaction, but it executes "set implicit_transactions on." In many cases, "set implicit_transactions on" is equivalent to T-SQL BEGIN TRANS. However, there are subtle differences. This article illustrates one of those differences.

MORE INFORMATION

How Does ADO Behave with Respect to Transactions

By default ADO operates in AutoCommit mode, unless you start a implicit transaction by executing Connection.BeginTrans.

Implicit_transactions begin a transaction on the server for each statement, and commits do not occur until they are manually issued.

So,


   set implicit_transactions on
   go
   insert
   insert
   insert

is internally turned into


   BEGIN TRAN
   Insert
   insert
   insert
   ...

The above transaction will not be rolled back or committed unless the user issues the correct statement.

Without implicit transaction, which by default is the behavior of ADO (Auto Commit mode), the following is (conceptually) occurring:


   begin tran
   insert
   commit tran
   begin tran
   insert
   commit tran
   ...

The following code sequence, written in Visual Basic, shows a difference between the raw SQL "BEGIN TRANSACTION" and the "set implicit_transactions on" issued when the ADO connection method BeginTrans is invoked:

Create a new standard .exe Visual Basic project. Add two command buttons to Form1, the default form.


      Command1.Caption : Use ADO Transactions
      Command2.Caption : Use T-SQL Transactions

Go to the references and add the following reference:


      Microsoft ActiveX Data Objects 2.0 Library

The code below uses the stores table of the Pubs database. Make sure you set the database properly in your connectstring.

Paste the following lines into the Code window for Form1:


      Option Explicit
      Dim Cn As New ADODB.Connection
      Dim Cmd As New ADODB.Command
      Dim rst As New ADODB.Recordset
      Private Sub Command1_Click()
        Cn.Execute "Delete from stores where stor_id LIKE  '1%'"
        Cn.BeginTrans
        Cn.Execute "set implicit_transactions off"
        Cn.Execute "Insert INTO Stores(stor_id, _
        stor_name,stor_address,city)" & _
        "VALUES(101,'Store One','123 Oak St.','Seattle')"
        Cn.Execute "Insert INTO Stores(stor_id, _
        stor_name,stor_address,city)" & _
        "VALUES(102,'Store Two','123 Main St.','Tacoma')"
        Cn.RollbackTrans
        With rst
          .ActiveConnection = Cn
          .CursorType = adOpenStatic
          .Source = "select * from stores where stor_id LIKE '10%'"
          .Open
        End With
        MsgBox rst.RecordCount
        rst.Close
      End Sub

      Private Sub Command2_Click()
       Cn.Execute "Delete from stores where stor_id LIKE  '1%'"
       Cn.Execute "BEGIN TRANSACTION"
       Cn.Execute "set implicit_transactions off"
       Cn.Execute "Insert INTO Stores (stor_id, _
       stor_name,stor_address,city)" & _
       "VALUES(101,'Store One','123 Oak St.','Seattle')"
       Cn.Execute "Insert INTO Stores (stor_id, _
       stor_name,stor_address,city)" & _
       "VALUES(102,'Store Two','123 Main St.','Tacoma')"
       Cn.Execute "ROLLBACK TRANSACTION"
       With rst
          .ActiveConnection = Cn
          .CursorType = adOpenStatic
          .Source = "select * from stores where stor_id LIKE '10%'"
          .Open
        End With
       MsgBox rst.RecordCount
       rst.Close
      End Sub

      Private Sub Form_Load()
         Dim strConn As String
         strConn = "Provider=SQLOLEDB;User ID=sa;Data" & _
         "Source=(local);database=pubs"
         Cn.Open strConn
         Cn.CursorLocation = adUseClient
         Command1.Caption = "Use ADO Transactions"
         Command2.Caption = "Use T-SQL Transactions"
      End Sub

Click on Command1. You will get a RecordCount of 2.

Click on Command2. You will get a RecordCount of 0.

Reason for the Difference in Results

With ADO Connection.BeginTrans will not increment @@TranCount until any of the following statements are encountered:


   FETCH      ALTER TABLE
   DELETE     INSERT
   CREATE     OPEN
   GRANT      REVOKE
   DROP       TRUNCATE TABLE
   SELECT     UPDATE

When this option (set implicit_transactions on) is turned on and if there are no outstanding transactions, every ANSI SQL statement will automatically start a transaction. If there is an open transaction, no new transaction will be started. This transaction has to be explicitly committed by the user by using the command COMMIT TRANSACTION for the changes to take affect and the locks to be released.

Whereas executing Cn.Execute "BEGIN TRANSACTION" starts an explicit transaction and @@TranCount is immediately incremented by 1.

In the above Visual Basic code for the procedure Command1_Click() the @@TranCount remains at 0 and the Rollback has no effect because before executing the first insert, you revert back to Auto Commit mode by issuing Cn.Execute "set implicit_transactions off."

Whereas in the Visual Basic code for procedure Command2_Click(), the @@TranCount is immediately incremented to 1 when you issue Cn.Execute "BEGIN TRANSACTION; hence, the subsequent RollBack works.

The SQL Server 6.5 Trace utility is very useful for observing the Transaction commands this sample issues.

REFERENCES

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

Q177138 INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO

Additional query words: kbSQLServer700

Keywords : kbcode kbADO200 kbDatabase kbGrpMDAC kbDSupport kbADO210sp2 kbGrpODBC
Version : WINDOWS:1.5,2.0,2.1 SP2,3.7,7.0
Platform : WINDOWS
Issue type : kbinfo