澳门金沙vip 3

SQL Server里面Deadlock 犹如无可制止

两个进程发生死锁的典型例子是:进程T1中获取锁A,申请锁B;进程T2中获取锁B,申请锁A,我们下面动手来演示一下这种情况:

在今天的文章里我想演示下SQL
Server里在表上丢失索引如何引起死锁(deadlock)的。为了准备测试场景,下列代码会创建2个表,然后2个表都插入4条记录。

从SQL Server的Online Book里面描述Deadlock策略里面发现有下面的一种情况:

  1. 创建一个Database,名为InvDB。

  2. 执行下面脚本创建person表并填充两条数据:

  3. 在SQL Server Management Studio的两个窗口中同时执行下面的查询:

 1 -- Create a table without any indexes
 2 CREATE TABLE Table1
 3 (
 4     Column1 INT,
 5     Column2 INT
 6 )
 7 GO
 8 
 9 -- Insert a few record
10 INSERT INTO Table1 VALUES (1, 1)
11 INSERT INTO Table1 VALUES (2, 2)
12 INSERT INTO Table1 VALUES (3, 3)
13 INSERT INTO Table1 VALUES (4, 4)
14 GO
15 
16 -- Create a table without any indexes
17 CREATE TABLE Table2
18 (
19     Column1 INT,
20     Column2 INT
21 )
22 GO
23 
24 -- Insert a few record
25 INSERT INTO Table2 VALUES (1, 1)
26 INSERT INTO Table2 VALUES (2, 2)
27 INSERT INTO Table2 VALUES (3, 3)
28 INSERT INTO Table2 VALUES (4, 4)
29 GO

Worker threads. A queued task waiting for an available worker thread
can cause deadlock. If the queued task owns resources that are blocking
all worker threads, a deadlock will result. For example, session S1
starts a transaction and acquires a shared (S) lock on row r1 and then
goes to sleep. Active sessions running on all available worker threads
are trying to acquire exclusive (X) locks on row r1. Because session S1
cannot acquire a worker thread, it cannot commit the transaction and
release the lock on row r1. This results in a deadlock.

这段代码在默认的READ
COMMITTED隔离级别下运行,两个进程分别在获取一个排它锁的情况下,申请对方的共享锁从而造成死锁。

在我向你重现死锁前,先看下列的代码,它是个简单的UPDATE语句,在第1个表里更新一个指定行。

 

可见一个进程可以正常更新并显示结果,而另一个进程已经被回滚:

1 -- Acquires an Exclusive Lock on the row
2 UPDATE Table1 SET Column1 = 3 WHERE Column2 = 1
  • *工作线程。排队等待可用工作线程的任务可能导致死锁。如果排队等待的任务拥有阻塞所有工作线程的资源,则将导致死锁。例如,会话
    S1 启动事务并获取行 r1 的共享锁(S
    锁)后,进入睡眠状态。在所有可用工作线程上运行的活动会话正尝试获取行
    r1 的排他锁(X 锁)。因为会话 S1
    无法获取工作线程,所以无法提交事务并释放行 r1 的锁。这将导致死锁。*

    澳门金沙vip,也就是说并不一定要是都是Update、Insert这种会上排它锁操作的语句才会造成死锁。只要在不同的线程里面访问了同一个资源(某个数据行)的情况下,一个Select动作碰上了一个Update动作也会产生死锁。你的SQL语句执行时间越长碰上这种情况的机会越大。看看下面这张图

  • 澳门金沙vip 1

     

    左边的进程完全就是一个普通的查询语句,对资源上的也只是一个共享锁(S).而右边的进程是上了一个Index的排它锁(IX).跟通常意义上的两边都上排它锁才可能产生死锁的情况完全不一样,下面的图是我们写程序可以避免的死锁情况澳门金沙vip 2

  • 怎么处理好SQL Server里面的死锁的话可以参照下面这篇文章:

    Reducing SQL Server
    Deadlocks

    里面提到了,要避免的Deadlock,把不同事务里面两个Update或者Insert资源的操作锁定资源顺序保持一致是最基本的,不过也就是能避免第二张图里面出现的情况。

    • 而要避免第一张图的情况好像是不太可能,除非你每个SQL
      语句都加上With
      Nolock,第一张图的情况基本上只能够通过减少查询时间,减少事务处理的时间来减少而无可避免。

(1 row(s) affected)Msg 1205, Level 13, State 45, Line 8Transaction
(Process ID 55) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

因为在Column2上没有索引定义,对于我们的UPDATE语句,查询优化器在执行计划里必须选择表扫描(Table
Scan)
运算符来查找符合的记录:

  1. 启动 SQL Server Profiler,选择下面4种Events:

澳门金沙vip 3 

再执行一次上面的死锁实验,可以看到如下所示的死锁图:

这就是说我们必须扫描整个堆表来找我们想更新的行。在那个情况下,SQL
Server用排它锁(Exclusive
Lock)
锁定表里的第1行。当你在不同的会话执行一个SELECT语句,引用另一个堆表里“将发生”的行,表扫描(Table
Scan)运算符会阻塞,因为首先你必须读取所有堆表里“已发生”的行,即获取你查询里逻辑请求的行。

非常有趣的一点是:第二次执行上述语句不会发生死锁!这是因为此时两个进程中,SQL
Server会智能的识别出update语句是不需要做的,所以都不会去获取排它锁,当然也就不会死锁了。SQL
Server 2008 的查询优化器还真是非常强大!

-- This query now requests a Shared Lock, but get's blocked, because the other session/transaction has an Exclusive Lock on one row, that is currently updated
SELECT Column1 FROM Table1
WHERE Column2 = 4