deadlock on a table while insert and update

Question

deadlock on a table while insert and update

Vijay Singh 21

<deadlock> <victim-list> <victimProcess id="process2589ab55c28"/> </victim-list> <process-list> <process id="process2589ab55c28" taskpriority="0" logused="1124" waitresource="KEY: 5:72057594074693632 (7af3533b9a23)" waittime="4794" ownerId="18823940" transactionname="UPDATE" lasttranstarted="2021-12-25T09:33:53.677" XDES="0x258ab1dc428" lockMode="X" schedulerid="2" kpid="7724" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-25T09:33:53.677" lastbatchcompleted="2021-12-25T09:33:53.673" lastattention="2021-12-24T17:15:11.780" clientapp="EDISPHERE" hostname="TEST-STATION-02" hostpid="30132" loginname="transactions" isolationlevel="read committed (2)" xactid="18823940" currentdb="5" currentdbname="EDISPHERE" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="EDISPHERE.transactions.ABI_SP_UPDATE_JOBQUEUE" line="26" stmtstart="1264" stmtend="1442" sqlhandle="0x030005009ecb9c4ffb8f8800faad000001000000000000000000000000000000000000000000000000000000"> UPDATE transactions.JobQueue with (rowlock,HOLDLOCK) SET JOBSTATE=@P01 WHERE RECNO=@nP0 </frame> </executionStack> <inputbuf> Proc [Database Id = 5 Object Id = 1335675806] </inputbuf> </process> <process id="process258a37b5848" taskpriority="0" logused="68288" waitresource="KEY: 5:72057594060210176 (f9e00a65a0f8)" waittime="4793" ownerId="18823895" transactionname="tNewFile" lasttranstarted="2021-12-25T09:33:53.530" XDES="0x2580e1c4428" lockMode="U" schedulerid="2" kpid="6840" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-25T09:33:53.530" lastbatchcompleted="2021-12-25T09:33:53.523" lastattention="1900-01-01T00:00:00.523" clientapp="EDISPHERE" hostname="TEST-STATION-02" hostpid="30132" loginname="transactions" isolationlevel="read committed (2)" xactid="18823895" currentdb="5" currentdbname="EDISPHERE" lockTimeout="4294967295" clientoption1="673579040" clientoption2="128056"> <executionStack> <frame procname="EDISPHERE.transactions.ABI_SP_GETNEWFILES" line="70" stmtstart="4504" stmtend="4756" sqlhandle="0x03000500b6227729ed572b00faad000001000000000000000000000000000000000000000000000000000000"> Update transactions.Jobqueue --with (UPDLOCK, HOLDLOCK) set JobState=31 where JobState=@nJobState and ServerID=@strServerI </frame> </executionStack> <inputbuf> Proc [Database Id = 5 Object Id = 695673526] </inputbuf> </process> </process-list> <resource-list> <keylock hobtid="72057594074693632" dbid="5" objectname="EDISPHERE.transactions.JobQueue" indexname="jobstate_priority_pickfile_tdate" id="lock258ab847680" mode="RangeS-U" associatedObjectId="72057594074693632"> <owner-list> <owner id="process258a37b5848" mode="RangeS-U"/> </owner-list> <waiter-list> <waiter id="process2589ab55c28" mode="X" requestType="wait"/> </waiter-list> </keylock> <keylock hobtid="72057594060210176" dbid="5" objectname="EDISPHERE.transactions.JobQueue" indexname="JobQueue_PK" id="lock2585ff5bd00" mode="X" associatedObjectId="72057594060210176"> <owner-list> <owner id="process2589ab55c28" mode="X"/> </owner-list> <waiter-list> <waiter id="process258a37b5848" mode="U" requestType="wait"/> </waiter-list> </keylock> </resource-list> </deadlock>

jobqueue table structure on which deadlock is occurring

-------------------------------------------------------------

CREATE TABLE [transactions].JobQueue ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Foreign key

-------------

ALTER TABLE [transactions].[JobQueue] WITH CHECK ADD CONSTRAINT [FK_JobQueue_EdiFile] FOREIGN KEY([FileID])
REFERENCES [transactions].[EdiFile] ([FileId])
GO

ALTER TABLE [transactions].[JobQueue] CHECK CONSTRAINT [FK_JobQueue_EdiFile]
GO

ALTER TABLE [transactions].[JobQueue] WITH CHECK ADD CONSTRAINT [FK_JobQueue_idTransaction] FOREIGN KEY([TransactionID])
REFERENCES [transactions].[idTransaction] ([ID])
GO

ALTER TABLE [transactions].[JobQueue] CHECK CONSTRAINT [FK_JobQueue_idTransaction]
GO

ALTER TABLE [transactions].[JobQueue] WITH CHECK ADD CONSTRAINT [FK_JobQueue_Tenant] FOREIGN KEY([TenantId])
REFERENCES [masters].[Tenant] ([TenantId])
GO

ALTER TABLE [transactions].[JobQueue] CHECK CONSTRAINT [FK_JobQueue_Tenant]
GO

indexes

-----------

ALTER TABLE [transactions].[JobQueue] ADD CONSTRAINT [JobQueue_PK] PRIMARY KEY CLUSTERED
(
[RecNo] ASC
)
go

CREATE NONCLUSTERED INDEX [jobstate_priority_pickfile_tdate] ON [transactions].[JobQueue]
(
[JobState] ASC,
[Priority] ASC,
[PickFileOneAtATime] ASC,
[TranslateDate] ASC
)
INCLUDE([RecNo],[ServerID],[RecvSourceDir])
go

CREATE NONCLUSTERED INDEX [Jobstate_ServerId_peiority] ON [transactions].[JobQueue]
(
[JobState] ASC,
[ServerID] ASC,
[Priority] ASC
)
INCLUDE([RecNo],[Logkey],[FileName],[ReceivedTime],[RecvSourceDir],[TransactionID],[FileID],[TenantId],[Info])
go

CREATE NONCLUSTERED INDEX [PICKFILEONEATATIME_NDX] ON [transactions].[JobQueue]
(
[PickFileOneAtATime] ASC
)
INCLUDE([RecNo])
go

CREATE NONCLUSTERED INDEX [Priority_NDX] ON [transactions].[JobQueue]
(
[Priority] ASC
)
INCLUDE([RecNo])
go

Ronen Ariely 15,206 Reputation points

2021-12-27T13:46:04.687+00:00

You have deadlock of two UPDATE transaction. What is the question?

3 answers

Your answer

Ronen Ariely 15,206 Reputation points

2021-12-27T13:46:04.687+00:00

You have deadlock of two UPDATE transaction. What is the question?

Answer 1

Tom Phillips 17,771

Deadlocks are something you try to avoid, not something you "fix". There is always an opportunity in a multi-user system for deadlocks. This is perfectly normal and expected behavior. Your application needs to be able to recover from deadlocks, generally by retrying the operation again.

https://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock
https://stackoverflow.com/questions/320636/how-to-get-efficient-sql-server-deadlock-handling-in-c-sharp-with-ado

Vijay Singh 21 Reputation points

2021-12-29T05:24:17.997+00:00

Hi TomPhillips-1744 Thanks for the suggestion. We are retrying after deadlock. My effort is to avoid it.

Answer 2

Tom Phillips 17,771

In addition, your code is using:
UPDATE transactions.JobQueue with (rowlock,HOLDLOCK)
You are specifying "HOLDLOCK" which is going to cause likely of deadlocks.

Vijay Singh 21 Reputation points

2021-12-30T03:43:05.633+00:00

TomPhillips thanks for the suggestion. After removing HOLDLOCK occurrence of deadlock has been reduced but it is still occurring.
Tom Phillips 17,771 Reputation points

2021-12-30T14:27:54.373+00:00
You are not going to be able to totally eliminate them. You have 2 processes apparently trying to update the same data in the same table.

Not sure if this will help or not, but I would change "EDISPHERE.transactions.ABI_SP_GETNEWFILES" from:

Update transactions.Jobqueue --with (UPDLOCK, HOLDLOCK) set JobState=31 where JobState=@nJobState and ServerID=@strServerI

to:

Update transactions.Jobqueue set JobState=31 where RecNo IN (SELECT RecNo FROM transactions.Jobqueue WHERE JobState=@nJobState and ServerID=@strServerI)
Erland Sommarskog 122K Reputation points MVP Volunteer Moderator

2021-12-30T22:43:46.983+00:00

Yes, Tom's query may eliminate the deadlock, because the UPDLOCK will now (probably) for both queries be taken at the key in the CI index. Since the queries are taking the locks in the same order, the deadlock should not occur.

Unfortunately, this can lead to other problems. Say that the @nJobState has the value 45,T his query comes along and finds the Recno 99 has state 45. But when it comes to perform the update, the other process has a UPDLOCK on the row and sets the jobstate to 0. That update is then lost when the query above completes and sets jobState to 31. Whether this is an actual problem, I don't know, but you need to analyse it.
Vijay Singh 21 Reputation points

2022-01-04T06:28:55.5+00:00

Hi TomPhillips, I have changed SQL script as per your suggestion. But one deadlock is still occurring in a day during 24x7 test. Deadlock detail is as

First processess is executing this sql
Update TRANSACTIONS.Jobqueue --with (UPDLOCK) set JobState=@nJobStateBusy where recno in (select recno from TRANSACTIONS.Jobqueue where JobState=@nJobStateAlllocated and ServerID=@strServerID

Second processess is executing below
Update transactions.Jobqueue --with (UPDLOCK) set JobState=31 where recno in (select recno from transactions.Jobqueue where JobState=@nJobState and ServerID=@strServerID

Please suggest is there any possibility to resolve the issue?
Erland Sommarskog 122K Reputation points MVP Volunteer Moderator

2022-01-04T22:20:40.74+00:00

So how does the deadlock graph look these days?

You can probably work around the deadlock by inserting the result of the subquery into a temp table or table variable and use that in the UPDATE.

But please read my discussion above about the risk for lost updates. Make sure you understand what I'm talking about and make your analysis before you go ahead.

Answer 3

First of all, one of the processes is running a multi-statement transaction, so it is perfectly possible that locks in involved in the deadlock were taken prior to the statement displayed in the deadlock.

On the other hand, it is perfectly understandable that these two UPDATE statements are clashing. As Tom points out, one of the processes uses the HOLDLOCK hint, which is dubious. HOLDLOCK is a synonym to SERIALIZABLE, and this isolation level leads to range locks which are wider that strict row locks. With only row locks, there can only be a clash if they try to update the same row. Now there can be a clash also if they are updating adjacent rows.

Both processes are updating the JOBSTATE column, and this column is indexed. This means that they must both update the data page and the index page. One process arrive to the row through the RECNO column which is the clustered index, so it starts with taking an UPDATE lock on this level, and then try to lock the index row. But the other process comes in through the index on JOBSTATE and first takes a UPDATE lock here, and then tries to lock the data page. Which is already locked by the first process. Ergo: deadlock.

How this should be resolved, I don't know (save that removing HOLDLOCK may help), because I don't how these tables are used. There are a number of options, including retry on deadlock, a low-priorty process using SET LOCK_TIMEOUT to back out early, or serialisation with application locks. To name a few options.

Share via

deadlock on a table while insert and update

3 answers

Your answer