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

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

 1 //查询锁表的情况
 2 SELECT 
 3 　　A.locktype,
 4 　　A.DATABASE,
 5 　　A.pid,
 6 　　A.relation,
 7 　　b.relname 
 8 FROM
 9 　　pg_locks
10 A JOIN pg_class b ON A.relation = b.oid;
11
12 //杀死进程 
13 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文件)

 1 typedef enum LockTagType
 2 {
 3 　　LOCKTAG_RELATION, /* whole relation */
 4 　　/* ID info for a relation is DB OID + REL OID; DB OID = 0 if shared */
 5 　　LOCKTAG_RELATION_EXTEND, /* the right to extend a relation */
 6 　　/* same ID info as RELATION */
 7 　　LOCKTAG_PAGE, /* one page of a relation */
 8 　　/* ID info for a page is RELATION info + BlockNumber */
 9 　　LOCKTAG_TUPLE, /* one physical tuple */
10 　　/* ID info for a tuple is PAGE info + OffsetNumber */
11 　　LOCKTAG_TRANSACTION, /* transaction (for waiting for xact done) */
12 　　/* ID info for a transaction is its TransactionId */
13 　　LOCKTAG_VIRTUALTRANSACTION, /* virtual transaction (ditto) */
14 　　/* ID info for a virtual transaction is its VirtualTransactionId */
15 　　LOCKTAG_OBJECT, /* non-relation database object */
16 　　/* ID info for an object is DB OID + CLASS OID + OBJECT OID + SUBID */
17 
18 /*
19 * Note: object ID has same representation as in pg_depend and
20 * pg_description, but notice that we are constraining SUBID to 16 bits.
21 * Also, we use DB OID = 0 for shared objects such as tablespaces.
22 */
23 　　LOCKTAG_USERLOCK, /* reserved for old contrib/userlock code */
24 　　LOCKTAG_ADVISORY /* advisory user locks */
25 } LockTagType;

 

 

 

 

 

posted @ 2020-03-13 15:34  Jeessu  阅读(7050)  评论(0)  编辑  收藏  举报