psql | 希曼日记

使用特定用户登录特定的数据库 -h: 地址 -U: 用户 -d: 数据库

psql -h localhost -U postgres -d simple_bank

死锁调试

常见的死锁原因

一个表的外键约束, 当一个事务更新时则会可能引起死锁
更新锁, 一个事务在操作一行数据时(没有提交或者回滚), 其它事务也试图修改此行就被阻塞, 然后当第一个事务也试图更新第二个事务所修改的行时就会引发死锁, 即互相等待对方的锁

调试步骤:

一般都是由并发运行两个琥珀多个事务引起的死锁, 复现步骤可以使用两个终端连接数据库实例
两个终端输入BEGIN开启事务
运行命令直到有一个事务被阻塞
使用以下链接或示例的代码找出死锁问题的原因: https://wiki.postgresql.org/wiki/Lock_Monitoring

 SELECT blocked_locks.pid                  AS blocked_pid,
        blocked_activity.usename           AS blocked_user,
        blocking_locks.pid                 AS blocking_pid,
        blocking_activity.usename          AS blocking_user,
        blocked_activity.query             AS blocked_statement,
        blocking_activity.query            AS current_statement_in_blocking_process,
        blocked_activity.application_name  AS blocked_application,
        blocking_activity.application_name AS blocking_application
 FROM pg_catalog.pg_locks blocked_locks
          JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
          JOIN pg_catalog.pg_locks blocking_locks
               ON blocking_locks.locktype = blocked_locks.locktype
                   AND blocking_locks.DATABASE IS NOT DISTINCT
 FROM blocked_locks.DATABASE
     AND blocking_locks.relation IS NOT DISTINCT
 FROM blocked_locks.relation
     AND blocking_locks.page IS NOT DISTINCT
 FROM blocked_locks.page
     AND blocking_locks.tuple IS NOT DISTINCT
 FROM blocked_locks.tuple
     AND blocking_locks.virtualxid IS NOT DISTINCT
 FROM blocked_locks.virtualxid
     AND blocking_locks.transactionid IS NOT DISTINCT
 FROM blocked_locks.transactionid
     AND blocking_locks.classid IS NOT DISTINCT
 FROM blocked_locks.classid
     AND blocking_locks.objid IS NOT DISTINCT
 FROM blocked_locks.objid
     AND blocking_locks.objsubid IS NOT DISTINCT
 FROM blocked_locks.objsubid
     AND blocking_locks.pid != blocked_locks.pid
     JOIN pg_catalog.pg_stat_activity blocking_activity
 ON blocking_activity.pid = blocking_locks.pid
 WHERE NOT blocked_locks.GRANTED;

进一步使用下面教程的代码来排查问题 https://www.bilibili.com/video/BV1dy4y1u7Sq/?p=7&spm_id_from=pageDriver&vd_source=63554dc934fc61f53a9594e7bfe32a1b

示例:

-- a.application_name pg应用程序名,一般是psql
-- relation::regclass: 表名
-- transactionid: 事务id
-- l.mode: 锁的模式
-- l.locktype: 锁的类型
-- l.GRANTED: 锁是否被授权
-- a.query: 锁是否持有或试图获取锁的查询
SELECT a.application_name,
       l.relation::regclass, l.transactionid,
       l.mode,
       l.locktype,
       l.GRANTED,
       a.query,
       a.pid
FROM pg_stat_activity a
         JOIN pg_locks l ON l.pid = a.pid
WHERE a.application_name = 'psql'
ORDER BY a.pid;