Postgresql源码（124）两个事务更新同一行数据时的行为和原理分析

mingjie

发布于 2024-02-21 11:05:55

2430

发布于 2024-02-21 11:05:55

文章被收录于专栏：Postgresql源码分析

XactLockTableWait函数、transactionid锁的一些原理和分析

结论

更新行时，会根据xmax拿transactionid锁，等对应的事务结束。
- 如果结束是回滚，则heap_update继续更新。
- 如果结束时提交，则heap_update要返回上层ExecUpdate调用EvalPlanQual重新拿到数据再更新（也有可能更新后不符合谓词就看不到了）。

场景

先执行事务1更新3为30，不提交。

再执行事务2更新所有小于10的数。

drop table t81;
create table t81(i int);
insert into t81 select t.i from generate_series(1,5) t(i);

-- 事务745
begin;
update t81 set i = 30 where i = 3;

-- 事务746
begin;
update t81 set i = 100 where i < 10;

事务二的update会卡住等待事务1。

postgres=# select * from pg_locks where pid <> pg_backend_pid() order by pid,locktype;
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | fastpath |
      waitstart
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------+-------------------------------
 relation      |        5 |    16389 |      |       |            |               |         |       |          | 3/446              | 29044 | RowExclusiveLock | t       | t        |
 transactionid |          |          |      |       |            |           745 |         |       |          | 3/446              | 29044 | ExclusiveLock    | t       | f        |
 virtualxid    |          |          |      |       | 3/446      |               |         |       |          | 3/446              | 29044 | ExclusiveLock    | t       | t        |
 relation      |        5 |    16389 |      |       |            |               |         |       |          | 4/22               | 29246 | RowExclusiveLock | t       | t        |
 transactionid |          |          |      |       |            |           746 |         |       |          | 4/22               | 29246 | ExclusiveLock    | t       | f        |
 transactionid |          |          |      |       |            |           745 |         |       |          | 4/22               | 29246 | ShareLock        | f       | f        | xxxx-xx-xx 16:53:14.828479+08
 tuple         |        5 |    16389 |    0 |     3 |            |               |         |       |          | 4/22               | 29246 | ExclusiveLock    | t       | f        |
 virtualxid    |          |          |      |       | 4/22       |               |         |       |          | 4/22               | 29246 | ExclusiveLock    | t       | t        |
(8 rows)

分析

事务746的等锁堆栈

...
  
#5  0x00000000009a4c23 in WaitOnLock (locallock=0x1f8d120, owner=0x1f9dbc0) at lock.c:1818
#6  0x00000000009a3961 in LockAcquireExtended (locktag=0x7ffd197ae4f0, lockmode=5, sessionLock=false, dontWait=false, reportMemoryError=true, locallockp=0x0) at lock.c:1082
#7  0x00000000009a2f3f in LockAcquire (locktag=0x7ffd197ae4f0, lockmode=5, sessionLock=false, dontWait=false) at lock.c:740
#8  0x00000000009a181c in XactLockTableWait (xid=740, rel=0x7f29222b20d8, ctid=0x7ffd197ae594, oper=XLTW_Update) at lmgr.c:702
#9  0x00000000004f1453 in heap_update (relation=0x7f29222b20d8, otid=0x7ffd197ae8aa, newtup=0x205baf8, cid=0, crosscheck=0x0, wait=true, tmfd=0x7ffd197ae8d8, lockmode=0x7ffd197ae82c, update_indexes=0x7ffd197ae828) at heapam.c:3316
#10 0x00000000004fdfa3 in heapam_tuple_update (relation=0x7f29222b20d8, otid=0x7ffd197ae8aa, slot=0x205b518, cid=0, snapshot=0x1f98370, crosscheck=0x0, wait=true, tmfd=0x7ffd197ae8d8, lockmode=0x7ffd197ae82c, update_indexes=0x7ffd197ae828) at heapam_handler.c:327
#11 0x000000000077f5a0 in table_tuple_update (rel=0x7f29222b20d8, otid=0x7ffd197ae8aa, slot=0x205b518, cid=0, snapshot=0x1f98370, crosscheck=0x0, wait=true, tmfd=0x7ffd197ae8d8, lockmode=0x7ffd197ae82c, update_indexes=0x7ffd197ae828) at ../../../src/include/access/tableam.h:1535
#12 0x000000000078278f in ExecUpdateAct (context=0x7ffd197ae8b0, resultRelInfo=0x205a4a8, tupleid=0x7ffd197ae8aa, oldtuple=0x0, slot=0x205b518, canSetTag=true, updateCxt=0x7ffd197ae824) at nodeModifyTable.c:2101
#13 0x0000000000782cbb in ExecUpdate (context=0x7ffd197ae8b0, resultRelInfo=0x205a4a8, tupleid=0x7ffd197ae8aa, oldtuple=0x0, slot=0x205b518, canSetTag=true) at nodeModifyTable.c:2322
#14 0x000000000078533d in ExecModifyTable (pstate=0x205a298) at nodeModifyTable.c:3824
#15 0x0000000000746fa6 in ExecProcNodeFirst (node=0x205a298) at execProcnode.c:464
#16 0x000000000073ad27 in ExecProcNode (node=0x205a298) at ../../../src/include/executor/executor.h:273

...

可以看到事务746在等待事务745的transactionid锁。

事务746流程分析

heap_update拿到目标元组的otid和拼好的新元组后

heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, ...)

先把buffer锁上，因为另一个事务已经更新完了，所以buffer锁当前可以拿到。

LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);

然后拿到被修改元组的xmax，万一被别人改了呢？例如这里xwait=745，且745还没提交。

xwait = HeapTupleHeaderGetRawXmax(oldtup.t_data);

注意这里要先放buffer，因为有可能别的事务会修改，后面需要重新锁上拿数据

LockBuffer(buffer, BUFFER_LOCK_UNLOCK);

先把行锁拿到，避免别人正在更新

heap_acquire_tuplock(relation, &(oldtup.t_self), LockTupleNoKeyExclusive, LockWaitBlock, &have_tuple_lock);

在去拿xmax=745的事务锁，确保修改那个事务已经没了

XactLockTableWait(xwait, relation, &oldtup.t_self, XLTW_Update);

这里会加transactionid的ShareLock模式。

746事务自己拿了一个transactionid ExclusiveLock因为自己也更新了数据。
745事务有写入数据，所以745已经拿到transactionid的ExclusiveLock。
746事务去获取745的transactionid ShareLock，开始等锁。

这里等锁就发生了，保证了RC级别的隔离性。

postgres=# select * from pg_locks where pid <> pg_backend_pid() order by pid,locktype;
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | fastpath |
      waitstart
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------+-------------------------------
 transactionid |          |          |      |       |            |           745 |         |       |          | 3/446              | 29044 | ExclusiveLock    | t       | f        |
 transactionid |          |          |      |       |            |           746 |         |       |          | 4/22               | 29246 | ExclusiveLock    | t       | f        |
 transactionid |          |          |      |       |            |           745 |         |       |          | 4/22               | 29246 | ShareLock        | f       | f        | xxxx-xx-xx 16:53:14.828479+08

如果事务745发生了提交
- 那么事务2就不应该更新3这条数据了。代码继续运行会检查oldtup.t_data，确认xmax到底有没有回滚。这里heap_update不会继续进行更新动作了，直接返回TM_Updated。外层函数ExecUpdate收到TM_Updated后，会调用EvalPlanQual重新读取这一行数据，如果还能看到就返回epqslot新元组下面重新更新；如果现在已经看不到这一行了，就返回NULL，这次的更新就结束了。
如果事务745发生了回滚
- 那么事务2就还能看到3这条数据。代码继续运行检查发现xmax已经回滚了，可以继续更新，所以在heap_update中完成了本次更新，返回TM_Ok。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2024-02-20，如有侵权请联系 cloudcommunity@tencent.com 删除

源码