postgresql死锁

查看测试日志，发现了死锁问题：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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；

 

?

1 2 3 4 5 6 7 8 9

$ 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

 

?

1 2 3 4 5 6 7

# SELECT relname FROM pg_class where oid=31042;        relname        ----------------------  habp (1 row)   #

可以查看到表名：habp

 

?

1	# select * from habp where ctid='(2129,23)';

结果为0行，可以知道对应的元组已经被修改了。
尽管ctid可以用来快速定位对应的行版本，但是对应的行如果被update了或被vacuum full操作过，就无法找到之前的元组了。