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  

digoal’s 大量PostgreSQL文章入口