Sql-server – Deadlock – Due to update causing Index page movement within an index

deadlockperformancesql serversql server 2014

I am still troubleshooting deadlock mentioned in this questions. In that I got help in understanding why a select would hold on to a shared lock and I was able to test and confirm that also.

Now I am looking at second spid involved in the deadlock. That session does an update using a primary key(WHERE BatchId = @BatchI , BatchId is the PK) and it needs IX on two pages. Initially I couldn't figure out why it needed locks on two pages of a non clustered index when it was just updating one row. Then I realized that the update to that row would change the location of the row in the non clustered index and that could be reason why it needs locks on two pages.

Is that correct?

Secondly, please help confirm my understanding of this deadlock is correct.
For simplicity sake lets assume that here are only two pages in that non clustered index. Page1 and Page2. So is this what is roughly happening
enter image description here

Best Answer

So did some testing and the deadlock is in fact caused by page movement within the non clustered index caused by an update which changes the position of the row in the index.

enter image description here

So initially Session 2 (update) takes out IX lock on Page2 where the index row is originally located. Since the update would change the value and in turn change the position of the index row within the index, session 2 will also want to take out another IX lock on the target page where the index row will end up after the update.

For understanding the reason why a select would want to hold on to S lock please refer to this question.

REPO

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
Related Question