Shulou(Shulou.com)05/31 Report--

How to analyze and solve the deadlock problem of SQLServer, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Deadlocks, in short, two or more trans requests for an object that the other is requesting at the same time, causing both parties to wait for each other. Simple examples are as follows:

Trans1 trans2

-

1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction

2.update table A 2.update table B

3.update table B 3.update table A

4.IDBConnection.Commit 4.IDBConnection.Commit

So, it's easy to see that if trans1 and trans2 arrive at step3 respectively, then trans1 will request an X lock for B, and trans2 will request an X lock for A, and the locks of both are already held by the other party on step2. The subsequent Commit cannot be executed because the lock is not available, so both parties begin to deadlock.

OK, let's look at a simple example to explain how to solve the deadlock problem.

-Batch # 1

CREATE DATABASE deadlocktest

GO

USE deadlocktest

SET NOCOUNT ON

DBCC TRACEON (1222,-1)

-in SQL2005, a new dbcc parameter is added, which is 1222. Originally, under 2000, we know that we can execute dbcc.

-traceon (1204. 3605) sees all the deadlock messages. In SqlServer 2005, there is an enhancement for 1204, which is 1222.

Specific usage: dbcc traceon (1204, 1222, 3605) so that deadlock records can be generated in ErrorLog, recording deadlock types and stored procedures that cause deadlocks.

The command to close is dbcc traceoff.

GO

IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE T1

IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1

IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2

GO

CREATE TABLE T1 (C1 int, c2 int, c3 int, c4 char (5000))

GO

DECLARE @ x int

SET @ x = 1

WHILE (@ x = [@ p1] AND [T1]. [c2] = [@ p1] AND [deadlocktest]. [DBO]. [T1]. [c2]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.