SQL Server lock partitioning feature causes deadlock resolution

Original: SQL Server lock partitioning feature raises deadlock resolution

The lock partitioning technique allows SQL Server to better handle concurrency scenarios, but it can also have a negative impact, as described in this example, to analyze the deadlock situation caused by lock partitioning.

Previous Time Park friends @JentleWang in my blog lock partition elevation concurrency, as well as lock waiting instance asked some of the features of the lock partition cause deadlock problems, such deadlocks are not common, Let's take a closer look at this. Friends who do not understand the lock partitioning technology please see my lock section for the example.

Code (note the order of execution when executing the test script, description)

Step 1 Create test data

 UsetempdbGoCreate Tabletestdlk (IDint Identity(1,1)Primary Key, str1Char( the))GoInsert  intoTestdlk (STR1)Select 'AAA'Insert  intoTestdlk (STR1)Select 'BBB'Insert  intoTestdlk (STR1)Select 'CCC'Insert  intoTestdlk (STR1)Select 'DDD'

Step 2 Open session 1 Execution statement

--beginTranupdateset str1='ttt  'where id=1---session ID Example--  ---manual after session 3 rollback session 1

Step 3 Open Session 2 execution statement

--Session 2BEGIN TRANUpdateTestdlkSetStr1='ABC' whereId=2 ---update The content of id=2SELECT *  fromTestdlk with(Tablockx)------try to get X lock on the object Testdlkrollback Tran---session ID Example

Step 4 Open session 3 execution data

--Session 3BEGIN TRANUpdateTestdlkSetStr1='ABC' whereId=3-------Update the content of id=3SELECT *  fromTestdlk with(Tablockx)---try to get X lock on the object Testdlkrollback Tran---session ID Example

Step 5 Create a script to execute the statement in the session

SelectRequest_session_id,resource_lock_partition,resource_type,object_name(resource_associated_entity_id) as object_name, Request_mode,request_status fromSys.dm_tran_lockswhereresource_database_id=2  andResource_type='OBJECT'Selectsession_id,blocking_session_id,wait_type,resource_description fromSys.dm_os_waiting_taskswhereblocking_session_id is  not NULL

Step 6 session 1 in Rollback

Rollback 1 -- When session 1 rollback then session 3 deadlock

When session 1 is rolled back, Session2 session 3 causes a deadlock, session 3 is sacrificed.

Cause analysis.

Through step four we can get the corresponding session lock, and the related wait situation 1-1

Figure 1-1

You can see session 1 (Figure 55) because it just updates the id=1 column, it will lock on the key (not listed in the figure, interested friends can see for themselves), and in the object testdlk a lock partition in the figure for the lock partition 1 plus intent exclusive lock.

Session 2 (Figure 58) because the id=2 column is updated, an exclusive lock is added to the corresponding key and an intent exclusive lock (IX) is added to a lock partition, at the same time as the query has a table-level Tablockx Hint under this transaction, at which point 58 attempts an exclusive lock (x Lock) at the table level. Since the X lock needs to be obtained in all lock partitions, at this point 58 obtains an X lock in lock partition 0, but because the lock partition 1 has 55 acquired the intent exclusive lock (IX), 58 in the lock partition 1 in the attempt to obtain X lock state is not convert, is blocked by 55.

Session 3 (Fig. 59) because the Id=3 column is updated, an exclusive lock is added to the corresponding key, and an intent exclusive lock (IX) is added to a lock partition at the same time because the query under this transaction also has the table-level Tablockx Hint, At this point 59 will also attempt to obtain an X lock on all partitions of the table. Since 58 has obtained the X lock of the lock partition 0, when 59 tries to acquire the X lock of the lock partition 0, it is blocked by 58 and the status is wait.

The problem is that when 55 rolls back, the lock on it will also be released. At this point 58,59 all attempts to obtain all partition x locks at the table level, while holding the IX lock in the lock partition, the deadlock is unavoidable.

As shown in deadlock View 1-2.

Figure 1-2

Problem solving

After analysis, it can be seen that because the characteristics of the lock partition cause IX to be mutually exclusive with different spid, if the lock partition feature can be disabled, there is no IX on the individual partition. Here is a boot tag trace flag 1229, You can disable the lock partitioning feature. We can add a boot tag through Configuration Manager or a response parameter when command starts. It should be noted that when using Configuration Manager, we should configure "-t1229" at the end of the startup parameter, and if you use command, this is t1229 ( Case sensitive)

Here I use win command to enable

Code

Net Start mssqlserver/t1229

After restarting the above instance, the deadlock does not appear.

Note: This instance is only a description of the deadlock situation caused by the lock partition, which is rare in actual production, unless there is such a situation and there is no better way to circumvent it, we recommend this feature as a default. This is helpful for promoting concurrency.

About the lock partitioning feature.

Microsoft's online documentation shows that this feature is turned on by default only if the logical number of CPUs is equal to 16 o'clock, and authorization is charged to the CPU. The problem comes when the CPU is less than 16 if I want to take advantage of this feature, can I? This will give you a boot trace flag, trace flag. 1228. When there are two or more logical CPUs, the lock partitioning feature is initiated. However, we use it to understand whether our usage scenarios will benefit from this TF. Since this is a feature without official documentation, use should only be specific to your needs and be cautious.

Conclusion: The introduction of any feature in SQL Server or other database systems always adapts to most scenarios, but it also comes with the drawbacks of a particular scenario, the pros and cons of it, and the use of it, so that SQL Server adapts to the scenario and we can adapt to SQL Server.

SQL Server lock partitioning feature causes deadlock resolution