建设公司网站需要什么资料,网站建设仪器配置表,企业网站建设word,做网站维护工作难吗XactLockTableWait函数、transactionid锁的一些原理和分析 结论
更新行时#xff0c;会根据xmax拿transactionid锁#xff0c;等对应的事务结束。 如果结束是回滚#xff0c;则heap_update继续更新。如果结束时提交#xff0c;则heap_update要返回上层ExecUpdate调用EvalP… 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.82847908tuple | 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 (locallock0x1f8d120, owner0x1f9dbc0) at lock.c:1818
#6 0x00000000009a3961 in LockAcquireExtended (locktag0x7ffd197ae4f0, lockmode5, sessionLockfalse, dontWaitfalse, reportMemoryErrortrue, locallockp0x0) at lock.c:1082
#7 0x00000000009a2f3f in LockAcquire (locktag0x7ffd197ae4f0, lockmode5, sessionLockfalse, dontWaitfalse) at lock.c:740
#8 0x00000000009a181c in XactLockTableWait (xid740, rel0x7f29222b20d8, ctid0x7ffd197ae594, operXLTW_Update) at lmgr.c:702
#9 0x00000000004f1453 in heap_update (relation0x7f29222b20d8, otid0x7ffd197ae8aa, newtup0x205baf8, cid0, crosscheck0x0, waittrue, tmfd0x7ffd197ae8d8, lockmode0x7ffd197ae82c, update_indexes0x7ffd197ae828) at heapam.c:3316
#10 0x00000000004fdfa3 in heapam_tuple_update (relation0x7f29222b20d8, otid0x7ffd197ae8aa, slot0x205b518, cid0, snapshot0x1f98370, crosscheck0x0, waittrue, tmfd0x7ffd197ae8d8, lockmode0x7ffd197ae82c, update_indexes0x7ffd197ae828) at heapam_handler.c:327
#11 0x000000000077f5a0 in table_tuple_update (rel0x7f29222b20d8, otid0x7ffd197ae8aa, slot0x205b518, cid0, snapshot0x1f98370, crosscheck0x0, waittrue, tmfd0x7ffd197ae8d8, lockmode0x7ffd197ae82c, update_indexes0x7ffd197ae828) at ../../../src/include/access/tableam.h:1535
#12 0x000000000078278f in ExecUpdateAct (context0x7ffd197ae8b0, resultRelInfo0x205a4a8, tupleid0x7ffd197ae8aa, oldtuple0x0, slot0x205b518, canSetTagtrue, updateCxt0x7ffd197ae824) at nodeModifyTable.c:2101
#13 0x0000000000782cbb in ExecUpdate (context0x7ffd197ae8b0, resultRelInfo0x205a4a8, tupleid0x7ffd197ae8aa, oldtuple0x0, slot0x205b518, canSetTagtrue) at nodeModifyTable.c:2322
#14 0x000000000078533d in ExecModifyTable (pstate0x205a298) at nodeModifyTable.c:3824
#15 0x0000000000746fa6 in ExecProcNodeFirst (node0x205a298) at execProcnode.c:464
#16 0x000000000073ad27 in ExecProcNode (node0x205a298) 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万一被别人改了呢例如这里xwait745且745还没提交。
xwait HeapTupleHeaderGetRawXmax(oldtup.t_data);
注意这里要先放buffer因为有可能别的事务会修改后面需要重新锁上拿数据
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
先把行锁拿到避免别人正在更新
heap_acquire_tuplock(relation, (oldtup.t_self), LockTupleNoKeyExclusive, LockWaitBlock, have_tuple_lock);
在去拿xmax745的事务锁确保修改那个事务已经没了
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.82847908如果事务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。
