Transactions and locks in SQL Server (iii)-range s-u,x-x and deadlocks

Forgot a detail in the previous article. What does Range t-k really mean? Range T-k Lock represents a class of lock patterns (avoiding phantom reads) that are used in the SERIALIZABLE isolation level to protect data within the scope from being affected by concurrent transactions. It is composed of two parts:

The first part represents the lock of an index range, in which all indexes are locked with a T lock;

The second part is the key that has been hit in this range, and these keys will be locked with a K lock.

Merging together we say that within this range, the index range and the specific row lock mode are range t-k.

Let's take an example:

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

The usage of the lock is:

In fact, the above statement produces a lock has two parts, the first is a range S lock, the range is 20-40 index range, the second is the S lock used on the key, in the figure can see three keys are hit, respectively, "Infinity", "25" corresponding index and "30" corresponding index. The Mode is range S-s, whose Type is KEY, that is, their range is locked to range S,key lock is S lock.

Locks involved in update and insert operations

The locks involved are mainly two kinds, one is range s-u lock, the other is range x-x lock.

Range S-u, this selected index range obtains the S lock and the hit Key is locked with a U lock for future conversion to an X lock. In the update, it becomes the X lock completely, and the lock mode within this range becomes the range x-x. Because the updated data columns are different (possibly an indexed column, possibly not), the indexes used are different (clustered, nonclustered, unique, etc.), so the situation is not as easy to rule as the Range s-s lock. In general, there are several situations that are consistent, and there is no longer an experiment here (it is highly recommended to read the chapter on locks in SQL Server internals, which tells you clearly):

First, in the case of an equality judgment (for example, "="), and the index is a unique index. If the index hits, there will be no range t-k lock lock record range, and the corresponding record directly obtains U lock or X lock;

Second, in equal judgment, the Range t-k lock locks the "next" record, regardless of whether the index is a unique index or not, if the index does not have a hit record. (See the previous article for an explanation of "next");

Thirdly, in the scope condition (>, <, between), regardless of whether the index is unique or not, if the index is hit, not only will the range get a range t-k lock, but the range's "next" record will also get a range t-k lock.

Why Serializable isolation level is more prone to deadlock

As we can see from the first diagram, the SERIALIZABLE level guarantees the strictest data consistency, but these guards can evolve into deadlocks as soon as they change slightly. In fact, the higher the data consistency in the various isolation levels, the more prone to deadlocks, and the lower the data consistency, the smaller the probability of deadlocks.

In these isolation levels, SERIALIZABLE is the easiest to deadlock, thanks to the range t-k lock, which allows the lock to be scoped not only to existing data, but also to future data, not just to some existing data pages, but to a broad range.

One of the most terrifying problems is the lock-up of the "next" data. This is very easy to cause a wide range of deadlocks. Let's take the first example to illustrate:

1234567891011121314

SELECT@findCount=COUNT(id) FROMMyTableWHERE[fk_related_id][email protected]IF (@findCount > 0)BEGIN    ROLLBACKTRANSACTION    RETURNERROR_CODEENDINSERTINTOMyTable ([fk_related_id],…)VALUES(@Argument,…)COMMITTRANSACTIONRETURNSUCCESS_CODE

In this example, the column fk_related_id of Table MyTable is a unique index (nonclustered) and the transaction isolation level is SERIALIZABLE. Different stored procedure executions pass through different @Argument, and on the face of it, there is no problem, but because of the "next" data lock, there is about a 80% failure situation at a slightly higher level of concurrency, all of which originate from deadlocks. We picked out one of them:

We tried to make a stored procedure call at 15 pressure per second within the range of the @Argument belonging to [1, 1000]. In this process, there is a record of @Argument 115 first successfully inserted into the!

Id	fk_related_id	Data
1	115	...

Next there is an opportunity for a @Argument record of 74, and we assume that its Session Id is a. It executes the SELECT statement:

Id	fk_related_id	Data
1	(a) (a) (a range s-s Lock is obtained)	...

Next there is an opportunity for a @Argument record of 4, and we assume that it has a Session Id of B. It executes the SELECT statement:

Id	fk_related_id	Data
	(A, B obtained range s-s Lock)	...

Next, session A executes to the INSERT statement, and the range S-s lock attempts a conversion test (range i-n lock), but this obviously does not work because session B also obtains the Range s-s lock, so the session A was caught in the waiting;

and Session B also executes to the INSERT statement, the same, it is also caught in the wait, so that session a waits for session B to discard the Range lock, session B waits for session A to discard the lock, this is a deadlock.

The worse thing is that any @Argument less than 115 of records, he will try to make the next record to obtain a new Range s-s lock, so as to enter the infinite waiting, at least, 1-115 record deadlock, and finally 114 need to give up, 1 success. This is why the SERIALIZABLE isolation level will not only deadlock, but also, at some point, a large area deadlock.

In summary: under the SERIALIZABLE isolation level, as long as there is a condition similar to the same index as conditional read and write, the probability of a large concurrent release of a deadlock is very high, and if it happens that the records index is in a very close position according to the collation, a large area deadlock is likely to occur.

So how to solve this problem, uh, reducing the isolation level is certainly a method, for example, if you can accept phantom reading, then repeatable Read is a good choice. But I suddenly saw a way to use SELECT with UPDLOCK in a blog post. In fact, this kind of thing makes the deadlock easier.

For example, a stored procedure select B, then select A, and the other stored procedure select a, then select B, because the order is different, the exclusive lock is only Read the case of a deadlock can occur.

So why is repeatable READ going to be much better? Because repeatable READ tightly locks an existing record, it does not use a Range lock. We still take the stored procedure as an example, so that only two locked rows of data on the same page (because the page-level lock is used by default), or close enough to be able to deadlock, and this deadlock is limited to records on this data page without affecting other records, so the probability of deadlock is greatly reduced.

In our actual tests, less than 0.1% of the deadlock failure rate is achieved when the concurrency is increased to 100 under the same test conditions. Of course we paid the price of allowing the Phantom to read.

(go) transactions and locks in SQL Server (iii)-range s-u,x-x and deadlocks