PostgreSQL之死锁模拟

死锁的概念：

1.什么是死锁
多个进程竞争资源，A进程占用了一部分资源，又去申请其他资源，A进程申请的资源被B进程占用， B进程申请A资源占用的资源，这样就形成了死锁。
2.产生死锁的条件是什么
1⃣️互斥 2⃣️占有并等待 3⃣️ 非剥夺 4⃣️循环等待
3.解决死锁的方法
1⃣️预防死锁 通过破坏死锁产生的条件一致 ->效率低
2⃣️避免死锁 通过安全性算法计算资源的分配 ->效率低
3⃣️检测并解除死锁 现在常用的

死锁模拟

1.建表
create table a (id int primary key,info timestamp);
CREATE TABLE
2.插入数据
test=# insert into a select generate_series(1,10);
INSERT 0 10
3.打开两个会话 分别执行
会话1 执行SQL

test=#  update a set info =clock_timestamp() from (values(1),(2))t(id) where a.id = t.id and pg_sleep(1) is not null;       

会话2 马上执行SQL

test=#  update a set info =clock_timestamp() from (values(2),(1))t(id) where a.id = t.id and pg_sleep(1) is not null;    
2019-08-16 14:12:33.978 CST [7880] ERROR:  deadlock detected
2019-08-16 14:12:33.978 CST [7880] DETAIL:  Process 7880 waits for ShareLock on transaction 1861043; blocked by process 7951.
        Process 7951 waits for ShareLock on transaction 1861042; blocked by process 7880.
        Process 7880: update a set info =clock_timestamp() from (values(2),(1))t(id) where a.id = t.id and pg_sleep(2) is not null;
        Process 7951: update a set info =clock_timestamp() from (values(1),(2))t(id) where a.id = t.id and pg_sleep(2)is not null;
2019-08-16 14:12:33.978 CST [7880] HINT:  See server log for query details.
2019-08-16 14:12:33.978 CST [7880] CONTEXT:  while updating tuple (0,21) in relation "a"
2019-08-16 14:12:33.978 CST [7880] STATEMENT:  update a set info =clock_timestamp() from (values(2),(1))t(id) where a.id = t.id and pg_sleep(2) is not null;
ERROR:  deadlock detected
DETAIL:  Process 7880 waits for ShareLock on transaction 1861043; blocked by process 7951.
Process 7951 waits for ShareLock on transaction 1861042; blocked by process 7880.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,21) in relation "a"