Introduction to deadlocks in SQL Server

Introduction to deadlocks in SQL Server

Introduction

What is a deadlock?

In my opinion, the deadlock is because two objects apply for another resource when they have one resource, and the other resource is exactly what the two objects are holding, as a result, the two objects cannot complete the operation and their resources cannot be released.

What is blocking?

Blocking is caused by insufficient resources. For example, two processes update a table at the same time.

Here we can use blocking as a necessary condition for deadlock. Next, let's take a look at the deadlocks and blocking. Next, let's take a look at a problem that I encountered recently and how to solve it.

Deadlock in SQL Server

Corresponding to SQL Server, when two or more tasks, if each task locks the resources that other tasks attempt to lock, these tasks will be permanently blocked, resulting in a deadlock;

These resources may be: single row (RID, single row in the heap), KEY (KEY, row lock) in the index, page (PAG, 8 KB), partition structure (EXT, 8 consecutive pages), heap or B-tree (HOBT), table (TAB, including data and indexes), File (File, database File), dedicated application resources (APP) METADATA (METADATA), allocation unit (Allocation_Unit), and the entire database (DB ).

Here is a simple example to illustrate the principle of a deadlock:

 

, Follow these steps:

1. begin tran
Update test1 set aaa = 1

2.

Begin tran

Update test2 set aaa = 1

Update test1 set bbb = 2

3. Execute Update test2 set bbb = 2 in Figure 1 again

After the execution is complete, no data is inserted and the data remains in the running status.

 

In this case, we use statements to query the deadlocked processes and statements. The following result is displayed:

 

It is easy to find a Deadlock statement. You can also use SQL Server Profiler to analyze the Deadlock: add the Deadlock graph event class to the trail. This event class uses the process involved in the deadlock and the XML data of the object to fill the TextData column in the trace. The SQL Server event probe can extract the XML file to the deadlock XML file. You can view the file later in SQL Server Management Studio.

Next, let's talk about how to deal with deadlocks.

1. temporary solution: Kill the deadlock process first, but solve the problem temporarily.

2. SQL Server automatically selects an SQL statement as the deadlock victim: When a deadlock occurs, the lock Monitor thread executes the deadlock check, and the database engine selects the Session of the transaction with the smallest rollback pin as the deadlock victim, return Error 1205, roll back the transaction of the deadlock victim and release all the locks held by the transaction, so that transactions in other threads can request resources and continue to run.

Server: Message 1205, Level 13, status 50, Row 1 transaction (process ID xx) and another process have been deadlocked on the lock resource, and the transaction has been selected as a deadlock victim. Run the transaction again.

3. SET LOCK_TIMEOUT timeout_period (unit: milliseconds) to SET request timeout.

4. You can modify the code in both SQLServer and program. This is not described in detail here. It is mainly solved by finding a deadlock and waiting for a while before trying again.

Prevent and avoid deadlocks

1. Minimize the transaction execution time.

2. Reduce the isolation level within a reasonable range.

3. Avoid loop processing for the same table in the same transaction.

4. Less user interaction in the same transaction, that is, lock competition.

5. try to ensure that the logic processing sequence is as follows: for example, the table processing is performed in one order.

6. For tables that require various logic processing, you can add indexes to reduce lock competition.

7. Minimize include columns of non-clustered indexes and reduce the occurrence of foreign key deadlocks.

8. Try to use select for the same object before update.

9. You can use with (nolock) to query tables with low real-time requirements, but dirty reads may occur.

Summary

This article briefly introduces the cause of the deadlock and how to solve and prevent it. Of course, everything is a double-edged sword. We also need to reasonably reduce the deadlock and congestion based on the actual situation; for the problems caused by the locks of different isolation sectors, let's take a look at my previous introduction to locks. It is expected to help prevent and solve deadlocks.

This article permanently updates the link address: