View locks of different granularities in the database by yourself

The following content is a work letter sent by myself. We recommend that you do not use the default isolation level (serilizable) when using transactionscope ), instead, use readcommitted (which is the default isolation level of transactions in SQL Server ).

 

I did some research on the content of this suggestion and wrote back the following.

 

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 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 "shocould 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 Really rough SolutionFor me. if "readcommited" is always the best for all the transactions, MS cocould just cut off all the other isolation level options. the truth is, as transaction developers, We shoshould 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 cocould keep un-changed.
For example, in PTN service, here is a transaction like Bellow (I deleted all the UN-related code ):
"
Using(TransactionscopeScope =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" WorksJ