postgresql – postgres中的简单更新查询死锁

概述我正在使用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 HI 我正在使用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=,version=version+1 where ID=013-08-18 11:00:25 IDT ERROR:  deadlock detected2013-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=013-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=013-08-18 11:00:25 IDT ERROR:  deadlock detected2013-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=

模式是：

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)
交易包括两个步骤：

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_taskadd  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;BEGINsession1=> insert into vm_task values( 100,2 );INSERT 0 1session1=>

在会话2的同时,另一个交易开始：

session2=> begin;BEGINsession2=> insert into vm_task values( 200,2 );INSERT 0 1session2=>

那么第一个事务执行更新：

session1=> update vm_action_info set last_on_demand_task_ID=100,version=version+1session1=> where ID=2;

但是这个命令挂起来正在等待锁…..
那么第二个会话执行更新……..

session2=> update vm_action_info set last_on_demand_task_ID=200,version=version+1 where ID=2;BŁĄD:  wykryto zakleszcZenIESZCZEGÓŁY:  Proces 9384 oczekuje na ExclusiveLock na krotka (0,5) relacji 33083 bazy danych 16393; zablokowany przez 3808.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;

总结

以上是内存溢出为你收集整理的postgresql – postgres中的简单更新查询死锁全部内容，希望文章能够帮你解决postgresql – postgres中的简单更新查询死锁所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。