RID锁发生了,但是没搞懂什么原因.

[shaoming01]

A线程开事务
Update XXX表第1行
Insert 一条 记录到log表

B线程开事务
做一些其他工作
Insert一条 记录到log表
Update XXX表第2行

然后这2个update语句就发生死锁了
用sql profile监控得到下面的结果


上图中的sql语句是左边的,是A线程的
下面的sql语句是右边的,是B线程的



A事务仅2条 语句
,B事务语句多一点
处理结果是:事务(进程 ID 93)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务

[發糞塗牆]

1、表上加聚集索引和一些适当的非聚集索引，避免update时全表扫描（意味着锁表） 2、insert 和update考虑是否能分到2个事务中完成

[發糞塗牆]

RID意味着你的表上没有聚集索引，在update时会锁表/分区，数据量越大，运行速度越慢，锁持有时间越久，如果接下来是带有where条件的insert，那么insert通常会被阻塞。按照你的描述，是比较明显的死锁链。

[shaoming01]

呵呵,不好意思,我常识不足. 如果我将update的表加聚焦索引应该会解决这个问题. 但是我还是不明白我这个锁的根本原因在哪里. 因为即使是整表锁,我也不知道为什么我发生死锁. insert没有where条件的.

[shaoming01]

即使没有聚焦索引,insert也不会锁表的吧?

[發糞塗牆]

1、加聚集索引不是解决问题的根本。 2、insert不排除会锁表，要看插入时是否顺序插入。 3、你的死锁是因为： A B update --速度太慢锁表 insert --一般没问题 insert --一般没问题 update --因为A可能还没完成，所以要等 这个问题主要是看谁先获取锁并且要申请什么锁。 解决建议： 1、对XXX表加聚集索引，并且看是否需要加非聚集索引协助UPDATE，这是重点，update如果快，或者锁资源的粒度很小，那么对其他影响就不大。 2、如果insert /update没有直接的关联关系，可以放到两个事务中，并且完成事务后马上commit、回滚 3、实在不行的情况下，使用快照隔离模式，不过这个有点难度

[shaoming01]

你分析的死锁原因我还是不理解. 如果A操作update速度慢, B就等一会就好了,等A完成了,就可以了. 必须是永远等不到才会称为死锁吧? 我这里B做update的时候会对XXX表执行多次update,这个会是问题的关键吗?

[發糞塗牆]

我这里B做update的时候会对XXX表执行多次update,这个会是问题的关键吗? 会，因为没有合适的索引，所以每次update都是锁表， 假设A也有多次update，b也有 再假设a是首先发生的，第一次Update时锁了一次，然后B开始update，而A过了短暂时间又要update，B又要update，都发生在相同的表资源上，就造成互相等待

[shaoming01]

A事务只Update了一次XXX表. B有多次, 如果A先锁表,则B一直等到A完成再操作,貌似不会死锁 如果B先锁表,A等待B所有循环update做完以后再操作,貌似也不会死锁, 老大,这个死锁到底是怎么回事呢?

[發糞塗牆]

1、如果多个update是一个事务里面的话，update会到事务结束才释放锁，而不是第一个update之后就释放。
2、按你说的应该是B先update，这样就获取了资源上的一个锁，而A接着UPDATE，就会申请相同资源上的锁，因为B锁住了，所以A等待，然后B的第一个update结束了开始第二个update，这时候它就等待A释放U锁（update是先申请U锁再转换成X锁），这样一来就互相等待，最后死锁。
例子：

/*
创建50万数据表，无索引
*/
SELECT TOP 500000 ROW_NUMBER()OVER(ORDER BY GETDATE())id,'a' AS NAME INTO TestTB
FROM sys.syscolumns a CROSS JOIN sys.syscolumns b

在第一个窗口输入（模拟B）

BEGIN TRAN 
UPDATE testtb
SET NAME='b'
WHERE id=10

WAITFOR DELAY '00:00:10'

UPDATE testtb
SET NAME='c'
WHERE id=100

WAITFOR DELAY '00:00:10'

UPDATE testtb
SET NAME='d'
WHERE id=10000 
ROLLBACK

在第二个窗口输入（模拟A）

BEGIN TRAN 
UPDATE testtb
SET NAME='b'
WHERE id=908
--ROLLBACK

先执行B，再马上执行A，就看到这样的结果：

[發糞塗牆]

这是死锁图：

[Andy-W]

看圖，就可以知道是由於鎖的自動升級導致的死鎖。 解決的方法，可以在update的語句指定rowlock. (使用rowlock的時候非常小心，氾濫使用會佔用大量的內存和CPU資源)

[shaoming01]

真的很感谢,我多试了几次,果然如此. 看来我的处理方式也只有是加聚焦索引了, 这样update的时候就只锁行了,这样就OK了. 至少我以我这边的业务逻辑就OK的,因为我这边2个事务更新的行是不交叉的.所以锁行是不会再死锁的了.

[shaoming01]

引用 11 楼 DVD_01 的回复:

看圖，就可以知道是由於鎖的自動升級導致的死鎖。 解決的方法，可以在update的語句指定rowlock. (使用rowlock的時候非常小心，氾濫使用會佔用大量的內存和CPU資源)

update默认不是锁行的吗? 但是如果搜索条件不是索引的话就会全表扫描.这样就锁表了. 我现在是这样理解的,不知道对吗?

[發糞塗牆]

锁表的前提在你这个例子还要加上没有或者没有高效的非聚集索引，不是单纯加聚集索引就能杜绝问题。