Analysis, processing and prevention of deadlock in SQL Server

1. Basic Principles

The so-called "deadlock", defined in the operating system, is that each process in a set of processes occupies a resource that will not be freed, but is in a permanent state of waiting for each other to request resources that are not freed by other processes.

Defining comparative abstractions can help you to understand deadlocks more intuitively:

There are several necessary conditions to meet the deadlock: a) mutual exclusion: Process exclusive resources, resources are not shared;

b) Request and maintain: the process in which resources have been obtained may reapply for new resources; c) No deprivation: the allocated resources cannot be forcibly deprived by other processes;

D) Loop wait: Several processes constitute loops, each waiting for the resources that are being occupied;

corresponding to SQL Server, in 2 or more tasks (insert, UPDATE, DELETE, select, ALTER TABLE or TRAN transaction, and so on), if each task locks the resources that other tasks want to lock, it will cause these tasks to be permanently blocked. Thus a deadlock occurs. These resources might be single-line data (RID, rows in the heap), keys in the index (key, row locks), pages (PAGE,8KB), extents (extent,8 contiguous pages), heap or B-trees, tables (table, data and indexes), files (file, database files), The entire database.

The lack of resources in the system or improper resource allocation policies can lead to deadlocks due to resource contention between processes. But more likely there is a problem with the programmer's program. There are several ways to "lock", such as intent lock, shared lock, exclusive lock, and so on. Locks also have a variety of granularity, such as row locks, table locks.

Knowing the cause of the deadlock, it is possible to avoid and prevent deadlocks. As long as 1 of the 4 prerequisites above are not satisfied, no deadlock occurs. Therefore, in the system design, implementation phase can be in the resource allocation and occupancy, the order of resource access to take the necessary measures.

2. An example

Face the deadlock, take a look at an example: 1, create a new query window, and take advantage of the atomic nature of the transaction and the exclusive lock feature of the UPDATE statement to lock the records in 2 tables, 2, create a new query window again, 2 very simple SQL statements for a long time still do not complete.

3. Testing and troubleshooting

3.1 Look dead lock via Profile tool

Profile is a trace analysis tool that comes with SQL Server, which enables you to see information more intuitively by turning on profile to capture deadlock information.

3.2 Look dead lock via system stored procedure

sp_who and Sp_lock are the 2 system stored procedures for SQL Server that you can use to query for lock conditions in the database. sp_who provides information about the current users, sessions, and processes in the DB instance, such as the session that we see SPID=56 (UPDATE statement) is blocked by the spid=54 session.

Sp_lock provides information about the lock, such as. We can know which session has locked the resource through the SPID, and we can know what the locked resource is through ObjID.

Execute the following SQL script to get the database to which the locked resource and resource belong:

SELECT object_name (421666738) as Lockedresource,db_name (one) as DBName;

--------------------------------------------------------------

Lockedresource DBName

--------------------------------------------------------------

Tb_te_sizeinformation Jybgdb

Execute the following script to get the SQL script that is executing the session of the lock resource:

DBCC InputBuffer (54);

--------------------------------------------------------------

EventType Parameters EventInfo

--------------------------------------------------------------

Language Event 0--according to the atomic nature of the transaction to achieve a necessary condition

--Request and wait BEGIN TRAN--update statement to add an exclusive lock on the data line

--incompatible with all other locks--the realization of a necessary condition: mutual exclusion

Update tb_te_brandinformation set iscompensate=0 update tb_te_sizeinformation set [description]= '

4. Handling Methods

4.1 SQL Server Automatic Processing

"Inaction". When the database generates a deadlock, SQL Server captures the deadlock information through a thing called "lock Monitor" and automatically selects a SQL as the victim of the lock according to certain rules and returns the following error

Information:

Server: Msg 1205, Level 13, State 50, line 1

The transaction (Process ID xx) and another process have been deadlocked on the lock resource, and the transaction has been selected as the deadlock victim. Please rerun the transaction.

If you are not familiar with the database, it is recommended that you ask other experienced people for help, before this is not easy to modify the database.

4.2 Kill session

Information related to deadlocks can be obtained through the system stored procedures mentioned in 3.2. You can query which SPID is causing the deadlock, and use the kill SPID method to kill it. However, this can only be a temporary solution, we can not encounter a deadlock in the user's production environment to troubleshoot the deadlock, kill sp. Similarly, it is not possible to restart the SQL Server service or even restart the database server when a deadlock is encountered.

SQL script:

Kill 54; --Here 54 is the SPID value obtained after analysis

4.3 Set lock Request Timeout

By default, the database does not have a lock time-out period. This means that when a session requests a new resource, if the resource is already locked by another process, the session will remain in the waiting state. There is no doubt that this is problematic. We can use the SQL command to set the lock request timeout. You can also access the global variable @ @LOCK_TIMEOUT to see this value.

SET lock_timeout 20000; --Unit is milliseconds

When the request lock exceeds the set time, SQL Server returns an error. Our program can handle the response based on the error returned, avoiding long-time user waits.

Server: Msg 1222, Level 16, State 50, line 1 has exceeded the lock request time-out period.

Of course, it is not appropriate and irresponsible to use this approach to handle all lock requests. In most cases it is the design of our program, the problem of implementation that caused the deadlock. In the process of treatment, we have to be a palliative, but also to cure.

4.4 Modifying a program

At the end of 3.2, we found the SQL command that locked the resource through the system stored procedure and several commands. Taking this ll project as an example, we found that a statistical report (SELECT) on the Web management system was locked for a long time during the execution of the production information table, resulting in a long wait for the field machine system to insert a new production record (insert). So the problem recurs after the field project team restarts the SQL Server service or restarts the database server for 2 hours at a time.

This time if the kill off this statistic report request way processing, the result and restart SQL Server service, restart the database server no difference, 2 hours is still the problem. If the set lock request time-out process, then the statistical report every execution will not get the desired results, and each execution will also lock a certain amount of time to cause the scene host computer waiting.

This time our measures are: 1) Temporarily disable this report on the Web Management System, 2) Restart the SQL Server service, 3) Optimize the report SQL statement; 4) enable the report feature. There was no recurrence of such problems for some time afterwards.

By optimizing the performance of this report, the problem is almost solved. However, after hindsight, it is not the developer's negligence or level that found the performance problem of the report. The problem is fundamentally problematic in the design of this production information table. In a table with a data volume of 1000w, we use char (10) to hold the date value, although there is no problem with insert, UPDATE, delete, but a performance problem occurs when a select is executed and the Date value field is a filter condition. After testing, the data type of this field is changed to DateTime when the execution time is less than 10% after performance optimization.

Therefore, not only in the development phase, early in the design phase has been a performance hazard.

4.5 Upgrading hardware

Do not repeat.

5. How to Prevent

The first thing to understand is that deadlocks are unavoidable in multiple concurrent environments, only through reasonable database design,

Measures such as good indexing, appropriate query statements, and isolation levels minimize deadlocks.

The 4 prerequisites for deadlocks are listed at the beginning, so you can avoid deadlocks if you try to destroy any 1 or more of these conditions. The following methods help minimize deadlocks:

A) Access the object in the same order;

b) Avoid user interaction in the transaction, that is, do not include user interaction during the execution of the transaction; c) keep the transaction short and in a batch; d) SELECT statement Plus with (NOLOCK) hint;

SELECT * from TABLE1 with (NOLOCK);

SELECT * from TABLE2 with (NOLOCK);

This notation does not lock the queried resource in execution, allowing 2 SQL to access the same resource concurrently. However, the add with (NOLOCK) hint may cause dirty reads!!!

e) Use a lower isolation level;

Do not need to know, do not repeat. f) using a binding connection;

Handler end deadlock, non-database side, not repeat.

6. Concluding remarks

It is unavoidable to encounter deadlock in the course of project implementation. Through the previous introduction, I hope you will be able to

There is a relatively simple understanding, in the face of abnormal situation is not helpless. If the above content has any technical problems or views, welcome to me directly to the study of communication, but also welcome all the problems encountered in other databases can be discussed with me, and jointly improve.

Analysis, processing and prevention of deadlock in SQL Server