这是表格(简化):
Table "public.link"
Column | Type | Modifiers
---------------+-----------------------------+---------------------------------------------------
id | integer | not null default nextval('link_id_seq'::regclass)
page_id | integer |
placed_at | timestamp without time zone | default now()
Indexes:
"link_pkey" PRIMARY KEY, btree (id)
"link_page_id_index" btree (page_id)
Foreign-key constraints:
"link_page_id_foreign_key" FOREIGN KEY (page_id) REFERENCES page(id) ON UPDATE RESTRICT ON DELETE RESTRICT
这里是查询(简化):
UPDATE link SET page_id = ?, placed_at = now() WHERE id IN ( SELECT id FROM link ... ) AND page_id IS NOT NULL
死锁消息:
ERROR: deadlock detected
Detail: Process 5822 waits for ShareLock on transaction 19705; blocked by process 5821.
Process 5821 waits for ShareLock on transaction 19706; blocked by process 5822.
Hint: See server log for query details.
How can that query, executed in parallel by several processes, lead to deadlock ?
谢谢!
2 回答
会话A尝试更新ID 10,2,30,4,会话B尝试更新40,30,20,10
他们都试图锁定他们各自的行准备好更新,A得到10并等待30,而B得到30并等待10.死锁 .
您的基本问题是您尝试在并发事务中更新(某些)相同的ID .
如果不知道您的数据库结构以及您正在尝试做什么,就很难建议最佳解决方案 . 通常,您要么确保不同的后端不更新相同的行,要么减少超时,并在随机暂停后重试 .
在大多数情况下死锁到来是因为行之间的循环等待会更新,所以如果你想解决死锁,你可以简单地使用你要更新的行的排序