postgresql锁检测及处理(一)---识别事务锁

1.模拟deadlock，begin一个事务，update表t1

2.开启另外的session,同样begin一个事务，使用同上的条件update同样的table

这里，发现事务hang住了
3.使用如下sql去检测事务级的deadlock.
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
ka.query AS current_or_recent_statement_in_blocking_process,
ka.state AS state_of_blocking_process,
now() - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.query AS blocked_statement,
now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid =bl.pid
JOIN pg_catalog.pg_locks kl ON
kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid =kl.pid
WHERE NOT bl.GRANTED;
结果如下，3117554被3116649阻塞
4.kill 造成阻塞的pid,这里是3116649

注意，由于pg_terminate_backend寒素需要superuser权限，可以关闭所有的后台进程，如果没有superuser权限，可以考虑使用pg_cancel_backend函数，这个函数能关闭自己用户下的后台进程
补充：
Row locks are not stored in the shared memory locking table permanently, but on the table row itself in the xmax system column. The value stored there is the transaction number of the blocking transaction (usually).
Once a transaction has found out who holds the lock on that row, it starts waiting for that transaction to finish, which will release the exclusive lock it holds on its own transaction ID.
postgresql透过xmax栏位来识别行锁，xmax记录transaction id,能够很快识别哪一个事务（这里暂成为holder)正在hold行锁，其他想要获得这行exclusivelock的事务(暂称为waiter)，将尝试获取holder事务的sharelock,直到holder释放lock
因为，lock table将不有xmax记录，因此识别这种lock,需要看使用如下连接的方式：
postgresql锁检测及处理(二)—识别lock table发起的表级锁