暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 磐维表锁和行锁示例

磐维表锁和行锁示例

原创 ThinkPad小黑粉 2024-11-07
304

支持的锁模式有八种，按排他（Exclusive）级别从低到高分别是：

  • 访问共享锁（AccessShareLock）：一个内部锁模式，进行查询（SELECT 操作）时自动施加在被查询的表上 。
  • 行共享锁（RowShareLock）：当语句中采用了 SELECT...FOR UPDATE 和 FOR SHARE 时将使用行共享锁对表加锁。
  • 行排他锁（RowExclusiveLock）：使用 UPDATE、DELETE、INSERT 语句时将使用行排他锁对表加锁。
  • 共享更新排他锁（ShareUpdateExclusiveLock）：使用 VACUUM（不带 FULL 选项）、ANALYZE 或 CREATE lNDEX CONCURRENTLY 语句时使用共享更新排他锁对表加锁。
  • 共享锁（ShareLock）：使用不带 CONCURRENTLY 选项的 CREATE INDEX 语句请求时用共享锁对表加锁。
  • 共享行排他锁（ShareRowExclusiveLock）：类似于排他锁，但是允许行共享。
  • 排他锁（ExclusiveLock）：阻塞行共享和 SELECT... FOR UPDATE。
  • 访问排他锁（AccessExclusiveLock）：被 ALTER TABLE、DROP TABLE 以及 VACUUM FULL 操作要求。
编号锁模式用途冲突关系
1AccessShareLockSELECT8
2RowShareLockSELECT FOR UPDATE/FOR SHARE7|8
3RowExclusiveLockINSERT, UPDATE, DELETE5|6|7|8
4ShareUpdateExclusiveLockVACUUM (non-FULL),ANALYZE, CREATE INDEX CONCURRENTLY4|5|6|7|8
5ShareLockCREATE INDEX (WITHOUT CONCURRENTLY)3|4|6|7|8
6ShareRowExclusiveLockROW SELECT ... FOR UPDATE3|4|5|6|7|8
7ExclusiveLockblocks ROW SHARE/SELECT ... FOR UPDATE2|3|4|5|6|7|8
8AccessExclusiveLockALTER TABLE, DROP TABLE, VACUUM FULL1|2|3|4|5|6|7|8

表锁实操

1. 创建测试表

create table test_lock (id integer,name varchar2(100));
insert into test_lock values (1,'test_lock');

2. 第一个窗口，查询pid，并锁定一张表
查看当前会话的pid

select pg_backend_pid();
+----------------+
| pg_backend_pid |
+----------------+
| 22703474472704 |
+----------------+
 
START transaction ;
lock table test_lock;

3. 第二个窗口，对表加锁

select pg_backend_pid(); 
+----------------+
| pg_backend_pid |
+----------------+
| 22700840974080 |
+----------------+
 
START transaction ;
lock table test_lock;

执行sql语句发现，该窗口的锁表语句会被阻塞住

4. 第三个窗口中查看数据库中的锁的情况

select pg_backend_pid();
+----------------+
| pg_backend_pid |
+----------------+
| 22703260038912 |
+----------------+
 
select locktype,relation::regclass as rel,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2,pid,mode,granted from pg_locks where pid = 22703474472704;
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
|   locktype    |    rel    |   vxid   |   xid    |  vxid2   |      pid       |        mode         | granted |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
| virtualxid    |           | 27/16111 |          | 27/16111 | 22703474472704 | ExclusiveLock       | t       |
| transactionid |           |          | 25664149 | 27/16111 | 22703474472704 | ExclusiveLock       | t       |
| relation      | test_lock |          |          | 27/16111 | 22703474472704 | AccessExclusiveLock | t       |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
(3 行记录)

5. 查看两个进程的锁情况

select locktype,relation::regclass as rel,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2,pid,mode,granted from pg_locks where pid in (22703474472704,22700840974080) order by pid;

+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
|   locktype    |    rel    |   vxid   |   xid    |  vxid2   |      pid       |        mode         | granted |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
| virtualxid    |           | 26/3566  |          | 26/3566  | 22700840974080 | ExclusiveLock       | t       |
| transactionid |           |          | 25664777 | 26/3566  | 22700840974080 | ExclusiveLock       | t       |
| relation      | test_lock |          |          | 26/3566  | 22700840974080 | AccessExclusiveLock | f       |
| virtualxid    |           | 27/16111 |          | 27/16111 | 22703474472704 | ExclusiveLock       | t       |
| transactionid |           |          | 25664149 | 27/16111 | 22703474472704 | ExclusiveLock       | t       |
| relation      | test_lock |          |          | 27/16111 | 22703474472704 | AccessExclusiveLock | t       |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
(6 行记录)

发现两个进程都对表加了锁
进程 22703474472704 中的granted字段为t，说明它获得了这把锁
进程 22700840974080 中的granted字段为f，说明该进程没有获得这把锁，从而被阻塞


行锁实操

1. 第一个窗口执行如下操作（在加表锁的基础上加行锁）

select * from test_lock for update;

2. 第三个窗口中查看数据库中的锁的情况

select locktype,relation::regclass as rel,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2,pid,mode,granted from pg_locks where pid in (22703474472704,22700840974080) order by pid;
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
|   locktype    |    rel    |   vxid   |   xid    |  vxid2   |      pid       |        mode         | granted |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
| virtualxid    |           | 27/16113 |          | 27/16113 | 22703474472704 | ExclusiveLock       | t       |
| transactionid |           |          | 25665048 | 27/16113 | 22703474472704 | ExclusiveLock       | t       |
| relation      | test_lock |          |          | 27/16113 | 22703474472704 | RowShareLock        | t       |
| relation      | test_lock |          |          | 27/16113 | 22703474472704 | AccessExclusiveLock | t       |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
(4 行记录)

行锁不仅会在表上加意向锁，也会在相应的主键上加意向锁。


3. 第二个窗口加行锁：

select * from test_lock for update;该窗口阻塞


4. 第三个窗口中查看数据库中的锁的情况

select locktype,relation::regclass as rel,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2,pid,mode,granted from pg_locks where pid in (22703474472704,22700840974080) order by pid;
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
|   locktype    |    rel    |   vxid   |   xid    |  vxid2   |      pid       |        mode         | granted |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
| virtualxid    |           | 26/3567  |          | 26/3567  | 22700840974080 | ExclusiveLock       | t       |
| relation      | test_lock |          |          | 26/3567  | 22700840974080 | RowShareLock        | f       |
| virtualxid    |           | 27/16113 |          | 27/16113 | 22703474472704 | ExclusiveLock       | t       |
| transactionid |           |          | 25665048 | 27/16113 | 22703474472704 | ExclusiveLock       | t       |
| relation      | test_lock |          |          | 27/16113 | 22703474472704 | RowShareLock        | t       |
| relation      | test_lock |          |          | 27/16113 | 22703474472704 | AccessExclusiveLock | t       |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
(7 行记录)

xid为 25665048 的锁被进程 22703474472704 持有了，所以 22700840974080 的进程获取锁标识为False。

5. 查看阻塞sql语句

Select datname,application_name,client_addr,'select pg_terminate_backend('|| pid || ');',query from pg_stat_activity where pid=22703474472704;
+----------+------------------+-------------+----------------------------------------------+-------------------------------------+
| datname  | application_name | client_addr |                   ?column?                   |                query                |
+----------+------------------+-------------+----------------------------------------------+-------------------------------------+
| postgres | gsql             |             | select pg_terminate_backend(22703474472704); | select * from test_lock for update; |
+----------+------------------+-------------+----------------------------------------------+-------------------------------------+


6. 根据线程ID结束会话

SELECT pg_terminate_backend(22703474472704);

注意：
pg_locks并不能显示出每个行锁的信息，因为行锁信息并不会被记录到共享内存中。如果记录到内存，意味着对表做全表更新时，表有多少行就需要在内存中记录多少条行锁信息，那么内存会吃不消，所以 opengauss 设计成不在内存中记录行锁信息。


行锁示例2(RowExclusiveLock 行排他锁 阻塞 AccessExclusiveLock 访问排他锁)

1. 第一个窗口执行开始事务和UPDATE语句，此时会话表上的锁模式为RowExclusiveLock

BEGIN;
update test_lock set id = 2 where id = 1;

2. 第二个窗口执行ALTER TABLE语句,这时看到修改表语句处于等待状态
此时会话表上需要加锁的模式是AccessExclusiveLock，该模式与第一个窗口上持有的锁模式冲突，所以此SQL语句会一直等在那里。

alter table test_lock add info varchar2(20);

ERROR:  Lock wait timeout: thread 22700840974080 on node dn_6001_6002 waiting for AccessExclusiveLock on relation 68651 of database 19356 after 60000.124 ms
描述:  blocked by hold lock thread 22703474472704, statement <update test_lock set id = 2 where id = 1;>, hold lockmode RowExclusiveLock.


3. 第三个窗口查看数据库中的锁的情况

select locktype,relation::regclass as rel,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2,pid,mode,granted from pg_locks where pid in (22703474472704,22700840974080) order by pid;

+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
|   locktype    |    rel    |   vxid   |   xid    |  vxid2   |      pid       |        mode         | granted |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
| virtualxid    |           | 26/3569  |          | 26/3569  | 22700840974080 | ExclusiveLock       | t       |
| transactionid |           |          | 25665570 | 26/3569  | 22700840974080 | ExclusiveLock       | t       |
| relation      | test_lock |          |          | 26/3569  | 22700840974080 | AccessExclusiveLock | f       |
| virtualxid    |           | 27/16114 |          | 27/16114 | 22703474472704 | ExclusiveLock       | t       |
| transactionid |           |          | 25665491 | 27/16114 | 22703474472704 | ExclusiveLock       | t       |
| relation      | test_lock |          |          | 27/16114 | 22703474472704 | RowExclusiveLock    | t       |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+

--查找被阻塞的进程信息

select locktype,relation::regclass as rel,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2,pid,mode,granted from pg_locks where granted='f'; 
+----------+-----------+------+-----+---------+----------------+---------------------+---------+
| locktype |    rel    | vxid | xid |  vxid2  |      pid       |        mode         | granted |
+----------+-----------+------+-----+---------+----------------+---------------------+---------+
| relation | test_lock |      |     | 26/3569 | 22700840974080 | AccessExclusiveLock | f       |
+----------+-----------+------+-----+---------+----------------+---------------------+---------+ 

 这里可以看到等待的锁是一个表锁，锁模式是 AccessExclusiveLock ，是第二个窗口的pid 22700840974080


-- 查看被阻塞的sql语句

SELECT datname, usename, state, query FROM pg_stat_activity WHERE waiting = true;
+----------+---------+--------+----------------------------------------------+
| datname  | usename | state  |                    query                     |
+----------+---------+--------+----------------------------------------------+
| postgres | omm     | active | alter table test_lock add info varchar2(20); |
+----------+---------+--------+----------------------------------------------+


4. 查看阻塞sql语句

Select datname,application_name,client_addr,'select pg_terminate_backend('|| pid || ');',query from pg_stat_activity where pid=22703474472704;
+----------+------------------+-------------+----------------------------------------------+-------------------------------------------+
| datname  | application_name | client_addr |                   ?column?                   |                   query                   |
+----------+------------------+-------------+----------------------------------------------+-------------------------------------------+
| postgres | gsql             |             | select pg_terminate_backend(22703474472704); | update test_lock set id = 2 where id = 1; |
+----------+------------------+-------------+----------------------------------------------+-------------------------------------------+


5. 查看锁阻塞详情

SELECT w.query as waiting_query,
w.pid as w_pid,
w.usename as w_user,
l.query as locking_query,
l.pid as l_pid,
l.usename as l_user,
t.schemaname || '.' || t.relname as tablename
from pg_stat_activity w join pg_locks l1 on w.pid = l1.pid
and not l1.granted join pg_locks l2 on l1.relation = l2.relation
and l2.granted join pg_stat_activity l on l2.pid = l.pid join pg_stat_user_tables t on l1.relation = t.relid
where w.waiting;
+----------------------------------------------+----------------+--------+-------------------------------------------+----------------+--------+------------------+
|                waiting_query                 |     w_pid      | w_user |               locking_query               |     l_pid      | l_user |    tablename     |
+----------------------------------------------+----------------+--------+-------------------------------------------+----------------+--------+------------------+
| alter table test_lock add info varchar2(20); | 22585783744256 | omm    | update test_lock set id = 2 where id = 1; | 22703474472704 | omm    | public.test_lock |
+----------------------------------------------+----------------+--------+-------------------------------------------+----------------+--------+------------------+
locking_query：持锁sql
waiting_query：被阻塞sql


6. 根据持锁线程ID结束会话

SELECT pg_terminate_backend(22703474472704);


##########################################################################

行锁实操3(ShareLock 共享锁阻塞 RowExclusiveLock 行排他锁)

1. 第一个窗口执行 create index 语句
此时会话表上需要加锁的模式是 ShareLock

--查看当前会话的pid

select pg_backend_pid();
+----------------+
| pg_backend_pid |
+----------------+
| 22703474472704 |
+----------------+

BEGIN;
create index idx_test_lock_id on test_lock (id);

2. 第二个窗口执行开始事务和 insert 语句，这时看到修改表语句处于等待状态
此时会话表上的锁模式为 RowExclusiveLock，该模式与第一个窗口上持有的锁模式冲突，所以此SQL语句会一直等在那里。
--查看当前会话的pid

select pg_backend_pid();
+----------------+
| pg_backend_pid |
+----------------+
| 22700840974080 |
+----------------+

BEGIN;
insert into test_lock values (2,'test_lock');

ERROR:  Lock wait timeout: thread 22700840974080 on node dn_6001_6002 waiting for RowExclusiveLock on relation 68651 of database 19356 after 60000.593 ms
第1行insert into test_lock values (2,'test_lock');
                 ^
描述:  blocked by hold lock thread 22703474472704, statement <create index idx_test_lock_id on test_lock (id);>, hold lockmode ShareLock.


3. 第三个窗口查看数据库中的锁的情况

select locktype,relation::regclass as rel,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2,pid,mode,granted from pg_locks where pid in (22703474472704,22700840974080) order by pid;

+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
|   locktype    |    rel    |   vxid   |   xid    |  vxid2   |      pid       |        mode         | granted |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+
| relation      | test_lock |          |          | 26/3557  | 22700840974080 | RowExclusiveLock    | f       |
| virtualxid    |           | 26/3557  |          | 26/3557  | 22700840974080 | ExclusiveLock       | t       |
| relation      | 108289    |          |          | 27/16110 | 22703474472704 | AccessExclusiveLock | t       |
| virtualxid    |           | 27/16110 |          | 27/16110 | 22703474472704 | ExclusiveLock       | t       |
| transactionid |           |          | 25461732 | 27/16110 | 22703474472704 | ExclusiveLock       | t       |
| relation      | test_lock |          |          | 27/16110 | 22703474472704 | AccessShareLock     | t       |
| relation      | test_lock |          |          | 27/16110 | 22703474472704 | ShareLock           | t       |
+---------------+-----------+----------+----------+----------+----------------+---------------------+---------+

--查找被阻塞的进程信息

select locktype,relation::regclass as rel,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2,pid,mode,granted from pg_locks where granted='f'; 
+----------+-----------+------+-----+---------+----------------+------------------+---------+
| locktype |    rel    | vxid | xid |  vxid2  |      pid       |       mode       | granted |
+----------+-----------+------+-----+---------+----------------+------------------+---------+
| relation | test_lock |      |     | 26/3557 | 22700840974080 | RowExclusiveLock | f       | +----------+-----------+------+-----+---------+----------------+------------------+---------+

这里可以看到等待的锁是一个行排他锁，锁模式是 RowExclusiveLock ，是第二个窗口的pid 22700840974080 

-- 查看被阻塞的sql语句

SELECT datname, usename, state, query FROM pg_stat_activity WHERE waiting = true;
+----------+---------+--------+-----------------------------------------------+
| datname  | usename | state  |                     query                     |
+----------+---------+--------+-----------------------------------------------+
| postgres | omm     | active | insert into test_lock values (2,'test_lock'); |
+----------+---------+--------+-----------------------------------------------+


4. 查看阻塞sql语句

Select datname,application_name,client_addr,'select pg_terminate_backend('|| pid || ');',query from pg_stat_activity where pid=22703474472704;
+----------+------------------+-------------+----------------------------------------------+--------------------------------------------------+
| datname  | application_name | client_addr |                   ?column?                   |                      query                       |
+----------+------------------+-------------+----------------------------------------------+--------------------------------------------------+
| postgres | gsql             |             | select pg_terminate_backend(22703474472704); | create index idx_test_lock_id on test_lock (id); |
+----------+------------------+-------------+----------------------------------------------+--------------------------------------------------+


5. 查看锁阻塞详情

SELECT w.query as waiting_query,
w.pid as wait_pid,
w.usename as w_user,
l.query as locking_query,
l.pid as lock_pid,
l.usename as l_user,
t.schemaname || '.' || t.relname as tablename
from pg_stat_activity w join pg_locks l1 on w.pid = l1.pid
and not l1.granted join pg_locks l2 on l1.relation = l2.relation
and l2.granted join pg_stat_activity l on l2.pid = l.pid join pg_stat_user_tables t on l1.relation = t.relid
where w.waiting;
+-----------------------------------------------+----------------+--------+--------------------------------------------------+----------------+--------+------------------+
|                 waiting_query                 |     wait_pid   | w_user |                  locking_query                   |   lock_pid     | l_user |    tablename     |
+-----------------------------------------------+----------------+--------+--------------------------------------------------+----------------+--------+------------------+
| insert into test_lock values (2,'test_lock'); | 22700840974080 | omm    | create index idx_test_lock_id on test_lock (id); | 22703474472704 | omm    | public.test_lock |
| insert into test_lock values (2,'test_lock'); | 22700840974080 | omm    | create index idx_test_lock_id on test_lock (id); | 22703474472704 | omm    | public.test_lock |
+-----------------------------------------------+----------------+--------+--------------------------------------------------+----------------+--------+------------------+
locking_query：持锁sql
waiting_query：被阻塞sql


6. 根据持锁线程ID结束会话

SELECT pg_terminate_backend(22703474472704);











墨力计划磐维数据库
最后修改时间：2024-11-07 17:34:58
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论