許多並行查詢掛起但沒有死鎖

sql 是一個很長的多查詢，例如 select-join。所有鍵都被索引。當我使用 70 個連接連續查詢執行負載測試時，它們都在某個時候停止接收任何網路響應（在我設置命令超時 = 0 之前是異常）。我看到許多 AccessSharedLocks 是在 15 分鐘前開始的！同樣的查詢也有幾個 ExclusiveLocks（為什麼？）。在 pgAdmin 中，這個查詢在 12 毫秒內執行（同時！）。磁碟和 CPU 活動都很低。

SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

• 返回 0 行，因此它們不等待鎖定。

所有查詢都有waiting = 'f'

我該如何解決？

（可點擊）

select user0_.id as id73_0_, user0_.nickname as nickname73_0_, ..., challenges1_.start_at as start2_78_1_, challenges1_.counter as counter78_1_, challenges1_.user_id as user5_78_1_, challenges1_.challenge_id as challenge6_78_1_, challenges1_.user_id as user5_0__, challenges1_.id as id0__ from public.users user0_ left outer join public.user_challenges challenges1_ on user0_.id=challenges1_.user_id where user0_.id in ([single id]);

SELECT this_.id as id73_4_,..., characters2_.user_id as user28_6_, characters2_.id as id6_, characters2_.id as id1_0_, ..., smartchall3_.character_id as character3_7_, smartchall3_.id as id7_, smartchall3_.id as id79_1_, ..., userupdate4_.id as id99_2_, userupdate4_.coins as coins99_2_, userupdate4_.gold as gold99_2_, ..., userupdate4_.tournament_xp as tournament10_99_2_, userupdate4_.tournament_class_xp as tournament11_99_2_, userupdate4_.tournament_xp_in_current_guild as tournament12_99_2_, userupdate4_.last_login_guild_user_leave_counter as last13_99_2_, userupdate4_.last_login_guild_user_join_counter as last14_99_2_, ..., user5_.id as id73_3_, user5_.nickname as nickname73_3_, ... FROM public.users this_ left outer join public.characters characters2_ on this_.id=characters2_.user_id left outer join public.user_character_smart_challenge_trackers smartchall3_ on characters2_.id=smartchall3_.character_id left outer join public.users_updateable_data userupdate4_ on this_.id=userupdate4_.id left outer join public.users user5_ on userupdate4_.id=user5_.id WHERE this_.id in ([single id])

請注意，添加 SSD 會以某種方式將此問題推遲到更高的負載。

我在客戶端使用 Npgsql。所有客戶端連接都在等待Socket.Receive。

我的日誌設置是：

log_min_duration_statement = 1000   # -1 is disabled, 0 logs all statements
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_lock_waits = on         # log lock waits >= deadlock_timeout
#log_statement = 'none'         # none, ddl, mod, all
log_temp_files = 0 # 0 logs all temp files

日誌文件在這裡https://1drv.ms/u/s!AhAvFYRsqyUIg6tvjXHPxe8iabOFWA

該問題是由 Npgsql 中的錯誤引起的，現在更新到最新版本後可以正常工作。

引用自：https://dba.stackexchange.com/questions/183045