SQL & #160; analysis, processing and prevention of Server deadlocks, SQL Server

SQL Server deadlock analysis, processing and prevention, SQL Server

1. Basic Principles

The so-called "deadlock" is defined in the operating system: each process in a group of processes occupies resources that will not be released, however, a permanent waiting state is in which resources that are not released by other processes are requested from each other.

The definition is abstract to help you understand deadlocks more intuitively:

When a deadlock occurs, the following conditions must be met: a) Mutual Exclusion: Processes occupy resources exclusively and resources are not shared;

B) requests and maintenance: processes that have obtained resources can apply for new resources again; c) No deprivation: allocated resources cannot be forcibly deprived by other processes;

D) loop wait: several processes constitute a loop, waiting for resources in use;

Corresponding to SQL Server, in two or more tasks (insert, update, delete, select, alter table, or Tran transaction, etc ), if each task locks the resources that other tasks want to lock, these tasks will be permanently blocked, resulting in a deadlock. These resources may be: single row data (row in the RID and HEAP), KEY (KEY, row lock), Page (8 KB), partition (Extent, 8 consecutive pages), heap or B-tree, Table (Table, data and index), File (DataBase File), and whole DataBase ).

If the resources in the system are insufficient or the resource allocation policy is incorrect, a deadlock may occur due to resource contention between processes. But it is more likely that the programmer's program has problems. There are multiple methods to "Lock", such as forwarding locks, shared locks, and exclusive locks. The lock also has multiple granularities, such as row locks and table locks.

By understanding the cause of the deadlock, you can avoid and prevent the deadlock as much as possible. As long as one of the above four conditions is not met, no deadlock will occur. Therefore, necessary measures can be taken in terms of resource allocation and occupation and resource access sequence in the system design and implementation phases.

2. Example

Let's look at an example in the face of deadlocks: 1. Create a query window and lock the records in two tables using the atomic properties of the transaction and the exclusive lock feature of the update statement. 2 shows that, create a query window again. Two simple SQL statements are still not executed for a long time.

3. Detection and troubleshooting

3.1 check the deadlock using the Profile tool

Profile is a tracking and analysis tool provided by SQL Server. Enabling Profile to capture deadlock information allows you to see relevant information more intuitively.

3.2 check deadlocks through system stored procedures

Sp_who and sp_lock are two system stored procedures of SQL Server. They can be used to query the locks in the database. Sp_who provides information about the current user, session, and process in the database instance. For example, we can see that sessions with spid = 56 (UPDATE statement) are blocked by sessions with spid = 54.

Sp_lock provides information about the lock, such. We can use spid to know which session has locked the resource and ObjId to know what the locked resource is.

Run the following SQL script to obtain the database to which the locked resources and resources belong:

SELECT OBJECT_NAME (421666738) AS LockedResource, DB_NAME (11) AS DBName;

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

LockedResource DBName

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

Tb_TE_SizeInformation JYBGDB

Execute the following script to obtain the SQL script being executed by the lock resource session:

Dbcc inputbuffer (54 );

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

EventType Parameters EventInfo

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

Language Event 0 -- implement necessary conditions based on the atomicity of the transaction

-- Request and wait for begin tran -- update statement apply exclusive lock to the data row

-- Incompatible with all other locks -- Implement mutex among the necessary conditions

UPDATE tb_TE_BrandInformation SET IsCompensate = 0 UPDATE tb_TE_SizeInformation SET [Description] =''

4. Processing Method

4.1 automatic SQL Server Processing

"Governance without failure ". When a database has a deadlock, SQL Server uses a lock monitor to capture the deadlock information, and automatically selects an SQL statement as the lock victim according to certain rules, and returns the following error:

Information:

Server: Message 1205, Level 13, status 50, Row 1

The transaction (process ID xx) and the other process have been deadlocked on the lock resource, and the transaction has been selected as the deadlock victim. Run the transaction again.

If you are not familiar with the database, you are advised to ask for help from other experienced users. do not modify the database easily before that.

4.2 Kill session

You can obtain information related to deadlocks through the system stored procedures mentioned in section 3.2. You can query which spid causes the deadlock and Kill the spid. However, this can only be a temporary solution. It is impossible for us to troubleshoot deadlocks and Kill sp in the user's production environment when a deadlock occurs. Similarly, it is impossible to restart the SQL Server service or even the database Server when a deadlock occurs.

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 timeout period. That is to say, when a session applies for a new resource, if the resource has been locked by other processes, the session will remain in the waiting state. This is undoubtedly a problem. We can use SQL commands to set the lock request timeout. You can also access the global variable @ LOCK_TIMEOUT to view the value.

SET LOCK_TIMEOUT 20000; -- unit: millisecond

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

Server: Message 1222, level 16, status 50, Row 1 has exceeded the lock request timeout period.

Of course, using this method to handle all lock requests is inappropriate and irresponsible. In most cases, the design and implementation of our programs lead to deadlocks. In the process of processing, we must both cure the symptoms and the root cause.

4.4 modify the program

At the end of 3.2, we found the SQL command for locking the resource through the system stored procedures and several commands. Taking this LL project as an example, we found that the production information table of a statistical report (SELECT) on the WEB management system was locked For A Long Time during execution, as a result, on-site host computer systems want to INSERT new production records (INSERT) for a long time. Therefore, this problem still repeats two hours after the project team restarts the SQL Server service or the database Server.

At this time, if you use Kill to process the statistical report request, the result is no different from restarting the SQL Server service or restarting the database Server. The problem persists for two hours. If the lock request time-out method is set, the statistical report will not get the desired result every time it is executed, and each execution will lock a certain period of time, leading to the waiting of the on-site host computer.

Our solution is as follows: 1) temporarily disable the report function on the WEB management system; 2) restart the SQL Server service; 3) Optimize the SQL statement of the report; 4) enable the report function. This problem does not occur again later.

This problem is almost solved by optimizing the performance of this report. However, I learned afterwards that the report performance problem is not due to the negligence or insufficient level of the developers. The problem lies in the design of the Production Info table. In a table with a data size of million, we use char (10) To Save the Date value, although there is no problem in INSERT, UPDATE, and DELETE operations, however, it is inevitable that performance problems occur when the SELECT statement is executed and the date value field is used as the filter condition. After testing, when the data type of this field is changed to datetime, the execution time is less than 10% of the performance optimization.

Therefore, not only in the development stage, performance risks exist as early as the design stage.

4.5 upgrade hardware

Do not go into details.

5. How to Prevent

First of all, we must understand that deadlocks are inevitable in a multi-concurrency environment, and can only be achieved through rational database design,

Good indexes, appropriate query statements, isolation levels, and other measures should be taken to minimize deadlocks.

The four conditions for deadlock are listed at the beginning. If you want to destroy any one or more conditions, you can avoid deadlock. The following methods help minimize deadlocks:

A) access objects in the same order;

B) avoid user interaction in the transaction, that is, do not include the steps for user interaction during the transaction execution; c) Keep the transaction brief and in a batch; d) prompt when the SELECT statement is added WITH (NOLOCK;

SELECT * FROM TABLE1 WITH (NOLOCK );

SELECT * FROM TABLE2 WITH (NOLOCK );

This method does not lock the queried resources during execution, and allows two SQL statements to concurrently access the same resource. But the WITH (NOLOCK) Prompt may cause dirty read !!!

E) Use a lower isolation level;

I do not need to know about it. I will not repeat it here. F) use the bound connection;

The deadlock at the processing end, not the Database End, is not described in detail.

6. Conclusion

Deadlock is inevitable during project implementation. I hope you can

I have a simple understanding that I will not be helpless in the case of exceptions. If you have any technical questions or opinions about the above content, you are welcome to contact me directly for research and communication. You are also welcome to discuss with me when you encounter other database problems, improve together.