自己动手查看数据库中的不同粒度锁

下面的内容全部是自己发出的一封工作信件。起由是公司的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