INFO: Understanding ADO Transactions with MS SQL Server

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:

1. 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
   						

2. Go to the references and add the following reference:

         Microsoft ActiveX Data Objects 2.0 Library
   						

3. The code below uses the stores table of the Pubs database. Make sure you set the database properly in your connectstring.
4. Paste the following lines into the Code window for Form1:
   
   Note You must change the User ID <username> value and the password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

         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=<username>;Password=<strong password>;Data" & _
            "Source=(local);database=pubs"
            Cn.Open strConn
            Cn.CursorLocation = adUseClient
            Command1.Caption = "Use ADO Transactions"
            Command2.Caption = "Use T-SQL Transactions"
         End Sub
   						

5. Click on Command1. You will get a RecordCount of 2.
6. 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.