SQL Server database deadlock causes and solutions

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 is caused by the existence of A Share Lock

Must wait for A release
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 ....

SQL Server deadlock check tool

The code is as follows:

Copy code

If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [sp_who_lock] ') and OBJECTPROPERTY (id, n' IsProcedure ') = 1) Drop procedure [dbo]. [sp_who_lock] GO Use master Go Create procedure sp_who_lock As Begin Declare @ spid int, @ bl int, @ IntTransactionCountOnEntry int, @ IntRowcount int, @ IntCountProperties int, @ IntCounter int Create table # tmp_lock_who ( Id int identity (1, 1 ), Spid smallint, Bl smallint) IF @ ERROR <> 0 RETURN @ ERROR Insert into # tmp_lock_who (spid, bl) select 0, blocked From (select * from sysprocesses where blocked> 0) Where not exists (select * from sysprocesses where blocked> 0) B Where a. blocked = spid) Union select spid, blocked from sysprocesses where blocked> 0 IF @ ERROR <> 0 RETURN @ ERROR -- Find the number of records in the temporary table Select @ intCountProperties = Count (*), @ intCounter = 1 From # tmp_lock_who IF @ ERROR <> 0 RETURN @ ERROR If @ intCountProperties = 0 Select 'no blocking and deadlock information' as message -- Start of loop While @ intCounter <= @ intCountProperties Begin -- Retrieve the first record Select @ spid = spid, @ bl = bl From # tmp_lock_who where Id = @ intCounter Begin If @ spid = 0 Select '+ CAST (@ bl as varchar (10) +' causes database deadlock. The SQL statement executed by 'process no. 'is AS follows' Else Select 'process No. SPID: '+ CAST (@ spid as varchar (10) + 'be' + 'process No. SPID:' + CAST (@ bl AS VARCHAR (10) + 'blocking, the SQL syntax executed by the current process is as follows' Dbcc inputbuffer (@ bl) End -- Move the Loop pointer down Set @ intCounter = @ intCounter + 1 End Drop table # tmp_lock_who Return 0 End

Deadlock handling method:

(1) Check that the spid is in the wait status according to the SQL statement provided in 2, and then kill the spid (that is, the fourth necessary condition for cracking the deadlock: Loop

Wait); of course, this is only a temporary solution. We can't check the deadlock and Kill sp in the user's production environment when a deadlock occurs. We should

Consider how to avoid deadlocks.

(2) use SET LOCK_TIMEOUT timeout_period (unit: milliseconds) to SET lock request timeout. By default, the database does not have a timeout period.

The code is as follows:	Copy code
(The value of timeout_period is-1. You can use SELECT @ LOCK_TIMEOUT to view the value, that is, wait for an indefinite period ). When the request lock exceeds timeout_period

Will return an error. When the timeout_period value is 0, the message is returned when the lock is encountered. Set lock request timeout, break the deadlock

Two necessary conditions (request and retention conditions ).

Server: message 1222, level 16, status 50, row 1
The lock request timeout period has been exceeded.

(3). There is a lock monitor thread in SQL Server to execute the deadlock check. When the lock monitor starts the deadlock search for a specific thread, it will identify that the thread is waiting.

Then, find the owner of a specific resource and recursively execute the deadlock search for those threads until a loop that constitutes the deadlock condition is found.

. After a deadlock is detected, the database engine selects the session of the transaction with the smallest rollback and rollback transactions as the deadlock victim, returns error 1205, and rolls back the deadlock at the sacrifice.

And release all the locks held by the transaction, so that transactions in other threads can request resources and continue to run.

Deadlock example and solution
5.1 SQL deadlock
(1). Basic data for testing:

The code is as follows:	Copy code
Create table Lock1 (C1 int default (0 )); Create table Lock2 (C1 int default (0 )); Insert into Lock1 VALUES (1 ); Insert into Lock2 VALUES (1 );

(2) open two query windows and execute the following two SQL statements:

The code is as follows:	Copy code
-- Query 1 Begin Tran Update Lock1 Set C1 = C1 + 1; WaitFor Delay '00: 01: 00 '; SELECT * FROM Lock2 Rollback Tran; -- Query 2 Begin Tran Update Lock2 Set C1 = C1 + 1; WaitFor Delay '00: 01: 00 '; SELECT * FROM Lock1 Rollback Tran;

The preceding SQL statement WaitFor Delay '00: 01: 00' is used to wait for 1 minute to check the lock.

Solution
A). SQL Server automatically selects an SQL statement as the deadlock victim: After running the two queries above, we will find that one SQL statement can be executed normally,

Another SQL statement reports the following error:

Server: message 1205, level 13, status 50, row 1
The transaction (process ID xx) and the other process have been deadlocked on the lock resource, and the transaction has been selected as the deadlock victim. Run the transaction again.
This is the lock monitor described in section 4 above.

B). Access objects in the same order: reversing the order of Update and SELECT statements in any SQL statement. For example, modify the second SQL statement as follows:

The code is as follows:	Copy code
-- Query2 Begin Tran SELECT * FROM Lock1 -- apply for S lock on Lock1 WaitFor Delay '00: 01: 00 '; Update Lock2 Set C1 = C1 + 1; -- Lock2: RID: X Rollback Tran;

Of course, this modification also has a price. This will cause the second SQL to remain in the blocking state until the execution of the first SQL statement is completed. Execute Query1 or

Query2 takes about 1 minute, but if Query2 is executed immediately when Query1 is executed, Query2 takes 2 minutes to complete.

The requested resources reduce the concurrency to a certain extent.

C) when the SELECT statement is added With (NoLock), the prompt is: by default, the SELECT statement will apply the S Lock (shared lock) to the queried resource, and the S Lock and X lock (exclusive lock) will not

Compatible; but With (NoLock), SELECT does not lock the queried resources (or apply the Sch-S lock, the Sch-S lock can be compatible With any lock);

These two sqls can concurrently access the same resource. Of course, this method is suitable for solving read and write concurrency deadlocks, but adding With (NoLock) may cause

Dirty read.

The code is as follows:	Copy code
SELECT * FROM Lock2 WITH (NOLock) SELECT * FROM Lock1 WITH (NOLock)

D) use a lower isolation level. SQL Server 2000 supports four transaction isolation levels (TIL): READ UNCOMMITTED and READ

Snapshot til is added to COMMITTED, repeatable read, and SERIALIZABLE; SQL Server 2005. By default

With read committed til, we can add a set transaction isolation level read statement before the preceding two SQL statements.

UNCOMMITTED to reduce the TIL to avoid deadlocks. In fact, the SELECT statement in the transaction that runs in read uncommitted til is not the result asset.

The source locks or Sch-S locks instead of S locks. However, you need to note that read uncommitted til allows dirty reads, although it reduces the level of TIL.

The preceding two SQL statements do not report errors during execution, but the execution result is one return 1 and one returns 2, that is, the dirty data is read.

It is what we expect.

E). Add SET LOCK_TIMEOUT timeout_period before SQL. When the request lock exceeds the SET timeout_period time, the current SQL

Execute, sacrifice yourself, and fulfill others.

F). Use the row-based version control isolation level (supported by SQL Server 2005): After the following options are enabled, SELECT does not apply the S lock to the requested resources,

No locks or Sch-S locks are applied to minimize the chance of deadlocks between read and write operations, and dirty reads are not performed. Ah

SET ALLOW_SNAPSHOT_ISOLATION ON
SET READ_COMMITTED_SNAPSHOT ON