SQLTransaction IsolationLevel questions

[eric.goforth]

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