SQL server deadlock cause Analysis Summary (1/2)

In fact, the deepest reason for all deadlocks is: resource competition
SQL server deadlock 1:
A user A accesses table A (locking table A) and then accesses Table B
Another user B accesses Table B (Table B is locked) and then attempts to access Table
At this time, user A has locked Table B because user B has to wait for User B to release Table B to continue. Well, the old man will have to wait honestly.
Similarly, User B has to wait for User A to release table A to continue.
SQL server deadlock solution:
This deadlock is caused by bugs in your program. There is no other way to adjust the logic of your program.
Carefully analyze the logic of your program,
1: try to avoid locking two resources at the same time
2: When two resources must be locked at the same time, ensure that the resources should be locked in the same order at any time.

SQL server deadlock performance 2:
User A reads A record and modifies it.
This is the record modified by user B.
Here, the nature of the lock in User A's transaction is increased from the share lock attempt to the exclusive lock (for update), and the exclusive lock in User B must wait for A to release because A has A share lock.
The shared lock is released, and the exclusive lock that A cannot rise due to the exclusive lock of B cannot be released, so A deadlock occurs.
Such deadlocks are relatively hidden, but they often occur in projects that are a little larger.
SQL server deadlock solution:
Let User A's transactions (that is, the first read and then write operations), in the select statement, Update lock is used.
Syntax:
Select * from table1 with (updlock) where ....

Deadlock check

For database deadlocks, trace flag 1204, 1205, and 1206 can be used to check the output in ERRORLOG and analyze the execution context of SQLTRACE to determine the cause of the deadlock.
The third parameter of the TRACEON function is set to-1, indicating that it is not only for the current connection, but for all
. In this way, it is enough, otherwise it is only monitoring the existing database connection.

Run the following command to record the deadlock to the Errorlog:

The code is as follows:	Copy code
Dbcc traceon (1204,360 5,-1) Go Dbcc tracestatus (-1) Go

The output is as follows:

The code is as follows:	Copy code
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. TraceFlag Status --------------- 1204 1 1205 1 3605 1

(The number of affected rows is 3)

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

After that, you can view the routine logs of the database. The database checks deadlocks at intervals. The log text is as follows:

The code is as follows:

Copy code

18:34:38. 50 spid4 ---------------------------------- 18:34:38. 50 spid4 Starting deadlock search 1976 18:34:38. 50 spid4 Target Resource Owner: 18:34:38. 50 spid4 ResType: LockOwner Stype: 'OR' Mode: u spid: 55 ECID: 0 Ec :( 0xAA577570) Value: 0x4c25cba0 18:34:38. 50 spid4 Node: 1 ResType: LockOwner Stype: 'OR' Mode: u spid: 55 ECID: 0 Ec :( 0xAA577570) Value: 0x4c25cba0 18:34:38. 50 spid4 Node: 2 ResType: LockOwner Stype: 'OR' Mode: u spid: 71 ECID: 0 Ec :( 0xABF07570) Value: 0x9bd0ba00 18:34:38. 50 spid4 18:34:38. 50 spid4 -- next branch -- 18:34:38. 50 spid4 Node: 2 ResType: LockOwner Stype: 'OR' Mode: u spid: 71 ECID: 0 Ec :( 0xABF07570) Value: 0x9bd0ba00 18:34:38. 50 spid4 18:34:38. 50 spid4 18:34:38. 50 spid4 End deadlock search 1976... a deadlock was not found.

18:34:38. 50 spid4 ----------------------------------

Appendix:
DBCC TRACEON
Enable the specified trace flag.

Note
Trace tag is used to customize certain control Microsoft® SQL Server? Features of the operation method. Trace tag in server
Keep enabling until you disable dbcc traceoff. When dbcc traceon is issued
Before the statement, no trace flag is visible for new connections connected to the server. Once this statement is issued, the connection will be able to see the service
All trace tags currently enabled in the device, even if these tags are enabled by other connections ).

Homepage 1 2 Last page