postgresql模拟死鎖

今天教你模拟postgresql實作死鎖。

1準備

create table t1(id int primary key,info character varying(20));
insert into dss_pmart.t1 values(22,'hello');
insert into dss_pmart.t1 values(23,'world');
           

2模拟死鎖

session1，在指令視窗A，輸入以下資訊：

--指令視窗A
begin;
select * from t1 where id =23 for update;
           

session2，在指令視窗B，輸入以下資訊：

--指令視窗B
begin;
select * from t1 where id in (22,23) for update;
           

session1，回到指令視窗A，繼續輸入以下資訊：

--指令視窗A
select * from t1 where id =22 for update;
           

3模拟結果

ERROR: deadlock detected
SQL 狀态: 40P01
詳細:Process 3202 waits for ShareLock on transaction 19236551; blocked by process 3559.
Process 3559 waits for ShareLock on transaction 19236550; blocked by process 3202.
指導建議:See server log for query details.