Я пытаюсь решить исключение взаимоблокировки в приложении весенней загрузки, работающем с JPA. Я получаю следующую ошибку в журнале весенней загрузки.
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
Когда я захожу в журнал SSMS и смотрю отчет об ошибках взаимоблокировки, я вижу следующее
<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>
Странно то, что кажется, что он блокируется во время нескольких запросов выбора, хотя я думал, что это должно происходить только во время выбора - вставка/обновление
ОБНОВИТЬ:
Основываясь на комментариях ниже, я также добавил журнал запросов Hibernate, который фактически показывает кучу вставок и выборок, происходящих параллельно.
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 (?))
Кто-нибудь знает, как это решить?
nvarchar(4000)
, предлагая типы строк по умолчанию вместо соответствия фактическому типу SQL. ORM печально известны этим и могут вызвать серьезные проблемы с производительностью, особенно со столбцами varchar и устаревшими сопоставлениями SQL. - person Dan Guzman   schedule 24.02.2020