CODEWITHSUNDEEP
sql-servervb.nettransactionstransactionscope
Bill
Bill

Reputation: 33

Is this .NET/SQL Server transaction scenario possible?

I just realized that I fundamentally don't understand how .NET/SQL Server transactions work. I feel like I might pushing the envelop on "there's no such thing as a dumb question", but all of the documentation I've read is not easy to follow. I'm going to try to phrase this question in such a way that the answer will be pretty much yes/no.

If I have a .NET process running on one machine that is effectively doing this (not real code):

For i as Integer = 0 to 100
    Using TransactionScope
        Using SqlClient.SqlConnection
            'Executed using SqlClient.SqlCommand'
            "DELETE from TABLE_A"
            Thread.Sleep(5000)
            "INSERT INTO TABLE_A (Col1) VALUES ('A')"
            TransactionScope.Complete()
        End Using
    End Using
Next i

Is there any Transaction / Isolation-Level configuration that will make 'SELECT count(*) FROM TABLE_A' always return '1' when run from other processes (i.e. even though there are 5 second chunks of time when there are no rows in the table in the context of the transaction)?

Upvotes: 3

Views: 203

Answers (3)

usr
usr

Reputation: 171206

Yes, you can make other processes not see the changes you do in the transaction shown. To do that you need to alter the other processes, not the one making the modification.

Turn on snapshot isolation and use IsolationLevel.Snapshot on the other reading processes. They will see the table in the state right before you made any modifications. They won't block (wait).

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294407

SNAPSHOT isolation is what you're looking for. Assuming that the table has a row when you start your loop, a concurrent SELECT running under SNAPSHOT isolation level will always see 1 row, no matter when is run, without ever waiting.

All other isolation levels, except READ UNCOMMITTED, will also always see exactly 1 row, but will often block for up to 5 seconds. Note that I consider READ_COMMITTED_SNAPSHOT as SNAPSHOT for this argument.

Dirty reads, ie. SELECTs running under REAd UNCOMMITTED isolation level, will 0, 1 or even 2 rows. That is no mistake, dirty reads may see 2 rows even though you never inserted 2 at a time, it is because race conditions between the scan point of the SELECT and the insert point of your transaction, see Previously committed rows might be missed if NOLOCK hint is used for a similar issue discussion.

Upvotes: 1

Christopher Cook
Christopher Cook

Reputation: 830

I believe the default transaction timeout is 1 minute (see: http://msdn.microsoft.com/en-us/library/ms172070.aspx ) so within the context of your transaction I think you're correct to expect the table to have no records before your insert (regardless of the pause), as each command will complete in sequence within the transaction and that would have been the result of the delete.

Hope that helps.

Upvotes: 0

Related Questions