# 咨询锁函数 咨询锁函数用于管理咨询锁（Advisory Lock）。 - pg_advisory_lock(key bigint) 描述：获取会话级别的排他咨询锁。 返回值类型：void 备注：pg_advisory_lock锁定应用程序定义的资源，该资源可以用一个64位或两个不重叠的32位键值标识。如果已经有另外的会话锁定了该资源，则该函数将阻塞到该资源可用为止。这个锁是排他的。多个锁定请求将会被压入栈中，因此，如果同一个资源被锁定了三次，它必须被解锁三次以将资源释放给其他会话使用。 - pg_advisory_lock(key1 int, key2 int) 描述：获取会话级别的排他咨询锁。 返回值类型：void 备注：只允许sysadmin对键值对(65535, 65535)加会话级别的排他咨询锁，普通用户无权限。 - pg_advisory_lock(lock_id int4, lock_id int4, datebase_name Name) 描述：通过传入锁ID和数据库名字，获取指定数据库的排他咨询锁。 返回值类型：void - pg_advisory_lock_shared(key bigint) 描述：获取会话级别的共享咨询锁。 返回值类型：void - pg_advisory_lock_shared(key1 int, key2 int) 描述：获取会话级别的共享咨询锁。 返回值类型：void 备注：pg_advisory_lock_shared类似于pg_advisory_lock，不同之处仅在于共享锁会话可以和其他请求共享锁的会话共享资源，但排他锁除外。 - pg_advisory_unlock(key bigint) 描述：释放会话级别的排他咨询锁。 返回值类型：Boolean - pg_advisory_unlock(key1 int, key2 int) 描述：释放会话级别的排他咨询锁。 返回值类型：Boolean 备注：pg_advisory_unlock释放先前取得的排他咨询锁。如果释放成功则返回true。如果实际上并未持有指定的锁，将返回false并在服务器中产生一条SQL警告信息。 - pg_advisory_unlock(lock_id int4, lock_id int4, datebase_name Name) 描述：通过传入锁ID和数据库名字，释放指定数据库上的排他咨询锁。 返回值类型：Boolean 备注：如果释放成功则返回true；如果未持有锁，则返回false。 - pg_advisory_unlock_shared(key bigint) 描述：释放会话级别的共享咨询锁。 返回值类型：Boolean - pg_advisory_unlock_shared(key1 int, key2 int) 描述：释放会话级别的共享咨询锁。 返回值类型：Boolean 备注：pg_advisory_unlock_shared类似于pg_advisory_unlock，不同之处在于该函数释放的是共享咨询锁。 - pg_advisory_unlock_all() 描述：释放当前会话持有的所有咨询锁。 返回值类型：void 备注：pg_advisory_unlock_all将会释放当前会话持有的所有咨询锁，该函数在会话结束的时候被隐含调用，即使客户端异常地断开连接也是一样。 - pg_advisory_xact_lock(key bigint) 描述：获取事务级别的排他咨询锁。 返回值类型：void - pg_advisory_xact_lock(key1 int, key2 int) 描述：获取事务级别的排他咨询锁。 返回值类型：void 备注：pg_advisory_xact_lock类似于pg_advisory_lock，不同之处在于锁是自动在当前事务结束时释放，而且不能被显式的释放。只允许sysadmin对键值对(65535, 65535)加事务级别的排他咨询锁，普通用户无权限。 - pg_advisory_xact_lock_shared(key bigint) 描述：获取事务级别的共享咨询锁。 返回值类型：void - pg_advisory_xact_lock_shared(key1 int, key2 int) 描述：获取事务级别的共享咨询锁。 返回值类型：void 备注：pg_advisory_xact_lock_shared类似于pg_advisory_lock_shared，不同之处在于锁是在当前事务结束时自动释放，而且不能被显式的释放。 - pg_try_advisory_lock(key bigint) 描述：尝试获取会话级排他咨询锁。 返回值类型：Boolean 备注：pg_try_advisory_lock类似于pg_advisory_lock，不同之处在于该函数不会阻塞以等待资源的释放。它要么立即获得锁并返回true，要么返回false表示目前不能锁定。 - pg_try_advisory_lock(key1 int, key2 int) 描述：尝试获取会话级排他咨询锁。 返回值类型：Boolean 备注：只允许sysadmin对键值对(65535, 65535)加会话级别的排他咨询锁，普通用户无权限。 - pg_try_advisory_lock_shared(key bigint) 描述：尝试获取会话级共享咨询锁。 返回值类型：Boolean - pg_try_advisory_lock_shared(key1 int, key2 int) 描述：尝试获取会话级共享咨询锁。 返回值类型：Boolean 备注：pg_try_advisory_lock_shared类似于pg_try_advisory_lock，不同之处在于该函数尝试获得共享锁而不是排他锁。 - pg_try_advisory_xact_lock(key bigint) 描述：尝试获取事务级别的排他咨询锁。 返回值类型：Boolean - pg_try_advisory_xact_lock(key1 int, key2 int) 描述：尝试获取事务级别的排他咨询锁。 返回值类型：Boolean 备注：pg_try_advisory_xact_lock类似于pg_try_advisory_lock，不同之处在于如果得到锁，在当前事务的结束时自动释放，而且不能被显式的释放。只允许sysadmin对键值对(65535, 65535)加事务级别的排他咨询锁，普通用户无权限。 - pg_try_advisory_xact_lock_shared(key bigint) 描述：尝试获取事务级别的共享咨询锁。 返回值类型：Boolean - pg_try_advisory_xact_lock_shared(key1 int, key2 int) 描述：尝试获取事务级别的共享咨询锁。 返回值类型：Boolean 备注：pg_try_advisory_xact_lock_shared类似于pg_try_advisory_lock_shared，不同之处在于如果得到锁，在当前事务结束时自动释放，而且不能被显式的释放。 - lock_cluster_ddl() 描述：尝试对集群内所有存活的CN节点获取会话级别的排他咨询锁。 返回值类型：Boolean 备注：只允许sysadmin调用，普通用户无权限。 - unlock_cluster_ddl() 描述：尝试对CN节点会话级别的排他咨询锁。 返回值类型：Boolean 会话级别咨询锁示例： ``` gaussdb=# select pg_advisory_lock(1); --依次获取3个会话级别的咨询锁 pg_advisory_lock ------------------ (1 row) gaussdb=# select pg_advisory_lock(1,2); pg_advisory_lock ------------------ (1 row) gaussdb=# select pg_advisory_lock(2,2,'postgres'); pg_advisory_lock ------------------ (1 row) gaussdb=# select * from pg_locks; --查询pg_locks表，看到新增了3个advisory类型的锁 locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | sessionid | mode | granted | fastpath | locktag | global_sessionid ------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------------+-----------------+-----------------+---------+----------+------ -------------+------------------ relation | 12850 | 10652 | | | | | | | | | 4/27611 | 140245958915840 | 140245958915840 | AccessShareLock | t | t | 3232: 299c:0:0:0:0 | 0:0#0 virtualxid | | | | | | 4/27611 | | | | | 4/27611 | 140245958915840 | 140245958915840 | ExclusiveLock | t | t | 4:6bd b:0:0:0:7 | 0:0#0 virtualxid | | | | | | 19/16219 | | | | | 19/16219 | 140246771033856 | 140246771033856 | ExclusiveLock | t | t | 13:3f 5b:0:0:0:7 | 0:0#0 virtualxid | | | | | | 17/97293 | | | | | 17/97293 | 140246750590720 | 140246750590720 | ExclusiveLock | t | t | 11:17 c0d:0:0:0:7 | 0:0#0 advisory | 12850 | | | | | | | 0 | 1 | 1 | 4/27611 | 140245958915840 | 140245958915840 | ExclusiveLock | t | f | 3232: 0:1:1:0:b | 0:0#0 advisory | 12850 | | | | | | | 1 | 2 | 2 | 4/27611 | 140245958915840 | 140245958915840 | ExclusiveLock | t | f | 3232: 1:2:2:0:b | 0:0#0 advisory | 12850 | | | | | | | 2 | 2 | 2 | 4/27611 | 140245958915840 | 140245958915840 | ExclusiveLock | t | f | 3232: 2:2:2:0:b | 0:0#0 (7 rows) gaussdb=# select pg_advisory_unlock(1); --依次释放3个会话级别的咨询锁 pg_advisory_unlock -------------------- t (1 row) gaussdb=# select pg_advisory_unlock(1,2); pg_advisory_unlock -------------------- t (1 row) gaussdb=# select pg_advisory_unlock(2,2,'postgres'); pg_advisory_unlock -------------------- t (1 row) gaussdb=# select * from pg_locks; --查询pg_locks表，看到3个advisory类型的锁已被释放 locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | sessionid | mode | granted | fastpath | locktag | global_sessionid ------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------------+-----------------+-----------------+---------+----------+------ -------------+------------------ relation | 12850 | 10652 | | | | | | | | | 4/27618 | 140245958915840 | 140245958915840 | AccessShareLock | t | t | 3232: 299c:0:0:0:0 | 0:0#0 virtualxid | | | | | | 4/27618 | | | | | 4/27618 | 140245958915840 | 140245958915840 | ExclusiveLock | t | t | 4:6be 2:0:0:0:7 | 0:0#0 virtualxid | | | | | | 19/16254 | | | | | 19/16254 | 140246771033856 | 140246771033856 | ExclusiveLock | t | t | 13:3f 7e:0:0:0:7 | 0:0#0 virtualxid | | | | | | 17/97506 | | | | | 17/97506 | 140246750590720 | 140246750590720 | ExclusiveLock | t | t | 11:17 ce2:0:0:0:7 | 0:0#0 (4 rows) gaussdb=# select pg_advisory_lock(1); --再次获取会话级别的咨询锁 pg_advisory_lock ------------------ (1 row) gaussdb=# select pg_advisory_unlock_all(); --释放所有会话级别的咨询锁 pg_advisory_unlock_all ------------------------ (1 row) gaussdb=# select * from pg_locks; --查询pg_locks表，看到advisory类型的锁已被释放 locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | sessionid | mode | granted | fastpath | locktag | global_sessionid ------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------------+-----------------+-----------------+---------+----------+------ -------------+------------------ relation | 12850 | 10652 | | | | | | | | | 4/27629 | 140245958915840 | 140245958915840 | AccessShareLock | t | t | 3232: 299c:0:0:0:0 | 0:0#0 virtualxid | | | | | | 4/27629 | | | | | 4/27629 | 140245958915840 | 140245958915840 | ExclusiveLock | t | t | 4:6be d:0:0:0:7 | 0:0#0 virtualxid | | | | | | 19/16369 | | | | | 19/16369 | 140246771033856 | 140246771033856 | ExclusiveLock | t | t | 13:3f f1:0:0:0:7 | 0:0#0 virtualxid | | | | | | 17/98194 | | | | | 17/98194 | 140246750590720 | 140246750590720 | ExclusiveLock | t | t | 11:17 f92:0:0:0:7 | 0:0#0 (4 rows) ``` 事务级别咨询锁示例： ``` gaussdb=# select pg_advisory_xact_lock_shared(1); --获取事务级别的共享咨询锁 pg_advisory_xact_lock_shared ------------------------------ (1 row) gaussdb=# select * from pg_locks; --因单条语句视为一个事务，因此咨询锁已随事务提交自动释放。查询pg_locks表，看不到事务级咨询锁 locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | sessionid | mode | granted | fastpath | locktag | global_sessionid ------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------------+-----------------+-----------------+---------+----------+------ -------------+------------------ relation | 12850 | 10652 | | | | | | | | | 4/27631 | 140245958915840 | 140245958915840 | AccessShareLock | t | t | 3232: 299c:0:0:0:0 | 0:0#0 virtualxid | | | | | | 4/27631 | | | | | 4/27631 | 140245958915840 | 140245958915840 | ExclusiveLock | t | t | 4:6be f:0:0:0:7 | 0:0#0 virtualxid | | | | | | 19/16396 | | | | | 19/16396 | 140246771033856 | 140246771033856 | ExclusiveLock | t | t | 13:40 0c:0:0:0:7 | 0:0#0 virtualxid | | | | | | 17/98359 | | | | | 17/98359 | 140246750590720 | 140246750590720 | ExclusiveLock | t | t | 11:18 037:0:0:0:7 | 0:0#0 (4 rows) gaussdb=# start transaction; --显式开启事务块 START TRANSACTION gaussdb=# select pg_advisory_xact_lock_shared(1); --获取事务级别的共享咨询锁 pg_advisory_xact_lock_shared ------------------------------ (1 row) gaussdb=# select * from pg_locks; --查询pg_locks表，新增了一个advisory类型锁 locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | sessionid | mode | granted | fastpath | locktag | global_sessionid ------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------------+-----------------+-----------------+---------+----------+------ -------------+------------------ relation | 12850 | 10652 | | | | | | | | | 4/27632 | 140245958915840 | 140245958915840 | AccessShareLock | t | t | 3232: 299c:0:0:0:0 | 0:0#0 virtualxid | | | | | | 4/27632 | | | | | 4/27632 | 140245958915840 | 140245958915840 | ExclusiveLock | t | t | 4:6bf 0:0:0:0:7 | 0:0#0 virtualxid | | | | | | 19/16399 | | | | | 19/16399 | 140246771033856 | 140246771033856 | ExclusiveLock | t | t | 13:40 0f:0:0:0:7 | 0:0#0 virtualxid | | | | | | 17/98377 | | | | | 17/98377 | 140246750590720 | 140246750590720 | ExclusiveLock | t | t | 11:18 049:0:0:0:7 | 0:0#0 advisory | 12850 | | | | | | | 0 | 1 | 1 | 4/27632 | 140245958915840 | 140245958915840 | ShareLock | t | f | 3232: 0:1:1:0:b | 0:0#0 (5 rows) gaussdb=# commit; --提交事务块 COMMIT gaussdb=# select * from pg_locks; --查询pg_locks表，事务级别advisory类型锁已自动释放 locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | sessionid | mode | granted | fastpath | locktag | global_sessionid ------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------------+-----------------+-----------------+---------+----------+------ -------------+------------------ relation | 12850 | 10652 | | | | | | | | | 4/27633 | 140245958915840 | 140245958915840 | AccessShareLock | t | t | 3232: 299c:0:0:0:0 | 0:0#0 virtualxid | | | | | | 4/27633 | | | | | 4/27633 | 140245958915840 | 140245958915840 | ExclusiveLock | t | t | 4:6bf 1:0:0:0:7 | 0:0#0 virtualxid | | | | | | 19/16401 | | | | | 19/16401 | 140246771033856 | 140246771033856 | ExclusiveLock | t | t | 13:40 11:0:0:0:7 | 0:0#0 virtualxid | | | | | | 17/98385 | | | | | 17/98385 | 140246750590720 | 140246750590720 | ExclusiveLock | t | t | 11:18 051:0:0:0:7 | 0:0#0 (4 rows) ``` 尝试获取咨询锁示例： ``` gaussdb=# select pg_try_advisory_lock_shared(1); --在没有其他连接加咨询锁时，尝试获取对象1的共享咨询锁成功 pg_try_advisory_lock_shared ----------------------------- t (1 row) gaussdb=# select pg_advisory_unlock_all(); --释放这个咨询锁 pg_advisory_unlock_all ------------------------ (1 row) gaussdb=# select pg_advisory_lock(1); --获取对象1的排他咨询锁 pg_advisory_lock ------------------ (1 row) --保持这个连接不断开，新启动一个连接 gaussdb=# select pg_try_advisory_lock_shared(1); --因已有连接持有排他锁，所以新连接中尝试获取对象1的共享咨询锁失败 pg_try_advisory_lock_shared ----------------------------- f (1 row) ```