作者介绍
陈雁飞,开源PostgreSQL爱好者,一直从事PostgreSQL数据库运维工作
问题现象
接前一篇文章,这里继续介绍在工作中遇到的一个死锁案例。 经过对业务模型的抽取分析(后面会介绍表结构和数据,业务模型来源于开源组件的实际业务),模拟得到的死锁日志信息如下:2019-09-01 21:01:08.359 CST [1482] ERROR: deadlock detected
2019-09-01 21:01:08.359 CST [1482] DETAIL: Process 1482 waits for ShareLock on transaction 523; blocked by process 1610.
Process 1610 waits for ShareLock on transaction 524; blocked by process 1482.
Process 1482: select test2.a,test2.b,test2.c from test2 join test1 on test2.a = test1.a where test2.b = 2 and test1.c = 3 for update;
Process 1610: delete from test1 where a = 1;
2019-09-01 21:01:08.359 CST [1482] HINT: See server log for query details.
2019-09-01 21:01:08.359 CST [1482] CONTEXT: while locking tuple (0,1) in relation "test1"</