Causes of SQL Server deadlock and Solutions

In fact, the deepest reason for all deadlocks is: Resource Competition

Performance 1:

One user a accesses Table A (locking table A), then accesses table B, and another user B accesses table B (locking table B), and then tries 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 before it can continue. Well, the old man had to wait honestly, similarly, user B must wait for user a to release Table A to continue the deadlock.

Solution:

This deadlock is caused by yourProgramExcept for adjusting 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.

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 ), because A has a shared lock, the exclusive lock in user B must wait for a to release the shared lock, and the exclusive lock that a cannot rise due to the exclusive lock of B cannot be released, A deadlock occurs.

Such deadlocks are relatively hidden, but they often occur in projects that are a little larger.

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 ....