Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to reduce deadlock in SQL Server. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

What are the ways to reduce SQLServer deadlock

For example, thread T1 of transaction 1 has an exclusive lock on table A, thread T2 of transaction 2 has an exclusive lock on table B, and then requires a lock on table A. Transaction 2 cannot acquire this lock because transaction 1 already owns it. Transaction 2 is blocked, waiting for transaction 1. Transaction 1 then requires a lock on table B, but cannot get the lock because transaction 2 locks it. Transactions cannot release held locks before committing or rolling back. Because transactions require locks controlled by the other party to continue operation, they cannot be committed or rolled back, resulting in a deadlock in the database.

For example, when writing stored procedures, because some stored procedures have frequent transactional operations, if table An is locked first, and then table B is locked, they should be locked in this order in all stored procedures. If table B is unintentionally locked before table An in a stored procedure, this may result in a deadlock. And deadlocks are generally not easy to find.

If this deadlock occurs frequently on the server, it will degrade the server's performance, so when the application is in use, we need to track it, using sp_who and sp_who2 to determine which users may be blocking other users, and we can also use the following stored procedure to track the impact of specific deadlock execution:

Createproceduresp_who_lock

As

Begin

Declare@spidint,@blint

@ intTransactionCountOnEntryint

@ intRowcountint

@ intCountPropertiesint

@ intCounterint

Createtable#tmp_lock_who (idintidentity (1Pol 1), spidsmallint,blsmallint)

IF@@ERROR0RETURN@@ERROR

Insertinto#tmp_lock_who (spid,bl) select0,blocked

From (select*fromsysprocesseswhereblocked > 0) a

Wherenotexists (select*from (select*fromsysprocesseswhereblocked > 0) b

Wherea.blocked=spid)

Unionselectspid,blockedfromsysprocesseswhereblocked > 0

IF@@ERROR0RETURN@@ERROR

-the number of records found in the temporary table

Select@intCountProperties=Count (*), @ intCounter=1

From#tmp_lock_who

IF@@ERROR0RETURN@@ERROR

If@intCountProperties=0

Select' now has no blocking and deadlock information 'asmessage

-- the cycle begins

While@intCounter

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.