INFO: Understanding ADO Transactions with MS SQL Server

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

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

References

For additional information, please see the following article(s) in the Microsoft Knowledge Base:
177138 INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO
Propiedades

Id. de artículo: 198024 - Última revisión: 10/30/2003 - Revisión: 1

Comentarios