PostgreSQL中的行锁

news/2024/12/18 20:40:09/

行锁在PG中比较特殊，在9.4以前，只有两种类型的行锁， FOR UPDATE 和FOR SHARE，因为只有两种锁，粒度比较大，极大的影响了并发性。所以从9.4开始引入了FOR KEY SHARE和FOR NO KEY UPDATE这两种行锁。目前这四种行锁，其中两个是排他性锁，一次只能被一个事务获取；另外两个则是可由多个事务同时持有的共享锁。

有时候很多人使用SELECT…FOR UPDATE显式地锁定某些行以防止并发更新。但大多数时候，这种行为太过了。只有在打算删除整行或修改主键列或唯一键列时，才应该使用FOR UPDATE锁。否则，最好使用FOR NO KEY UPDATE锁，这样就不会阻塞对引用正在更新的表的表的插入。

PG中行锁保存在当前堆元组的头部，而且行锁实际上是堆页面的一个属性，不是真正意义上的锁。由于不在内存中，所以对内存也不会有影响。但是每个行锁都会修改表，修改后的块必须写回持久存储。所以行锁也有额外的I/O负载。

当我们获取行锁的时候会发生以下流程步骤：
  1. 首先检查是否有别的事物在该行上持有锁。
  2. 如果没有，第一个事物则在该行加上一个元组锁，元组锁是在内存中的，该锁的目的是如果多个会话想要锁定同一行，它们必须排队。这个队列由元组锁维护。该锁保护行免受并发修改，直到我们将锁写入行本身。
  3. 第二个事物检查到其他事务持有该行上的锁，则该事物持有元组锁并进入睡眠状态，等待第一个事务结束。一旦第一个事物结束，阻塞也就结束，我们就可以继续了。但首先，我们检查在等待的过程中是否存在对该行的并发修改。如果是，则该行为取决于当前事务隔离级别,如果是READ COMMITTED隔离级别，那么拿取已提交事物的最新行版本，如果是其它更高的隔离级别，那么会抛出一个序列化错误。
  4. 如果第一个事物没有提交，第三个事务(以及随后的任何事务)也看到该行被锁定。它阻塞并等待行上的元组锁。
  5. 以上事物一旦所有阻塞的行锁都消失了，那么我们得到了该行的最新版本，就可以继续了。最后修改行上的xmax和一些提示位，并释放元组锁。
模拟一下以上的流程，看下具体示例：
session 1: 创建测试表，并模拟一个我们正常的更新，一般来说for no key update和普通执行的update命令类似
postgres=# create table test (id int, info text);
CREATE TABLE
postgres=# insert into test values (1,'a'),(2,'b'); 
INSERT 0 2
postgres=# begin ;
BEGIN
postgres=*# select txid_current(), pg_backend_pid();txid_current | pg_backend_pid 
--------------+----------------2277673 |           1767
(1 row)postgres=*#  select * from test where id=1 for no key update;id | info 
----+------1 | a
(1 row)session 2:
postgres=# begin ;
BEGIN
postgres=*# select txid_current(), pg_backend_pid();txid_current | pg_backend_pid 
--------------+----------------2277674 |           2447
(1 row)
postgres=*# select * from test where id=1 for no key update;
此处夯住session 3:
postgres=# begin;
BEGIN
postgres=*# select txid_current(), pg_backend_pid();txid_current | pg_backend_pid 
--------------+----------------2277675 |           4229
(1 row)postgres=*#  select * from test where id=1 for no key update;
#等待持有tuple锁

如下图，session4分别查一下持有的锁，注意一下tuple锁，session2的granted为t,session3为f
在这里插入图片描述
一旦session1,pid为1767的事物提交了，那么就如下图，session2,pid为2447的会释放tuple锁，然后session3,pid为4229的获取tuple锁，granted变为t。
在这里插入图片描述
当然可以通过pgrowlocks插件查看锁内容，如下：

postgres=# SELECT * FROM pgrowlocks('test');locked_row | locker  | multi |   xids    |         modes         |  pids  
------------+---------+-------+-----------+-----------------------+--------(0,1)      | 2277674 | f     | {2277674} | {"For No Key Update"} | {2447}
(1 row)

以上介绍了行锁的一些概念和原理，下面我们看一下这四类锁的冲突矩阵，以及每种锁影响的操作。

在这里插入图片描述

FOR UPDATE：该模式允许修改任何元组字段，甚至删除整个元组

FOR NO KEY UPDATE：对除主(唯一)键外的字段更新，此更改也不影响外键

FOR SHARE：当我们需要读取一行，但不允许其他事务更改它时,使用该模式

FOR KEY SHARE：读该行的键值，但只允许对除键外的其他字段更新。在检查外键约束时会自动使用该锁。

排它模式的两个锁：FOR UPDATE 和 FOR NO KEY UPDATE
#创建测试表
CREATE TABLE accounts(acc_no integer PRIMARY KEY,amount numeric
);
INSERT INTO accounts VALUES (1, 100.00), (2, 200.00), (3, 300.00);#创建插件
CREATE EXTENSION pageinspect;
CREATE EXTENSION pgrowlocks;#更新表，一个是key update一个是no key update
BEGIN;
UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;  --no key update
UPDATE accounts SET acc_no = 20 WHERE acc_no = 2; --key update = for update#查看标志位
SELECT '(0,'||lp||')' AS ctid,t_xmax as xmax,CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd,CASE WHEN (t_infomask & 16) > 0 THEN 't' END AS keyshr_lock,CASE WHEN (t_infomask & 16+64) = 16+64 THEN 't' END AS shr_lock
FROM heap_page_items(get_raw_page('accounts',0))
ORDER BY lp;ctid  |  xmax   | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock 
-------+---------+-----------+----------+----------+-------------+----------(0,1) | 2277687 |           |          |          |             | (0,2) | 2277687 |           |          | t        |             | (0,3) |       0 |           |          |          |             | (0,4) |       0 |           |          |          |             | (0,5) |       0 |           |          |          |             | 
(5 rows)#查看锁模式
SELECT * FROM pgrowlocks('accounts');locked_row | locker  | multi |   xids    |       modes       |  pids   
------------+---------+-------+-----------+-------------------+---------(0,1)      | 2277687 | f     | {2277687} | {"No Key Update"} | {27249}(0,2)      | 2277687 | f     | {2277687} | {Update}          | {27249}
(2 rows)#再看一下select ... for和上面的不同
postgres=# begin;
BEGIN
postgres=*# SELECT * FROM accounts WHERE acc_no = 1 FOR NO KEY UPDATE;acc_no | amount 
--------+--------1 | 100.00
(1 row)postgres=*# SELECT * FROM accounts WHERE acc_no = 2 FOR UPDATE;acc_no | amount 
--------+--------2 | 200.00
(1 row)#标志位lock_only为true,和前面的实验在标志位设置还是有区别的,说明行只是被锁定，并没有被删除，行还是活动的
postgres=# SELECT '(0,'||lp||')' AS ctid,t_xmax as xmax,CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd,CASE WHEN (t_infomask & 16) > 0 THEN 't' END AS keyshr_lock,CASE WHEN (t_infomask & 16+64) = 16+64 THEN 't' END AS shr_lock
FROM heap_page_items(get_raw_page('accounts',0))
ORDER BY lp;ctid  |  xmax   | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock 
-------+---------+-----------+----------+----------+-------------+----------(0,1) | 2277689 | t         |          |          |             | (0,2) | 2277689 | t         |          | t        |             | (0,3) |       0 |           |          |          |             | (0,4) |       0 |           |          |          |             | (0,5) |       0 |           |          |          |             | 
(5 rows)postgres=# SELECT * FROM pgrowlocks('accounts');locked_row | locker  | multi |   xids    |         modes         |  pids   
------------+---------+-------+-----------+-----------------------+---------(0,1)      | 2277689 | f     | {2277689} | {"For No Key Update"} | {27249}(0,2)      | 2277689 | f     | {2277689} | {"For Update"}        | {27249}
(2 rows)
共享模式的两个锁FOR SHARE 和 FOR KEY SHARE
#select ... for语句加锁
postgres=# begin;
BEGIN
postgres=*# SELECT * FROM accounts WHERE acc_no = 1 FOR KEY SHARE;acc_no | amount 
--------+--------1 | 100.00
(1 row)postgres=*# SELECT * FROM accounts WHERE acc_no = 2 FOR SHARE;acc_no | amount 
--------+--------2 | 200.00
(1 row)#查看标志位，在这两种情况下，都设置了keyshr_lock标志位，并且SHARE模式通过多设置一个标志位来表示。
postgres=# SELECT '(0,'||lp||')' AS ctid,t_xmax as xmax,CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd,CASE WHEN (t_infomask & 16) > 0 THEN 't' END AS keyshr_lock,CASE WHEN (t_infomask & 16+64) = 16+64 THEN 't' END AS shr_lock
FROM heap_page_items(get_raw_page('accounts',0))
ORDER BY lp;ctid  |  xmax   | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock 
-------+---------+-----------+----------+----------+-------------+----------(0,1) | 2277690 | t         |          |          | t           | (0,2) | 2277690 | t         |          |          | t           | t(0,3) |       0 |           |          |          |             | (0,4) |       0 |           |          |          |             | (0,5) |       0 |           |          |          |             | 
(5 rows)#查看行锁
postgres=# SELECT * FROM pgrowlocks('accounts');locked_row | locker  | multi |   xids    |       modes       |  pids   
------------+---------+-------+-----------+-------------------+---------(0,1)      | 2277690 | f     | {2277690} | {"For Key Share"} | {27249}(0,2)      | 2277690 | f     | {2277690} | {"For Share"}     | {27249}
(2 rows)#因为For Key Share和For no key Update是兼容不冲突的，如下：
postgres=# begin;
BEGIN
postgres=*# UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
UPDATE 1#查看标志位，is_multi设置为true
postgres=# SELECT '(0,'||lp||')' AS ctid,t_xmax as xmax,CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd,CASE WHEN (t_infomask & 16) > 0 THEN 't' END AS keyshr_lock,CASE WHEN (t_infomask & 16+64) = 16+64 THEN 't' END AS shr_lock
FROM heap_page_items(get_raw_page('accounts',0))
ORDER BY lp;ctid  |  xmax   | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock 
-------+---------+-----------+----------+----------+-------------+----------(0,1) |      12 |           | t        |          |             | (0,2) | 2277690 | t         |          |          | t           | t(0,3) |       0 |           |          |          |             | (0,4) |       0 |           |          |          |             | (0,5) |       0 |           |          |          |             | (0,6) | 2277690 | t         |          |          | t           | #可以看到同一行，持有2个行锁"Key Share"和"No Key Update"
postgres=#  SELECT * FROM pgrowlocks('accounts');locked_row | locker  | multi |       xids        |             modes             |     pids      
------------+---------+-------+-------------------+-------------------------------+---------------(0,1)      |      12 | t     | {2277690,2277691} | {"Key Share","No Key Update"} | {27249,30530}(0,2)      | 2277690 | f     | {2277690}         | {"For Share"}                 | {27249}
(2 rows)

1 单事务锁行时, 各种行锁模式和infomask标志位的关系 :

1.1select … for key share

t_infomask 设置 HEAP_XMAX_LOCK_ONLY 以及 HEAP_XMAX_KEYSHR_LOCK

1.2 select … for share

t_infomask 设置 HEAP_XMAX_LOCK_ONLY 以及 HEAP_XMAX_SHR_LOCK [也就是HEAP_XMAX_KEYSHR_LOCK和HEAP_XMAX_EXCL_LOCK]

1.3 select … for no key update

t_infomask 设置 HEAP_XMAX_LOCK_ONLY 以及 HEAP_XMAX_EXCL_LOCK

1.4 select … for update [也就是key update]

t_infomask 设置 HEAP_XMAX_LOCK_ONLY 以及 HEAP_XMAX_EXCL_LOCK 同时 t_infomask2 设置 HEAP_KEYS_UPDATED

1.5 update命令不更新主键

如：[UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;]
t_infomask没有设置相关标志位,此操作只是插入新的行，并修改旧行的xmax值。

1.6 update命令更新主键

如：[UPDATE accounts SET acc_no = 20 WHERE acc_no = 2;]
t_infomask2设置为HEAP_KEYS_UPDATED,同时也修改xmax值。

标志位见源码src/include/access/htup_details.h
/** information stored in t_infomask:*/
#define HEAP_HASNULL			0x0001	/* has null attribute(s) */
#define HEAP_HASVARWIDTH		0x0002	/* has variable-width attribute(s) */
#define HEAP_HASEXTERNAL		0x0004	/* has external stored attribute(s) */
#define HEAP_HASOID_OLD			0x0008	/* has an object-id field */
#define HEAP_XMAX_KEYSHR_LOCK	0x0010	/* xmax is a key-shared locker */
#define HEAP_COMBOCID			0x0020	/* t_cid is a combo CID */
#define HEAP_XMAX_EXCL_LOCK		0x0040	/* xmax is exclusive locker */
#define HEAP_XMAX_LOCK_ONLY		0x0080	/* xmax, if valid, is only a locker *//* xmax is a shared locker */
#define HEAP_XMAX_SHR_LOCK	(HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)#define HEAP_LOCK_MASK	(HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \HEAP_XMAX_KEYSHR_LOCK)
#define HEAP_XMIN_COMMITTED		0x0100	/* t_xmin committed */
#define HEAP_XMIN_INVALID		0x0200	/* t_xmin invalid/aborted */
#define HEAP_XMIN_FROZEN		(HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
#define HEAP_XMAX_COMMITTED		0x0400	/* t_xmax committed */
#define HEAP_XMAX_INVALID		0x0800	/* t_xmax invalid/aborted */
#define HEAP_XMAX_IS_MULTI		0x1000	/* t_xmax is a MultiXactId */
#define HEAP_UPDATED			0x2000	/* this is UPDATEd version of row */
#define HEAP_MOVED_OFF			0x4000	/* moved to another place by pre-9.0* VACUUM FULL; kept for binary* upgrade support */
#define HEAP_MOVED_IN			0x8000	/* moved from another place by pre-9.0* VACUUM FULL; kept for binary* upgrade support */
#define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)#define HEAP_XACT_MASK			0xFFF0	/* visibility-related bits */** information stored in t_infomask2:*/
#define HEAP_NATTS_MASK			0x07FF	/* 11 bits for number of attributes */
/* bits 0x1800 are available */
#define HEAP_KEYS_UPDATED		0x2000	/* tuple was updated and key cols* modified, or tuple deleted */
#define HEAP_HOT_UPDATED		0x4000	/* tuple was HOT-updated */
#define HEAP_ONLY_TUPLE			0x8000	/* this is heap-only tuple */#define HEAP2_XACT_MASK			0xE000	/* visibility-related bits */
查看标志位SQL
#可以根据需求查看t_infomask或者t_infomask2值，比如(t_infomask & 128) > 0   THEN 't' END AS lock_only
#比如宏定义#define HEAP_XMAX_LOCK_ONLY		0x0080，而0x0080为16进制转为10进制为128，(t_infomask & 128)>0表示标志位设置为了HEAP_XMAX_LOCK_ONLY
SELECT '(0,'||lp||')' AS ctid,t_xmax as xmax,CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd,CASE WHEN (t_infomask & 16) > 0 THEN 't' END AS keyshr_lock,CASE WHEN (t_infomask & 16+64) = 16+64 THEN 't' END AS shr_lock
FROM heap_page_items(get_raw_page('accounts',0))
ORDER BY lp;SELECT '(0,'||lp||')' AS ctid,CASE lp_flagsWHEN 0 THEN 'unused'WHEN 1 THEN 'normal'WHEN 2 THEN 'redirect to '||lp_offWHEN 3 THEN 'dead'END AS state,t_xmin as xmin,t_xmax as xmax,(t_infomask & 64) > 0  AS HEAP_XMAX_EXCL_LOCK,(t_infomask & 128) > 0  AS HEAP_XMAX_LOCK_ONLY,(t_infomask & 256) > 0  AS xmin_commited,(t_infomask & 512) > 0  AS xmin_aborted,(t_infomask & 1024) > 0 AS xmax_commited,(t_infomask & 2048) > 0 AS xmax_aborted,t_ctid
FROM heap_page_items(get_raw_page('accounts',0));

参考：
https://www.cybertec-postgresql.com/en/row-locks-in-postgresql/
https://www.cybertec-postgresql.com/en/whats-in-an-xmax/
https://habr.com/en/companies/postgrespro/articles/503008/


http://www.ppmy.cn/news/85530.html

相关文章

Windows 10 主机上的 VMware Workstation 和设备/凭据防护不兼容“错误

Windows 10 主机上的 VMware Workstation 和设备/凭据防护不兼容“错误

Windows 10 主机上的 VMware Workstation 和设备/凭据防护不兼容“错误 VMware Workstation 和 Device/Credential Guard 不兼容。VMware 工作站可以在禁用设备/凭据防护后运行。 排查错误的过程： 要解决错误，请按照以下步骤操作： 如果您…
阅读更多...
Abaqus——悬臂梁问题之工字梁实例

Abaqus——悬臂梁问题之工字梁实例

参考 有限元基础-第7课（悬臂梁问题）-2022年_哔哩哔哩_bilibili 实例 软件 Abaqus 2022 方法 通过线形式建模 具体操作 建模 1.打开部件的模块，创建一个零件。Name：自定义命名；Modeling Space：梁的空…
阅读更多...
Db2 HADR的replay-only window以及DB2_HADR_ROS_AVOID_REPLAY_ONLY_WINDOW

Db2 HADR的replay-only window以及DB2_HADR_ROS_AVOID_REPLAY_ONLY_WINDOW

环境 Db2 v11.5.0.0Red Hat Enterprise Linux release 9.0 (Plow) 概念 在Db2 HADR系统中，如果打开了ROS（Read On Standby），在standby上可以运行查询，以减轻primary的负担。 但是，在primary上的一些特定…
阅读更多...
扩散模型DDPM与基于分数的模型之间的公式证明联系及原理详解【论文精读SGM】

扩散模型DDPM与基于分数的模型之间的公式证明联系及原理详解【论文精读SGM】

【论文精读SGM】Score-Based Generative Modeling through Stochastic Differential Equations 0、前言1、介绍2 BACKGROUND2.1 DENOISING SCORE MATCHING WITH LANGEVIN DYNAMICS (SMLD)用朗之万动力学的去噪分数匹配2.2 DENOISING DIFFUSION PROBABILISTIC MODELS (DDPM)3 SC…
阅读更多...
【P32】JMeter While 控制器（While Controller）

【P32】JMeter While 控制器（While Controller）

文章目录 一、While 控制器（While Controller）参数说明二、测试计划设计2.1、变量2.2、函数2.2.1、groovy脚本2.2.2、jex13脚本2.2.3、js脚本 一、While 控制器（While Controller）参数说明 可以对部分逻辑按变量条件进行循环迭代…
阅读更多...
pt工具的使用（8） pt-deadlock-logger的使用

pt工具的使用（8） pt-deadlock-logger的使用

一、简介 pt-deadlock-logger 记录有关给定 DSN 上的 MySQL 死锁的信息。 信息打印到 STDOUT，也可以通过指定 --dest 将其保存到表中。 pt-deadlock-logger 通过轮询和解析 SHOW ENGINE INNODB STATUS 打印有关 MySQL 死锁的信息。 当一个新的死锁发生时&#xff…
阅读更多...
对比学习简单介绍教程

对比学习简单介绍教程

文章目录 对比学习简单介绍教程1. 对比学习简介2. 对比学习方法2.1 SimCLR2.2 MoCo2.3 BYOL2.4 CPC 3. 对比学习的应用4. 总结 对比学习简单介绍教程 对比学习（Contrastive Learning）是一种无监督学习方法，它通过对比正例和负例来学习特征表…
阅读更多...
升级iOS16.5后无法使用闪电转USB3相机转换器怎么办？

升级iOS16.5后无法使用闪电转USB3相机转换器怎么办？

上周，苹果给大家推送了iOS 16.5更新，号称修复了超14个漏洞。但很快，安装了更新的iPhone和iPad用户发现iOS 16.5的一个BUG，Lightning转USB 3相机转换器无法工作。 使用闪电转 USB 3 相机转换器，你可以轻松地将照片和视…
阅读更多...
最新文章

Copyright @ 2022~2023