我正在使用postgres 9.1,并在一个简单的更新方法的过度执行下获得死锁异常.
根据日志,由于同时执行两个相同的更新而发生死锁.
update public.vm_action_info set last_on_demand_task_id=$1,version=version+1
两个相同的简单更新如何相互死锁？
我在日志中收到的错误
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=$1,version=version+1 where id=$2
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=$1,version=version+1 where id=$2
Process 31530: update public.vm_action_info set last_on_demand_task_id=$1,version=version+1 where id=$2
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=$1,version=version+1 where id=$2
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=$1,version=version+1 where id=$2
Process 31876: update public.vm_action_info set last_on_demand_task_id=$1,version=version+1 where id=$2
模式是：
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,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 );
我的猜测是,问题的根源是表中的一个循环外键引用.
TABLE vm_action_info
==> FOREIGN KEY(last_completed_vm_task_id)参考vm_task(id)
TABLE vm_task
==> FOREIGN KEY(vm_action_info_id)参考vm_action_info(id)
交易包括两个步骤：
原文链接：https://www.f2er.com/postgresql/192519.htmlTABLE vm_action_info
==> FOREIGN KEY(last_completed_vm_task_id)参考vm_task(id)
TABLE vm_task
==> FOREIGN KEY(vm_action_info_id)参考vm_action_info(id)
交易包括两个步骤：
- add a new entry to task table
- updates corresponding entry in vm_action_info the vm_task table.
当两个事务同时在vm_action_info表中更新相同的记录时,这将完成死锁.
看简单的测试用例：
CREATE TABLE vm_task
(
id integer NOT NULL,CONSTRAINT vm_task_pkey PRIMARY KEY (id )
)
WITH ( OIDS=FALSE );
insert into vm_task values
( 0,0 ),( 1,1,1 ),( 2,2,2 );
CREATE TABLE vm_action_info(
id integer NOT NULL,CONSTRAINT vm_action_info_pkey PRIMARY KEY (id )
)
WITH (OIDS=FALSE);
insert into vm_action_info values
( 0,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
;
在会话1中,我们在vm_action_info中向vm_task添加了一个引用id = 2的记录
session1=> begin; BEGIN session1=> insert into vm_task values( 100,2 ); INSERT 0 1 session1=>
在会话2的同时,另一个交易开始：
session2=> begin; BEGIN session2=> insert into vm_task values( 200,2 ); INSERT 0 1 session2=>
那么第一个事务执行更新：
session1=> update vm_action_info set last_on_demand_task_id=100,version=version+1 session1=> where id=2;
但是这个命令挂起来正在等待锁…..
那么第二个会话执行更新……..
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=>
检测到死锁！
这是因为由于外键引用,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,2 ); update vm_action_info set last_on_demand_task_id=100,version=version+1 where id=2; commit;
