标签

PostgreSQL , 死锁 , update , 模拟

背景

在单条UPDATE中模拟死锁，需要借助扫描方法，以及明确让AB两个会话分别锁定一条记录后再锁定对方已经锁定的记录。

利用批量update的语法以及values子句即可实现，这里利用了嵌套循环，确保被更新的表被多次扫描，并且每次扫描时两个会话更新的记录被错开，达到死锁效果。

同时为了让速度慢下来，使用pg_sleep函数，让每一条更新都放缓1秒。

例子

1、建表

postgres=# create table a (id int primary key, info timestamp);  CREATE TABLE

2、写入测试数据

postgres=# insert into a select generate_series(1,10);  INSERT 0 10

3、会话1 SQL，执行计划

先锁定ID=2的记录，然后锁定ID=1的记录。

postgres=# explain update a set info=clock_timestamp() from (values (2),(1)) t(id) where a.id=t.id and pg_sleep(1) is not null;                                   QUERY PLAN                                    -----------------------------------------------------------------------------   Update on a  (cost=0.15..4.80 rows=2 width=46)     ->  Nested Loop  (cost=0.15..4.80 rows=2 width=46)           Join Filter: (pg_sleep('1'::double precision) IS NOT NULL)   -- 放缓1秒           ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=32)  -- 按values写的顺序扫描           ->  Index Scan using a_pkey on a  (cost=0.15..2.37 rows=1 width=10)  -- 先更新2，再更新1                  Index Cond: (id = "*VALUES*".column1)  (6 rows)

4、会话2 SQL，执行计划，与会话1相反。

先锁定ID=1的记录，然后锁定ID=2的记录。

postgres=# explaIN update a set info=clock_timestamp() from (values (2),(1)) t(id) where a.id=t.id and pg_sleep(1) is not null;                                   QUERY PLAN                                    -----------------------------------------------------------------------------   Update on a  (cost=0.15..4.80 rows=2 width=46)     ->  Nested Loop  (cost=0.15..4.80 rows=2 width=46)            Join Filter: (pg_sleep('1'::double precision) IS NOT NULL)      -- 放缓1秒           ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=32)  -- 按values写的顺序扫描           ->  Index Scan using a_pkey on a  (cost=0.15..2.37 rows=1 width=10)  -- 先更新1，再更新2                  Index Cond: (id = "*VALUES*".column1)    (6 rows)

5、模拟死锁

会话1    postgres=# update a set info=clock_timestamp() from (values (2),(1)) t(id) where a.id=t.id and pg_sleep(1) is not null;  ERROR:  deadlock detected  DETAIL:  Process 19893 waits for ShareLock on transaction 18613573; blocked by process 9910.  Process 9910 waits for ShareLock on transaction 18613572; blocked by process 19893.  HINT:  See server log for query details.  CONTEXT:  while updating tuple (0,1) in relation "a"    马上发起会话2    postgres=# update a set info=clock_timestamp() from (values (1),(2)) t(id) where a.id=t.id and pg_sleep(1) is not null;  UPDATE 2