hibernate - 更新单行的简单 PL/pgsql 存储过程中的死锁(由 Hibernate 调用)

Question

我写了一个简单的存储过程:

CREATE OR REPLACE FUNCTION "public"."update_table01" (
    int4, -- $1 (population)
    int2 -- $2 (id)
) RETURNS "pg_catalog"."void" AS
$body$
BEGIN
    UPDATE "table01"
    SET
            population = $1
    WHERE id = $2;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

我在我的 Java 服务器 (jre7) 中调用它，我使用 Hibernate 4 和 C3P0 作为我的连接池。这是在 PostgreSQL 9.2.4 上执行的。我有一个对应于 table01 的 Hibernate 实体(由注释映射)，我指定此过程用作 SQL 更新:

@SQLUpdate(sql = "{call update_table01(?, ?)}", callable = true)

当我对经常调用它的多个线程(大约 20-30 个)进行负载测试时，出现了一些死锁，这让我非常惊讶。这是日志的相关部分:

2013-08-14 14:51:19 CEST [23495]: [3-1] LOG:  process 23495 detected deadlock while waiting for ShareLock on transaction 140127434 after 1000.048 ms
2013-08-14 14:51:19 CEST [23495]: [4-1] STATEMENT:  update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23495]: [5-1] ERROR:  deadlock detected
2013-08-14 14:51:19 CEST [23495]: [6-1] DETAIL:  Process 23495 waits for ShareLock on transaction 140127434; blocked by process 23481.
Process 23481 waits for ShareLock on transaction 140127431; blocked by process 23495.
Process 23495: update table01 set population=$1 where id=$2
Process 23481: update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23495]: [7-1] HINT:  See server log for query details.
2013-08-14 14:51:19 CEST [23495]: [8-1] STATEMENT:  update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23481]: [3-1] LOG:  process 23481 still waiting for ShareLock on transaction 140127431 after 1000.086 ms
2013-08-14 14:51:19 CEST [23481]: [4-1] STATEMENT:  update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23481]: [5-1] LOG:  process 23481 acquired ShareLock on transaction 140127431 after 1000.227 ms
2013-08-14 14:51:19 CEST [23481]: [6-1] STATEMENT:  update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23938]: [3-1] LOG:  process 23938 still waiting for ExclusiveLock on tuple (8,72) of relation 16890 of database 16751 after 1000.119 ms
2013-08-14 14:51:19 CEST [23938]: [4-1] STATEMENT:  update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23938]: [5-1] LOG:  process 23938 acquired ExclusiveLock on tuple (8,72) of relation 16890 of database 16751 after 1000.174 ms
2013-08-14 14:51:19 CEST [23938]: [6-1] STATEMENT:  update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23520]: [3-1] LOG:  duration: 970.319 ms  execute <unnamed>: update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23520]: [4-1] DETAIL:  parameters: $1 = '5731', $2 = '294'
2013-08-14 14:51:19 CEST [23481]: [7-1] LOG:  duration: 1000.361 ms  execute <unnamed>: update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23481]: [8-1] DETAIL:  parameters: $1 = '1586', $2 = '253'
2013-08-14 14:51:19 CEST [23524]: [3-1] LOG:  duration: 531.909 ms  execute <unnamed>: update table01 set population=$1 where id=$2
2013-08-14 14:51:19 CEST [23524]: [4-1] DETAIL:  parameters: $1 = '1546', $2 = '248'
2013-08-14 14:51:19 CEST [23938]: [7-1] LOG:  duration: 1004.863 ms  execute <unnamed>: update table01 set population=$1 where id=$2

我不擅长解释 Postgres 日志，如果我错了请纠正我。我认为这表明两个进程陷入了僵局。两者都在执行相同的语句。

她有很多事情让我困惑。最重要的是:当在存储过程中只获得具有该特定 ID 的行的单个行级锁时，我不明白两个进程(甚至更新不同的行)怎么会陷入死锁？这是更改此表的唯一过程。难道不是应该获得独占锁(执行SELECT ... FOR UPDATE时也是如此)吗？ ShareLocks 从何而来？后面几行 (23938) 中显示的过程是什么？我最好的猜测是 23938 也在等待锁，当 23495 被杀死时，它获得了锁。

然后我尝试了以下方法:

CREATE OR REPLACE FUNCTION "public"."update_table01" (
    int4, -- $1 (population)
    int2 -- $2 (id)
) RETURNS "pg_catalog"."void" AS
$body$
BEGIN
    PERFORM 1 FROM "table01" WHERE id = $2 FOR UPDATE;

    UPDATE "table01"
    SET
            population = $1
    WHERE id = $2;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

再次运行时，无法重现。没有更多的死锁。

为什么会这样？

编辑: 稍作调查后，似乎 Hibernate 有时会在刷新 session 时自行调用此方法。在某些情况下，针对不同的实体多次，都在同一个事务中。这会导致死锁，因为每次调用 update_table01() 都会使用 FOR UPDATE 锁锁定特定的 table01 列行。如果调用顺序不当，可能会造成循环等待。在我使这个实体不可更新后(即用 update=false 标记所有列)，一切正常。现在，我对这种 Hibernate 行为感到非常惊讶，因为 RAM 中的 table01 实体没有附加到任何负责以后事务的 session 。然而，Hibernate 将这些实体刷新到数据库中，我不知道为什么。

至于锁，我已经确定了我的另外两个存储过程，它们插入/更新到引用 table01 的其他一些表(其中一个更改 FK 列)。这些将在 table01 中的适当 FK 行上请求 ShareLock，因此将与 update_table01() 发生冲突。所以这三个存储过程会互相等待对方完成。仅此一项无法创建循环等待，但如果在调用这些存储过程后添加几个由 Hibernate 引起的对 update_table01() 的调用，则有可能创建循环等待。

Answer 1

首先，你看到的独占锁:

ExclusiveLock on tuple (8,72) of relation 16890 of database 16751

这应该是您的“table01” - 尝试:

SELECT * FROM pg_class WHERE oid = 16890;

其次，是的，您有进程 23495 和 23481 相互阻塞。 PostgreSQL 在等待 1 秒后检测到这一点并取消 23495。然后，在 1000.361 毫秒后继续向下几行 23481。

要对此进行测试，您需要打开两个终端，每个终端都运行 psql。这样你就可以控制每个中的停顿。在每个终端中发出 BEGIN 并尝试在两个终端中运行更新。查看 pg_locks View 以了解发生了什么。

我的所有实验都无法重现这一点，而且我看不出它如何能重现。

您看到的 ShareLock 可能是在 UPDATE 发生时阻止更改表的那个。您不希望有人删除您正在尝试更新的列。

自然地，两个共享锁 can't conflict所以一定有其他因素在起作用。

从您的日志提取中让我印象深刻的是，您还有许多其他简单的更新，对于这样一个简单的更新来说，这些更新似乎已经放置了很长时间。

有可能是死锁检测器弄错了——您的服务器刚好处于极端负载状态，并且您的两个事务之间没有冲突。如果你的deadlock_timeout设置时间有点长，这可能永远不会发生。