sql - 恼人的 SQL 死锁

Question

考虑以下设置:

create table Tabl
    (
    ClustInd int,
    NonClustInd int,
    NonClustInd_Addon int,
    OtherCol int
    )
    create clustered index CLI on Tabl (ClustInd)
    create nonclustered index NCLI on Tabl (NonClustInd) include(NonClustInd_Addon)
    go

    --just generating & inserting 2^4*4 = 65536 records:
    ;with h(a) as (select 0 union all select 0), 
    h1(a) as (select h1.a from h h1, h h2, h h3, h h4),
    h2(a) as (select h1.a from h1, h1 h2, h1 h3, h1 h4),
    r(a) as (select row_number() over (order by a) from h2)
    insert into Tabl(ClustInd,  NonClustInd, NonClustInd_Addon, OtherCol)
    select a, a*10 - 10000, 60 + a*5, a*3 from r

现在，如果您在一个 session 中运行此命令:

    declare @g int
    while 1 = 1
    --Using "OtherCol" to force the NCLI -> CLI -> Tbl lookup
    select @g = OtherCol from Tabl where NonClustInd = 477210

这是另一个:

    while 1 = 1 
    update Tabl
    --Updating NonClustInd_Addon to force the updating of the NCLI and CLI indexes
    set NonClustInd_Addon = case when NonClustInd_Addon = 1 then 2 else 1 end
    where NonClustInd = 477210

“select”语句将与 update 语句死锁。

他们死锁的原因是因为select语句首先锁定“NCLI”索引，然后锁定“CLI”索引。更新语句 - 首先是“CLI”索引，然后是“NCLI”。显然这最终会导致僵局。

有办法打破这个僵局吗？也许有一种方法可以控制索引锁定的顺序(因为，例如，如果 SQL 锁定 CLI，然后锁定 NCLI 进行更新和选择，则这种死锁永远不会出现)。谢谢。

PS:附加信息:

更新期间存在以下锁:

在选择期间:

死锁前几秒:

Answer 1

使用快照隔离或读取已提交的快照。这通常很容易实现，而且负面后果通常是值得的。它通过完全排除只读事务来简化您的生活。他们不再锁定或阻止。

如果你能做到这一点，问题就解决了。