锁

锁简介

数据库通过锁对公共资源进行并发控制，使用锁的一般流程操作可以简述为3步：加锁、临界区操作、释放锁。

当对表进行DDL/DML操作时，数据库会对表进行加锁操作，在事务结束时释放。GaussDB提供了8个级别的锁分别用于不同语句的并发，各操作对应的锁如下表所示：

当两个事务的锁产生冲突时，未获取到锁的线程会等待锁。如果等待时间超过了系统设置的参数lockwait_timeout（默认20分钟），则会发生锁等待超时。其他与锁相关的参数见“配置运行参数 > GUC参数说明 > 锁管理”。

--建表并插入数据。
gaussdb=# CREATE TABLE testl1(c1 INT, c2 VARCHAR(5));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

gaussdb=# INSERT INTO testl1 VALUES (1,'a'),(2,'b'),(3,'c');
INSERT 0 3

--查看会话参数。
gaussdb=# SHOW lockwait_timeout;
 lockwait_timeout 
------------------
 20min
(1 row)

--在第一个会话中执行。
gaussdb=# BEGIN;
BEGIN
gaussdb=# INSERT INTO testl1 VALUES (4,'d');
INSERT 0 1

--在第二个会话中执行，该SQL会一直等到第一个会话结束才开始执行。
gaussdb=# CREATE INDEX idx_testl1_c1 ON testl1(c1);

--第一个会话中结束事务。
gaussdb=# END;
COMMIT

--删除表。
gaussdb=# DROP TABLE testl1;
DROP TABLE

查询锁等待

--建表并插入数据。
gaussdb=# CREATE TABLE testl2(id INT,info VARCHAR(10));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# INSERT INTO testl2 VALUES (1,'info1'),(2,'info2');
INSERT 0 2

--第一个会话中执行。
gaussdb=# BEGIN;
BEGIN
gaussdb=# CREATE INDEX idx_testl2_id ON testl2(id);
CREATE INDEX

--第二个会话中执行。
gaussdb=# INSERT INTO testl2 VALUES (3,'info3');

• 打开一个新的会话，通过如下SQL查询出等待执行的SQL。

  gaussdb=# SELECT datname,pid,query,query_id,waiting FROM pg_stat_activity WHERE waiting = TRUE;
  datname  |       pid       |                 query                  |     query_id     | waiting 
  ----------+-----------------+----------------------------------------+------------------+---------
   postgres | 140389444876032 | INSERT INTO testl2 VALUES (3,'info3'); | 3940649678789642 | t
  (1 row)

• 通过pid查询等待事件。

  gaussdb=# SELECT node_name,db_name,query_id,tid,wait_status,wait_event FROM pg_thread_wait_status WHERE tid = 140389444876032 AND db_name IS NOT NULL;
   node_name | db_name  |     query_id     |       tid       | wait_status  | wait_event 
  -----------+----------+------------------+-----------------+--------------+------------
   dn_6001   | postgres | 3940649678789642 | 140389444876032 | acquire lock | relation
  (1 row)

• 通过如下SQL查询出持锁SQL的pid。

  --通过如下sql查询出持锁语句关联的pid， locktype和上面查询出的wait_event保持一致。
  gaussdb=# SELECT relation,pid,mode,granted FROM pg_locks WHERE pid = 140389444876032 AND locktype='relation';
   relation |       pid       |       mode       | granted 
  ----------+-----------------+------------------+---------
      26466 | 140389444876032 | RowExclusiveLock | f
  (1 row)
  
  -- 通过如下语句查询出持锁语句的pid， granted字段为t表示持锁语句。
  gaussdb=# SELECT relation,pid,mode,granted FROM pg_locks WHERE relation = 26466;
   relation |       pid       |       mode       | granted 
  ----------+-----------------+------------------+---------
      26466 | 140389444876032 | RowExclusiveLock | f
      26466 | 140389939934976 | AccessShareLock  | t
      26466 | 140389939934976 | ShareLock        | t
  (3 rows)

• 通过如下SQL可以查询出持锁语句的详细信息。
  如下信息显示该语句状态为"idle in transaction" 表示事务待提交。手动提交事务后，待执行的INSERT语句才会开始执行。

  gaussdb=# SELECT datname,pid,query,query_id,waiting,state FROM pg_stat_activity WHERE pid = 140389939934976;
   datname  |       pid       |                   query                   | query_id | waiting |        state        
  ----------+-----------------+-------------------------------------------+----------+---------+---------------------
   postgres | 140389939934976 | CREATE INDEX idx_testl2_id ON testl2(id); |        0 | f       | idle in transaction
  (1 row)

  根据实际情况决定是否通过pg_terminate_backend函数去对应的节点上结束持锁SQL的线程。

  示例：

  gaussdb=# SELECT pg_terminate_backend(140389939934976);
   pg_terminate_backend 
  ----------------------
   t
  (1 row)
  
  --删除表。
  gaussdb=# DROP TABLE testl2;
  DROP TABLE