postgresql select for update 多行加锁顺序_PostgreSQL中的行级锁略谈-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_39615219/article/details/111647334

本文介绍了PostgreSQL中的行级锁种类，特别是FOR UPDATE锁的工作原理。通过实例分析了行级锁的冲突模式，并探讨了如何通过pg_locks和pgrowlocks插件来观察和理解行级锁的使用情况。文章还提到了如何监控和处理锁等待，以及一些实用的SQL查询技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 引言

这两天碰到一个项目,最终通过排查锁来定位到问题所在,PostgreSQL的表级锁比较熟悉了,来看看PostgreSQL中的行级锁。

2 行级锁的种类

PostgreSQL有几种不同类型的可锁定对象：整个关系(例如表),单个page,单个tuple,事务ID(虚拟ID和永久ID)以及通用数据库对象(由OID类和OID对象标识) 同样,扩展relation page时也会被锁定,还有咨询锁。

行级锁有四种,for update、for no key update、for share和for key share。注意行级锁不会记录在内存中,如果每个行级锁都在内存中有一条记录的话,全表更新时,表有多少行,在内存中就有多少条行锁信息,内存吃不消。第二,加行级锁首先得在对应的page上加上锁,比如share和exclusive锁,假如你的page是local的,也就是backend process独占的,那么也不需要对page加锁,因为只对你自身backend process可见,比如临时表。对于shared page就需要加锁了,因为是不同process之间共享的,通过锁机制来保障一致性。

注意这句话：PostgreSQL doesn't remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes.,意思就是不会在内存中记录行级锁信息,但select for update由于需要标记这些行被锁住,便会产生IO,这里也就又抛出了那个问题，select是否会产生WAL？除了hint bit,也还有这个情况,当发生SetHintBits时,并不是每次都需要写XLOG,仅仅当开启了wal_log_hints或者initdb时开启了checksum,并且这个被sethintbits的数据块是在checkpoint后第一次被修改这种情况下才需要写full page到WAL,其他情况不会写WAL。

冲突模式如下：

3 观察行级锁

开启三个会话

会话1：

postgres=# begin;

BEGIN

postgres=# select pg_backend_pid();

pg_backend_pid

----------------

4773

(1 row)

postgres=# select txid_current();

txid_current

--------------

513

(1 row)

postgres=# update test set id = 999 where id = 66;

UPDATE 1

会话2：

postgres=# begin;

BEGIN

postgres=# select pg_backend_pid();

pg_backend_pid

----------------

4784

(1 row)

postgres=# select txid_current();

txid_current

--------------

514

(1 row)

postgres=# update test set id = 999 where id = 66;

这里夯住

会话3：

postgres=# select locktype as lc,relation::regclass as relname,page||','||tuple as ctid,virtualxid ,transactionid as txid,virtualtransaction,pid,mode,granted from pg_locks where pid = 4773;

---------------+---------+------+------------+------+--------------------+------+------------------+---------

relation | test | | | | 3/18 | 4773 | RowExclusiveLock | t

virtualxid | | | 3/18 | | 3/18 | 4773 | ExclusiveLock | t

transactionid | | | | 513 | 3/18 | 4773 | ExclusiveLock | t

(3 rows)

postgres=# select locktype as lc,relation::regclass as relname,page||','||tuple as ctid,virtualxid ,transactionid as txid,virtualtransaction,pid,mode,granted from pg_locks where pid = 4784;

---------------+---------+------+------------+------+--------------------+------+------------------+---------

relation | test | | | | 4/14 | 4784 | RowExclusiveLock | t

virtualxid | | | 4/14 | | 4/14 | 4784 | ExclusiveLock | t

transactionid | | | | 514 | 4/14 | 4784 | ExclusiveLock | t

tuple | test | 0,66 | | | 4/14 | 4784 | ExclusiveLock | t

transactionid | | | | 513 | 4/14 | 4784 | ShareLock | f

(5 rows)

对于4773进程(会话1),可以看到对virtualxid和transactionid加上了exclusive锁,virtualxid是虚拟事务ID,每产生一个事务ID,都会在commit log文件中占用2bit。但有些事务没有产生任何实质的变更,比如只读事务或空事务,给它们也分配一个事务ID就很浪费。因此,对这类没有实质变更的事务,只分配虚拟事务ID,不分配事务ID,比如查询,就不需要这种永久事务ID,只需要处理好MVCC,锁的获取和释放即可,因此virtual transaction id也就足够了。不需要去获取XidGenLock而产生transaction id,从而提高数据库性能。另外,数据库也不会因为查询而导致transaction id快速回卷。

官网对这一块做了解释：在整个事务运行过程中,服务器进程对虚拟事务ID持有排他锁。如果分配了永久性事务ID,它还将对永久性事务ID持有排他锁,直到结束。所以印证了上面两个exclusive lock。当某个进程发现需要等待另一个事务结束时,它还会尝试在另一个事务ID上加上share锁(可能是虚拟事务ID,也可能是永久事务ID)。仅当另一个事务终止并释放锁时,操作才会成功。所以,我们再看下第二条SQL,最后一行,可以看到对513(第一个会话的事务ID)这个事务加上了ShareLock,也印证了上面的说法,同时granted为f,表示在等待锁,在发起请求获得锁,被513这个事务阻塞。对于事务ID,再多嘴一句,并不是在begin的时候就进行分配,PostgreSQL为了进一步提升性能,当执行BEGIN后的第一条命令时,事务管理器才会分配txid,并真正启动事务。再看看第一行的RowExclusiveLock就是update时获取的,第四行可以看到,对tuple获取了ExclusiveLock,至此,上面两条SQL的结果基本分析清楚了。

但是对于行锁,我们直接通过pg_locks可能不太直观,我们可以用pgrowlocks插件来观察,contrib下面有：

postgres=# create extension pgrowlocks ;

CREATE EXTENSION

postgres=# select * from test as t,pgrowlocks('test') as lc where t.ctid = lc.locked_row;

----+------------+--------+-------+-------+-------------------+--------

66 | (0,66) | 513 | f | {513} | {"No Key Update"} | {4773}

(1 row)

可以清晰地看到,被锁住的行的CTID,被513这个事务锁住了(会话1),被4773进程锁住了(会话1),和上面相符。

除了这个,我们还可以和pg_stat_activity结合查看

postgres=# select locktype as lc,relation::regclass as relname,page||','||tuple as ctid,virtualxid ,transactionid as txid,virtualtransaction,lc.pid,mode,granted,wait_event,wait_event_type from pg_locks as lc left join pg_stat_activity psa on lc.pid = psa.pid;

---------------+---------------------------+------+------------+------+--------------------+------+------------------+---------+---------------+-----------------

relation | pg_stat_activity | | | | 5/69 | 4806 | AccessShareLock | t | |

relation | pg_locks | | | | 5/69 | 4806 | AccessShareLock | t | |

virtualxid | | | 5/69 | | 5/69 | 4806 | ExclusiveLock | t | |

virtualxid | | | 4/14 | | 4/14 | 4784 | ExclusiveLock | t | transactionid | Lock

virtualxid | | | 3/18 | | 3/18 | 4773 | ExclusiveLock | t | ClientRead | Client

relation | pg_authid_rolname_index | | | | 5/69 | 4806 | AccessShareLock | t | |

transactionid | | | | 514 | 4/14 | 4784 | ExclusiveLock | t | transactionid | Lock

transactionid | | | | 513 | 3/18 | 4773 | ExclusiveLock | t | ClientRead | Client

tuple | test | 0,66 | | | 4/14 | 4784 | ExclusiveLock | t | transactionid | Lock

relation | pg_database | | | | 5/69 | 4806 | AccessShareLock | t | |

relation | pg_authid | | | | 5/69 | 4806 | AccessShareLock | t | |

relation | pg_database_datname_index | | | | 5/69 | 4806 | AccessShareLock | t | |

transactionid | | | | 513 | 4/14 | 4784 | ShareLock | f | transactionid | Lock

relation | pg_database_oid_index | | | | 5/69 | 4806 | AccessShareLock | t | |

relation | pg_authid_oid_index | | | | 5/69 | 4806 | AccessShareLock | t | |

(17 rows)

注意，仅仅对于单个事务而言，也不存在所谓的锁竞争。

4 总结

postgresql中还有页级锁、咨询锁和死锁，可以参考官方文档。

1) 如何检查或监控锁等待呢？

postgresql提供了两个视图

1. pg_locks展示锁信息，每一个被锁或者等待锁的对象一条记录。

2. pg_stat_activity，每个会话一条记录，显示会话状态信息。

我们通过这两个视图可以查看锁等待情况。同时可以了解发生锁冲突的情况。

2) 查看阻塞别人/被阻塞进程

select locktype,mode,relation::regclass as rel,page||','||tuple as ctid,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2,pid,grantedfrom pg_locks where granted='t/f';

granted=t是阻塞别人的，f是被阻塞的

3) 通过pid查询sql具体信息

select xact_start, query_start, backend_start, state_change, state from pg_stat_activity where pid in (xxx);

4) kill 会话

select pg_cancel_backend(xxx);

selectpg_terminate_backend(xxx);

推荐一个德哥的关于lock的SQL，可以创建为视图观察：

https://github.com/digoal/blog/blob/master/201705/20170521_01.md?spm=a2c4e.10696291.0.0.50d619a4iHydbu&file=20170521_01.md