I am trying to solve a deadlock exception in a spring-boot application running with JPA. I get the following error in the spring boot log.
24 Feb 2020 10:04:11.382 [WARN ] {pool-10-thread-4} [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Warning Code: 0, SQLState: null
24 Feb 2020 10:04:11.382 [WARN ] {pool-10-thread-4} [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] [TDS Driver]#9 Done Error
24 Feb 2020 10:04:11.521 [WARN ] {pool-10-thread-10} [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 1205, SQLState: 40000
24 Feb 2020 10:04:11.521 [ERROR] {pool-10-thread-10} [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] [HLKVM-SQL14]Transaction (Process ID 724) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 1205, Level 13, State 51, Line 1, Sqlstate 40000
When I go to SSMS log and look at the deadloack error report I see the following
<victim-list>
<victimProcess id="process255c1703468" />
</victim-list>
<process-list>
<process id="process255c1703468" taskpriority="0" logused="1512" waitresource="KEY: 152:72057594042449920 (350aa4acce8b)" waittime="2342" ownerId="357788483" transactionname="user_transaction" lasttranstarted="2020-02-24T10:03:27.493" XDES="0x2591c278428" lockMode="S" schedulerid="4" kpid="8188" status="suspended" spid="724" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-02-24T10:03:58.097" lastbatchcompleted="2020-02-24T10:03:58.090" lastattention="1900-01-01T00:00:00.090" clientapp="i-net MERLIA" hostname="DEV-sajith" hostpid="8" loginname="hlk_sajith" isolationlevel="read committed (2)" xactid="357788483" currentdb="152" currentdbname="hlk_core_sajith3" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" stmtend="370" sqlhandle="0x020000006418f807081a3d3e4efe882724f1493cc0b489990000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_execute" line="1" stmtstart="-1" sqlhandle="0x0400ff7f47dacef5010000000000000000000000000000000000000000000000000000000000000000000000">
sp_execute </frame>
</executionStack>
<inputbuf>
(@p1 nvarchar(4000),@p2 nvarchar(4000))select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type= @p1 and (forcedid0_.forced_id in ( @p2 )) </inputbuf>
</process>
<process id="process25566f26ca8" taskpriority="0" logused="1512" waitresource="KEY: 152:72057594042449920 (350aa4acce8b)" waittime="2342" ownerId="357788469" transactionname="user_transaction" lasttranstarted="2020-02-24T10:03:27.487" XDES="0x25a9e868428" lockMode="S" schedulerid="3" kpid="5588" status="suspended" spid="617" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-02-24T10:03:58.090" lastbatchcompleted="2020-02-24T10:03:58.080" lastattention="1900-01-01T00:00:00.080" clientapp="i-net MERLIA" hostname="DEV-sajith" hostpid="4" loginname="hlk_sajith" isolationlevel="read committed (2)" xactid="357788469" currentdb="152" currentdbname="hlk_core_sajith3" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" stmtend="370" sqlhandle="0x020000006418f807081a3d3e4efe882724f1493cc0b489990000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_execute" line="1" stmtstart="-1" sqlhandle="0x0400ff7f47dacef5010000000000000000000000000000000000000000000000000000000000000000000000">
sp_execute </frame>
</executionStack>
<inputbuf>
(@p1 nvarchar(4000),@p2 nvarchar(4000))select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type= @p1 and (forcedid0_.forced_id in ( @p2 )) </inputbuf>
</process>
<process id="process256ead0f088" taskpriority="0" logused="1512" waitresource="KEY: 152:72057594042449920 (ba121720a3f7)" waittime="155" ownerId="357788485" transactionname="user_transaction" lasttranstarted="2020-02-24T10:03:27.500" XDES="0x2591c200428" lockMode="S" schedulerid="1" kpid="10448" status="suspended" spid="743" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-02-24T10:03:58.090" lastbatchcompleted="2020-02-24T10:03:58.080" lastattention="1900-01-01T00:00:00.080" clientapp="i-net MERLIA" hostname="DEV-sajith" hostpid="10" loginname="hlk_sajith" isolationlevel="read committed (2)" xactid="357788485" currentdb="152" currentdbname="hlk_core_sajith3" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" stmtend="370" sqlhandle="0x020000006418f807081a3d3e4efe882724f1493cc0b489990000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_execute" line="1" stmtstart="-1" sqlhandle="0x0400ff7f47dacef5010000000000000000000000000000000000000000000000000000000000000000000000">
sp_execute </frame>
</executionStack>
<inputbuf>
(@p1 nvarchar(4000),@p2 nvarchar(4000))select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type= @p1 and (forcedid0_.forced_id in ( @p2 )) </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594042449920" dbid="152" objectname="hlk_core_sajith3.dbo.hfj_forced_id" indexname="PK__hfj_forc__DD37D91A6B9E67CF" id="lock25928f5fc00" mode="X" associatedObjectId="72057594042449920">
<owner-list>
<owner id="process25566f26ca8" mode="S" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process255c1703468" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594042449920" dbid="152" objectname="hlk_core_sajith3.dbo.hfj_forced_id" indexname="PK__hfj_forc__DD37D91A6B9E67CF" id="lock25928f5fc00" mode="X" associatedObjectId="72057594042449920">
<owner-list>
<owner id="process256ead0f088" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process25566f26ca8" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594042449920" dbid="152" objectname="hlk_core_sajith3.dbo.hfj_forced_id" indexname="PK__hfj_forc__DD37D91A6B9E67CF" id="lock256b4736500" mode="X" associatedObjectId="72057594042449920">
<owner-list>
<owner id="process255c1703468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process256ead0f088" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
The odd thing is it seems to lock during multiple select queries, while I thought this should only happen during a select – insert/update
UPDATE:
Based on the comments below, I have also added the Hibernate query log which actually shows a bunch of inserts and selects happening in parallel
Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_resource (res_deleted_at, res_version, forced_id_pid, has_tags, res_published, res_updated, sp_has_links, hash_sha256, sp_index_status, res_language, sp_cmpstr_uniq_present, sp_coords_present, sp_date_present, sp_number_present, sp_quantity_present, sp_string_present, sp_token_present, sp_uri_present, res_profile, res_type, res_ver, res_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: insert into hfj_forced_id (forced_id, resource_pid, resource_type, pid) values (?, ?, ?, ?) Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: update hfj_resource set res_deleted_at=?, res_version=?, forced_id_pid=?, has_tags=?, res_published=?, res_updated=?, sp_has_links=?, hash_sha256=?, sp_index_status=?, res_language=?, sp_cmpstr_uniq_present=?, sp_coords_present=?, sp_date_present=?, sp_number_present=?, sp_quantity_present=?, sp_string_present=?, sp_token_present=?, sp_uri_present=?, res_profile=?, res_type=?, res_ver=? where res_id=? and res_ver=? Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?)) Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?)) Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?)) Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?)) Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?)) Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?)) Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?)) Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?)) Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?)) Hibernate: select forcedid0_.resource_pid as col_0_0_ from hfj_forced_id forcedid0_ where forcedid0_.resource_type=? and (forcedid0_.forced_id in (?))
Does anyone have any insight on solving this?
Advertisement
Answer
Based on the comments to the comment, some some further research by the author, the actual deadlock was bypassed by setting the isolation level to READ_UNCOMMITED
https://www.sqlservercentral.com/articles/isolation-levels-in-sql-server
While this is not recommended for every usecase, for this particular scenario it was good enough