暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL 实力解剖锁系列(一) 表级锁

PostgreSQL 实力解剖锁系列(一) 表级锁

励志成为PostgreSQL大神 2021-03-25
2317

请考虑以下情形：

1.会话A和会话B同时想更新同一行记录。

2.会话A查询表中的数据，会话B直接truncate或drop表。

3.会话A正在修改数据，会话B直接vacuum full这张表。

如果任其发展，就会出现大问题。因此，您需要一个锁来控制并发。

在官方文档中，PostgreSQL锁分为四种类型：表级锁，行级锁，页面级锁和咨询锁。因为官方文档提到，页面级别的共享/排他锁被用来控制对共享缓冲池中表页面的读/写。这些锁在行被抓取或者更新后马上被释放。应用开发者通常不需要关心页级锁。因此，我们只对表级锁，行级锁和页面级锁感兴趣。

❝

In addition to table
and row
locks, page-level share/exclusive
locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a row is fetched or updated. Application developers normally need not be concerned with page-level locks.

❞

表级锁

表级锁是通过内置的SQL获取的(隐式)。当然，您也可以使用lock table命令显示获取。

  • ACCESS SHARE
  • ROW SHARE
  • ROW EXCLUSIVE
  • SHARE UPDATE EXCLUSIVE
  • SHARE
  • SHARE ROW EXCLUSIVE
  • EXCLUSIVE
  • ACCESS EXCLUSIVE

这些锁的模式在语言上描述非常枯燥无味，我们将通过案例进行解释。在开始案例讲解前，我们需要先创建一张表。

postgres=# create table testlock(id serial,name varchar(30));
CREATE TABLE

postgres=# insert into testlock(name) values('aaa'), ('bbb');
INSERT 0 2

Access Share

--会话1
postgres=# begin;
BEGIN
postgres=*# select *,pg_sleep(30000) from testlock;

打开另外一个会话，观察pg_locks视图。

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        | relname  | locktype | relation | pid  |      mode       | granted 
-------------------------------+----------+----------+----------+------+-----------------+---------
 2021-01-21 16:18:40.184766+00 | testlock | relation |    90278 | 2845 | AccessShareLock | t

显然，很容易看到会话1的锁称为AccessShareLock。此时，如果我们打开会话2来执行truncate table操作，让我们看一下会发生什么事。

--会话2
postgres=# begin;
BEGIN
postgres=*# truncate table testlock;

此时再次查看pg_locks视图。

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        | relname  | locktype | relation | pid  |        mode         | granted 
-------------------------------+----------+----------+----------+------+---------------------+---------
 2021-01-21 16:19:32.808111+00 | testlock | relation |    90278 | 2845 | AccessShareLock     | t
 2021-01-21 16:19:32.808123+00 | testlock | relation |    90278 | 2852 | AccessExclusiveLock | f

此时会话2需要获取AccessExclusiveLock模式的锁。由于锁冲突，您必须等待。pg_locks视图的granted的字段为true，表示当前进程2845持有了AccessShareLock锁，而进程2852想要申请AccessExclusiveLock模式的锁，granted字段为false，表示需要等待。

白话一点说，我正在对这个表做一个查询操作，我还没有完成查询，在我查的过程中是不允许任何人 truncate table。您可以想象一下，我查到一半有人truncate table了，查询就会出错了。

那么显式做AccessShareLock的方法也很简单，直接lock table in access share mode就行。

postgres=# begin;
BEGIN
postgres=*# lock table testlock in access share mode;
LOCK TABLE

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        | relname  | locktype | relation | pid  |      mode       | granted 
-------------------------------+----------+----------+----------+------+-----------------+---------
 2021-01-21 16:26:45.890428+00 | testlock | relation |    90278 | 2872 | AccessShareLock | t

Row Share

row share主要是select for update和 select for share命令对表获取的锁定模式，它与exclusive和 access exclusive锁定模式冲突。

--会话1执行
postgres=# begin;
BEGIN
postgres=*# select * from testlock for update;
 id | name 
----+------
  1 | aaa
  2 | bbb

查看pg_locks，可以发现此时已经在表上获取了RowShareLock锁。

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        | relname  | locktype | relation | pid  |     mode     | granted 
-------------------------------+----------+----------+----------+------+--------------+---------
 2021-01-22 01:48:42.451736+00 | testlock | relation |    90278 | 2739 | RowShareLock | t

此时如果你要修改表上的行数据，是会被阻塞的。

--会话2执行
postgres=# update testlock set name='ccc' where id=1;

--再次查看pg_locks
postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        | relname  | locktype | relation | pid  |       mode       | granted 
-------------------------------+----------+----------+----------+------+------------------+---------
 2021-01-22 02:04:14.933484+00 | testlock | relation |    90278 | 2802 | RowExclusiveLock | t
 2021-01-22 02:04:14.93349+00  | testlock | relation |    90278 | 2759 | RowShareLock     | t
 2021-01-22 02:04:14.933491+00 | testlock | tuple    |    90278 | 2802 | ExclusiveLock    | t

请注意，在这里 granted都是true，表示每个进程都可以拥有这个锁，但既然每个人都可以拥有这个锁，为什么第二个会话的update不能执行呢？因为 PostgreSQL的 update实际上是 insert记录到新的元组，然后将旧元组标记为 dead。因此，这里将使用RowExclusiveLock行锁定，然后locktype为tuple的持有ExclusiveLock锁。虽然都能持有锁，但后部的锁会被前面的 RowShareLock所阻止。select for update语法同时涉及到行级锁，比较特殊，我们后面说行级锁的时候还会讲到。

Row Exclusive Lock

当insert、 update、 delete语句执行时，在目标表上都将获取RowExclusiveLock。它与 share、 share row exclusive、 exclusive、 access exclusive锁定模式相冲突。

--会话1
postgres=# begin;
BEGIN
postgres=*# insert into testlock(name) values('ccc');
INSERT 0 1

再看一下pg_locks，此时表中已经存在 RowExclusiveLock锁。

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |     relname     | locktype | relation | pid  |       mode       | granted 
-------------------------------+-----------------+----------+----------+------+------------------+---------
 2021-01-22 06:16:37.459052+00 | testlock_id_seq | relation |    90276 | 3548 | RowExclusiveLock | t
 2021-01-22 06:16:37.459062+00 | testlock        | relation |    90278 | 3548 | RowExclusiveLock | t

请注意这里有两个排他锁，因为我们设置了 id为 serial类型，因此在这个序列上还持有一个独占锁。在这个时候，如果 session 2上执行了一个修改表字段的操作，将被阻止。

postgres=# alter table testlock alter column name type varchar(30);

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |     relname     | locktype | relation | pid  |        mode         | granted 
-------------------------------+-----------------+----------+----------+------+---------------------+---------
 2021-01-22 06:34:07.193006+00 | testlock_id_seq | relation |    90276 | 3548 | RowExclusiveLock    | t
 2021-01-22 06:34:07.193013+00 | testlock        | relation |    90278 | 3548 | RowExclusiveLock    | t
 2021-01-22 06:34:07.193014+00 | testlock        | relation |    90278 | 3570 | AccessExclusiveLock | f
(3 rows)

您也许会说我不是对表中行操作吗？怎样会是表级锁呢，这个我也有点疑惑。事实上，这些名字并不重要。官方文档中对此有一些解释。要记住所有这些锁定模式都是表级锁定，即使它们的名字中含有" row"(这是历史遗留问题)。

❝

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.

❞
postgres=# begin;
BEGIN
postgres=*# lock table testlock IN ROW EXCLUSIVE MODE;
LOCK TABLE

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted           
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid 
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        | relname  | locktype | relation | pid  |       mode       | granted 
-------------------------------+----------+----------+----------+------+------------------+---------
 2021-01-22 07:48:39.062274+00 | testlock | relation |    90278 | 3833 | RowExclusiveLock | t
(1 row)

因此，显式获取Row Exclusive Lock的方法也很简单，直接用 lock table in row exclusive mode。

Share Update Exclusive

该锁主要防止一个表不受并发模式改变和VACUUM运行的影响。执行以下命令能获取锁：vacuum (不含 vacuum full)、 analyze、 create index concurrently、 reindex concurrently、 create statistics和其他一些 alter index和 alter table。它与share update exclusive、 share、 share  row exclusive、 exclusive和 access exclusive模式互斥。

--会话1执行vacuum
pgbench=# vacuum pgbench_accounts;

通过检查pg_locks可以发现，此时进程已经获取了 ShareUpdateExclusiveLock锁。

pgbench=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |        relname        | locktype | relation | pid  |           mode           | granted 
-------------------------------+-----------------------+----------+----------+------+--------------------------+---
 2021-01-22 16:18:16.188378+08 | pgbench_accounts_pkey | relation |    17862 | 2193 | RowExclusiveLock         | t
 2021-01-22 16:18:16.188389+08 | pgbench_accounts      | relation |    17848 | 2193 | ShareUpdateExclusiveLock | t

使用in share update exclusive mode，显式指定获取锁。

postgres=# begin;
BEGIN
postgres=*# LOCK TABLE testlock in share update exclusive mode;

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        | relname  | locktype | relation | pid  |           mode           | granted 
-------------------------------+----------+----------+----------+------+--------------------------+---------
 2021-01-22 08:31:55.400279+00 | testlock | relation |    90278 | 2777 | ShareUpdateExclusiveLock | t

Share Lock

此模式可以保护表免受并行数据更改的影响。通过create index(不带concurrently)获取。它与row exclusive、 share update exclusive、 share row exclusive、exclusive、 access exclusive模式互斥。

--会话1
pgbench=# create index idx_filler on pgbench_accounts(filler);        

查看pg_locks，您会发现此时表上已有 ShareLock锁。有三个锁在上面。

pgbench=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |     relname      | locktype | relation | pid  |   mode    | granted 
-------------------------------+------------------+----------+----------+------+-----------+---------
 2021-01-22 16:36:47.87156+08  | pgbench_accounts | relation |    17848 | 7168 | ShareLock | t
 2021-01-22 16:36:47.871575+08 | pgbench_accounts | relation |    17848 | 7169 | ShareLock | t
 2021-01-22 16:36:47.871577+08 | pgbench_accounts | relation |    17848 | 2193 | ShareLock | t

之所以有三个锁，是因为并发模式已自动打开。在pg_stat_activity视图中可以看到，共发起了三个create index指令。此时如果执行 insert、 update、 delete都会等待，您将看到进程申请不到 RowExclusiveLock锁定。所以创建索引的时候不加concurrently会导致表上的事务无法执行，会产生很大的影响。

--会话2
pgbench=# delete from pgbench_accounts where aid=10000;

pgbench=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |     relname      | locktype | relation | pid  |       mode       | granted 
-------------------------------+------------------+----------+----------+------+------------------+---------
 2021-01-22 16:40:37.877626+08 | pgbench_accounts | relation |    17848 | 7828 | RowExclusiveLock | f
 2021-01-22 16:40:37.877637+08 | pgbench_accounts | relation |    17848 | 7739 | ShareLock        | t
 2021-01-22 16:40:37.877639+08 | pgbench_accounts | relation |    17848 | 7740 | ShareLock        | t
 2021-01-22 16:40:37.877641+08 | pgbench_accounts | relation |    17848 | 2193 | ShareLock        | t
(4 rows)

使用 in share update exclusive mode，显式指定使用方式。

pgbench=# select * from pgbench_accounts for share;

pgbench=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |        relname        | locktype | relation | pid  |     mode     | granted 
-------------------------------+-----------------------+----------+----------+------+--------------+---------
 2021-01-22 16:44:30.094752+08 | pgbench_accounts_pkey | relation |    17862 | 8411 | RowShareLock | t
 2021-01-22 16:44:30.094763+08 | pgbench_accounts      | relation |    17848 | 8411 | RowShareLock | t
(2 rows)

Share Row Exclusive

此模式保护表不受并发数据修改的影响，并且是排他的，因此在一个时刻只能有一个会话持有它。从 CREATE TRIGGER、 CREATE COLLATION和某些 ALTER TABLE形式中获得改锁。它与 row exclusive、 share update exclusive、 share、 share row exclusive、 exclusive和 access exclusive等锁定模式互斥。

该锁定并不是很好的演示，我们使用 in share row exclusivemode直接显式锁定。

--会话1
postgres=# begin;
BEGIN
postgres=*# lock table testlock in share row exclusive mode;
LOCK TABLE

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        | relname  | locktype | relation | pid  |         mode          | granted 
-------------------------------+----------+----------+----------+------+-----------------------+---------
 2021-01-22 08:53:02.078563+00 | testlock | relation |    90278 | 2851 | ShareRowExclusiveLock | t

此时会话2执行事务，就会等待。

--会话2
postgres=# update testlock set name='ddd' where id=1;

postgres=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        | relname  | locktype | relation | pid  |         mode          | granted 
-------------------------------+----------+----------+----------+------+-----------------------+---------
 2021-01-22 08:54:25.440777+00 | testlock | relation |    90278 | 2855 | RowExclusiveLock      | f
 2021-01-22 08:54:25.440799+00 | testlock | relation |    90278 | 2851 | ShareRowExclusiveLock | t

Exclusive

这种模式只允许并发的ACCESS SHARE锁，即只有来自于表的读操作可以与一个持有该锁模式的事务并行处理。实际上，前面演示的时候我们也遇到了 ExclusiveLock，在演示 ROW SHARE锁的时候，但是那个锁是针对tuple的，如果要针对relation，只有命令refresh materialized view concurrently才符合。此模式与 row share, row exclusive, share update exclusive, share, share row exclusive, exclusive和 access exclusive锁模式互斥。

我们来测试一下。

--会话1
pgbench=# CREATE MATERIALIZED VIEW pgbench_tellers_mv as select * from pgbench_tellers;
SELECT 200000

pgbench=# create unique index idx_tid on pgbench_tellers_mv(tid);

pgbench=# update pgbench_tellers set filler='aaa';
UPDATE 200000

pgbench=# REFRESH MATERIALIZED VIEW CONCURRENTLY pgbench_tellers_mv with data;

现在来看pg_locks，你可以看在物化视图上获取了AccessShareLock，然后又获取了ExclusiveLock锁。与前面拗口描述一致。

pgbench=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |       relname        | locktype | relation | pid  |      mode       | granted 
-------------------------------+----------------------+----------+----------+------+-----------------+---------
 2021-01-22 17:07:24.979614+08 | pgbench_tellers_pkey | relation |    17860 | 8411 | AccessShareLock | t
 2021-01-22 17:07:24.979628+08 | pgbench_tellers      | relation |    17845 | 8411 | AccessShareLock | t
 2021-01-22 17:07:24.979634+08 | pgbench_tellers_mv   | relation |    24880 | 8411 | AccessShareLock | t
 2021-01-22 17:07:24.979636+08 | pgbench_tellers_mv   | relation |    24880 | 8411 | ExclusiveLock   | t

只要执行in exclusive mode，就可以轻松地显式锁定。

pgbench=# begin;
BEGIN
pgbench=# LOCK TABLE pgbench_tellers in exclusive mode;
LOCK TABLE

pgbench=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |     relname     | locktype | relation | pid  |     mode      | granted 
-------------------------------+-----------------+----------+----------+------+---------------+---------
 2021-01-22 17:13:34.709146+08 | pgbench_tellers | relation |    17845 | 8411 | ExclusiveLock | t

Access Exclusive lock

此模式保证只有持有者才能访问表。它与所有的模式(access share、 row share、 row exclusive、 share update exclusive、 share、share row exclusive、 exclusive和 access exclusive)都互斥。这种主要是执行诸如 drop table, truncate, reindex, cluster, vacuum full和 refresh materialized view(不包括concurrently)等命令，需要使用排他锁。

--会话1
pgbench=# vacuum full pgbench_accounts;


pgbench=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |     relname      | locktype | relation |  pid  |        mode         | granted 
-------------------------------+------------------+----------+----------+-------+---------------------+---------
 2021-01-22 17:46:30.559696+08 | pgbench_accounts | relation |    17848 | 19835 | AccessExclusiveLock | t
(1 row)

使用 lock table语句，不指定模式将显示持有排他锁。

pgbench=# begin;
BEGIN
pgbench=# lock table pgbench_accounts;
LOCK TABLE

pgbench=# SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype,pg_locks.relation,pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active';
        clock_timestamp        |     relname      | locktype | relation |  pid  |        mode         | granted 
-------------------------------+------------------+----------+----------+-------+---------------------+---------
 2021-01-22 17:48:53.105304+08 | pgbench_accounts | relation |    17848 | 20348 | AccessExclusiveLock | t

全部模式介绍完毕。我们用一张图可以看到所有锁定互斥的情况。

总结以下几个可以得出结论：

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

例如，如果存在正在进行的access share lock，则另一个会话将无法获取access exclusive。

  • 「许多事务可以同时保持无冲突的锁定模式。」

例如，如上图所示，Row Share lock与Row Exclusive lock没有冲突，因此一次可以由多个事务会话持有。

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

例如，一个access exclusive
锁不能被一个以上的事务持有。

  • 「有一些锁定模式不会自冲突」

例如，一个access share
锁可以被多个事务持有。

后记

今天就到这里，下一篇是行级锁。


postgresql
文章转载自励志成为PostgreSQL大神，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论