SQL Server transactions and locks (II)-range S-S locks

In this article, we mainly focus on key-range lock. Key-range lock has s-s, S-u, I-n, X-X several situations. One by one, we strive to understand. Unfortunately, the deadlock analysis may be prolonged.

Acquisition rules for range S-S locks

Msdn partially describes the range lock rules, but it is concise. Below we will separate various situations and clarify the rules involved or not involved in msdn, these rules apply to SQL Server 2000/2005/2008/2008 R2. For the description of msdn, see: http://technet.microsoft.com/zh-cn/library/ms191272 (En-US, SQL .110). aspx.

Before describing the rule, we need to declare that our clustered index is built on the where clause, which is very important. Otherwise, we will not obtain the range lock and will not meet the requirements of serializable; in addition, for ease of discussion, the following SQL statements omit the declaration of SET transaction isolation level serializable.

Let's assume there are the following tables:

Create Table [DBO]. [mytable] ([ID] [int] Identity (1,1) not null, [index_column] [int] not null, [data] [int] not null, constraint [pk_mytable] primary key nonclustered ([ID] ASC) with (pad_index = OFF, rows = OFF, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on) on [primary]) on [primary] create unique clustered index [ix_mytable] on [DBO]. [mytable] ([index_column] ASC) with (pad_index = OFF, rows = OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF, drop_existing = OFF, online = OFF, allow_row_locks = on, allow_page_locks = on) on [primary]
 

Assume that we have the following data:

Insert into [mytable] ([index_column], [data]) values (1, 1) insert into [mytable] ([index_column], [data]) values (2, 2) insert into [mytable] ([index_column], [data]) values (3, 3) insert into [mytable] ([index_column], [data]) values (4, 4) insert into [mytable] ([index_column], [data]) values (5, 5) insert into [mytable] ([index_column], [data]) values (15, 6) insert into [mytable] ([index_column], [data]) values (16, 7) insert into [mytable] ([index_column], [data]) values (18, 8) insert into [mytable] ([index_column], [data]) values (25, 9) insert into [mytable] ([index_column], [data]) values (30, 10)
 

This table should look like this (I also dumped the hash value of the index and the data page where the row is located so that we can do the experiment ).

ID	Index_column	Data	Index HASH	Row page
1	1	1	(8194443284a0)	78
2	2	2	(61a06abd401c)	78
3	3	3	(98ec012aa510)	78
4	4	4	(A0c936a3c965)	78
5	5	5	(59855d342c69)	78
6	15	6	(F1de2a205d4a)	78
7	16	7	(F07ed88b2b23)	78
8	18	8	(E9069d930a93)	78
9	25	9	(B81181109ebc)	78
10	30	10	(8034b699f2c9)	78

When conditions in the WHERE clause hit existing records

Rule 1: If the WHERE clause uses the "Equal" condition, for example, "where [index_column] = 6", and the index is a unique index, the index won't get the key-range S-S lock, just get the general s lock on the key;

Suppose we execute

Select [data] from [mytable] Where [index_column] = 1

So we use sp_lock to get the lock:

We can find that the S lock is obtained on the first index, but it is not the range S-S lock.

Rule 2: If the WHERE clause uses "range" conditions, such as ">, <, between, and in. Whether or not the index is unique, the where clause specifies that the range S-S lock applies. In addition, under the index sorting rule, the "Next" index for this range also gets the range S-S lock.

We must first explain"Next"The "Next" index has two situations:

First, if an existing or "residual" index item can be found outside the scope of the index sorting rule according to the Data Layout direction (submitted for deletion, the database can no longer be seen, but it has not been deleted from the B-tree data page), then this index item is the "Next" index item;

Second, if no remaining index items are found in the direction of data distribution outside the scope of the index sorting rule, then infinitely far (resource hash is 0 xffffffff) is the "Next" index.

We will describe it with rule 2, for example, we will execute

Select [data] from [mytable] Where [index_column]> = 1 and [index_column] <= 4

Then the index with values of 1, 2, 3, 4 in index_column will get the range S-S lock, in addition, the next index value after 4, that is, the index corresponding to 5 gets the range S-S lock. This is exactly the same as our experiment results.

 

Let's look at another one. For example, we execute:

Select [data] from [mytable] Where [index_column]> = 20 and [index_column] <= 40

Then index_column is 25, 30 index will get the range S-S lock, in addition, the next index value after 30, that is, "infinitely far" will get the range S-S lock, please refer to the usage of the actual dump lock:

 

We finally tried a slightly more complex situation:

Select [data] from [mytable]

Where ([index_column]> = 2 and [index_column] <= 4) or ([index_column]> = 10 and [index_column] <= 16) or ([index_column]> = 30 and [index_column] <= 40)

The problem here is that our "range" refersApply rules to analyze closed scopes one by oneWe have three closed ranges: [2, 4], [10, 16], and [30, 40]. We come one by one, for [2, 4], within this range of 2, 3, 4, 5 get range S-S lock;

Get range S-S lock for range [], range 15, 16, 18; get range S-S lock for range [], 30, infinity get range lock, total 9.

 

Rule 1 supplement: If the WHERE clause uses an equal condition, but the index is not a unique index, except for the index hit by the WHERE clause to get the range S-S lock, the next index also gets the range S-S lock.

I carefully verified this rule today. In addition, I checked the records on this issue in the SQL Server 2000-2008 internals book. If it is not a unique index, there is no such fixed selection rule. The preceding rules only appear in certain situations. Other rules are fine.

Conditions in the WHERE clause cannot hit any records

Rule 3: If the WHERE clause uses the "Equal" condition, no matter whether the index is a unique index or not, if it cannot hit any record, except for the non-existent reCord specified by the WHERE clause as part of the range S-S, the "Next" index value of the record will also get the range S-S lock.

For example

Select [data] from [mytable] Where [index_column] = 6

Then the next index record is the index corresponding to 15, so this index will get the range S-S lock.

 

For example, we execute

Select [data] from [mytable] Where [index_column] = 31

Then the next index record should be "infinitely far" corresponding to the index, then this index will get the range S-S lock.

 

Rule 4: If the WHERE clause uses the "range" condition, no matter whether the index is a unique index or not, if it cannot hit any record, except for the nonexistent range specified by the WHERE clause as part of the range S-S, the "Next" index value for the range also gets the range S-S lock.

For example

Select [data] from [mytable] Where [index_column]> = 6 and [index_column] <= 10

I can't write it anymore. Please use your brains. Here is the result:

 

Let's take another example.

Select [data] from [mytable] Where [index_column]> 30 and [index_column] <40

The result is:

 

Okay, this article is finally done. Next we arrived at the range S-U and range I-N this will be deadlocked, there is a good play to see.