postgresql postgres 中简单更新查询的死锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18297478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
deadlock in postgres on simple update query
提问by moshe
I'm working with postgres 9.1 and getting deadlock exception under excessive execution of a simple update method.
我正在使用 postgres 9.1 并在过度执行简单的更新方法时出现死锁异常。
According to the logs the deadlock occurs due to execution of two identical updates at the same time.
根据日志，由于同时执行两个相同的更新而发生死锁。
update public.vm_action_info set last_on_demand_task_id=$1, version=version+1
更新 public.vm_action_info 设置 last_on_demand_task_id=$1, version=version+1
How does two identical simple updates can deadlock each other ?
两个相同的简单更新如何相互死锁？
The error that I'm getting in the log
我在日志中得到的错误
2013-08-18 11:00:24 IDT HINT: See server log for query details.
2013-08-18 11:00:24 IDT STATEMENT: update public.vm_action_info set last_on_demand_task_id=, version=version+1 where id=
2013-08-18 11:00:25 IDT ERROR: deadlock detected
2013-08-18 11:00:25 IDT DETAIL: Process 31533 waits for ShareLock on transaction 4228275; blocked by process 31530.
Process 31530 waits for ExclusiveLock on tuple (0,68) of relation 70337 of database 69205; blocked by process 31533.
Process 31533: update public.vm_action_info set last_on_demand_task_id=, version=version+1 where id=
Process 31530: update public.vm_action_info set last_on_demand_task_id=, version=version+1 where id=
2013-08-18 11:00:25 IDT HINT: See server log for query details.
2013-08-18 11:00:25 IDT STATEMENT: update public.vm_action_info set last_on_demand_task_id=, version=version+1 where id=
2013-08-18 11:00:25 IDT ERROR: deadlock detected
2013-08-18 11:00:25 IDT DETAIL: Process 31530 waits for ExclusiveLock on tuple (0,68) of relation 70337 of database 69205; blocked by process 31876.
Process 31876 waits for ShareLock on transaction 4228275; blocked by process 31530.
Process 31530: update public.vm_action_info set last_on_demand_task_id=, version=version+1 where id=
Process 31876: update public.vm_action_info set last_on_demand_task_id=, version=version+1 where id=
the schema is:
架构是：
CREATE TABLE vm_action_info(
id integer NOT NULL,
version integer NOT NULL DEFAULT 0,
vm_info_id integer NOT NULL,
last_exit_code integer,
bundle_action_id integer NOT NULL,
last_result_change_time numeric NOT NULL,
last_completed_vm_task_id integer,
last_on_demand_task_id bigint,
CONSTRAINT vm_action_info_pkey PRIMARY KEY (id ),
CONSTRAINT vm_action_info_bundle_action_id_fk FOREIGN KEY (bundle_action_id)
REFERENCES bundle_action (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT vm_discovery_info_fk FOREIGN KEY (vm_info_id)
REFERENCES vm_info (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT vm_task_last_on_demand_task_fk FOREIGN KEY (last_on_demand_task_id)
REFERENCES vm_task (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT vm_task_last_task_fk FOREIGN KEY (last_completed_vm_task_id)
REFERENCES vm_task (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE vm_action_info
OWNER TO vadm;
-- Index: vm_action_info_vm_info_id_index
-- DROP INDEX vm_action_info_vm_info_id_index;
CREATE INDEX vm_action_info_vm_info_id_index
ON vm_action_info
USING btree (vm_info_id );
CREATE TABLE vm_task
(
id integer NOT NULL,
version integer NOT NULL DEFAULT 0,
vm_action_info_id integer NOT NULL,
creation_time numeric NOT NULL DEFAULT 0,
task_state text NOT NULL,
triggered_by text NOT NULL,
bundle_param_revision bigint NOT NULL DEFAULT 0,
execution_time bigint,
expiration_time bigint,
username text,
completion_time bigint,
completion_status text,
completion_error text,
CONSTRAINT vm_task_pkey PRIMARY KEY (id ),
CONSTRAINT vm_action_info_fk FOREIGN KEY (vm_action_info_id)
REFERENCES vm_action_info (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE vm_task
OWNER TO vadm;
-- Index: vm_task_creation_time_index
-- DROP INDEX vm_task_creation_time_index ;
CREATE INDEX vm_task_creation_time_index
ON vm_task
USING btree
(creation_time );
回答by krokodilko
My guess is that the source of the problem is a circular foreign key reference in your tables.
TABLE vm_action_info
==> FOREIGN KEY (last_completed_vm_task_id) REFERENCES vm_task (id)
TABLE vm_task
==> FOREIGN KEY (vm_action_info_id) REFERENCES vm_action_info (id)
The transaction consists of two steps:
我的猜测是问题的根源是表中的循环外键引用。
TABLE vm_action_info
==> FOREIGN KEY (last_completed_vm_task_id) REFERENCES vm_task (id)
TABLE vm_task
==> FOREIGN KEY (vm_action_info_id) REFERENCES vm_action_info (id)
事务包括两个步骤：
- add a new entry to task table
- updates corresponding entry in vm_action_info the vm_task table.
- 向任务表添加新条目
- 更新 vm_task 表中 vm_action_info 中的相应条目。
When two transactions are going to update the same record in the vm_action_infotable at the same time, this will finish with a deadlock.
Look at simple test case:
当两个事务同时更新vm_action_info表中的同一条记录时，这将以死锁结束。
看一个简单的测试用例：
CREATE TABLE vm_task
(
id integer NOT NULL,
version integer NOT NULL DEFAULT 0,
vm_action_info_id integer NOT NULL,
CONSTRAINT vm_task_pkey PRIMARY KEY (id )
)
WITH ( OIDS=FALSE );
insert into vm_task values
( 0, 0, 0 ), ( 1, 1, 1 ), ( 2, 2, 2 );
CREATE TABLE vm_action_info(
id integer NOT NULL,
version integer NOT NULL DEFAULT 0,
last_on_demand_task_id bigint,
CONSTRAINT vm_action_info_pkey PRIMARY KEY (id )
)
WITH (OIDS=FALSE);
insert into vm_action_info values
( 0, 0, 0 ), ( 1, 1, 1 ), ( 2, 2, 2 );
alter table vm_task
add CONSTRAINT vm_action_info_fk FOREIGN KEY (vm_action_info_id)
REFERENCES vm_action_info (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
;
Alter table vm_action_info
add CONSTRAINT vm_task_last_on_demand_task_fk FOREIGN KEY (last_on_demand_task_id)
REFERENCES vm_task (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
;
In session 1 we add a record to vm_task that reference to id=2 in vm_action_info
在会话 1 中，我们向 vm_task 添加一条记录，该记录引用 vm_action_info 中的 id=2
session1=> begin;
BEGIN
session1=> insert into vm_task values( 100, 0, 2 );
INSERT 0 1
session1=>
At the same time in session 2 an another transaction begins:
同时在会话 2 中开始另一个事务：
session2=> begin;
BEGIN
session2=> insert into vm_task values( 200, 0, 2 );
INSERT 0 1
session2=>
Then the 1st transaction performs the update:
然后第一个事务执行更新：
session1=> update vm_action_info set last_on_demand_task_id=100, version=version+1
session1=> where id=2;
but this command hangs and is waiting for a lock.....
then the 2nd session performs the update ........
但是此命令挂起并等待锁定.....
然后第二个会话执行更新........
session2=> update vm_action_info set last_on_demand_task_id=200, version=version+1 where id=2;
B??D: wykryto zakleszczenie
SZCZEGó?Y: Proces 9384 oczekuje na ExclusiveLock na krotka (0,5) relacji 33083 bazy danych 16393; zablokowany przez 380
8.
Proces 3808 oczekuje na ShareLock na transakcja 976; zablokowany przez 9384.
PODPOWIED?: Przejrzyj dziennik serwera by znale?? szczegó?y zapytania.
session2=>
Deadlock detected !!!
This is because both INSERTs into vm_task place a shared lock on row id=2 in the vm_action_info table due to the foreign key reference. Then the first update tries to place a write lock on this row and hangs because the row is locked by another (second) transaction. Then the second update tries to lock the same record in write mode, but it is locked in shared mode by the first transaction. And this cause a deadlock.
I think that this can be avoided if you place a write lock on record in vm_action_info, the whole transaction has to consist of 5 steps:
检测到死锁！！！
这是因为由于外键引用，到 vm_task 的两个 INSERT 都在 vm_action_info 表中的行 id=2 上放置了共享锁。然后第一次更新尝试在该行上放置一个写锁并挂起，因为该行被另一个（第二个）事务锁定。然后第二次更新尝试在写模式下锁定同一条记录，但它在共享模式下被第一个事务锁定。这会导致僵局。
我认为如果您在 vm_action_info 中对记录设置写锁，则可以避免这种情况，整个事务必须包括 5 个步骤：
begin;
select * from vm_action_info where id=2 for update;
insert into vm_task values( 100, 0, 2 );
update vm_action_info set last_on_demand_task_id=100,
version=version+1 where id=2;
commit;
回答by Richard Huxton
It may just be that your system was exceptionally busy. You say you've only seen this with "excessive execution" of the query.
可能只是您的系统异常繁忙。你说你只在查询的“过度执行”中看到过这种情况。
What appears to be the situation is this:
看起来的情况是这样的：
pid=31530 wants to lock tuple (0,68) on rel 70337 (vm_action_info I suspect) for update
it is waiting behind pid=31533, pid=31876
pid=31533 is waiting behind transaction 4228275
pid=31876 is waiting behind transaction 4228275
So - we have what seems to be four transactions all updating this row at the same time. Transaction 4228275 hasn't committed or rolled back yet and is holding the others up. Two of them have been waiting for deadlock_timeoutseconds otherwise we'd not see the timeout. Timout expires, deadlock detector takes a look, sees a bunch of intertwined transactions and cancels one of them. Might not strictly be a deadlock, but I'm not sure if the detector is smart enough to figure that out.
所以 - 我们似乎有四个事务同时更新这一行。事务 4228275 尚未提交或回滚，并且正在阻止其他事务。其中两个一直在等待deadlock_timeout秒，否则我们不会看到超时。超时到期，死锁检测器查看，看到一堆相互交织的事务并取消其中一个。严格来说可能不是死锁，但我不确定检测器是否足够聪明来解决这个问题。
Try one of:
尝试以下之一：
- Reduce the rate of updates
- Get a faster server
- Increase deadlock_timeout
- 降低更新率
- 获得更快的服务器
- 增加 deadlock_timeout
Probably #3 is the simplest :-) Might want to set log_lock_waits too so you can see if/when your system is under this sort of strain.
可能 #3 是最简单的 :-) 可能也想设置 log_lock_waits 以便您可以查看您的系统是否/何时处于这种压力之下。
