PostgreSQL DBA(158) - Locks(tuple locks)

本节简单介绍了tuple locks.
关于tuple locks,源码中的README文件给出以下解释:

Locking tuples is not as easy as locking tables or other database objects.
The problem is that transactions might want to lock large numbers of tuples at
any one time, so it’s not possible to keep the locks objects in shared memory.
To work around this limitation, we use a two-level mechanism. The first level
is implemented by storing locking information in the tuple header: a tuple is
marked as locked by setting the current transaction’s XID as its XMAX, and
setting additional infomask bits to distinguish this case from the more normal
case of having deleted the tuple. When multiple transactions concurrently
lock a tuple, a MultiXact is used; see below. This mechanism can accommodate
arbitrarily large numbers of tuples being locked simultaneously.

相对于tables或者其他数据库对象,元组的locks并不太容易处理,问题是事务可能在某个时间点
锁定大批量的tuples,因此不可能在共享内存中存储这些锁信息。为了突破这个限制,PG使用了两级机制。
第一级:把锁信息存储在tuple header实现:把当前事务的XID存储在XMAX中,同时设置特别的
infomask bits来区分已删除的tuples和这些被锁定的tuples。
第二级:如有并发操作,则使用MultiXact。
这种机制可以容纳同时被锁定的任意数量的元组。

示例
插入数据,并执行update,update操作会lock tuples

[local:/data/run/pg12]:5120 pg12@testdb=# begin;
BEGIN
[local:/data/run/pg12]:5120 pg12@testdb=#* insert into t values(1),(2);
INSERT 0 2
[local:/data/run/pg12]:5120 pg12@testdb=#* commit;
COMMIT
[local:/data/run/pg12]:5120 pg12@testdb=# begin;
BEGIN
[local:/data/run/pg12]:5120 pg12@testdb=#* update t set id = 2;
UPDATE 2
[local:/data/run/pg12]:5120 pg12@testdb=#* 
[local:/data/run/pg12]:5120 pg12@testdb=#* select txid_current();
   1512070049
-- 
[local:/data/run/pg12]:5120 pg12@testdb=# select xmin,xmax from t;
    xmin    |    xmax    
------------+------------
 1512070048 | 1512070049
 1512070048 | 1512070049
(2 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#

锁信息

[local:/data/run/pg12]:5120 pg12@testdb=# select pid,mode,locktype,relation,page,tuple,transactionid from pg_locks where pid <> pg_backend_pid();
 pid  |       mode       |   locktype    | relation | page | tuple | transactionid 
------+------------------+---------------+----------+------+-------+---------------
 1877 | RowExclusiveLock | relation      |    74856 |      |       |              
 1877 | ExclusiveLock    | virtualxid    |          |      |       |              
 1877 | ExclusiveLock    | transactionid |          |      |       |    1512070049
(3 rows)

参考资料
Locking tuples

请使用浏览器的分享功能分享到微信等