在应用程序中出现了单表死锁, 请高手帮忙分析一下原因.

lxx999

免费乐捐，感谢支持

Deadlock Id 2564: Process 30 was involved in application 'ZXIN10_APandCP'.
Deadlock Id 2564: Process 30 was involved on host name 'ZX133_1'.
Deadlock Id 2564: Process 30 was involved in transaction '$user_transaction'.
SQL Text: exec test.dbo.sp1 '000', '84596081', 'k19sKe', 1, 1652522, 4, 1 , 1 , 1 , '#####################1'  commit tran
Deadlock Id 2564: Process (Familyid 0, Spid 64, Suid 3) was executing a UPDATE command in the procedure sp1 at line 110 at nested level 1.
Deadlock Id 2564: Process 64 was involved in application 'ZXIN10_APandCP'.
Deadlock Id 2564: Process 64 was involved on host name 'ZX133_2'.
Deadlock Id 2564: Process 64 was involved in transaction '$user_transaction'.
SQL Text: exec test.dbo.sp_xxxxx1  0,'84596075', 1,1 commit tran
Deadlock Id 2564: Process (Familyid 0, Spid 64) was waiting for a 'update page' lock on page 129945 of the xxxxxxxxxxxx table in database 7 but process (Familyid 0, Spid 30) already held a 'exclusive page' lock on it.
Deadlock Id 2564: Process (Familyid 0, Spid 30) was waiting for a 'exclusive page' lock on page 513305 of the xxxxxxxxxxxx table in database 7 but process (Familyid 0, Spid 64) already held a 'update page' lock on it.

Deadlock Id 2564: Process (Familyid 0, Spid 30) was chosen as the victim. End of deadlock information.

00:00000:00030:2009/11/02 16:03:32.15 server  Deadlock Id 2565 detected
Deadlock Id 2565: detected. 1 deadlock chain(s) involved.

RE：说明一下， 正常的实时开户流程会先调用 sp1 ，然后再调用 sp2 ，应该是有多个用户开户时造成的死锁。
    原因是 sp1 执行了一个插入操作， 而在 sp2 中进行了更新。
    我的疑惑在于为何死锁来自同一张表？ 正常的死锁是由于多个表更新造成的。
    在插入的表中建有非聚集索引。 锁表是使用AOL锁表。

    我分析可能的原因，是在插入的时候先写下到了最后一个页面中， 然后会去修改索引页，
    而另外一个进程，已经在使用了索引页， 准备去更新数据页。所以造成了死锁。

ehxz

修改相应的存储过程吧。或调整表不合理的处理地方。

lxx999

呵呵, 能不能告知一下单表死锁产生的原因, 这样才方便修改啊.

shakeone

死锁不是多表才会有的,只要一个表有多页,就有死锁的可能,建议你把这个表变成datarow的,死锁可以大大改善

lxx999

使用行锁的确可以提高并发能力, 但是没有实际的使用过, 怕出现一些未知的问题,  比如:需要定时进行reorg操作，但该操作比较耗时，且影响业务.
请教一下楼上的兄弟,  单表死锁的原因, 一般在插入或更新的时候, 只会锁定一个数据页, 为什么需要修改或锁定多个数据页?  可以举个简单的例子说明一下么?

shakeone

放心使用datarow locking吧，这个东西已经出来10年了，很成熟了
对于apl来说，index page也会被加上事务级别的锁，比如一个插入会先锁住index page，然后是对应的data page，这个时候如果这个data page由另外一个事务控制，比如是也是一个插入，并造成这个data page的空间不够，需要**，这种**会造成对应的index page（就是被第一个事务锁住的那个）也要被锁住来增加一个对于这个data page的引用，这样就造成了死锁。
apl和dol的区别就是apl会锁index page，dol不会，所以dol的并发性要比apl好很多

shakeone

fenlie被论坛翻译成**了，英语是split

dj97116

免费乐捐，感谢支持

学习，我的程序也经常锁表

lxx999

非常的感, 再请教一下 shakeone .
1. 由于我们的业务系统是 24*7的应用, 数据库是不停的, 如果使用 datarow locking 的话, 如何日常维护, 当reorg操作时会影响业务的正常运行么?
2. 当一个可以使用索引的update 语句的时候, 应该是先扫描索引页, 如果这时数据页被阻塞了, 那么会在索引页上加锁么? 比如:  update  name = '**'  where id = 8888

shakeone

1. reorg操作会影响业务的正常运行，不过reorg这种操作，可能1个月做一次都够了，可以结合job scheduler来做，判断一个dol table是不是需要reorg，你可以先用dbcc page看看index page的分布情况，以及是不是存在比较多的forwarded rows，不过index page分布良好，空洞很少而且forwarded rows很少，就可以不用作reorg。
如果reorg碰到了forwarded rows，一定会用到表级别的写锁，所以在reorg的时候，这个表基本上就是用不了的了。
2. dol是不会在index page上加事务级别的锁的，如果是apl的表，碰到数据页被锁的情况，这个事务会释放对于索引页的读锁然后等待，以免达成持有锁并等待的这种经典死锁条件。

lxx999

再请教一下, shakeone
1. 对于 APL 锁表时,  如果表上有非聚集索引,  当查询/ 插入/ 更时, 对于数据页和索引页, 是如何加锁的?

我感觉是先锁索引页, 再去锁数据页,  如果数据页被其他的进程所作用, 那么会加一个意向锁, 然后释放索引页的锁.

shakeone

在数据页没有被写锁成功锁上之前，索引页都是被读锁锁着的，对于non-clustered index来说，只有数据页被成功update之后，non-clustered index page才会被加上写锁并更新，如果数据页没有加上写锁，这个事务就会一直等待，除非系统认为死锁发生了。
对于读锁，在iso level 0, 1 都不会干扰写锁的。你可以做个实验，搞两个事务进程等待，然后利用sp_lock, sp_who等命令观察锁的变化: EX_PAGE, SH_PAGE, EX_INT, SH_INT之类的