Experience Sharing the causes of SQL Server deadlocks and Solutions

The deepest cause of all deadlocks is resource contention. This article provides an example to illustrate this problem.

Symptom 1

One user A accesses Table A (Table A is locked), then table B is accessed, and another user B accesses table B (Table B is locked ), user A tries to access Table A. User B has locked table B and must wait for user B to release table B before continuing, similarly, user B must wait for user A to release Table A to continue the deadlock.

Solution:

This deadlock is caused by bugs in your program. In addition to adjusting the logic of your program, there is no other way. Analyze the logic of your program carefully:

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.

Symptom 2

User A reads A record and then modifies the record. This is because user B modifies the record, 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.

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 (T006)