Database deadlock causes and solutions Database Technology

Deadlock: a deadlock occurs when two or more processes compete for resources during execution. If there is no external force, they will not be able to proceed. It is said that the system is in a deadlock state or the system has a deadlock. These processes that are always waiting for each other are called deadlock processes. Because resource usage is mutually exclusive, after a process applies for resources, the relevant process will never be allocated with necessary resources and cannot continue to run without external assistance, this produces a special deadlock. In one case, two or more threads in the execution program are permanently blocked (waiting), and each thread is waiting for resources occupied and blocked by other threads. For example, if thread a locks record 1 and waits for record 2, and thread B locks record 2 and waits for record 1, a deadlock occurs between the two threads. In computer systems, if the system's resource allocation policy is inappropriate, it is more common that the program written by the programmer has errors and so on, it will lead to deadlocks caused by improper competition of resources. Locks can be implemented in multiple ways, such as intention locks, share-exclusive locks, lock tables, tree-like protocols, and timestamp protocols. The lock also has multiple granularities. For example, you can lock a table or a record.

The cause of the deadlock is as follows:
(1) Insufficient system resources. (2) The order in which the process is promoted is inappropriate. (3) improper resource allocation.
If the system resources are sufficient, all process resource requests can be satisfied, and the possibility of deadlock is very low. Otherwise, a deadlock will occur due to competition for limited resources. Second, the process may run in different order and speed, and may also lead to deadlocks.

Four Conditions for deadlock:
(1) mutex condition: A resource can only be used by one process at a time.

(2) request and retention conditions: when a process is blocked by requesting resources, it will not release the obtained resources.

(3) Non-deprivation condition: the resources obtained by the process cannot be forcibly deprived before they are used.

(4) Cyclic waiting condition: a cyclic waiting resource relationship is formed between several processes that are connected at the beginning and end..
These four conditions are necessary for a deadlock. As long as a deadlock occurs in the system, these conditions must be met. As long as one of the above conditions is not met, no deadlock will occur.
Deadlock Prevention and Removal:

Understanding the cause of the deadlock, especially the four necessary conditions for the deadlock, can avoid, prevent and remove the deadlock as much as possible. Therefore, in terms of system design and process scheduling, pay attention to how to prevent these four necessary conditions from being established and how to determine reasonable resource allocation algorithms to avoid permanent occupation of system resources by processes. In addition, it is necessary to prevent the process from occupying resources while waiting. During system operation, it dynamically checks the resource applications that each system can meet by the process, determine whether to allocate resources based on the check results. If the system may experience a deadlock after allocation, no allocation will be made; otherwise, the allocation will be made. Therefore, reasonable planning should be made for resource allocation.
How to minimize deadlocks

Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Reducing deadlocks to a minimum can increase transaction throughput and reduce system overhead, because only a few transactions are rolled back, and rollback will cancel all the work performed by the transaction. The application resubmit the rollback during the deadlock.

The following methods help minimize deadlocks:
(1) access objects in the same order.

(2) Avoid user interaction in transactions.

(3) keep the transaction brief and process it in a batch.

(4) use a low isolation level.

(5) use the bound connection.

 

Access objects in the same order

If all concurrent transactions access objects in the same order, the possibility of deadlock will be reduced. For example, if two concurrent transactions obtain the lock on the supplier table and then obtain the lock on the part table, the other transaction is blocked on the supplier table before one transaction is completed. After the first transaction is committed or rolled back, the second transaction continues. No deadlock occurs. The stored procedure is used to modify all the data to standardize the order of objects to be accessed.

 

Avoid user interaction in transactions

 

Avoid writing transactions that contain user interaction, because running batch processing without user interaction is much faster than manually responding to queries, for example, responding to application request parameter prompts. For example, if the transaction is waiting for user input, and the user goes to lunch or even goes home for a weekend, the user suspends the transaction so that it cannot be completed. This will reduce the system throughput, because any lock held by the transaction will be released only when the transaction is committed or rolled back. Even if no deadlock occurs, other transactions accessing the same resource will be blocked, waiting for the transaction to complete.

 

Keep the transaction brief and in a batch

A life-and-death lock is often used to concurrently execute multiple transactions that require long running in the same database. The longer the transaction runs, the longer it takes to hold the exclusive lock or update the lock, blocking other activities and possibly causing a deadlock.

Keeping transactions in one batch can minimize the number of network communications to and from the transaction, reduce the possible latency of completing the transaction and release the lock.

Low isolation level

Determine whether the transaction can run at a lower isolation level. The committed read operation allows the transaction to read the data that has been read (not modified) by another transaction without waiting for the completion of the first transaction. Using a lower isolation level (for example, commit read) instead of a higher isolation level (for example, serializable read) can shorten the time for holding shared locks, thus reducing lock contention.

Bind a connection

 

Bind the connection so that two or more connections opened by the same application can cooperate with each other. Any lock obtained by the secondary connection can be held as the lock obtained by the primary connection, and vice versa, so it will not block each other.
Use stored procedures to identify the process and SQL statements that cause the deadlock

If a deadlock occurs, how can we check which SQL statement or stored procedure is causing the deadlock? In this case, we can use the following stored procedure to detect the process and SQL statements that cause the deadlock.

 

SQL code

Use master
Go
Create procedure sp_who_lock
As
Begin
Declare @ spid int, @ BL int,
@ Inttransactioncountonentry int,
@ Introwcount int,
@ Intcountproperties int,
@ Intcounter int

Create table # tmp_lock_who (
Id int identity (1, 1 ),
Spid smallint,
Bl smallint)

IF @ ERROR <> 0 RETURN @ ERROR

Insert into # tmp_lock_who (spid, bl) select 0, blocked
From (select * from sysprocesses where blocked> 0)
Where not exists (select * from
(Select * from sysprocesses where blocked> 0) B
Where a. blocked = spid)
Union select spid, blocked from sysprocesses where blocked> 0

IF @ ERROR <> 0 RETURN @ ERROR

-- Find the number of records in the temporary table
Select @ intcountproperties = count (*), @ intcounter = 1
From # tmp_lock_who

If @ error <> 0 return @ Error

If @ intcountproperties = 0
Select 'no blocking and deadlock information' as message

-- Start of Loop
While @ intcounter <= @ intcountproperties
Begin
-- Retrieve the first record
Select @ spid = spid, @ BL = bl
From # tmp_lock_who where id = @ intcounter
Begin
If @ spid = 0
Select '+ CAST (@ bl as varchar (10 ))
+ 'Process number. The SQL syntax executed by the process is as follows'
Else
Select 'process No. SPID: '+ CAST (@ spid as varchar (10 ))
+ 'Process ID: '+ CAST (@ bl as varchar (10 ))
+ 'Blocking. The SQL syntax executed by the current process is as follows: 'dbcc INPUTBUFFER

Use mastergocreate procedure sp_who_lockasbegindeclare @ spid int, @ bl int, @ intTransactionCountOnEntry int, @ intRowcount int, @ intCountProperties int, @ intCounter intcreate table # tmp_lock_who (id int identity (1,1 ), spid smallint, bl smallint) IF @ ERROR <> 0 RETURN @ ERRORinsert into # tmp_lock_who (spid, bl) select 0, blockedfrom (select * from sysprocesses where blocked> 0) a where not exists (select * from sysprocesses where blocked> 0) B where. blocked = spid) union select spid, blocked from sysprocesses where blocked> 0IF @ ERROR <> 0 RETURN @ ERROR -- find the number of records in the temporary table select @ intCountProperties = Count (*), @ intCounter = 1 from # tmp_lock_whoIF @ ERROR <> 0 RETURN @ ERROR if @ intCountProperties = 0 select 'no blocking and deadlock information' as message -- the loop starts while @ intCounter <= @ intCountPropertiesbegin -- Take the first record select @ spid = spid, @ bl = blfrom # tmp_lock_who where Id = @ intCounter beginif @ spid = 0 select 'causes a database deadlock:' + CAST (@ bl as varchar (10 )) + 'process No. The executed SQL syntax is 'elselect' process no. SPID: '+ CAST (@ spid AS VARCHAR (10) + 'process No. SPID: '+ CAST (@ bl as varchar (10) +' blocking. The SQL syntax executed by the current process is AS follows: 'dbcc INPUTBUFFER

Two problems related to locking-deadlock and blocking

Deadlock

A deadlock is a condition that occurs not only in the relational database management system (RDBMS), but also in any multi-user system. When two users (or sessions) have different Object locks and each user needs another object lock, a deadlock occurs. Each user waits for another user to release his/her lock. Microsoft SQL Server? Will be detected. One connection is selected as a deadlock victim. The transaction of the connection is rolled back, and the application receives an error.

If deadlocks become a single public event and their rollback results in excessive performance degradation, You need to perform another in-depth and thorough investigation. Use trace tag 1204. For example, the following command starts SQL Server from the command prompt and enables trace flag 1204:
C: \ mssql \ binn \ sqlservr-T1204

All messages are displayed on the SQL Server startup console screen and error logs.
A deadlock may also occur when distributed transactions are used.

Blocking

Any lock-Based Concurrent system is inevitably characteristic of blocking in some cases. When one connection controls one lock and the other connection needs to conflict with the lock type, blocking will occur. The result is to force the second connection to wait, or block the first connection.

 

In this topic, the term "connection" refers to a single login session of a database. Each connection appears as a system process ID (SPID. Although each SPID is generally not a separate process context, it is often used for a process. More specifically, each SPID is composed of server resources and data structures (providing services for a single connection request of a given customer. A single customer application may have one or more connections. For SQL Server, there is no difference between multiple connections from a single client application and multiple connections from multiple client applications or clients. One connection can block another connection, whether from the same application or from two separate Applications on different clients.