如何在PostgreSQL中模拟死锁？ [英] How to simulate deadlock in PostgreSQL?

问题描述

我是PostgreSQL的新手。我要为此时间表模拟死锁：

I am new for PostgreSQL. I want to simulate deadlock for this schedule:

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

编辑：

How to simulate deadlock in PostgreSQL? Is it possible at all? How to lock particular column?

    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;

在另一个屏幕中，我已经运行了此

In another screen, I have run this

    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;

为什么没有发生死锁？您能提出一个建议，为刺激僵局我应该改变什么？

Why deadlock is not happening? Can you give a suggestion, what I should change in order to stimulate deadlock?

推荐答案

1. 打开两个并行连接，例如 psql 的两个实例或pgAdmin中的两个查询窗口（每个都有自己的会话）。


2. 在每个连接中启动事务。 BEGIN;


3. 依次运行相互冲突的命令。


4. 在可以提交之前，将使用死锁异常回滚这两者之一。


5. 您可能想回退另一个。 ROLLBACK;

1. Open two connections in parallel, like two instances of psql or two query windows in pgAdmin (each has its own session).
2. Start a transaction in each connection. BEGIN;
3. Run mutually conflicting commands in turns.
4. Before you can commit, one of the two will be rolled back with a deadlock exception.
5. You may want to roll back the other. ROLLBACK;

明确地锁定表很简单：

LOCK tbl;

锁定行可以通过以下方式完成：

Locking rows can be done with:

SELECT * FROM tbl WHERE boo = 3 FOR UPDATE;

或分享等。手册中的详细信息。

您添加的示例不会死锁。两者都尝试首先对同一表的同一行进行相同的锁定。第二个将等待第一个完成。

Your added example cannot deadlock. Both try to take the same lock on the same row of the same table first. The second will wait for the first to finish.

实际产生死锁的示例（行必须存在，否则将不采取任何锁定）：

Example to actually produce a deadlock (rows must exist or no lock will be taken):

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对此进行了贡献验证我的解决方案后的屏幕截图：

Result

The OP user3388473 contributed this screenshot after verifying my solution:

这篇关于如何在PostgreSQL中模拟死锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！