Sharp SQL2014: handling deadlocks, sql2014

Sharp SQL2014: handling deadlocks, sql2014
 

In two or more tasks, if each task locks the resources that other tasks attempt to lock, this will cause permanent blocking of these tasks and lead to deadlocks. For example:

Transaction A obtains the share lock of Row 1.

Transaction B obtains the shared lock of Row 2.

Now, transaction A requests the exclusive lock of Row 2, but it is blocked before transaction B completes and releases its shared lock held on Row 2.

Currently, transaction B requests the exclusive lock of Row 1, but it is blocked before transaction A completes and releases its share lock held on Row 1.

Transaction A can be completed only after transaction B is completed, but transaction B is blocked by transaction. This situation is also called cyclic dependency: transaction A depends on transaction B, and transaction B depends on transaction A, thus forming A loop.

Unless an external process is disconnected from the deadlock, both transactions in the deadlock will wait for an indefinite period of time. The Database Engine deadlock monitor regularly checks the deadlocked tasks. If a deadlock is detected, select a task as the victim, terminate the transaction, and prompt an error. In this way, other tasks can complete their transactions and thus lift the deadlock. For an application that terminates a transaction with an error, it can also retry the transaction, but it usually needs to wait until other transactions that are deadlocked with it are completed.

17.6.1 method to prevent deadlocks 1. 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 first obtain the lock on the Supplier table and then obtain the lock on the Part table. Before one transaction is completed, another transaction will be blocked on the Supplier table. When 1st transactions are committed or rolled back, the 2nd transactions continue to be executed, so that no deadlock will occur. If you use a stored procedure to modify data, you can standardize the object access sequence.

2. Avoid user interaction in transactions

Avoid writing transactions that contain user interaction, because batch processing without user intervention runs much faster than the time when users must manually respond to queries. For example, if the transaction is waiting for user input and the user goes to lunch, the user will delay the completion of the transaction. This reduces the system throughput because any lock held by the transaction is released only after the transaction is committed or rolled back. Even if no deadlock occurs, other transactions accessing the same resource will be blocked before the transactions that occupy the resource are completed.

3. Keep the transaction brief and in a batch

In the same database, deadlocks often occur when concurrent execution of multiple transactions that require long running. 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.

4. Use a lower isolation level

Determine whether the transaction can run at a lower isolation level. The implemented committed read allows the transaction to read the data that has been read (not modified) by another transaction, without waiting for the completion of 1st transactions. Using a lower isolation level (for example, committed read) is shorter than using a higher isolation level (for example, serializable) to hold a shared lock. This reduces lock contention.

5. Use the row-based version control isolation level

If you set the READ_COMMITTED_SNAPSHOT database option to ON, the row version control instead of the shared lock will be used during the read operation of transactions running at the committed read isolation level.

17.6.2 use TRY... CATCH to handle deadlocks

The CATCH Block constructed by TRY... CATCH can capture the 1205 deadlock error. After an error occurs, you can roll back the transaction to unlock the lock. The following statement creates a table indicating the deadlock status and a stored procedure for printing error information.

USE AdventureWorks;

GO

 

-- Verify whether the table already exists

IF OBJECT_ID (N 'my _ sales', N 'U') IS NOT NULL

Drop table my_sales;

GO

 

-- Create a table and insert data

Create table my_sales

(

Itemid int primary key,

Sales INT not null

);

GO

 

INSERT my_sales (itemid, sales) VALUES (1, 1 );

INSERT my_sales (itemid, sales) VALUES (2, 1 );

GO

-- Verify that the stored procedure already exists

IF OBJECT_ID (n'usp _ myerrorlog', n'p') IS NOT NULL

Drop procedure usp_MyErrorLog;

GO

 

-- Create a stored procedure for outputting error messages

Create procedure usp_MyErrorLog

AS

PRINT

N'error' + CONVERT (VARCHAR (50), ERROR_NUMBER () +

N', severity level '+ CONVERT (VARCHAR (5), ERROR_SEVERITY () +

N', status' + CONVERT (VARCHAR (5), ERROR_STATE () +

N', line' + CONVERT (VARCHAR (5), ERROR_LINE ());

PRINT

ERROR_MESSAGE ();

The following session 1 and Session 2 code scripts run simultaneously in two separate SQL Server Management Studio connections. Both sessions attempt to update the same row in the table. During the first attempt, one session will successfully complete the update operation, and the other session will be selected as the deadlock victim. The deadlock victim error will cause the execution to jump to the CATCH block, and the transaction will enter the uncommitted state. In the CATCH Block, the deadlock Victim rolls back the transaction and retries to update the table until the update is successful or the Retry limit is reached.

 

Session 1

Session 2

USE AdventureWorks; GO   -- Define and set variables to specify the number of attempts to submit updates DECLARE @ retry INT; SET @ retry = 5;   -- If it is used as a deadlock victim, keep updating it. WHILE (@ retry> 0) BEGIN BEGIN TRY Begin transaction; UPDATE my_sales SET sales = sales + 1 WHERE itemid = 1;   -- Delayed wait. At this time, the itemid is 1 and 2 rows. -- The lock cannot be released before it is submitted. Waitfor delay '00: 00: 13 '; UPDATE my_sales SET sales = sales + 1 WHERE itemid = 2;   SET @ retry = 0;   Commit transaction; END TRY BEGIN CATCH -- Check the error number. If the victim is a deadlock, -- Reduces the number of retries. For other -- Error, exit the WHILE LOOP IF (ERROR_NUMBER () = 1205) SET @ retry = @ retry-1; ELSE SET @ retry =-1;   -- Output error message EXECUTE usp_MyErrorLog;   -- The session contains transactions that cannot be committed. -- XACT_STATE will return-1 IF XACT_STATE () <> 0 Rollback transaction; End catch; END; -- END the WHILE LOOP

USE AdventureWorks; GO   -- Define and set variables to specify the number of attempts to submit updates DECLARE @ retry INT; SET @ retry = 5;   -- If it is used as a deadlock victim, keep updating it. WHILE (@ retry> 0) BEGIN BEGIN TRY Begin transaction; UPDATE my_sales SET sales = sales + 1 WHERE itemid = 2;   -- Delayed wait. At this time, the itemid is 1 and 2 rows. -- The lock cannot be released before it is submitted. Waitfor delay '00: 00: 07 '; UPDATE my_sales SET sales = sales + 1 WHERE itemid = 1;   SET @ retry = 0;   Commit transaction; END TRY BEGIN CATCH -- Check the error number. If the victim is a deadlock, -- Reduces the number of retries. For other -- Error, exit the WHILE LOOP IF (ERROR_NUMBER () = 1205) SET @ retry = @ retry-1; ELSE SET @ retry =-1;   -- Output error message EXECUTE usp_MyErrorLog;   -- The session contains transactions that cannot be committed. -- XACT_STATE will return-1 IF XACT_STATE () <> 0 Rollback transaction; End catch; END; -- END the WHILE LOOP

The following message is returned in session 1, indicating that both rows have been updated.

(One row is affected)

 

(One row is affected)

The following is the message returned from Session 2. Session 2 is the victim of a deadlock.

(One row is affected) -- due to a deadlock, only one row is successfully updated in session 2 transactions, and rollback will occur.

Error 1205, severity 13, status 51, Row 18 -- error message output by the Stored Procedure usp_MyErrorLog

The transaction (process ID52) and another process are deadlocked on the locked resource and have been selected as the deadlock victim. Run the transaction again. -- SQL Server prompt

 

(One Line is affected) -- the row and downstream messages are the prompt messages after the update attempt is retried.

 

(One row is affected)