SQL Server transactions and Locks (ii)-range s-s lock

In this essay, our main focus is on Key-range Lock. Key-range Lock has s-s, S-u, I-n, x-x several cases. One by one, we strive to understand. Unfortunately, this may be lengthy, so deadlock analysis has to be postponed in turn.

Get rule for Range s-s lock

MSDN has a partial description of the rules for range locks, but in brief, here we will break down the various scenarios and clarify the rules that are covered or not covered on MSDN that apply to SQL Server 2000/2005/2008/2008 R2. For a 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 based on the WHERE clause, which is very important, otherwise we will not get the Range lock, and we will not be able to reach the requirements of SERIALIZABLE; In addition, for the sake of discussion, the following SQL all omit SET TRANSACTION isolation LEVEL SERIALIZABLE statement.

We assume that the following tables are available:

1234567891011121314

CREATETABLE[dbo].[MyTable](    [id] [int] IDENTITY(1,1) NOTNULL,    [index_column] [int] NOTNULL,    [data] [int] NOTNULL,    CONSTRAINT[PK_MyTable] PRIMARYKEYNONCLUSTERED     (        [id] ASC    )WITH(PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON[PRIMARY]    ) ON [PRIMARY]CREATEUNIQUECLUSTERED INDEX[IX_MyTable] ON[dbo].[MyTable] (    [index_column] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON[PRIMARY]

And suppose we have the following data:

12345678910

INSERTINTO[MyTable] ([index_column],[data]) VALUES(1, 1)INSERTINTO[MyTable] ([index_column],[data]) VALUES(2, 2)INSERTINTO[MyTable] ([index_column],[data]) VALUES(3, 3)INSERTINTO[MyTable] ([index_column],[data]) VALUES(4, 4)INSERTINTO[MyTable] ([index_column],[data]) VALUES (5, 5)INSERTINTO[MyTable] ([index_column],[data]) VALUES(15, 6)INSERTINTO[MyTable] ([index_column],[data]) VALUES(16, 7)INSERTINTO[MyTable] ([index_column],[data]) VALUES(18, 8)INSERTINTO[MyTable] ([index_column],[data]) VALUES(25, 9)INSERTINTO[MyTable] ([index_column],[data]) VALUES(30, 10)

So this table should look like this (I'm also going to dump the hash value of index and the data page where the row is, so 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

case where the condition in the WHERE clause hits an existing record

Rule one: If the WHERE clause uses an "equality" condition, such as "WHERE [index_column]=6", and the index is a unique index, the index does not get a key-range s-s lock, only the normal S lock is obtained on the key;

Suppose we execute

SELECT [Data] from [MyTable] WHERE [index_column]=1

So we use the sp_lock to get the lock situation:

You can find that the S lock is obtained on the first index, but not the Range s-s lock.

Rule two: If the WHERE clause uses a "scope" condition, such as ">, <, between, in", and so on. Regardless of whether the index is unique, where clause provisions will become the range S-s lock action, in addition, under the index collation, the scope of the "next" index entry will also get a range s-s lock.

We must first explain what the "next " is all about, and the "next" index entry has two things:

First: If under the index collation, in the direction of the data arrangement can find an existing, or "residual" index entry (has been committed to delete, the database is no longer visible, but has not been removed from the B-Tree data page), then this index entry is the "Next" index entry;

Second: If, under the index collation, no remaining index entries are found in the direction of the data arrangement, the index entry of infinity (Resource hash 0xFFFFFFFF) is the "next" index entry.

We are bound by rule two, for example, we perform

SELECT [Data] from [MyTable] WHERE [Index_column]>=1 and [index_column]<=4

Then the index in Index_column with a value of 1, 2, 3, 4 obtains a range s-s lock, in addition to the next index value after 4, which is 5, the corresponding index obtains a range s-s lock. This coincides with the results of our experiment.

Let's look at one more, for example we do:

SELECT [Data] from [MyTable] WHERE [index_column]>=20 and [index_column]<=40

Then the index of Index_column 25, 30 will get the range s-s lock, in addition, the next index value after 30, that is "infinity" will get the range s-s lock, see the actual dump lock usage:

We end up with a slightly more complicated 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 question here is that our "scope" refers to a closed range of one by one, to apply the rules for analysis , we now have 3 blocks of closed range, respectively [2,4], [10,16], [30,40]. We come, for [2,4], in this range 2,3,4,5 get range s-s lock;

For the [10,16] range, 15,16,18 obtains a range s-s lock, and for the [30,40] range, 30, infinitely far obtains a range s-s lock, altogether 9.

Rule one adds: If the WHERE clause uses an "equality" condition, but the index is not a unique index, then the "Next" index will also get a range s-s lock in addition to the where hit index gets the range s-s lock.

I have done a thorough validation of this rule today. Read more about this issue in the books of SQL Server 2000-2008 internals. Without a unique index, there is no such fixed selection rule. The above rules only appear in some specific cases. There are no problems with other rules.

A condition in the WHERE clause cannot hit any record

Rule three: If the WHERE clause uses an "equality" condition, regardless of whether the index is a unique index, if no record can be hit, the "Next" index value of the record will also get a range s-s lock, except for the nonexistent record specified by the WHERE clause as part of the range s-s.

For example, we perform

SELECT [Data] from [MyTable] WHERE [index_column]=6

The next index is recorded as a 15 index, so the index will get a Range s-s lock.

Also, for example, we perform

SELECT [Data] from [MyTable] WHERE [index_column]=31

Then the next index record should be an "infinity" corresponding to the index, then this index will get a Range s-s lock.

Rule four: If the scope condition is used in the WHERE clause, regardless of whether the index is a unique index, if no record can be hit, the "Next" index value of the range will also be given a range s-s lock, except for the non-existent range specified by the WHERE clause as part of range s-s.

For example, we perform

SELECT [Data] from [MyTable] WHERE [index_column]>=6 and [index_column]<=10

I really can't write, please use your brains, here directly to the result:

Let's take another example.

SELECT [Data] from [MyTable] WHERE [index_column]>30 and [index_column]<40

The result is:

Well, this one is finally done. Next we're in range s-u and range i-n It's going to be a dead lock.

SQL Server transactions and Locks (ii)-range s-s lock