Introduction to deadlocks in SQL Server

Original: Introduction to deadlock in SQL Server

Brief introduction

What is a deadlock?

I think the deadlock is due to the fact that two objects are requesting another resource with one resource, and the other is exactly what these two objects are holding, causing the two objects to fail and the resources held cannot be freed.

What is blocking again?

Blocking is a queued wait phenomenon caused by insufficient resources. For example, two processes at a time to update a table.

Here we can use blocking as a necessary condition for deadlocks. Now let's take a look at the deadlock and the blockage and see what I've been through recently.

Deadlock in SQL Server

corresponds to sql Server , where in two or more tasks, if each task locks out resources that other tasks are trying to lock, it causes these tasks to block permanently, resulting in deadlocks;

These resources may be: Single row(RID, the single row in the heap), keys in the index(KEY, Row lock)Page(PAG，8KB), District structure(EXT, a continuous8Page), Heap, orBTree(HOBT)Table(TAB, including data and indexes)File(File, the database file), application-specific resources(APP), meta-data(METADATA), Allocation unit(Allocation_unit), the entire database (db).

Let me briefly give you an example of how the deadlock works:

, follow the steps:

1. Begin Tran
Update Test1 Set Aaa=1

2.

BEGIN Tran

Update Test2 Set Aaa=1

Update Test1 Set bbb=2

3. Perform the update test2 set bbb=2 in Figure 1 again

The data is not inserted and remains in the running state after execution is complete

At this point we query the deadlock process and statement through the statement. The following results are obtained:

It is easy to find the statement that the deadlock occurred, or you can use theSQL Server ProfilerAnalysis of Deadlocks:WillDeadlock graphThe event class is added to the trace. This event class uses a deadlock involving the processes and objects of theXmlData in the Fill trace TextData sql Server   xml  SQL Server Management Studio

Now let's talk about how to handle deadlocks.

1. Temporary solution, kill the deadlock process first, only temporarily solve the problem.

2.SQL Server automatically selects a SQL as a deadlock victim : When a deadlock occurs, the lock monitor thread performs a deadlock check, and the database engine chooses to run a session of the transaction with the least rollback cost as a deadlock victim , returns a 1205 error, rolls back the transaction for the deadlock victim, and frees all locks held by the transaction so that the transactions of other threads can request resources and continue to run.

Server: Msg 1205, Level 13, State 50, Line 1 transaction (Process ID xx) and another process has been deadlocked on the lock resource, and the transaction has been selected as a deadlock victim. Please rerun the transaction.

3. Use Set Lock_timeout timeout_period (in milliseconds) to set the request timeout.

4. In SQL Server and two aspects of the program can do code correction, here is not described in detail, mainly through the discovery of deadlock waiting for a period of time after the way to try to resolve.

Preventing and avoiding deadlocks

1. Minimize the time of transaction execution.

2. Reduce the isolation level within a reasonable range.

3. Avoid looping the processing of the same table as much as possible within the same transaction.

4. Less user interaction within the same transaction, that is, the competition for locks.

5. Try to ensure that the order of the logical processing, such as the processing of the table is in one order.

6. For tables that require a variety of logical processing, you can reduce the competition for locks by increasing the index.

7. Minimize the inclusion of nonclustered indexes and reduce the occurrence of foreign key deadlocks.

8. Use the same object as much as possible with select at update.

9. For low-real-time requirements can use with (NOLOCK) to implement the query of the table, but may be poor reading dirty.

Summarize

This article simply introduces the cause of deadlock, how to solve and prevent. Of course, everything is a double-edged sword, but also we should be based on the actual situation to reduce the occurrence of deadlock and blockage; for the different isolation lock problems can take a look at my previous article about the lock introduction. Hope to prevent and solve the deadlock has certain help.

Introduction to deadlocks in SQL Server