Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > SQLTransaction IsolationLevel questions

Reply
Thread Tools

SQLTransaction IsolationLevel questions

 
 
eric.goforth@gmail.com
Guest
Posts: n/a
 
      06-07-2006
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.ReadCom mitted, "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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
TransactionScope vs SqlTransaction vs (BEGIN TRAN/COMMIT TRAN) on a SINGLE 2005 database sloan ASP .Net 2 08-26-2008 09:15 PM
SqlTransaction problem Neven Klofutar ASP .Net 3 11-26-2005 01:49 PM
SqlTransaction Record Not Found Joe Rigley ASP .Net 0 11-11-2005 06:31 PM
SqlTransaction issue Piotr Strycharz ASP .Net 5 12-16-2004 08:28 AM
Help! SQLTransaction and Identity(?) column in SQL Server 2K .Net Newbie ASP .Net 2 07-09-2004 09:50 AM



Advertisments