PostgreSQL V9.6显式加锁

表级显式锁

PostgreSQL提供显式地对表对象加锁，所加的锁在事务结束时（commit或rollback）被释放。

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

where lockmode is one of:

    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE

    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

在显式加锁的命令中，支持八种加锁的模式：

/* 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，只有一个会话可以持有此锁，这样的会话中获得本锁是由于执行了CREATE TRIGGER、ALTER TABLE的部分语句（ALTER TABLE启用、禁止触发器） */

#define ExclusiveLock             7  /* blocks ROW SHARE/SELECT...FOR UPDATE，只允许另外的事务读被本事务锁住的表，即其他事务可以加AccessShareLock锁。许多情形下，可以获取本锁，如图X-X ExclusiveLock使用图 */

#define AccessExclusiveLock       8  /* ALTER TABLE, DROP TABLE, VACUUM FULL, and unqualified LOCK TABLE等需要完全锁定表对象的命令，都申请本锁以排斥其他任何在此表上的操作，再比如TRUNCATE操作 */                                                                                          * FULL, and unqualified LOCK TABLE */

    注意：有些锁的名称中，带有“Row”这并不表明此锁是行级锁，PostgreSQL官方文档说明如下：

Remember that all of these lock modes are table-level locks, even if the name contains the word "row"; the names of the lock modes are historical.