postgresql死锁
2020-05-14 11:33 abce 阅读(2104) 评论(1) 编辑 收藏 举报查看测试日志,发现了死锁问题:
2020-05-14 09:07:11.454 CST,"abce_user","abce",1042,"10.10.15.127:42056",5ebc834a.412,1,"UPDATE",2020-05-14 07:31:22 CST,23/486488,14535553,ERROR,40P01, "deadlock detected","Process 1042 waits for ShareLock on transaction 14535551; blocked by process 3169. Process 3169 waits for ShareLock on transaction 14535552; blocked by process 5632. Process 5632 waits for ExclusiveLock on tuple (2129,23) of relation 31042 of database 14386; blocked by process 1042. Process 1042: update habp set uuid = $1, uuid_t = $2, creater = $3 where id = $39 and uuid_t = $40 Process 3169: update habp set uuid = $1, uuid_t = $2, creater = $3 where id = $39 and uuid_t = $40 Process 5632: update habp set uuid = $1, uuid_t = $2, creater = $3 where id = $39 and uuid_t = $40", "See server log for query details.",,,"while updating tuple (2129,23) in relation ""habp""","update habp set uuid = $1, uuid_t = $2, creater = $3 where id = $39 and uuid_t = $40",,,"PostgreSQL JDBC Driver"
从日志可以看到进程1042被进程3169阻塞,3169被进程5632阻塞,5642被进程1042阻塞,从而形成了死锁。
此外,从日志也可以看到relation 31042即表habp;database 14386即数据库abce;
$ oid2name All databases: Oid Database Name Tablespace ---------------------------------- 14848 postgres pg_default 14847 template0 pg_default 1 template1 pg_default 14386 abce pg_default $
可以看到database 14386是数据库abce
# SELECT relname FROM pg_class where oid=31042; relname ---------------------- habp (1 row) #
可以查看到表名:habp
# select * from habp where ctid='(2129,23)';
结果为0行,可以知道对应的元组已经被修改了。
尽管ctid可以用来快速定位对应的行版本,但是对应的行如果被update了或被vacuum full操作过,就无法找到之前的元组了。