如何在PostgreSQL中模拟死锁？

Question

我是PostgreSQL的新手。我想模拟以下日程安排的死锁：

如何在PostgreSQL中模拟死锁？ 是否有可能？ 如何锁定特定列？

BEGIN;
UPDATE deadlock_demonstration
SET salary1=(SELECT salary1 
FROM deadlock_demonstration
WHERE worker_id = 1 
FOR UPDATE)+100
WHERE worker_id=1;
SELECT pg_sleep(5);
commit;
SELECT salary2 FROM deadlock_demonstration WHERE worker_id = 1 FOR UPDATE;

在另一个屏幕上，我运行了这个：

BEGIN;
UPDATE deadlock_demonstration
SET salary2=(SELECT salary1 
FROM deadlock_demonstration
WHERE worker_id = 1
FOR UPDATE)+200
WHERE worker_id=1;
SELECT pg_sleep(5);
commit;
SELECT salary1 FROM deadlock_demonstration WHERE worker_id = 1 FOR UPDATE;

为什么死锁没有发生？你能提出建议吗，我应该改变什么以刺激死锁的发生？

- user3388473

Answer 1

1. 并行打开两个连接，例如启动两个psql实例或者在pgAdmin中打开两个查询窗口（每个窗口拥有自己的会话）。
2. 在每个连接中开始一个事务：BEGIN;
3. 轮流运行相互冲突的命令。
4. 在提交之前，其中一个将因死锁异常而回滚。
5. 你可能想要回滚另一个：ROLLBACK;

显式地对表进行加锁就像这样简单：

LOCK tbl;

锁定行可以使用以下方法：

SELECT * FROM tbl WHERE boo = 3 FOR UPDATE;

或者使用 FOR SHARE 等语句。详情请参阅手册。
（或者隐含地使用 UPDATE 或 DELETE 语句。）

示例

您添加的示例不会发生死锁。两个查询先尝试在同一行的同一表上获取相同的锁，第二个查询将等待第一个查询完成。

下面是一个实际产生死锁的示例（必须存在行才能获取锁）：

Transaction 1                    Transaction 2
BEGIN;
                                 BEGIN;
SELECT salary1 
FROM   deadlock_demonstration
WHERE  worker_id = 1
FOR    UPDATE;
                                 SELECT salary1 
                                 FROM   deadlock_demonstration
                                 WHERE  worker_id = 2
                                 FOR    UPDATE;
UPDATE deadlock_demonstration
SET    salary1 = 100
WHERE  worker_id = 2;

                                 UPDATE deadlock_demonstration
                                 SET    salary1 = 100
                                 WHERE  worker_id = 1;

                    --> ...  deadlock!

结果

在验证解决方案后，OP用户3388473提供了此截图：

- Erwin Brandstetter

---

你不能在PL/pgSQL函数内部执行该操作。函数总是作为一个整体在事务内执行。你需要使用普通的SQL命令。 - Erwin Brandstetter

我已经插入了纯SQL命令（请参见编辑后的问题）。但出于某种原因，死锁没有发生。我不知道为什么。我做错了什么？ - user3388473

我已经在一个SQL查询中尝试了这段代码，然后在两个查询屏幕中进行了测试。结果在13-14毫秒内返回，没有发生死锁。 - user3388473

@user3388473。对我来说可以工作。我测试过了。你可能漏掉了什么。需要两个独立的事务（两个psql会话或pgAdmin中的两个单独的查询窗口）。逐步执行命令，而不是一次性全部执行。所引用的行必须存在！否则将不会进行锁定。 - Erwin Brandstetter

@Cramps：在两个会话中运行相同的查询不能产生死锁（同时以确定性方式锁定行）。这实际上是避免死锁的推荐策略（例如：https://dba.stackexchange.com/a/195220/3684）。尝试我的上面的例子来产生死锁。 - Erwin Brandstetter

Answer 2

这是意味着发生了死锁吗？

不是的。它的意思就是它所说的，你不能在pgsql中使用commit，在这里清楚地说明了。

- Str.

---

s/pgsql/plpgsql. - Erwin Brandstetter