asked
Isolation level strategy for asp.net and sql server 2008.
Hi, What should be ideal Isolation level strategy for
asp.net and sql server 2008. We use read-commited but it still hangs see ex below. Table Name - Customers Isolation level - Read Committed Transaction 1: set transaction isolation level read committed Begin Tran Update Customersset ordercount=50 where id=2 WAITFOR DELAY '000:00:20' rollback tran Transaction 2: set transaction isolation level read committed Begin Tran Select * From Customers Trans commited Transaction 2 hangs for 20 second!! how to handle this scenario? Regards, Chandan
That's normal blocking based on the fact that the table is being updated. That's how the system is supposed to behave as part of maintaining the ACID properties of transactions within the system (Atomic, Consistent, Isolated, Durable, [read here for more detail][1]). In short, you want the transaction to complete it's actions without interference from other processes. You can try using the read committed snapshot isolation level. That will allow you to continue to read an older version of the data while the update is taking place. But, be prepared for additional load on your tempdb if you implement it. [Here's an introduction to the topic][2] from the Books Online. [1]:
http://msdn.microsoft.com/en-us/library/aa480356.aspx [2]:
http://msdn.microsoft.com/en-us/library/tcbchxcb(v=VS.80).aspx
14 People are following this question.
