锁拾遗
前言
今天在和PGCE学员上课的时候，发现一起有趣的锁案例 ~ 案例虽小，却不乏趣味性，分享一下。
表级锁
首先回顾一下表级锁矩阵图
新建一个测试表
postgres=# create table test(id int);
CREATE TABLE
postgres=# insert into test values(1);
INSERT 0 1
postgres=# insert into test values(2);
INSERT 0 1新开一个会话，简单查询一下
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
pg_backend_pid
----------------
538
(1 row)
postgres=*# select id from test where id = 1;
id
----
1
(1 row)不难猜测，获取的锁是AccessShareLock
postgres=# select locktype,relation::regclass as relname,relation,virtualxid,transactionid,pid,mode,granted from pg_locks where pid = 538;
locktype | relname | relation | virtualxid | transactionid | pid | mode | granted
------------+---------+----------+------------+---------------+-----+-----------------+---------
relation | test | 42704 | | | 538 | AccessShareLock | t
virtualxid | | | 5/4694 | | 538 | ExclusiveLock | t
(2 rows)这里根据查出来的数据，可以得出两个小结论：
•对于select这一类不会对数据库产生永久性变更的查询，只需要获取虚拟事务ID即可，而不需要获取永久事务ID，毕竟事务ID是一个有限的资源，莫不要以为有了64位的事务ID就可以高枕无忧了，即使目前15正在做64位 xid，出来了之后，也是需要定时freeze的，以减少CLOG的大小。另外，在整个事务运行过程中，还会对虚拟事务ID持有ExclusiveLock排他锁，直至事务结束•根据锁矩阵，获取的锁是AccessShareLock
所以，假如当我们使用了txid_current()去查看当前事务ID的时候，就会事务ID持有ExclusiveLock排他锁了，直至事务结束，每次调用txid_current()都会使事务号加1。
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid(),txid_current();
pg_backend_pid | txid_current
----------------+--------------
538 | 1209809
(1 row)
postgres=*# select id from test where id = 1;
id
----
1
(1 row)再次查看锁
postgres=# select locktype,relation::regclass as relname,relation,virtualxid,transactionid,pid,mode,granted from pg_locks where pid = 538;
locktype | relname | relation | virtualxid | transactionid | pid | mode | granted
---------------+---------+----------+------------+---------------+-----+-----------------+---------
relation | test | 42704 | | | 538 | AccessShareLock | t
virtualxid | | | 5/4697 | | 538 | ExclusiveLock | t
transactionid | | | | 1209809 | 538 | ExclusiveLock | t
(3 rows)可以看到，对transactionid也持有了ExclusiveLock，这个可能是我个人习惯导致，我喜欢同时看一下事务ID和进程号，但是这样就很容易忽视虚拟事务ID这个特性了，比如上面这个例子，查询完全不会申请永久事务ID，而是我自己的操作，导致了这一现象。因此，推荐各位在操作前，可以先使用txid_current_if_assigned()来判断是否分配了永久事务ID。
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
pg_backend_pid
----------------
538
(1 row)
postgres=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
postgres=*# insert into test values(3);
INSERT 0 1
postgres=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
1209810
(1 row)另外还有一个小小的注意点，我们在日常使用PostgreSQL的过程中，会经常使用TAB键进行补全，但是在我们分析锁的时候，尽量不要使用TAB键，因为这会产生很多冗余项，如下：
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
pg_backend_pid
----------------
538
(1 row)
postgres=*# select id from t ---此处按了TAB键，进行了补全
t1 t1_id_seq t1_old test test_lock
postgres=*# select id from test where id = 1;
id
----
1
(1 row)再次根据pid查询pg_locks，可以看到，产生了很多系统对象。其实究其原因，补全这个动作就是查询的系统表，所以这里就会产生这么多条目。因此，当我们在分析锁亦或是尝试复现锁的时候，老老实实敲，不要补全。
postgres=# select locktype,relation::regclass as relname,relation,virtualxid,transactionid,pid,mode,granted from pg_locks where pid = 538;
locktype | relname | relation | virtualxid | transactionid | pid | mode | granted
------------+-----------------------------------+----------+------------+---------------+-----+-----------------+---------
relation | test | 42704 | | | 538 | AccessShareLock | t
relation | pg_class_tblspc_relfilenode_index | 3455 | | | 538 | AccessShareLock | t
relation | pg_class_relname_nsp_index | 2663 | | | 538 | AccessShareLock | t
relation | pg_class_oid_index | 2662 | | | 538 | AccessShareLock | t
relation | pg_namespace_oid_index | 2685 | | | 538 | AccessShareLock | t
relation | pg_namespace_nspname_index | 2684 | | | 538 | AccessShareLock | t
relation | pg_namespace | 2615 | | | 538 | AccessShareLock | t
relation | pg_class | 1259 | | | 538 | AccessShareLock | t
virtualxid | | | 5/4695 | | 538 | ExclusiveLock | t
(9 rows)比如上面这个例子，我们可以看到，会查询pg_class。
那么简单测一下，开一个会话，显式锁定pg_class
postgres=# begin;
BEGIN
postgres=*# lock table pg_class;
LOCK TABLE老样子再进行补全的时候，你就会发现按TAB键补全完全没有反应
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
pg_backend_pid
----------------
538
(1 row)
postgres=*# select * from t ---此处按TAB键没有反应并且再开一个会话进行登录，也是会卡住的。原理都是一样的，都需要查询系统表pg_class，所以系统表的重要性不言而喻了吧？
[postgres@xiongcc ~]$ psql
---此处卡住当释放了锁之后，你便会发现补全的内容全部出来了，因为我按了N次TAB键
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
postgres=*# select * from t
t1 t1_id_seq t1_old test test_lock
...
...OK，扯远了，回到主题。
以往我在给学员讲锁的时候，一般都是针对冲突的锁进行演示，看看今天没有产生冲突的情况，此处根据创建索引和查询select不冲突为例
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
pg_backend_pid
----------------
538
(1 row)
postgres=*# select id from test where id = 1;
id
----
1
(1 row)开个会话创建索引
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
pg_backend_pid
----------------
1164
(1 row)
postgres=*# create index myidx on test(id);
CREATE INDEX看一下锁的情况
postgres=# select locktype,relation::regclass as relname,relation,virtualxid,transactionid,pid,mode,granted from pg_locks where pid in (538,1164) order by pid;
locktype | relname | relation | virtualxid | transactionid | pid | mode | granted
---------------+---------+----------+------------+---------------+------+---------------------+---------
virtualxid | | | 5/4700 | | 538 | ExclusiveLock | t
relation | test | 42704 | | | 538 | AccessShareLock | t
virtualxid | | | 3/13480 | | 1164 | ExclusiveLock | t
relation | test | 42704 | | | 1164 | ShareLock | t
transactionid | | | | 1209812 | 1164 | ExclusiveLock | t
relation | 42707 | 42707 | | | 1164 | AccessExclusiveLock | t
(6 rows)538进程持有的锁很简单，不再赘述。可以看到，1164这个进程，还对transactionid持有了ExclusiveLock，正如前面所说，事务生命期间，会对虚拟事务ID和永久事务ID（假如分配了的话）持有ExclusiveLock，因为产生了写操作，所以分配了永久事务ID。
除了这个，我们还看到，有个42707的对象，这个是什么东西呢？为什么通过regclass转化看不到名字呢？但是位于shared buffers里面的的pg_locks又可以看到呢？
postgres=# select 'test'::regclass::oid;
oid
-------
42704
(1 row)
postgres=# select oid,relfilenode,relname from pg_class where oid = 42707 or relfilenode = 42707;
oid | relfilenode | relname
-----+-------------+---------
(0 rows)居然一下子没有反应过来，愣是思考了半天，甚至还想用GDB去追一下。
不过后面回过神来了之后，豁然开朗。当我们看到AccessExclusiveLock的时候，第一反应就是诸如DDL这一类很硬的操作，那么我做了什么DDL呢？原来我创建了索引啊！既然如此，在创建索引的会话里查询一下看看
postgres=*# select oid,relfilenode,relname from pg_class where oid = 42707 or relfilenode = 42707;
oid | relfilenode | relname
-------+-------------+---------
42707 | 42707 | myidx
(1 row)
postgres=*# select pg_relation_filepath('myidx');
pg_relation_filepath
----------------------
base/13890/42707
(1 row)果然，myidx的oid是42707。因为在PostgreSQL里面，DDL是可以回滚的，并且我们要知道，DDL本质上是系统表的DML，也存在可见性的问题
postgres=*# select xmin,oid,relfilenode,relname from pg_class where oid = 42707 or relfilenode = 42707;
xmin | oid | relfilenode | relname
---------+-------+-------------+---------
1209812 | 42707 | 42707 | myidx
(1 row)在不可见的会话中，快照如下
postgres=# select oid,relfilenode,relname from pg_class where oid = 42707 or relfilenode = 42707;
oid | relfilenode | relname
-----+-------------+---------
(0 rows)
postgres=# select txid_current_snapshot();
txid_current_snapshot
-----------------------
1209812:1209812:
(1 row)因为myidx的xmin，大于等于snapshot.xmax，所以不可见。不过有点"搞"的是，虽然这个会话中，查询看不见（快照决定了不可见），但是能在pg_locks里面看见一二，不知道这个该怎么算呢？
嗦嘎，原来是一场乌龙啊..
行级锁
另外就是行级锁，也得再多提一嘴。表级锁存在于shared buffers中，所以可以很直观的通过pg_locks查看。但是行级锁不一样，PostgreSQL不会将已获得的行级锁信息存储在内存中，行锁只在每一行的header中设置标记位来标识此行记录已经被锁。这两个关键的标记为xmax和infomask。xmax放置当前事务的xid，infomask放置标记位，那么怎么判断xmax到底是删除或者更新时插入的事务ID呢，还是行锁的事务ID呢？这便是infomask的作用了
先手动获取一下行锁
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
pg_backend_pid
----------------
2156
(1 row)
postgres=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
postgres=*# select id from test where id = 1 for update;
id
----
1
(1 row)
postgres=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
1209816
(1 row)看一下标志位
postgres=# SELECT '(0,'||lp||')' AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 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('test',0));
-[ RECORD 1 ]-------+--------
ctid | (0,1)
state | normal
xmin | 1209807
xmax | 1209816
heap_xmax_excl_lock | t
heap_xmax_lock_only | t
xmin_commited | t
xmin_aborted | t
xmax_commited | f
xmax_aborted | f
t_ctid | (0,1)
-[ RECORD 2 ]-------+--------
ctid | (0,2)
state | normal
xmin | 1209808
xmax | 0
heap_xmax_excl_lock | f
heap_xmax_lock_only | f
xmin_commited | t
xmin_aborted | t
xmax_commited | f
xmax_aborted | t
t_ctid | (0,2)
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0));
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
----+---------+---------+--------+------------------------------------------------------+--------------------
1 | 1209807 | 1209816 | (0,1) | XMIN_INVALID|XMIN_COMMITTED|XMAX_LOCK_ONLY|EXCL_LOCK | UPDATE_KEY_REVOKED
2 | 1209808 | 0 | (0,2) | XMAX_INVALID|XMIN_INVALID|XMIN_COMMITTED |
(2 rows)很明显，xmax插入了获取行锁的事务ID，同时infomask里面存放了XMAX_LOCK_ONLY和HEAP_XMAX_EXCL_LOCK，代表是行级锁
#define HEAP_XMAX_EXCL_LOCK 0x0040 /* xmax is exclusive locker */
#define HEAP_XMAX_LOCK_ONLY 0x0080 /* xmax, if valid, is only a locker */插入的动作new_xmax = add_to_xmax
/*heapam.c*/
switch (mode)
{
case LockTupleKeyShare: //FOR KEY SHARE
new_xmax = add_to_xmax;
new_infomask |= HEAP_XMAX_KEYSHR_LOCK;
break;
case LockTupleShare: //FOR SHARE
new_xmax = add_to_xmax;
new_infomask |= HEAP_XMAX_SHR_LOCK;
break;
case LockTupleNoKeyExclusive://FOR NO KEY UPDATE
new_xmax = add_to_xmax;
new_infomask |= HEAP_XMAX_EXCL_LOCK;
break;
case LockTupleExclusive://FOR UPDATE
new_xmax = add_to_xmax;
new_infomask |= HEAP_XMAX_EXCL_LOCK;
new_infomask2 |= HEAP_KEYS_UPDATED;
break;
default:
new_xmax = InvalidTransactionId; /* silence compiler */
elog(ERROR, "invalid lock mode");
}
}再做个更新看看
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
pg_backend_pid
----------------
2156
(1 row)
postgres=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
postgres=*# select id from test where id = 1 for update;
id
----
1
(1 row)
postgres=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
1209816
(1 row)
postgres=*# update test set id = 99 where id = 1;
UPDATE 1查看行锁
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0));
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
----+---------+---------+--------+------------------------------------------+--------------------------------
1 | 1209807 | 1209816 | (0,3) | XMIN_INVALID|XMIN_COMMITTED | UPDATE_KEY_REVOKED|HOT_UPDATED
2 | 1209808 | 0 | (0,2) | XMAX_INVALID|XMIN_INVALID|XMIN_COMMITTED |
3 | 1209816 | 1209816 | (0,3) | UPDATED|XMAX_LOCK_ONLY|XMAX_KEYSHR_LOCK | HEAP_ONLY_TUPLE
(3 rows)
postgres=# SELECT '(0,'||lp||')' AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 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('test',0));
-[ RECORD 1 ]-------+--------
ctid | (0,1)
state | normal
xmin | 1209807
xmax | 1209816
heap_xmax_excl_lock | f
heap_xmax_lock_only | f
xmin_commited | t
xmin_aborted | t
xmax_commited | f
xmax_aborted | f
t_ctid | (0,3)
-[ RECORD 2 ]-------+--------
ctid | (0,2)
state | normal
xmin | 1209808
xmax | 0
heap_xmax_excl_lock | f
heap_xmax_lock_only | f
xmin_commited | t
xmin_aborted | t
xmax_commited | f
xmax_aborted | t
t_ctid | (0,2)
-[ RECORD 3 ]-------+--------
ctid | (0,3)
state | normal
xmin | 1209816
xmax | 1209816
heap_xmax_excl_lock | f
heap_xmax_lock_only | t
xmin_commited | f
xmin_aborted | f
xmax_commited | f
xmax_aborted | f
t_ctid | (0,3)死锁
死锁也值得注意，当一个事务试图获取另一个事务已在使用的资源，而第二个事务又试图获取第一个事务在使用的资源时，就会发生死锁。
按照PG的死锁检测算法，在等待中的会话都会每隔sleep时长(deadlock_timeout，默认1秒)就发起死锁检测，所以谁先检测出死锁就rollback谁(也就是说发现自己在闭环中，属于自杀行为)。不建议设置太小的deadlock_timeout，不断唤起死锁检测，会导致性能消耗问题。
更多信息可以参考：PostgreSQL · 内核特性 · 死锁检测与解决
小结
这个案例虽小，但是不乏趣味性。DDL可以回滚，厉害吧，开不开森。
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
上一篇：
从一起等待事件说起
请登录后发表评论
登录
全部评论
