PostgreSQL模擬兩個update語句死鎖-利用掃描方法

標籤

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