浅谈Sql 死锁(Dead Lock)

Q132284591

浅谈Sql 死锁(Dead Lock)
　　
死锁是指进程之间相互block的，并形成一个block环路导致无unblock的情况。死锁必须发生在两个或更多的进程之间，如果是两个进程的话，那就是进程A block了进程B,而进程B也block了进程A.如果是三个进程之间发生死锁的例子有：进程A block了进程B,进程B block了进程C，进程C block 了进程A. Sql Sever在死的情况下会尝试终止某个进程，当然如果系统不中止某个进程或尝试中止失败，这个死锁的进程将一直保持死锁，除非人工干预来结束某个进程。
如果没有指定的话系统默认中止最后一个进程，因为这样是最优的对于要roll back这个事务的操作来说。然而从Sql server 2005起，可以通过设置每个session 的DEADLOCK_PRIORITY（-10~10），2008 可以支持这21个值，但Sql 2005只支持LOW 和NORMAL。系统将选取优先级别最低的进程进行终止。
下面通过一例子来理解下死锁：
A. 打开一个Sql 窗口（窗口1），并执行下列Sql语句
BEGIN TRAN;

UPDATE dbo.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
B. 打开一个Sql 窗口（窗口 2），并执行下列Sql语句
BEGIN TRAN;

UPDATE [dbo].[Order Details]
SET unitprice = unitprice + 1.00
WHERE productid = 2;
C. 回到窗口1中执行
SELECT orderid, productid, unitprice
FROM [dbo].[Order Details]
WHERE productid = 2;

COMMIT TRAN;
发现窗口1被窗口2 block
D. 回到窗口2中执行
SELECT productid, unitprice
FROM dbo.Products
WHERE productid = 2;
COMMIT TRAN;
发现窗口2中出现错误：
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
窗口1中事务执行成功。
如何避免和减少死锁呢？
1． 很显然，事务执行的时间越长，他所要用的锁就越多，产生死锁的机会就越大。因此我们需要让事务执行的时间越短越好。把逻辑上不是同一个事务的语句尽量剔除出事务。
2． 有些死锁是由于资源的访问顺序不对，比如上面例子的死锁，如果都把select 语句放在update前面就不会发生死锁了。因此在不影响事务逻辑的情况下应该将Select语句放在update之前
3． 上面的例子是逻辑上出现死锁，因为两个进程都试图访问统一资源并做出不同的操作。然而有些死锁却不是这种逻辑上的死锁，而是出现在没很好地规划索引（查询是用到或过滤）.比如假如在窗口2中的两个语句都在过滤product 5, 而此时在窗口一中正在处理product 2, 而窗口2在处理product 5，看起来不应该发生死锁的，因为没有逻辑上的冲突。然而如果没有把productId设置成索引，为了过滤product 5,系统需要扫描(扫描当然要加锁)整个product表。这中情况下当扫描到Product 2时还是会发生死锁。所以好的索引规划可以避免一些并发时的这类非逻辑性死锁。