I have a grails application that exposes an api that is bit DML heavy underneath. All the DMLs are happening via GORM and I am not using Sql directly. When this api is hit concurrently, I am running into deadlocks very frequently and almost always this keylock on primary key is always a part of the deadlock log. Here is snippet of the log from sql server:
Date,Source,Severity,Message
09/13/2017 17:02:13,spid21s,Unknown,waiter id=process4a1fb88 mode=RangeS-U requestType=wait
09/13/2017 17:02:13,spid21s,Unknown,waiter-list
09/13/2017 17:02:13,spid21s,Unknown,owner id=process59494c8 mode=RangeS-U
09/13/2017 17:02:13,spid21s,Unknown,owner-list
09/13/2017 17:02:13,spid21s,Unknown,keylock hobtid=72057594142588928 dbid=17 objectname=EPM-DEV.dbo.ParticipantTrace indexname=PK_ParticipantTrace id=lock8d0c6c80 mode=RangeS-U associatedObjectId=72057594142588928
09/13/2017 17:02:13,spid21s,Unknown,waiter id=process59494c8 mode=RangeS-U requestType=wait
09/13/2017 17:02:13,spid21s,Unknown,waiter-list
09/13/2017 17:02:13,spid21s,Unknown,owner id=process4a1fb88 mode=RangeX-X
09/13/2017 17:02:13,spid21s,Unknown,owner-list
09/13/2017 17:02:13,spid21s,Unknown,keylock hobtid=72057594142523392 dbid=17 objectname=EPM-DEV.dbo.Critters indexname=PK_Critters id=lock909d8a00 mode=RangeX-X associatedObjectId=72057594142523392
09/13/2017 17:02:13,spid21s,Unknown,resource-list
09/13/2017 17:02:13,spid21s,Unknown,(@P0 bigint)DELETE FROM evolutions WHERE evolutionId=@P0
09/13/2017 17:02:13,spid21s,Unknown,inputbuf
09/13/2017 17:02:13,spid21s,Unknown,unknown
09/13/2017 17:02:13,spid21s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
09/13/2017 17:02:13,spid21s,Unknown,DELETE FROM evolutions WHERE evolutionId=@P0
09/13/2017 17:02:13,spid21s,Unknown,frame procname=adhoc line=1 stmtstart=24 sqlhandle=0x02000000791d7d1fc5ed65e7287c3344e4d309e75a370674
09/13/2017 17:02:13,spid21s,Unknown,INNER JOIN deleted ON deleted.evolutionId=evolutionRuns.evolutionId
09/13/2017 17:02:13,spid21s,Unknown,FROM EvolutionRuns
09/13/2017 17:02:13,spid21s,Unknown,DELETE FROM EvolutionRuns
09/13/2017 17:02:13,spid21s,Unknown,frame procname=EPM-DEV.dbo.tr_del_Evolutions line=7 stmtstart=314 sqlhandle=0x030011005729580f72961b01ada700000000000000000000
09/13/2017 17:02:13,spid21s,Unknown,executionStack
09/13/2017 17:02:13,spid21s,Unknown,process id=process4a1fb88 taskpriority=0 logused=278800 waitresource=KEY: 17:72057594142588928 (ffffffffffff) waittime=9495 ownerId=18259173 transactionname=implicit_transaction lasttranstarted=2017-09-13T17:02:03.317 XDES=0xe5b7d970 lockMode=RangeS-U schedulerid=1 kpid=3088 status=suspended spid=222 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-09-13T17:02:03.580 lastbatchcompleted=2017-09-13T17:02:03.580 clientapp=PlatformService hostname=dshrestha hostpid=0 loginname=affiservUser isolationlevel=read committed (2) xactid=18259173 currentdb=17 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128058
09/13/2017 17:02:13,spid21s,Unknown,(@P0 bigint)DELETE FROM evolutionRuns WHERE evolutionId=@P0
09/13/2017 17:02:13,spid21s,Unknown,inputbuf
09/13/2017 17:02:13,spid21s,Unknown,unknown
09/13/2017 17:02:13,spid21s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
09/13/2017 17:02:13,spid21s,Unknown,DELETE FROM evolutionRuns WHERE evolutionId=@P0
09/13/2017 17:02:13,spid21s,Unknown,frame procname=adhoc line=1 stmtstart=24 sqlhandle=0x02000000c256370374cd8f1b213e95ec8d6768101b5b0230
09/13/2017 17:02:13,spid21s,Unknown,executionStack
09/13/2017 17:02:13,spid21s,Unknown,process id=process59494c8 taskpriority=0 logused=16832 waitresource=KEY: 17:72057594142523392 (ab422a83b86b) waittime=9503 ownerId=18259172 transactionname=implicit_transaction lasttranstarted=2017-09-13T17:02:03.317 XDES=0xe5773970 lockMode=RangeS-U schedulerid=2 kpid=8652 status=suspended spid=178 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-09-13T17:02:03.363 lastbatchcompleted=2017-09-13T17:02:03.357 clientapp=PlatformService hostname=dshrestha hostpid=0 loginname=affiservUser isolationlevel=read committed (2) xactid=18259172 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
09/13/2017 17:02:13,spid21s,Unknown,process-list
09/13/2017 17:02:13,spid21s,Unknown,deadlock victim=process59494c8
09/13/2017 17:02:13,spid21s,Unknown,deadlock-list
Deleting from "evolution" propagates delete to "evolutionRuns" and so on and so forth to some other tables due to triggers, as well as because of cascade delete.
Table evolution has trigger defined on it as follows:
ALTER TRIGGER [dbo].[tr_del_Evolutions] ON [dbo].[Evolutions] FOR DELETE AS DELETE FROM EvolutionRuns FROM EvolutionRuns INNER JOIN deleted ON deleted.evolutionId=evolutionRuns.evolutionId
table ParticipantTrace has constrain defined as:
ALTER TABLE [dbo].[ParticipantTrace] WITH CHECK ADD CONSTRAINT [FK_ParticipantTrace_EvolutionRuns] FOREIGN KEY([evolutionRunId]) REFERENCES [dbo].[EvolutionRuns] ([evolutionRunId]) ON DELETE CASCADE
table Critters has constrain defined as:
ALTER TABLE [dbo].[Critters] WITH CHECK ADD CONSTRAINT [FK_Critters_EvolutionRuns] FOREIGN KEY([evolutionRunId]) REFERENCES [dbo].[EvolutionRuns] ([evolutionRunId]) ON DELETE CASCADE
I have found that disabling ALLOW_PAGE_LOCKS solves the deadlock issue for me, but most of the blogs recommend on not doing so and instead add indexes properly.
Is there anyways I can solve for this keylock + deadlock issue?
Your suggestions are highly appreciated, thanks.
Yes you have a deadlock due to cascading deleting. Here is your graph shortly:
victim=process59494c8
has RangeS-U on PK_ParticipantTrace
waits for RangeS-U on PK_Critters
winner =process4a1fb88
has RangeX-X on keylock PK_Critters (EPM-DEV.dbo.Critters)
waits for RangeS-U on keylock PK_ParticipantTrace (EPM-DEV.dbo.ParticipantTrace)
2 Processes access this 2 tables in the inverse order, so one waits for a lock on the second table holding lock on a first and another waites for lock on the first while holding a lock on the second. Both processes think to delete from 1 table only but instead delete from both, in different order