PostgreSQL 锁

锁的类型

/* 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 */           

可以加锁的对象

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_SPECULATIVE_TOKEN,      /* speculative insertion Xid and token */
        /* ID info for a transaction is its TransactionId */
        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 */           

查询锁以及进程的相关视图

/*锁信息*/
select * from pg_locks;
/*记录表和类似于表这种具有列的结构*/
select * from pg_class;
/* 查询进程信息*/
select * from pg_stat_activity 

/*查询锁的相关信息*/
select t.locktype,
       t2.datname,
       t1.relname,
       mode,
       t3.usename,
       application_name,
       client_addr,
       query
  from pg_locks t, pg_class t1, pg_database t2, pg_stat_activity t3
 where t.DATABASE = t2.oid
   and t.relation = t1.oid
   and t.pid = t3.pid           

参考

pg中关于AccessShareLock和ExclusiveLock

的问题中德哥的回答