Serialize the real key range locked by the key-range at the isolation level

 

We all know that key range locking is introduced in the serialization isolation level. A key-range lock prevents other transactions from inserting a new row whose key value is within the range of the key value that can be read by a serialized transaction to ensure that this requirement is met. But are you sure you know the lock range?

 

A few days ago, I saw some people have doubts about the lock range. I found that there are more locked data than I thought.

 

The following is an example:

--- Create tableand insert Test Data

Create Table Test (c1intprimarykey, c2varchar (20 ))

Insert into testvalues (1, 'test'), (2, 'test'), (3, 'est ')

-- Query execution in the first window

 

Set transactionisolationlevelserializable

Begin tran

Select *
From test where c1
Between 1 and 3

-- Query execution in the second window

Insert into testvaluees (100, 'test ')

 

Generally, the rang lock should lock 1-3 data and do not allow any modifications to the data. Therefore, inserting 100 should be normal. However, the process with 100 inserted has been blocked and cannot be executed.

 

Through the resource_description field of SYS. dm_tran_locks, we can see "ffffffffffff". That is to say, the key range lock of SYS. dm_tran_locks is 1, 2, 3, to infinity, and many simple 1, 2, 3. This conflicts with the rang range we generally understand.

 

Check that msdn hasNote:: The number of contained ranges-s locks is n + 1, where N is the number of rows meeting the query Conditions

That is to say, the actual range of the key range lock is the value between the key range + to the next value (including the next value );

 

Compared with the above example, we can see that the table has only three pieces of data, which is equivalent to all the data. The value of next is infinite, so the range of key-range is infinite. If we insert record 4 and execute the preceding statement, we can see that the execution is successful (the locked record is 1-4,100 and is not in this range, so we can insert it successfully ).

 

The above example shows that the range of rang lock is larger than we generally know, so pay special attention to it during use.

 

For rang lock reference: http://msdn.microsoft.com/zh-cn/library/ms191272 (V = SQL .105). aspx