Shulou(Shulou.com)06/01 Report--

What is a SIX lock?

The official document lock mode says:

Intentional exclusive sharing (SIX): protects shared locks that are requested or acquired for some (but not all) lower-level resources in the hierarchy and those that are requested or acquired for some (but not all) lower-level resources. Top-level resources allow the use of concurrent IS locks. For example, acquiring the SIX lock on the table also acquires the intention exclusive lock on the page being modified and the exclusive lock on the modified row. Although each resource can have only one SIX lock at a time to prevent other transactions from updating the resource, other transactions can read lower-level resources in the hierarchy by acquiring table-level IS locks.

The official statement is rather obscure, so I try to explain what SIX is in an easy-to-understand way.

There are several concepts about locks: granularity, hierarchy, and compatibility between locks. Locks are used to lock resources, and resources include many kinds, and these different resources represent different granularities. There are hierarchies between different resources, such as tables, partitions, pages, rows, keys, and so on. There are many types of locks, including shared locks (S), update locks (U), exclusive locks (X), and schema locks (Sch). Among different types of locks, some are mutually exclusive and some are compatible. For example, shared locks are compatible with other types of locks, exclusive locks are mutually exclusive with other lock types.

When SQL Server allocates locks, it allocates locks along the hierarchy, starting at the table level, and then to the rows and keys at the lowest level. When assigning a lock, the parent's resource is assigned an intention lock (I) to indicate that a resource under that resource has been locked. Intention locks can also be classified into IS,IX,IU and other types. For example, updating a row in a table requires the allocation of an X lock on the row, an intention lock IX on the data page to which the row belongs, and an IX lock on the table to which the data page belongs.

If the transaction of a session currently holds the S lock of a table or data page, and it then modifies a row in the table. In this case, the transaction needs to acquire the X lock on the row and the IX lock on the table or data page, but SQL Server only allows a session to acquire a lock on a resource. That is, there is no way to assign IX to a table or page-level S lock after it has been acquired. In order to solve this problem, there is a combination of the two: S+IX=SIX. By the same token, if you hold IX first and then get S, you will also get SIX.

In addition, there are similar lock types UIX (U+IX) and SIU (S+IU) in SQL Server, and the mechanism is the same. These three types of locks are called conversion locks.

What is a lock partition?

First of all, don't confuse Lock Partitioning with Partition Lock.

Official document lock partition:

For large computer systems, locks placed on frequently referenced objects can become a performance bottleneck because acquiring and releasing locks creates contention for internal lock resources. Lock partitioning improves lock performance by splitting a single lock resource into multiple lock resources. This feature is only available for systems with 16 or more CPU, it is automatically enabled and cannot be disabled. Only object locks can be partitioned.

The lock task accesses several shared resources, two of which are optimized by locking partitions:

Adjust lock (Spinlock). It controls access to lock resources, such as rows or tables.

Without lock partitioning, a tuning lock has to manage all lock requests for a single lock resource. On systems with a lot of activity, resource contention occurs when the lock request waits for the release of the tuning lock. In this case, acquiring locks can become a bottleneck and may have a negative impact on performance.

In order to reduce the contention for a single lock resource, the lock partition splits the single lock resource into multiple lock resources in order to distribute the load to multiple tuning locks.

Memory. It is used to store lock resource structures.

After the adjustment lock is acquired, the lock structure is stored in memory, and then it can be accessed and possibly modified. Distributing lock access across multiple resources helps eliminate the need to transfer memory blocks between CPU, which helps improve performance.

When acquiring the lock of a partitioned resource:

Only NL, SCH-S, IS, IU, and IX lock modes for a single partition can be obtained.

For all partitions that start with partition ID 0 and are arranged in partition ID order, shared locks (S), exclusive locks (X), and other locks that are not in NL, SCH-S, IS, IU, and IX modes must be acquired. These locks for partitioned resources will take up more memory than locks for unpartitioned resources in the same mode, because each partition is a valid separate lock. The increase in memory is determined by the number of partitions. The SQL Server lock counter in the Windows performance Monitor displays memory information used by partitioned and unpartitioned locks.

When you start a transaction, it is assigned to a partition. For this transaction, all lock requests that can be partitioned use the partition assigned to the transaction. According to this method, the access of different transactions to the lock resources of the same object is distributed to different partitions.

Take a look at SIX and lock partitions through an example:

Create table T2 (id int identity, col1 int, col2 int) go insert into T2 values (floor (rand () * 100), floor (rand () * 100)) go 20set transaction isolation level serializable begin tran insert into T2 values (floor (rand () * 100), floor (rand () * 100)) select id from T2 where @ @ ROWCOUNT > 0 and id=SCOPE_IDENTITY () SELECT resource_type, request_mode, resource_description Resource_lock_partition FROM sys.dm_tran_locks WHERE resource_type 'database' and request_session_id=@@SPID rollback

This example has 24 CPU, so you can see the partition number up to 23 through resource_lock_partition. Because the SIX mode acquires all lock partitions, you can see that there is SIX on all partitions.

You can see from the figure that different lock resources can use different lock partitions in the same transaction.

Actual case analysis

When doing performance review recently, we found some deadlocks recorded in the Ring Buffer of some instances, one of which is as follows:

Session 113 holds the IX on the object and needs to apply for a SIX again. It indicates that it has to query the number after modifying the data.

Session 79 holds the SIX on the object and needs to apply for a SIX again. This is a little strange, you need to take a closer look at the deadlock information in xml format.

(@ 0 bigint,@1 varchar (20)) insert [dbo]. [CustomerVerify] ([CustomerId], [VerifyRegisterCode]) values (@ 0, @ 1) select [VerifyID] from [dbo]. [CustomerVerify] where @ @ ROWCOUNT > 0 and [VerifyID] = scope_identity () (@ 0 bigint,@1 varchar (20)) insert [dbo]. [CustomerVerify] ([CustomerId], [VerifyRegisterCode]) values (@ 0 @ 1) select [VerifyID] from [dbo]. [CustomerVerify] where @ @ ROWCOUNT > 0 and [VerifyID] = scope_identity ()

To sum up:

1. Both execute the same statement. Insert a piece of data, and then take out the self-incrementing ID of the data you just inserted. Heap table, no index.

(@ 0 bigint,@1 varchar (20)) insert [dbo]. [CustomerVerify] ([CustomerId], [VerifyRegisterCode]) values (@ 0, @ 1) select [VerifyID] from [dbo]. [CustomerVerify] where @ @ ROWCOUNT > 0 and [VerifyID] = scope_identity ()

2. SPID 79 holds IX on lock partition 10 and is waiting to allocate SIX on lock partition 0.

SPID 113holds SIX on lock partition 0 and is waiting for SIX on lock partition 10 to be assigned

3. The transaction isolation level of the session is serializable (isolationlevel= "serializable (4)").

Based on the above, you can understand how deadlocks occur:

113When inserting data, it holds the IX of a lock partition, assuming that the lock partition is N, and then it queries the data just inserted, so it is converted to SIX. SIX needs to assign all lock partitions, and it needs to start with lock 0. When assigned to partition 10, it was found that partition 10 was locked by an SIX-incompatible IX lock and was caught waiting.

79 is assigned an IX lock when inserting data, which is partitioned into partition 10, and then the IX needs to be converted to SIX when querying the data. So SIX is allocated from lock 0 partition, but partition 0 is already locked by SIX of 113, and SIX is not compatible with SIX, so it is caught in a wait.

How to solve

To sum up the causes of the previous deadlock, the problem becomes: how to avoid deadlock when inserting a heap table with self-incrementing ID concurrently and taking out the inserted self-increment ID?

This kind of deadlock is very rare. TF-1229 can disable locking partitions. Personally, for applications with high concurrency, instead of disabling lock partitions to avoid this rare situation, it is better to design a retry mechanism for applications.

Oddly enough, in the deadlock above, the transaction isolation level is serializable, while the database side is the default committed isolation level. The developer did not set the transaction isolation level for the connection session, but the isolation level for this session has changed. why?

My analysis is that Entity Framework is used in the program, and the default connection isolation level of EF before 6. 0 is serializable. Developers use it directly and don't notice the problem.

Reference:

What is the default transaction isolation level in Entity Framework when I issue "SaveChanges ()"?

Tips to avoid deadlocks in Entity Framework applications

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.