如果两个事务不得不相互等待,就将会发生一次死锁。
首先创建一个测试表:
CREATE TABLE t_deadlock (
id int
);
INSERT INTO t_deadlock
VALUES
(1),
(2);
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
SELECT * FROM t_deadlock WHERE id = 1 FOR UPDATE; | |
SELECT * FROM t_deadlock WHERE id = 2 FOR UPDATE; | |
SELECT * FROM t_deadlock WHERE id = 2 FOR UPDATE; | |
等待事务2 | SELECT * FROM t_deadlock WHERE id = 1 FOR UPDATE; |
等待事务2 | 等待事务1 |
1秒(deadlock_timeout)以后死锁解除 | |
【注1】 | |
COMMIT; | ROLLBACK; |
一旦检测到死锁,将会报错如下:
ERROR: deadlock detected
DETAIL: Process 6864 waits for ShareLock on transaction 669; blocked by process 6830.
Process 6830 waits for ShareLock on transaction 670; blocked by process 6864.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,1) in relation "t_deadlock"
PostgreSQL会告诉我们哪一行导致了冲突。在本例中,根源是元组(0, 1),这里能看到的是一个ctid,它告诉我们一行在表中的物理位置,在这里是第一块(0)中的第一行。
【注1】:如果在此处执行如下查询,可得:
SELECT
pid,
state,
query
FROM
pg_stat_activity
WHERE pid in (6830, 6864);
pid | state | query
------+-------------------------------+---------------------------------------------------
6830 | active | SELECT +
| | pid, +
| | state, +
| | query +
| | FROM +
| | pg_stat_activity +
| | WHERE pid in (6830, 6864);
6864 | idle in transaction (aborted) | SELECT
| | * +
| | FROM +
| | t_deadlock +
| | WHERE id = 1 FOR UPDATE; +
(2 rows)
如果这一行对用户的事务还可见,甚至可以查询到这一行:
SELECT
ctid,
*
FROM
t_deadlock
WHERE ctid = '(0, 1)';
ctid | id
--------+----
(0,1) | 1
(1 row)
如果这一行已经被删除或者修改,这个查询是不会返回该行的。