SQLTransaction IsolationLevel questions

Discussion in 'ASP .Net' started by eric.goforth@gmail.com, Jun 7, 2006.

  1. Guest

    Hello,

    I have a subroutine similar to the following:


    Private Sub RunUpdateTransaction(ByVal UpdateSQL As String, ByVal
    UpdateConn As SqlConnection)

    Dim trnDedupe As SqlTransaction =
    UpdateConn.BeginTransaction(IsolationLevel.ReadCommitted, "Dedupe")

    Dim cmdDedupe As New SqlCommand(UpdateSQL, UpdateConn,
    trnDedupe)

    Try

    Dim iUpdated As Integer

    iUpdated = cmdDedupe.ExecuteNonQuery

    trnDedupe.Rollback()

    Catch ex As Exception
    Throw ex
    Finally
    If Not cmdDedupe Is Nothing Then cmdDedupe.Dispose()
    End Try

    End Sub

    UpdateSQL looks like:

    INSERT INTO MyTable1(Field1, Field2)(SELECT Field1, Field2
    FROM MyTable1 WHERE MyId1 = 123);
    UPDATE MyTable2 SET MyID1 = (SELECT MAX(MyID1) FROM MyTable1)
    WHERE MyID1 = 123 AND MyID2 = 456;

    I have a breakpoint on trnDedupe.Rollback(). I then switched to Query
    Analyzer and tried to run a query SELECT * FROM MyTable1 WHERE MyID1 =
    (SELECT MAX(MyID1) FROM MyTable1)

    I started this query before I stepped away from my desk and it's been
    running for 2-1/2 hours. Should I try another type of IsolationLevel
    other than ReadCommitted. It looks like ReadCommitted might be good to
    prevent someone else from inserting a record into MyTable1 in between
    my INSERT and UPDATE. From the documentation:
    --------------------------------------------------------------------------------------
    ReadUncommitted:

    Shared locks are held while the data is being read to avoid dirty
    reads, but the data can be changed before the end of the transaction,
    resulting in non-repeatable reads or phantom data.

    ReadUncommitted:

    A dirty read is possible, meaning that no shared locks are issued and
    no exclusive locks are honored.

    RepeatableRead:

    Locks are placed on all data that is used in a query, preventing other
    users from updating the data. Prevents non-repeatable reads but phantom
    rows are still possible.
    --------------------------------------------------------------------------------------
    If I'm reading the documentation correctly, it looks like I might want
    to use ReadUncommitted for my testing and ReadUncommitted when I'm
    using the application in production to prevent inserts between my
    INSERT and UPDATE. Would any of the other IsolationLevels be useful?

    Thanks,
    Eric
     
    , Jun 7, 2006
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. .Net Newbie
    Replies:
    2
    Views:
    714
    =?Utf-8?B?RGVlcA==?=
    Jul 9, 2004
  2. Piotr Strycharz

    SqlTransaction issue

    Piotr Strycharz, Dec 15, 2004, in forum: ASP .Net
    Replies:
    5
    Views:
    614
    Piotr Strycharz
    Dec 16, 2004
  3. Joe Rigley

    SqlTransaction Record Not Found

    Joe Rigley, Nov 11, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    381
    Joe Rigley
    Nov 11, 2005
  4. Neven Klofutar

    SqlTransaction problem

    Neven Klofutar, Nov 23, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    529
  5. sloan
    Replies:
    2
    Views:
    1,155
Loading...

Share This Page