主页 / dba / 问题 / 220615
Accepted
jesijesi
Asked: 2018-10-20 13:40:27 +0800 CST

死锁 - 由于更新导致索引内的索引页面移动

  • 772

我仍在对这个问题中提到的死锁进行故障排除。在这方面，我得到了帮助，理解了为什么 select 会持有共享锁，并且我也能够测试并确认这一点。

现在我正在查看陷入僵局的第二个 spid。该会话使用主键进行更新（WHERE BatchId = @BatchI，BatchId 是 PK）并且它需要在两个页面上使用 IX。最初我不明白为什么它只更新一行时需要锁定非聚集索引的两页。然后我意识到对该行的更新将更改该行在非聚集索引中的位置，这可能是它需要在两个页面上锁定的原因。

那是对的吗？

其次，请帮助确认我对这个僵局的理解是正确的。为简单起见，我们假设该非聚集索引中只有两个页面。第 1 页和第 2 页。这就是正在发生的事情吗 在此处输入图像描述

sql-server performance

1 个回答

  1. Best Answer

    一些测试也是如此，死锁实际上是由非聚集索引内的页面移动引起的，该更新是由更改行在索引中的位置的更新引起的。

    在此处输入图像描述

    所以最初Session 2（update）在索引行原来所在的Page2上取出了IX锁。由于更新会更改值并进而更改索引行在索引中的位置，因此会话 2 还需要在目标页上获取另一个 IX 锁，索引行将在更新后结束。

    要了解 select 想要保留 S 锁的原因，请参阅此问题。

    回购协议

    set nocount on 
create table deadlck ( a int , b int, c char (3), constraint PKA primary key (a))
create index idx on deadlck(b)
--creating a big enough table so that selects use page lock instead of row lock
declare @a int =1
declare @b int =57800000 
while 1=1
begin
insert into deadlck select @a,@b,'111'
if (@a%521) =520 -- to make idx index pages fit one value in each page perfectly,  not necessary, just easier to simulate the issue while testing
set @b=@b-1
Set @a=@a+1
if @a=52100
break
end

ALTER INDEX idx ON deadlck
REBUILD WITH (FILLFACTOR = 90); -- same reason as above, i did the math with a fill factor of 90 to have the pages of idx index have one value per page and and also the pages needed to have some space so when we do the update the index entry can move to another page without have to do a page split

ALTER INDEX PKA ON deadlck
REBUILD WITH (FILLFACTOR = 90); 

--session 1
while 1=1
begin
begin tran
update deadlck set b=57799999 where a=2
rollback
end

--session 2
while 1=1
begin
begin tran
select b from deadlck with(paglock) where (b =57799999 or b=57800000)
rollback
end
    • 2

相关问题