自己動手查看資料庫中的不同粒度鎖

下面的內容全部是自己發出的一封工作信件。起由是公司的DB組建議大家在使用TransactionScope的時候，盡量不要使用預設的隔離等級（Serilizable），而應使用ReadCommitted（這正好是Sql server中事物的預設隔離等級）。

 

我對這個建議內容，做了一些小小的研究，回信如下。

 

 When I firstly received this email, I am not really sure about why “Serializable“ cause the problem and how “ReadCommited” solves it.When I tried to read the description of different Isolation Levels, I saw a lot of “Volatile data”, but what it exactly means? For example, you update a record of a table, the “Volatile data” should be the table? The updated row? Related index page? And more, which kind of lock is used to make sure “Volatile data cannot be read during the transaction”? X lock? IX lock? S?

And more of more, to change all the isolation level to ReadCommited seems a really rough solution for me. If “ReadCommited” is always the best for all the Transactions, MS could just cut off all the other isolation level options. The truth is, as Transaction developers, we should analyze them case by case, and choose the best isolation level for it. 

Let’s checked the defect #number, which is listed bellow by DBA.
The code which causes deadlock is like this:
“
SELECT @Id = Id from [DVM].[EGMs] WHERE egmId = @egmId
UPDATE [DVM].[EGMs] set … where egmId = @egmId
”
Then why put these two sql sentences into a “Serializable” transaction can cause the problem?

1. the “select” sentence will try to apply a “S” lock on the whole table, like bellow
 2. The “update” sentence will try to apply a “X” lock on the whole table, like bellow

So, assume 2 threads both hold the “S” lock and want to apply a “X” lock, tragedy happens…

Then how “ReadCommited” helps?
1. the “select” sentence will not apply any additional locks
2. And , “update” will only apply “X” lock to the row, but “IX” to the index page and table, like bellow

So, It’s OK now for 2 threads to update the EGM table simultaneously.

After you understand all of the above, you will know, if we only want to avoid the dead-lock issue, a lot of Transactions could keep un-changed.
For example, in PTN service, here is a transaction like bellow (I deleted all the un-related code):
“
using (TransactionScope scope = new TransactionScope())

{

    repository.Remove(currentId);

  repository.Add(mPlayerConfig);

}
”
Because a “X” lock will be applied to the whole table for the first “remove” operation, no deadlock will ever have the chance to happen under “Serializable”.
Of cause, the “X” lock to the whole table definitely decrease the concurrency of the whole system, and you may want a “IX” to table and “X” to row --- And that is how “ReadCommited” works J