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


表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 $ c $中的同一记录时c>表将同时出现死锁。


查看简单的测试用例：

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;