Causes and solutions of deadlock in SQL Server

Causes and resolutions of deadlock in SQL Server 2006-07-18 05:12:10

Category: SQL Server

In fact, the deepest cause of all deadlocks is one: resource competition

Performance One:

A User A accesses table A (locks up table A), then accesses table B, another user B accesses table B (locked table B), and then attempts to access table A, when user a because User B has locked table B, it must wait for User B to release table B before it can continue, well, then he will have to be honest and wait here. The same user B waits for user A to release form A to continue this is a deadlock.

Workaround:

This deadlock is due to a bug in your program, except that you have to adjust the logic of your program.

Carefully analyze the logic of your program:

1: Try to avoid locking up to two resources at a time

2: When two resources must be locked at the same time, ensure that the resources are locked in the same order at any one time.

Performance Two:

User A reads a record and then modifies the record. This is the User B modifies the record, where the nature of the lock in User A's transaction is raised by a shared lock attempt to an exclusive lock (for update), while the exclusive lock in User B must wait for a to release the shared lock because a has a shared lock, and a because the exclusive lock of B cannot rise the exclusive lock can not release the shared lock, So there was a deadlock.

This kind of deadlock is relatively covert, but in fact, in a slightly larger project often occurs.

Workaround:

Let user A's transaction (that is, the operation of the first read and write type), when the Select is the update lock

The syntax is as follows:

SELECT * FROM table1 with (Updlock) where .... [@[email protected]]

Causes and solutions of deadlock in SQL Server