被锁住的大象(Postgres)，如何跟MySQL赛跑
P
Mr 胡约 5 分钟
Mr 胡约 5 分钟一. PostgreSQL锁问题
b）查看PostgreSQL日志可以查看到进一步的死锁信息，如下图所示，死锁日志展示造成死锁的两个事务，以及等锁的两条SQL，到此环节无更多信息推断出这两个事务是如何形成的死锁环。
1）首先在审计日志里面找到死锁事件，以及死锁事务所在的进程ID。
2024-05-27 03:04:28.296 EDT,"postgres","postgres",28417,"10.18.215.89:41572",6654306d.6f01,5,"UPDATE",2024-05-27 03:04:13 EDT,8/93,37429,ERROR,40P01,"deadlock detected","Process 28417 waits for ShareLock on transaction 37430; blocked by process 28419.Process 28419 waits for ShareLock on transaction 37429; blocked by process 28417.Process 28417: update people set age=23 where id=2;Process 28419: update people set age=33 where id=1;","See server log for query details.",,,"while updating tuple (1090,20) in relation ""people""","update people set age=23 where id=2;",,,"pgbench"
2）然后再在审计日志里梳理进程这几个PID在死锁时间点前后都执行了哪些SQL。
bash-4.2$ cat postgresql-2024-05-27_025951.csv |grep 284172024-05-27 03:04:13.265 EDT,"postgres","postgres",28417,"10.18.215.89:41572",6654306d.6f01,1,"idle",2024-05-27 03:04:13 EDT,8/93,0,LOG,00000,"statement: begin;",,,,,,,,,"pgbench"2024-05-27 03:04:13.268 EDT,"postgres","postgres",28417,"10.18.215.89:41572",6654306d.6f01,2,"idle in transaction",2024-05-27 03:04:13 EDT,8/93,0,LOG,00000,"statement: update people set age=23 where id=1;",,,,,,,,,"pgbench"2024-05-27 03:04:13.289 EDT,"postgres","postgres",28417,"10.18.215.89:41572",6654306d.6f01,3,"idle in transaction",2024-05-27 03:04:13 EDT,8/93,37429,LOG,00000,"statement: SELECT pg_sleep(5);",,,,,,,,,"pgbench"2024-05-27 03:04:18.293 EDT,"postgres","postgres",28417,"10.18.215.89:41572",6654306d.6f01,4,"idle in transaction",2024-05-27 03:04:13 EDT,8/93,37429,LOG,00000,"statement: update people set age=23 where id=2;",,,,,,,,,"pgbench"2024-05-27 03:04:28.296 EDT,"postgres","postgres",28417,"10.18.215.89:41572",6654306d.6f01,5,"UPDATE",2024-05-27 03:04:13 EDT,8/93,37429,ERROR,40P01,"deadlock detected","Process 28417 waits for ShareLock on transaction 37430; blocked by process 28419.Process 28419 waits for ShareLock on transaction 37429; blocked by process 28417.Process 28417: update people set age=23 where id=2;
死锁前后Pid1执行过的SQL
bash-4.2$ cat postgresql-2024-05-27_025951.csv |grep 284192024-05-27 03:04:13.281 EDT,"postgres","postgres",28419,"10.18.215.89:41576",6654306d.6f03,1,"idle",2024-05-27 03:04:13 EDT,9/173,0,LOG,00000,"statement: begin;",,,,,,,,,"pgbench"2024-05-27 03:04:13.282 EDT,"postgres","postgres",28419,"10.18.215.89:41576",6654306d.6f03,2,"idle in transaction",2024-05-27 03:04:13 EDT,9/173,0,LOG,00000,"statement: update people set age=33 where id=2;",,,,,,,,,"pgbench"2024-05-27 03:04:13.291 EDT,"postgres","postgres",28419,"10.18.215.89:41576",6654306d.6f03,3,"idle in transaction",2024-05-27 03:04:13 EDT,9/173,37430,LOG,00000,"statement: SELECT pg_sleep(5);",,,,,,,,,"pgbench"2024-05-27 03:04:18.307 EDT,"postgres","postgres",28419,"10.18.215.89:41576",6654306d.6f03,4,"idle in transaction",2024-05-27 03:04:13 EDT,9/173,37430,LOG,00000,"statement: update people set age=33 where id=1;",,,,,,,,,"pgbench"2024-05-27 03:04:28.296 EDT,"postgres","postgres",28417,"10.18.215.89:41572",6654306d.6f01,5,"UPDATE",2024-05-27 03:04:13 EDT,8/93,37429,ERROR,40P01,"deadlock detected","Process 28417 waits for ShareLock on transaction 37430; blocked by process 28419.Process 28419 waits for ShareLock on transaction 37429; blocked by process 28417.Process 28419: update people set age=33 where id=1;","See server log for query details.",,,"while updating tuple (1090,20) in relation ""people""","update people set age=23 where id=2;",,,"pgbench"2024-05-27 03:04:28.300 EDT,"postgres","postgres",28419,"10.18.215.89:41576",6654306d.6f03,5,"idle in transaction",2024-05-27 03:04:13 EDT,9/173,37430,LOG,00000,"statement: commit;",,,,,,,,,"pgbench"2024-05-27 03:04:28.319 EDT,"postgres","postgres",28419,"10.18.215.89:41576",6654306d.6f03,6,"idle",2024-05-27 03:04:13 EDT,9/174,0,LOG,00000,"statement: begin;",,,,,,,,,"pgbench"
3）如果是超过两个事务造成的死锁环，则需要梳理更多的事务SQL，最终可以梳理出死锁环。
近期DBdoctor3.2.0版本新上线了PostgreSQL锁透视功能，使用PostgreSQL数据库的朋友再也不用担心因数据库锁而导致的业务系统卡顿和死锁问题了，DBdoctor可助力您快速找到卡顿源头！
针对锁问题分析的难点，DBdoctor利用eBPF技术采集PostgreSQL事务SQL的执行过程数据，其中包括细粒度的锁数据，并通过泳道图可视化呈现锁在多事务并发执行中的详细形成过程。
在死锁可视化分析中，如下图，会将>=2个事务所形成的死锁环绘制出来，会标注各事务持有和等待的具体锁资源，如某个page或tuple，同时会用红色标注出被回滚的事务。