postgresql数据库大量锁表的问题解决

一、postgresql数据库出现锁表问题排查

//查询锁表的情况
SELECT 
 3 　　A.locktype,
　　A.DATABASE,
　　A.pid,
　　A.relation,
　　b.relname 
FROM
　　pg_locks
A JOIN pg_class b ON A.relation = b.oid;
11
//杀死进程 
select pg_terminate_backend(pid); //pid为进程id号

//用于查询系统进程状态的表

select * from pg_stat_activity;

主要关注waiting 是否等待中，stat 进程状态， query 具体语句

当waiting 为t 的进行需要特别注意，query可以查看到具体语句，然后进行查杀

最终的原因排查为，mq拥堵导致一些流程走不了，代码流程又涉及到更新等操作，产生数据库锁 一直占用着连接的资源 。

二、锁类型 8种：(pgsql源码的src/include/storage/lock.h文件)

/* NoLock is not a lock mode, but a flag value meaning "don't get a lock" */
#define NoLock                          0
#define AccessShareLock                 1 /* SELECT */
#define RowShareLock                    2 /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock                3 /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock        4 /* VACUUM (non-FULL),ANALYZE, CREATE
                                           * INDEX CONCURRENTLY */
#define ShareLock                       5 /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock           6 /* like EXCLUSIVE MODE, but allows ROW
                                           * SHARE */
#define ExclusiveLock                   7 /* blocks ROW SHARE/SELECT...FOR
                                           * UPDATE */
#define AccessExclusiveLock             8 /* ALTER TABLE, DROP TABLE, VACUUM
                                           * FULL, and unqualified LOCK TABLE */

三、锁冲突（可以看出哪些锁会发生冲突）

四、可加锁对象的定义(pgsql源码src/include/storage/lock.h文件)

typedef enum LockTagType
{
　　LOCKTAG_RELATION, /* whole relation */
　　/* ID info for a relation is DB OID + REL OID; DB OID = 0 if shared */
　　LOCKTAG_RELATION_EXTEND, /* the right to extend a relation */
　　/* same ID info as RELATION */
　　LOCKTAG_PAGE, /* one page of a relation */
　　/* ID info for a page is RELATION info + BlockNumber */
　　LOCKTAG_TUPLE, /* one physical tuple */
　　/* ID info for a tuple is PAGE info + OffsetNumber */
　　LOCKTAG_TRANSACTION, /* transaction (for waiting for xact done) */
　　/* ID info for a transaction is its TransactionId */
　　LOCKTAG_VIRTUALTRANSACTION, /* virtual transaction (ditto) */
　　/* ID info for a virtual transaction is its VirtualTransactionId */
　　LOCKTAG_OBJECT, /* non-relation database object */
　　/* ID info for an object is DB OID + CLASS OID + OBJECT OID + SUBID */

/*
* Note: object ID has same representation as in pg_depend and
* pg_description, but notice that we are constraining SUBID to 16 bits.
* Also, we use DB OID = 0 for shared objects such as tablespaces.
*/
　　LOCKTAG_USERLOCK, /* reserved for old contrib/userlock code */
　　LOCKTAG_ADVISORY /* advisory user locks */
} LockTagType;