Oracle数据库的语句级重启（Statement-level Restart）

下边实验步骤证实了Oracle数据库的statement-level restart。-- 【会话A】 id1的数据行的status0其他行status1先行锁住id6的数据行SQL SELECT id, status FROM t_for_update ;ID STATUS---------- ----------1 02 13 14 15 16 16 rows selected.SQL SELECT id, status FROM t_for_update WHERE id6 FOR UPDATE;ID STATUS---------- ----------6 1-- 【会话B】SQL SELECT /* gather_plan_statistics */ id, status FROM t_for_update WHERE status1 FOR UPDATE;-- 这里会在id6的数据行发生锁等待。需要等待会话A释放行锁。-- 注意此时已经扫描过id1的数据行不符合过滤条件status1所以在这一行没有加锁但是已经在id2/3/4/5的数据行上加锁。-- 【会话A】-- 将id1的status改为1。修改之后的数值将符合会话A的SELECT FOR UPDATE的过滤条件status1。SQL UPDATE t_for_update SET status1 WHERE id1;1 row updated.-- 将id6的status改为0。修改之后的数值将不再符合会话A的SELECT FOR UPDATE的过滤条件status1。SQL UPDATE t_for_update SET status0 WHERE id6;1 row updated.SQL commit;Commit complete.SQL SELECT id, status FROM t_for_update;ID STATUS---------- ----------1 12 13 14 15 16 06 rows selected.-- 【会话B】-- 在会话A的事务提交后会话B获得了id6的行锁返回了结果锁定了id1/2/3/4/5。-- 其中的id1数据行是锁等待之前已经扫描过的数据行锁等待之前并未对这行数据加锁。SQL SELECT /* gather_plan_statistics */ id, status FROM t_for_update WHERE status1 FOR UPDATE;ID STATUS---------- ----------1 12 13 14 15 1-- 查看该SELECT FOR UPDATE语句的执行计划类似于访问系统视图V$SQL_PLAN_STATISTICS_ALL从“Starts”列的数值是2可以确认该语句被重新执行了一遍。-- 这就是Oracle的语句级重启机制Statement-level Restart重新执行的结果就是锁定了id1/2/3/4/5而不是id2/3/4/5。SQL SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ALLSTATS LAST));PLAN_TABLE_OUTPUT-------------------------------------SQL_ID ck24jvdapm9qp, child number 0-------------------------------------SELECT /* gather_plan_statistics */ id, status FROM t_for_update WHEREstatus1 FOR UPDATEPlan hash value: 3075388249-------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | | 5 |00:07:39.58 | 22 | | | || 1 | FOR UPDATE | | 2 | | 5 |00:07:39.58 | 22 | | | || 2 | BUFFER SORT | | 3 | | 15 |00:00:00.01 | 5 | 2048 | 2048 | 2048 (0)|| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | T_FOR_UPDATE | 2 | 5 | 10 |00:00:00.01 | 5 | | | ||* 4 | INDEX RANGE SCAN | I_STATUS | 2 | 5 | 10 |00:00:00.01 | 2 | | | |-------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - access(STATUS1)22 rows selected.如果换成GaussDB或者MySQL InnoDB则会话B的SELECT FOR UPDATE语句执行结果将是锁定id2/3/4/5的数据行不会锁定在锁等待之前就已经扫描过的id1因为当时的status0不符合过滤条件。SQL SELECT id, status FROM t_for_update WHERE status1 FOR UPDATE;ID STATUS---------- ----------2 13 14 15 1这份实验记录非常经典地展示了Oracle数据库中一个高级并发控制机制——语句级重启。以下是对该实验步骤、结果以及底层原理的详细分析1. 实验初始状态准备数据准备表t_for_update中有6行数据。其中id1的行status0其余id2,3,4,5,6的行status1。会话A先行加锁会话A执行了SELECT id, status FROM t_for_update WHERE id6 FOR UPDATE;成功锁定了id6的数据行。2. 实验核心冲突过程会话B发起查询并加锁会话B执行SELECT /* gather_plan_statistics */ id, status FROM t_for_update WHERE status1 FOR UPDATE;。扫描与加锁轨迹Oracle会按照一定的顺序这里是通过索引I_STATUS扫描遍历数据。B会话首先扫描到id1因为此时status0不满足条件所以没有对其加锁。接着扫描到id2,3,4,5满足条件逐一加锁。发生阻塞当扫描到id6时虽然满足status1的条件但由于该行已经被会话A锁定会话B在这里发生行锁等待事务被挂起。会话A在会话B等待期间进行修改UPDATE t_for_update SET status1 WHERE id1;将会话B先前扫描过但未加锁的id1的状态改为 1使其变得符合B的查询过滤条件。UPDATE t_for_update SET status0 WHERE id6;将会话B正在等待的id6的状态改为 0使其变得不符合B的查询过滤条件。COMMIT;会话A提交事务释放id6上的行锁。3. 实验结果与Oracle的“神奇”表现会话B解除阻塞并获得结果会话A提交后会话B获得了id6的行锁查询继续执行并返回结果。返回的结果是id1, 2, 3, 4, 5。结果异常点注意id1被会话B锁定并返回了但在会话B最初扫描id1时它的status还是 0是不符合过滤条件的。会话B仿佛“时光倒流”一般把已经扫描过去的数据又重新捞了回来。4. 执行计划证实“语句级重启”通过查看DBMS_XPLAN.DISPLAY_CURSOR的输出可以找到发生“魔法”的硬核证据在执行计划的统计信息中Starts列的值均为 2对于 INDEX RANGE SCAN 和 TABLE ACCESS。这意味着当会话B在id6处解阻塞后Oracle并没有从id6继续往后执行而是将该 SELECT FOR UPDATE 语句从头到尾重新执行了一遍。重新执行时数据库中id1的status已经是 1 了所以这次重扫时id1被命中并加锁而id6的status已经变成了 0所以这次没有锁定id6。最终结果就是锁定了1, 2, 3, 4, 5。5. 为什么Oracle要引入 Statement-level Restart这是为了保证写一致性和数据完整性。如果一个事务在执行过程中因为等待锁而被挂起等待期间其他事务提交了数据变更。如果Oracle不重启而是从阻塞点继续执行那么这条语句看到的数据就是“一半是旧数据一半是新数据”的撕裂状态违反了语句级别的写一致性。因此Oracle在检测到此类冲突时选择了最安全的方法丢弃之前的执行结果以当前最新的数据状态重新执行该语句。6. 与其他数据库的对比参考信息最后提到了 GaussDB 和 MySQL InnoDB 的表现这突显了不同数据库在并发控制设计上的差异GaussDB / MySQL InnoDB它们不会发生语句级重启。会话B在id6处等待等A提交后B会直接从id6往下继续执行。结果差异因为它们不会回头去重新扫描id1所以id1即使变成了status1也不会被会话B锁定。会话B最终锁定的只是它在阻塞前已经锁定的id2,3,4,5以及阻塞后继续扫描到的符合条件的数据。代价差异MySQL/GaussDB的做法性能更好不需要重扫但牺牲了严格的“语句级读一致性”语句执行中途看到了其他事务提交的变更Oracle的做法绝对保证了读一致性但代价是付出了额外的资源开销重扫已扫描的数据如果表很大这种重启甚至可能引发严重的性能问题甚至死锁。