简单更新查询上的postgres死锁 [英] deadlock in postgres on simple update query

问题描述

我正在使用postgres 9.1，并且在过度执行简单的更新方法时遇到了死锁异常。

I'm working with postgres 9.1 and getting deadlock exception under excessive execution of a simple update method.

根据日志，死锁是由于同时执行两个相同的更新而发生的。

According to the logs the deadlock occurs due to execution of two identical updates at the same time.

更新public.vm_action_info设置了last_on_demand_task_id = $ 1，version = version + 1

update public.vm_action_info set last_on_demand_task_id=$1, version=version+1

两个相同的简单更新如何彼此死锁？

How does two identical simple updates can deadlock each other ?

我在日志中遇到的错误

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,
  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 );

推荐答案

我的猜测是问题的根源是表中的循环外键引用。


表vm_action_info

==>外键（last_completed_vm_task_id）参考vm_task（id）


表vm_task

==>外键（vm_action_info_id）参考vm_action_info（id）


事务包含两个步骤：

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:

1. 向任务表添加新条目


2. 更新vm_task表中vm_action_info中的相应条目。

当两个事务要更新 vm_action_info 表将同时出现死锁。


查看简单的测试用例：

When two transactions are going to update the same record in the vm_action_info table at the same time, this will finish with a deadlock.

Look at simple test case:

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
      ;

在会话1中，我们向vm_task添加一条记录，该记录引用了id = vm_action_info中的2

In session 1 we add a record to vm_task that reference to id=2 in vm_action_info

session1=> begin;
BEGIN
session1=> insert into vm_task values( 100, 0, 2 );
INSERT 0 1
session1=>

在会话2中的同一时间，另一笔交易开始：

At the same time in session 2 an another transaction begins:

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=>

检测到死锁!!!


这是因为两个INSERT都插入了由于有外键引用，vm_task在vm_action_info表中的行id = 2上放置了共享锁。然后，第一次更新尝试在该行上放置写锁并挂起，因为该行已被另一个（第二个）事务锁定。然后，第二个更新尝试将同一记录锁定为写模式，但第一个事务将其锁定为共享模式。这会导致死锁。


我认为，如果在vm_action_info中记录写锁定，可以避免这种情况，整个事务必须包括5个步骤：

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:

 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;

这篇关于简单更新查询上的postgres死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！