SQL死锁产生原因和解决方法

个人博客

死锁现象

表现一：一个用户A 访问表A(锁住了表A)，然后又访问表B，另一个用户B 访问表B(锁住了表B)，然后企图访问表A，这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B，才能继续，好了他老人家就只好老老实实在这等了，同样用户B要等用户A释放表A才能继续这就死锁了。

• 解决方法
  这种死锁是由于你的程序的BUG产生的，除了调整你的程序的逻辑别无他法
  　　仔细分析你程序的逻辑：
  　　1：尽量避免同时锁定两个资源
  　　2: 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源.

表现二：用户A读一条纪录，然后修改该条纪录。这时用户B修改该条纪录，这里用户A的事务里锁的性质由共享锁企图上升到独占锁(for update),而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。
　　这种死锁比较隐蔽，但其实在稍大点的项目中经常发生。其实这种死锁也可以叫做书签查找所引起的。

解决方法：
　　让用户A的事务（即先读后写类型的操作),在select 时就是用Update lock

select * from table1 with(updlock) where ....

死锁，简而言之，两个或者多个trans，同时请求对方正在请求的某个对象，导致双方互相等待。简单的例子如下：

trans1	trans2
1.IDBConnection.BeginTransaction	2.IDBConnection.BeginTransaction
2.update table A	2.update table B
3.update table B	3.update table A
4.IDBConnection.Commit	4.IDBConnection.Commit

那么，很容易看到，如果trans1和trans2，分别到达了step3，那么trans1会请求对于B的X锁，trans2会请求对于A的X锁，而二者的锁在step2上已经被对方分别持有了。由于得不到锁，后面的Commit无法执行，这样双方开始死锁。

好，我们看一个简单的例子，来解释一下，应该如何解决死锁问题。

 -- Batch #1
  CREATE DATABASE deadlocktest
  GO

  USE deadlocktest
  SET NOCOUNT ON
  DBCC TRACEON (1222, -1)
  -- 在SQL2005中，增加了一个新的dbcc参数，就是1222，原来在2000下，我们知道，可以执行dbcc
  -- traceon(1204,3605,-1)看到所有的死锁信息。SqlServer 2005中，对于1204进行了增强，这就是1222。
  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 <= 1000) BEGIN
           INSERT INTO t1 VALUES (