KingabseES的锁机制--表级锁

目录
  • 一、前言
  • 二、锁机制
  • 三、表级锁 ( Table-Level Locks )
    • 1、访问共享（ACCESS SHARE）
    • 2、行共享（ROW SHARE）
    • 3、行独占（ ROW EXCLUSIVE ）
    • 4、共享更新独占（SHARE UPDATE EXCLUSIVE）
    • 5、共享（SHARE）
    • 6、共享行独占（SHARE ROW EXCLUSIVE）
    • 7、排他（EXCLUSIVE）
    • 8、访问独占（ACCESS EXCLUSIVE）

一、前言

在并发控制的过程中，事务隔离起着重要作用。事务隔离是控制如何和何时进行更改以及何时必须对彼此、用户和系统可见的属性。

KingabseES 通过多版本并发控制架构实现隔离。多版本并发控制是一种允许多个会话同时访问同一记录的技术，即，当会话 A 正在更新一条记录时，会话 B 仍然可以访问该记录。

但是以下情况，应该怎么办：

  1. 如果会话 A 和会话 B 都想同时更新相同的记录。
  2. 如果在会话 A 访问表时，在会话 B 中试图 truncate 表。
  3. 如果会话 A 正在更新表，而会话 B 试图 vacuum 表。

这里出现了锁定的概念。

二、锁机制

锁机制在 KingbaseES 里非常重要 (对于其他的 RDBMS 也是如此)，特别是对于数据库应用开发人员，高并发应用的开发人员必须熟悉。大部分数据异常情况，跟死锁或者数据不一致有关系，基本上都是由于对锁机制不太了解导致的。

锁定类型取决于执行的命令类型。 KingabseES 支持三种锁定机制：

  1. 表级锁 ( Table-Level Locks )
  2. 行级锁 ( Row-Level Locks )
  3. 建议性锁 ( Advisory Locks )

表级和行级的锁可以是显式的也可以是隐式的，建议性锁一般是显式的。显式的锁由显式的用户请求（通过特殊的查询）获取，隐式的锁是通过标准的 SQL 命令来获取。

  • 隐式锁意味着当事务结束时锁会默认关闭。
  • 显式锁一旦获得，可能会一直保持到显式释放。我们可以使用该WITH LOCK 语句显式获取锁。

除了表级和行级的锁，还有页级共享/排除锁，用于控制对共享缓存池里表页的访问。在一行数据被读取或者更新后，这些锁会立即被释放。应用程序开发者通常不需要关注页级的锁。

三、表级锁 ( Table-Level Locks )

表级锁通过内置 SQL 命令获取（隐式）；此外，它们可以通过 LOCK 命令显式获取。表级锁包括：

  • 访问共享（ACCESS SHARE） - SELECT 命令可在查询中引用的表上获得该锁。一般规则是所有的查询中只有读表才获取此锁。
  • 行共享（ROW SHARE） - SELECT FOR UPDATE 和 SELECT FOR SHARE 命令可在目标表上获得该锁（以及查询中所有引用的表的访问共享锁）。
  • 行独占（ROW EXCLUSIVE） - UPDATE、INSERT 和 DELETE 命令在目标表上获得该锁（以及查询中所有引用的表的访问共享锁）。 一般规则是所有修改表的查询获得该锁。
  • 共享更新独占（SHARE UPDATE EXCLUSIVE） - VACUUM（不含FULL），ANALYZE，CREATE INDEX CONCURRENTLY，和一些 ALTER TABLE 的命令获得该锁。
  • 共享（SHARE） - CREATE INDEX 命令在查询中引用的表上获得该锁。
  • 共享行独占（SHARE ROW EXCLUSIVE） - 不被任何命令隐式获取。
  • 排他（EXCLUSIVE） - 这个锁模式在事务获得此锁时只允许读取操作并行。它不能由任何命令隐式获取。
  • 访问独占（ACCESS EXCLUSIVE） - ALTER TABLE，DROP TABLE，TRUNCATE，REINDEX，CLUSTER 和 VACUUM FULL 命令在查询中引用的表上获得该锁。此锁模式是 LOCK 命令的默认模式。

1、访问共享（ACCESS SHARE）

访问共享锁是由只从表中读取但不修改它的查询获取的。通常，这是一个选择查询。

隐式锁定示例:

  1. 从会话 1 中选择获取访问共享锁定的内容

    (SESSION 1)# begin ;
    BEGIN
    (SESSION 1)# select *, pg_sleep(300) from acl ;
    ...

  2. 尝试从会话 2 中截断表
    (SESSION 2)# begin;
    BEGIN
    (SESSION 2)# truncate table acl;
    ..

会话 1 获取 AccessShareLock ，以获取记录。

会话 2 想要 truncate 表，必须使用 AccessExclusiveLock 模式锁，但由于锁冲突，正在等待获取。

ACCESS SHARE LOCK与ACCESS EXCLUSIVE锁定模式冲突。

这时查看锁的信息，可以得到了两个锁。看到 AccessShareLock 的 granted 值是 true ，而 AccessExclusiveLock 的 granted 值是 false。

kingbase=#  \! ksql -c "select pid, virtualxid vxid, locktype lock_type, mode lock_mode, granted, relation::regclass relname from pg_locks WHERE  relation = 'acl'::regclass; "
pid  | vxid | lock_type |      lock_mode      | granted | relname
------+------+-----------+---------------------+---------+---------
7244 | 6/9  | relation  | AccessShareLock     | t       | acl
7364 | 3/12 | relation  | AccessExclusiveLock | f       | acl
(2 rows)

查找锁和锁的PID

kingbase=# \! ksql -c "SELECT locked.pid AS locked_pid, locker.pid AS locker_pid, locked_act.usename AS locked_user, locker_act.usename AS locker_user, locked.transactionid, relname FROM pg_locks locked LEFT OUTER JOIN pg_class ON (locked.relation = pg_class.oid), pg_locks locker, pg_stat_activity locked_act, pg_stat_activity locker_act WHERE locker.granted = true AND locked.granted = false AND locked.pid = locked_act.pid AND locker.pid = locker_act.pid AND locked.relation = locker.relation;"
 locked_pid | locker_pid | locked_user | locker_user | virtualtransaction |  relname
------------+------------+-------------+-------------+--------------------+---------------
       7364 |       7244 | kingbase    | kingbase    | 3/12               |  acl
(1 row)

显式锁定示例：

kingbase=# begin;
BEGIN
kingbase=# LOCK TABLE emp IN ACCESS SHARE MODE;
LOCK TABLE
kingbase=#  \! ksql -c "select pid, virtualxid vxid, locktype lock_type, mode lock_mode, granted, relation::regclass relname from pg_locks WHERE  relation = 'emp'::regclass; "
 pid  | vxid | lock_type |    lock_mode    | granted | relname
------+------+-----------+-----------------+---------+------------------------------------
6020 | 4/8  | relation  | AccessShareLock | t       | emp
(1 rows)

kingbase=#

2、行共享（ROW SHARE）

SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在目标表上获取此模式的锁。

EXCLUSIVE 与 *ACCESS EXCLUSIVE *锁定模式冲突 。

  1. 从会话 1 中选择获取访问共享锁定的内容

    (SESSION 1)# begin ;
    BEGIN
    (SESSION 1)# select *, pg_sleep(300) from acl for update ;
    ...

  2. 从会话 2 查看锁和锁的PID信息

    kingbase=# ! ksql -c "select pid, virtualxid vxid, locktype lock_type, mode lock_mode, granted, relation::regclass relname from pg_locks WHERE relation = 'acl'::regclass; "

     pid  | vxid | lock_type |  lock_mode   | granted | relname
 ------+------+-----------+---------------------+---------+---------
 7691 |      | relation  | RowShareLock | t       | acl
 (1 rows)

3、行独占（ ROW EXCLUSIVE ）

命令 UPDATE、 DELETE和 INSERT在目标表上获取此锁定模式。

SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE等锁模式，与 ACCESS EXCLUSIVE 锁模式冲突 。

隐式锁定示例：

例子

(SESSION 1)#begin;
BEGIN
(SESSION 1)#select * from acl;
 id | sno | name | sal | dept
----+-----+------+-----+-------
 1 | 1 | A | 200 | IT
 2 | 2 | B | 200 | IT
 3 | 3 | C | 300 | SALES
(3 rows)

(SESSION 1)#insert into acl values(4,4,'D',400,'IT');
INSERT 0 1
(SESSION 1)#

现在，这个会话 1 获得了一个行独占锁。

从会话 2 开始，尝试更改表。

(SESSION 2)#alter table acl drop dept;

会话 2 将等待会话 1 释放锁，因为 alter table drop column 需要 ACCESS EXCLUSIVE 锁，这与 ROW EXCLUSIVE 锁冲突。

kingbase=#  \! psql -c "select pid, virtualxid vxid, locktype lock_type, mode lock_mode, granted, relation::regclass relname from pg_locks WHERE  relation = 'acl'::regclass; "
 pid  |  vxid  | lock_type |      lock_mode      | granted | relname
------+--------+-----------+---------------------+---------+----------+---------
8038 | 4/1364 | relation  | AccessShareLock     | t       | acl
8038 | 4/1364 | relation  | RowExclusiveLock    | t       | acl
8058 | 3/14   | relation  | AccessExclusiveLock | f       | acl
(3 rows)

执行 pg_stat_activity 以找到 PID，

kingbase=# select pid, wait_event_type, wait_event, query from pg_stat_activity;
-[ RECORD 3 ]---+---------------------------------------------------------------
pid             | 8058
wait_event_type | Lock
wait_event      | relation
query           | alter table acl drop dept;

现在,根据 PID 查看 pg_locks 信息

kingbase=# select locktype, relation, virtualxid, transactionid, mode, granted from pg_locks where pid='8058';
   locktype    | relation | virtualxid | transactionid |        mode         | granted
---------------+----------+------------+---------------+---------------------+---------
virtualxid    |          | 3/14       |               | ExclusiveLock       | t
transactionid |          |            |           671 | ExclusiveLock       | t
relation      |    32803 |            |               | AccessExclusiveLock | f
(3 rows)

显式锁定示例：

(SESSION 1)#begin;
BEGIN
(SESSION 1)#lock table acl IN ROW EXCLUSIVE MODE;
LOCK TABLE
(SESSION 1)#\! psql -c "select pid, virtualxid vxid, locktype lock_type, mode lock_mode, granted, relation::regclass relname from pg_locks WHERE  relation = 'acl'::regclass; "
 pid  |  vxid  | lock_type |    lock_mode     | granted | relname
------+--------+-----------+------------------+---------+---------
 8509 | 4/1394 | relation  | RowExclusiveLock | t       | acl
(1 row)

4、共享更新独占（SHARE UPDATE EXCLUSIVE）

由 VACUUM（非 FULL）、ANALYZE、CREATE INDEX CONCURRENTLY、CREATE STATISTICS 和 ALTER TABLE VALIDATE 以及其他少数几个 ALTER TABLE 命令获取共享更新独占锁。

与 SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。此模式保护表，避免并发模式更改和 VACUUM 运行。

隐式锁定示例：

例子

(SESSION 1)#vacuum pgbench_accounts;
..
..

获得的锁是

kingbase=#  SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks ;
  locktype  | transactionid | virtualtransaction |           mode
------------+---------------+--------------------+--------------------------
 relation   |               | 3/180              | RowExclusiveLock
 virtualxid |               | 3/180              | ExclusiveLock
 relation   |               | 5/379              | AccessShareLock
 virtualxid |               | 5/379              | ExclusiveLock
 relation   |               | 3/180              | ShareUpdateExclusiveLock
(5 rows)

可以使用 lock_timeout 来避免等待锁。
s
lock_timeout 将不会在指定时间内获得访问权，如果发生超时则等待锁的会话断开。

SET lock_timeout TO '2s';

显式锁定示例：

(SESSION 1)#begin;
BEGIN
(SESSION 1)#LOCK TABLE acl IN SHARE UPDATE EXCLUSIVE MODE ;
LOCK TABLE
(SESSION 1)#

锁的信息是

kingbase#  \! psql --c "select pid, virtualxid vxid, locktype lock_type, mode lock_mode, granted, relation::regclass relname from pg_locks WHERE  relation = 'acl'::regclass; "
 pid  | vxid  | lock_type |        lock_mode         | granted | relname
 ------+-------+-----------+--------------------------+---------+---------
8719 | 3/185 | relation  | ShareUpdateExclusiveLock | t       | acl
(1 row)

5、共享（SHARE）

由 CREATE INDEX （无 CONCURRENTLY）获得共享锁。

CREATE INDEX 的非并发版本使用 ShareLock 防止表更新，例如 DROP TABLE 或 INSERT 或 DELETE。

ROW EXCLUSIVE与, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, 和 ACCESS EXCLUSIVE lock 模式冲突 。此模式保护表免受并发数据更改的影响。

隐式锁定示例：

(SESSION 1)#create index abalance_ind on pgbench_accounts(balance);
...
...

锁的信息是

kingbase=#  SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks ;
   locktype    | transactionid | virtualtransaction |        mode
---------------+---------------+--------------------+---------------------
 virtualxid    |               | 3/196              | ExclusiveLock
 relation      |               | 5/382              | AccessShareLock
 virtualxid    |               | 5/382              | ExclusiveLock
 relation      |               | 3/196              | ShareLock
 transactionid |           737 | 3/196              | ExclusiveLock
 relation      |               | 3/196              | AccessExclusiveLock
(6 rows)

显式锁定示例：

(SESSION 1)#begin;
BEGIN
(SESSION 1)#SELECT * FROM acl FOR SHARE;
 id | sno | name | sal | dept
----+-----+------+-----+-------
  1 |   1 | A    | 200 | IT
  2 |   2 | B    | 200 | IT
  3 |   3 | C    | 300 | SALES
  4 |   4 | D    | 400 | IT
(4 rows)

(SESSION 1)#

kingbase=#  \! psql -f implicit.sql
 pid  | vxid  | lock_type |  lock_mode   | granted | xid_lock | relname
------+-------+-----------+--------------+---------+----------+---------
 8719 | 3/197 | relation  | RowShareLock | t       |          | acl
(1 row)

6、共享行独占（SHARE ROW EXCLUSIVE）

由 CREATE COLLATION, CREATE TRIGGER和多种ALTER TABLE 获得共享行独占锁。

ROW EXCLUSIVE与, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, 和 ACCESS EXCLUSIVE lock 模式冲突 。此模式可保护表免受并发数据更改的影响，并且是自排斥的，因此一次只能有一个会话保持它。

显式锁定示例：

(SESSION 1)#BEGIN ;
BEGIN
(SESSION 1)#LOCK TABLE acl IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE
(SESSION 1)#

kingbase=#  \! psql -f implicit.sql
 pid  | vxid  | lock_type |       lock_mode       | granted | xid_lock | relname
------+-------+-----------+-----------------------+---------+----------+---------
 8719 | 3/198 | relation  | ShareRowExclusiveLock | t       |          | acl
(1 row)

7、排他（EXCLUSIVE）

由 REFRESH MATERIALIZED VIEW CONCURRENTLY 获得排他锁。

ROW SHARE与, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, 和 ACCESS EXCLUSIVE lock 模式冲突 。这种模式只允许并发 ACCESS SHARE 锁，即只从表中读取，可以与持有这种锁模式的事务并行进行。

隐式锁定示例：

kingbase=# REFRESH MATERIALIZED VIEW CONCURRENTLY pgbench_accounts_mv WITH DATA;
..
..
kingbase=#  SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks ;
   locktype    | transactionid | virtualtransaction |        mode
---------------+---------------+--------------------+---------------------
 relation      |               | 4/18               | AccessShareLock
 relation      |               | 4/18               | AccessShareLock
 virtualxid    |               | 4/18               | ExclusiveLock
 relation      |               | 3/10               | AccessShareLock
 virtualxid    |               | 3/10               | ExclusiveLock
 relation      |               | 4/18               | AccessExclusiveLock
 transactionid |           747 | 4/18               | ExclusiveLock
 relation      |               | 4/18               | AccessShareLock
 relation      |               | 4/18               | ExclusiveLock
(9 rows)

显式锁定示例：

kingbase=# begin;
BEGIN
kingbase=# LOCK TABLE acl IN  EXCLUSIVE MODE;
LOCK TABLE
kingbase=#
kingbase=# \! psql -f implicit.sql
 pid  | vxid | lock_type |   lock_mode   | granted | xid_lock | relname
------+------+-----------+---------------+---------+----------+---------
 2611 | 4/20 | relation  | ExclusiveLock | t       |          | acl
(1 row)

8、访问独占（ACCESS EXCLUSIVE）

由 DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, 和 REFRESH MATERIALIZED VIEW （不带 CONCURRENTLY）命令获取访问独占锁。也有很多形式， ALTER TABLE 在这个级别获取锁。这也是 LOCK TABLE 未明确指定模式的语句的默认锁定模式

与所有模式的锁冲突（ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, 和 ACCESS EXCLUSIVE）。这种模式保证持有者是唯一以任何方式访问表的事务。

隐式锁定示例：

kingbase=# vacuum full pgbench_accounts;
..
..
kingbase=#  SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks ;
   locktype    | transactionid | virtualtransaction |        mode
---------------+---------------+--------------------+---------------------
 virtualxid    |               | 4/22               | ExclusiveLock
 relation      |               | 3/12               | AccessShareLock
 virtualxid    |               | 3/12               | ExclusiveLock
 relation      |               | 4/22               | AccessExclusiveLock
 transactionid |           749 | 4/22               | ExclusiveLock
 relation      |               | 4/22               | AccessExclusiveLock
(6 rows)

显式锁定示例：

kingbase=# begin;
BEGIN
kingbase=# LOCK TABLE acl IN ACCESS EXCLUSIVE MODE;
LOCK TABLE

kingbase=# \! psql -f implicit.sql
pid | vxid | lock_type | lock_mode | granted | xid_lock | relname
------+------+-----------+---------------------+---------+----------+---------
2611 | 4/19 | relation | AccessExclusiveLock | t | | acl
(1 row)

下图描述了锁定模式的冲突。

请求的锁模式当前的锁模式
ACCESS SHAREROW SHAREROW EXCLUSIVESHARE UPDATE EXCLUSIVESHARESHARE ROW EXCLUSIVEEXCLUSIVEACCESS EXCLUSIVE
ACCESS SHARE       X
ROW SHARE      XX
ROW EXCLUSIVE    XXXX
SHARE UPDATE EXCLUSIVE   XXXXX
SHARE  XX XXX
SHARE ROW EXCLUSIVE  XXXXXX
EXCLUSIVE XXXXXXX
ACCESS EXCLUSIVEXXXXXXXX

从上图可以得出的结论是

  • 两个事务不能同时在同一张表上持有冲突模式的锁。

例如，如果存在正在进行的访问共享锁，则另一个会话无法获得独占访问。

  • 许多事务可以同时持有非冲突锁定模式。

例如，根据上图，行共享锁与行独占锁不冲突，因此它们可以由多个事务/会话一次持有。

  • 一些锁定模式是自冲突的

例如，一个ACCESS EXCLUSIVE 锁一次不能被多个事务持有

  • 虽然有些锁定模式，不会自冲突

例如，一个ACCESS SHARE锁可以由多个事务持有。


下一篇，继续介介绍 KingbaseES 行级锁。

存储

相关

7张图揭晓RocketMQ存储设计的精髓

二叉树的存储结构

队列的顺序/链式存储实现

顺序存储的循环队列（c++实现）

MySQL之视图、触发器、事务、存储过程、函数

MySQL数据库----存储引擎

024.hive-hive查询表、查询表的格式（内部表还是外部表、表的存储格式）

c# clr创建mssql的存储过程、函数

mysql存储过程赋值

PL/SQL 存储过程 异常处理 函数 包 视图

【saltstack】saltstack执行结果和事件存储到mysql

数据结构/PTA-邻接矩阵存储图的深度优先遍历/图

标签