grails - avoiding deadlock by disabling ALLOW_PAGE_LOCKS in sql server -

i have grails application exposes api bit dml heavy underneath. dmls happening via gorm , not using sql directly. when api hit concurrently, running deadlocks , keylock on primary key part of deadlock log. here snippet of log 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 evolutions 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 evolutions 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 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 evolutionruns 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 evolutionruns 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 "evolution" propagates delete "evolutionruns" , on , forth other tables due triggers, because of cascade delete.

table evolution has trigger defined on follows:

alter trigger [dbo].[tr_del_evolutions] on [dbo].[evolutions] delete delete evolutionruns evolutionruns inner join deleted on deleted.evolutionid=evolutionruns.evolutionid

table participanttrace has constrain defined as:

alter table [dbo].[participanttrace] 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] check add constraint [fk_critters_evolutionruns] foreign key([evolutionrunid]) references [dbo].[evolutionruns] ([evolutionrunid]) on delete cascade

• i have added indexes on referential keys didn't @ all.
• i tried toggling transaction in grails service , didn't either. have domains setup correct belongsto/hasmany relationship, since deletes happening via cascade delete not sure gorm has it!

i have found disabling allow_page_locks solves deadlock issue me, of blogs recommend on not doing , instead add indexes properly.

is there anyways can solve keylock + deadlock issue?

your suggestions highly appreciated, thanks.

yes have deadlock due cascadind deleting. here graph shortly:

victim=process59494c8

has ranges-u on pk_participanttrace

waits ranges-u on pk_critters

winner =process4a1fb88

has rangex-x on keylock pk_critters (epm-dev.dbo.critters)

waits ranges-u on keylock pk_participanttrace (epm-dev.dbo.participanttrace)

2 processes access 2 tables in inverse order, 1 waits lock on second table holding lock on first , waites lock on first while holding lock on second. both processes think delete 1 table instead delete both, in different order