Database deadlock and resolving deadlock problems

Database deadlock and resolving deadlock problems

deadlocks (Dead lock )

The so-called deadlock <deadlock>: Refers to two or more than two processes in the course of execution, because of the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to advance. The system is now in a deadlock state or the system has a deadlock These processes, which are always waiting for each other, are called deadlock processes. Because resource consumption is mutually exclusive, when a process requests resources, so that the process without external assistance, and can never allocate the necessary resources to continue to run, which creates a special phenomenon of deadlock. A situation in which two or more threads in the executing program are permanently blocked (waiting), and each thread waits for a resource that is occupied by another thread and is blocked. 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 for two threads. In computer systems, if the system's resource allocation policy is inappropriate, it may be more common that programmers write programs that have errors and so on, which can lead to a deadlock in the process due to competing resources. Locks are implemented in a variety of ways, such as intent locks, shared-exclusive locks, lock tables, tree protocols, timestamp protocols, and so on. Locks also have a variety of granularity, such as the ability to lock on the table, or to lock on the record.
The main causes of deadlocks are: (1) due to insufficient system resources. (2) The sequence of progress of the process is not appropriate. (3) Improper allocation of resources and so on.
If the system has sufficient resources, the resource requests of the process can be met, the likelihood of deadlocks is very low, otherwise it will be locked into a deadlock because of the contention for limited resources. Second, the process is run in a different order and speed, and may also produce a deadlock. The four necessary conditions for a deadlock: (1) Mutex: A resource can only be used by one process at a time.
(2) Request and hold condition: When a process is blocked by a request for resources, it remains in place for the resources that have been obtained.
(3) Conditions of deprivation: the resources that the process has acquired cannot be forcibly deprived of until the end of its use.
(4) Cyclic waiting condition: a cyclic waiting resource relationship is formed between several processes. These four conditions are necessary for the deadlock, as long as the system has a deadlock, these conditions must be established, and as long as one of the above conditions is not satisfied, there will be no deadlock. Release and prevention of deadlocks:
Understanding the causes of deadlocks, especially the four necessary conditions that generate deadlocks, can be avoided, prevented, and
Unlock the deadlock. Therefore, in the system design, process scheduling and other aspects of how to not let these four necessary conditions to set up, how to determine the rational allocation of resources algorithm, to avoid the process of permanent occupation of system resources. In addition, to prevent the process in the waiting state to occupy resources, during the system operation, each system issued by the process can be satisfied with the resource request for dynamic check, and based on the results of the check to determine whether to allocate resources, if the system can deadlock after the allocation, it is not allocated, otherwise allocated. Therefore, the allocation of resources should be given reasonable planning.

How to minimize the deadlock

Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because there are only a few transactions:

Roll back, and rollback cancels all work performed by the transaction.

Resubmitted by the application because of a deadlock rollback.

The following methods help to minimize deadlocks:

Access the object in the same order.

Avoid user interaction in a transaction.

Keep the transaction short and in one batch.

Use a low isolation level.

Use a bound connection.

Access objects in the same order

If all concurrent transactions access the object in the same order, the likelihood of a deadlock is reduced. For example, if two concurrent transactions get a lock on the Supplier table and then get a lock on the part table, another transaction is blocked on the Supplier table until one of the transactions completes. The second transaction continues after the first transaction commits or rolls back. No deadlock occurs. Using stored procedures for all data modifications can standardize the order in which objects are accessed.

Avoid user interaction in a transaction

Avoid writing transactions that contain user interaction, because running batches without user interaction is much faster than the speed at which users respond to queries manually, such as prompting for replies to application request parameters. For example, if a transaction is waiting for user input, and the user goes to lunch or even goes home for the weekend, the user suspends the transaction so that it cannot be completed. This reduces the throughput of the system because any locks held by the transaction are freed only when the transaction commits or rolls back. Even if there is no deadlock, other transactions that access the same resource are blocked waiting for the transaction to complete.

Keep transactions short and in one batch

Deadlocks typically occur in the same database while concurrently executing multiple transactions that require long running time. The longer a transaction runs, the longer it takes to hold an exclusive lock or update lock, blocking other activities and potentially causing deadlocks.

Keep transactions in one batch, you can minimize the network traffic round-trip of a transaction, reduce the possibility of completing a transaction, and release the lock.

Use low isolation Level

Determines whether a transaction can run at a lower isolation level. Performing a commit read allows a transaction to read data that has been read (unmodified) by another transaction without having to wait for the first transaction to complete. Using a lower isolation level (such as read-committed) without using a higher isolation level (such as serializable) can shorten the time to hold a shared lock, reducing lock contention.

Using a bound connection

Using a bound connection enables two or more connections opened by the same application to work together. Any lock acquired by a secondary connection can be held as a lock obtained by the primary connection, and vice versa, so it does not block each other.

use stored procedures to isolate processes and SQL statements that cause deadlocks

If there is a deadlock, how do we detect which SQL statement or stored procedure has a deadlock? At this point we can detect the process that caused the deadlock and the SQL statement by using the following stored procedure.

<ccid_nobr>

<ccid_code>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 (SMALLINT,BL), spid smallint) IF @ @ERROR <>0 RETURN @ @ERRORinsert into #tmp_lock_who (SPID,BL) Select . 0, Blockedfrom (select. * from sysprocesses where blocked>0) a where isn't exists (select. * FROM (SELECT. * FROM Sysproc Esses where blocked>0) b where a.blocked=spid) union SELECT. spid,blocked from sysprocesses where blocked>0if @ @ERROR <>0 RETURN @ @ERROR--Find the number of records in the staging table select. @intCountProperties = Count (*), @intCounter = 1from #tmp_lock_whoIF @ @ERROR <>0 RETURN @ @ERROR if @ Intcountproperties=0select. ' Now there is no blocking and deadlock information ' as the message--loop starts while @intCounter <= @intCountPropertiesbegin--Take the first record select. @spid = spid, @bl = Blfrom #tmp_lock_who where Id = @intCounter beginif @spid =0 Select. ' Cause the database deadlock is: ' + CAST (@bl as VARCHAR (10) + ' process number, which executes the SQL syntax as follows ' ElseseLect. ' Process number spid: ' + cast (@spid as varchar (10)) + ' by ' + ' process number spid: ' + cast (@bl as VARCHAR (10) + ' block, its current process executes SQL syntax as follows ' DBCC INPUTBU Ffer

Two issues related to locking--deadlocks and blocking

Dead lock

Deadlocks are a condition that occurs not only in a relational database management system (RDBMS), but also in any multiuser system. A deadlock occurs when two users (or sessions) have locks of different objects, and each user needs a lock on another object. Each user waits for another user to release his lock. When two connections fall into a deadlock, Microsoft®sql Server™ detects. One of the connections was selected as a deadlock victim. The transaction for the connection is rolled back, and the application receives an error.

If deadlocks become a single common event, and their rollback results in excessive performance degradation, a thorough and thorough investigation is required. Use trace flag 1204. For example, the following command starts SQL Server from a command prompt and enables trace flag 1204:

c:\\mssql\\binn\\sqlservr-t1204

All messages are now displayed on the console screen and in the error log that started SQL Server.

Deadlocks can also occur when a distributed transaction is used.

Blocking

Any lock-based concurrency system inevitably has characteristics that may be blocked in some cases. Blocking occurs when one connection controls one lock and the other requires a conflicting lock type. The result is forcing a second connection to wait, or blocking on the first connection.

In this topic, the term "connection" refers to a single logon session for 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 to refer to a process. More specifically, each SPID consists of server resources and data structures that serve a given customer's request for a single connection. A single customer application may have one or more connections. In the case of SQL Server, there is no difference between multiple connections from a single client application and multiple connections from multiple client applications or multiple clients. A connection can block another connection, whether it comes from the same application or from a separate application on two different client computers.

Database deadlock and resolving deadlock problems