/* * One edge in the waits-for graph. * * waiter and blocker may or may not be members of a lock group, but if either * is, it will be the leader rather than any other member of the lock group. * The group leaders act as representatives of the whole group even though * those particular processes need not be waiting at all. There will be at * least one member of the waiter's lock group on the wait queue for the given * lock, maybe more. */typedef struct{ PGPROC *waiter; /* the leader of the waiting lock group */ PGPROC *blocker; /* the leader of the group it is waiting for */ LOCK *lock; /* the lock being waited for */ int pred; /* workspace for TopoSort */ int link; /* workspace for TopoSort */} EDGE;
递归试图检验和消除死锁。
测试当前队列状态是否会发生死锁，如果不满足约束性检查，则死锁。对soft edge调整，并检验是否合法。
判断是否出现环，如果存在且不能调整，则死锁。
postgres=# SELECT w.query as waiting_query,postgres-# w.pid as w_pid,postgres-# w.usename as w_user,postgres-# l.query as locking_query,postgres-# l.pid as l_pid,postgres-# l.usename as l_user,postgres-# t.schemaname || '.' || t.relname as tablenamepostgres-# from pg_stat_activity w join pg_locks l1 on w.pid = l1.pidpostgres-# and not l1.granted join pg_locks l2 on l1.relation = l2.relationpostgres-# and l2.granted join pg_stat_activity l on l2.pid = l.pid join pg_stat_user_tables t on l1.relation = t.relid; waiting_query | w_pid | w_user | locking_query | l_pid | l_user | tablename--------------------+-------+----------+--------------------------------------+-------+----------+----------------truncate tab_ysl ; | 5391 | postgres | update tab_ysl set id =9 where id=1; | 5524 | postgres | public.tab_ysl(1 row)//其中l_pid为阻塞者的pid，w_pid为被阻塞者的pid。
postgres=# select oid from pg_class where relname= 'tab_ysl'； oid------24580 (1 row)使pg_class.oid=pg_locks.relation，则表tab_ysl上持有的锁为，RowExclusiveLock和AccessExclusiveLock ，对应了update和truncate的操作。postgres=# select * from pg_locks where pid in ('5524','5391'); locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------------+---------+----------relation | 13593 | 2659 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | trelation | 13593 | 2658 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | trelation | 13593 | 1249 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | trelation | 13593 | 3455 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | trelation | 13593 | 2663 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | trelation | 13593 | 2662 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | trelation | 13593 | 2685 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | trelation | 13593 | 2684 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | trelation | 13593 | 2615 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | trelation | 13593 | 1259 | | | | | | | | 7/11 | 5524 | AccessShareLock | t | tvirtualxid | | | | | 7/11 | | | | | 7/11 | 5524 | ExclusiveLock | t | tvirtualxid | | | | | 6/181 | | | | | 6/181 | 5391 | ExclusiveLock | t | ttransactionid | | | | | | 512 | | | | 7/11 | 5524 | ExclusiveLock | t | frelation | 13593 | 24580 | | | | | | | | 7/11 | 5524 | RowExclusiveLock | t | ftransactionid | | | | | | 513 | | | | 6/181 | 5391 | ExclusiveLock | t | frelation | 13593 | 24580 | | | | | | | | 6/181 | 5391 | AccessExclusiveLock | f | f(16 rows)
select pg_cancel_backend('上面查询到的阻塞着的pid')；
新闻|Babelfish使PostgreSQL直接兼容SQL Server应用程序
更多新闻资讯，行业动态，技术热点，请关注中国PostgreSQL分会官方网站
https://www.postgresqlchina.com
中国PostgreSQL分会生态产品
https://www.pgfans.cn
中国PostgreSQL分会资源下载站
https://www.postgreshub.cn
点赞、在看、分享、收藏
文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
