How To Implement Nested Transactions with ADO and SQL Server

Izjava o odricanju odgovornosti za sadržaj o otpisanom proizvodu u bazi znanja

Ovaj je članak napisan o proizvodu za koji Microsoft više ne pruža podršku. Članak će stoga ostati nepromijenjen i više se neće ažurirati.

Summary

ADO, with both SQLOLEDB and MSDASQL providers, does not support nested transactions with SQL Server. However, native T-SQL commands can still be used with ADO to implement nested transactions against SQL Server.


This can be accomplished in two different ways:

Model-1: Without using save points

- Begin OuterTrans
- Begin InnerTrans1
- Begin InnerTrans2
- Begin InnerTrans3

Analysis:
Committing OuterTrans commits all nested transactions. Rolling back OuterTrans rolls back all nested transactions.

Model-2: Using save points:

- Begin OuterTrans
- Save InnerTrans1
- Save InnerTrans2
- Save InnerTrans3

Analysis:
Committing OuterTrans commits all nested transactions. Rolling back any saved inner-transaction point rolls back all transactions nested under that point. Saved points can be used to roll back nested transactions from a given point on without affecting transactions nested under different saved points.

More Information

The following is a sample Visual Basic ADO application that goes against SQL Server. It uses the "stores" table in pubs database.
  1. Create a new standard EXE Visual Basic project. Form1 is created by default. Add two command buttons to Form1 as follows:
    Command1.Caption : Use Nested Transactions
    Command1.Name : NestedTrans

    Command2.Caption : Use Save Points
    Command2.Name : SavePoint
  2. Add a reference to the Microsoft ActiveX Data Objects Library.
  3. Paste the following lines into the Code window for Form1:

    Note You must change User ID=<User ID> to the correct value 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 SavePoint_Click()

    With rst
    .ActiveConnection = Cn
    .CursorType = adOpenStatic
    .Source = "select * from stores where stor_id LIKE '10%'"
    .Open
    End With

    Cn.Execute "Delete from stores where stor_id LIKE '10%'"

    ' OuterTrans
    Cn.Execute "BEGIN TRANSACTION OuterMost"

    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(101,'1st Store')"
    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(102,'2nd Store')"

    ' InnerTrans1 Transaction
    Cn.Execute "SAVE TRANSACTION InnerTrans1"

    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(103,'3rd Store')"
    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(104,'4th Store')"

    ' InnerTrans2 Transaction
    Cn.Execute "SAVE TRANSACTION InnerTrans2"

    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(105,'5th Store')"
    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(106,'6th Store')"

    ' InnerTrans3 Transaction
    Cn.Execute "SAVE TRANSACTION InnerTrans3"

    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(107,'7th Store')"
    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(108,'8th Store')"

    Cn.Execute "ROLLBACK TRANSACTION InnerTrans2"
    Cn.Execute "COMMIT TRANSACTION OuterMost"

    rst.Requery

    While Not rst.EOF
    Debug.Print rst(0), rst(1)
    rst.MoveNext
    Wend

    rst.Close
    End Sub

    Private Sub NestedTrans_Click()

    With rst
    .ActiveConnection = Cn
    .CursorType = adOpenStatic
    .Source = "select * from stores where stor_id LIKE '10%'"
    .Open
    End With

    Cn.Execute "Delete from stores where stor_id LIKE '10%'"

    ' OuterTrans
    Cn.Execute "BEGIN TRANSACTION OuterMost"

    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(101,'1st Store')"
    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(102,'2nd Store')"

    ' InnerTrans1 Transaction
    Cn.Execute "BEGIN TRANSACTION InnerTrans1"

    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(103,'3rd Store')"
    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(104,'4th Store')"

    ' InnerTrans2 Transaction
    Cn.Execute "BEGIN TRANSACTION InnerTrans2"

    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(105,'5th Store')"
    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(106,'6th Store')"

    ' InnerTrans3 Transaction
    Cn.Execute "BEGIN TRANSACTION InnerTrans3"

    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(107,'7th Store')"
    Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
    "VALUES(108,'8th Store')"

    ' Uncomment the following line to roll back the OuterTrans that rolls
    ' all nested transactions.
    ' Cn.Execute "ROLLBACK TRANSACTION OuterMost"

    ' The following commits all nested transactions.
    Cn.Execute "COMMIT TRANSACTION OuterMost"

    rst.Requery

    While Not rst.EOF
    Debug.Print rst(0), rst(1)
    rst.MoveNext
    Wend

    rst.Close
    End Sub

    Private Sub Form_Load()
    Cn.Open "Provider=SQLOLEDB;User ID=<User ID>;Data " & _
    "Source=<insert your SQL Server>;database=pubs"
    Cn.CursorLocation = adUseClient
    End Sub
  4. Change the connection string in the Form_Load method appropriately to connect to your SQL Server.
  5. Run the project, and click the NestedTrans command. This commits all nested transactions.
  6. Click SavePoint. This rolls back InnerTrans2 and InnerTrans3, yielding the following results:
    101 1st Store
    102 2nd Store
    103 3rd Store
    104 4th Store

References

For additional information, please click the article numbers below to view the articles in the Microsoft Knowledge Base:
177138 INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO
198024 INFO: Understanding ADO Transactions with MS SQL Server
SQL Server Books on Line, documentation.
Svojstva

ID članka: 238163 - posljednja izmjena: 1. srp 2004. - verzija: 1

Povratne informacije