postgresql死锁

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

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操作过，就无法找到之前的元组了。