Oracle多版本并发控制核心机制

oracle并发控制核心机制：
是指Oracle数据库管理系统在处理多个同时发生的事务时，确保数据的一致性、完整性的一系列技术和方法。

两个数据库会话：Session A（查询会话）、Session B（更新会话）
测试表：test_undo

----------------------------------------------------
一致性读验证
测试条件
create table test_undo (
id number primary key,
name varchar2(50),
salary number
);

insert into test_undo values (1, 'alice', 5000);
insert into test_undo values (2, 'bob', 6000);
commit;

session A开始事务
SQL> update scott.test_undo set salary = 7000 where id = 1;
1 row updated.
session B查询（此时无法对该行进行修改）
SQL> select * from scott.test_undo where id = 1;
ID NAME SALARY
---------- ---------- ----------
1 alice 5000
salary仍然是5000
对于未commit提交的数据修改，在另一个会话中查询到相关数据块，看到的其实是开始查询那个
时间点的‘快照’，是通过undo表空间中读取旧版本数据块来实现的。
当执行rollback时，再利用undo数据块将未提交的数据恢复原样。

---------------------------------------------------------
undo机制
查看UNDO信息
在新的Session C中执行，监控UNDO使用情况
select s.sid, s.serial#, s.username,
t.start_time,
t.used_ublk as "undo blocks",
t.used_urec as "undo records",
t.start_ubafil as "undo file#",
t.start_ubablk as "undo block#"
from v$transaction t
join v$session s on s.saddr = t.ses_addr
where s.username = 'SYS';
SID SERIAL# USERNAME START_TIME undo blocks undo records undo file# undo block#
---------- ---------- ---------- ------------------- ------------ ------------- ------------ ----------
125 11 SYS 10/21/25 15:12:42 1 1 3 855
Session A中执行多次更新，增加UNDO生成量
update scott.test_undo set salary = salary + 1000 where id = 1;
update scott.test_undo set name = 'alice_updated' where id = 1;
update scott.test_undo set salary = salary + 2000 where id = 1;
Session C中再次执行监控undo的查询
观察"Undo Blocks"和"Undo Records"的增长
SID SERIAL# USERNAME START_TIME undo blocks undo records undo file# undo block#
---------- ---------- ---------- ------------------- ------------ ------------- ------------ ----------
125 11 SYS 10/21/25 15:12:42 1 4 3 855
个事务都会分配一个UNDO段（回滚段）来存储回滚信息。
每次对数据的修改都会在UNDO段中记录前镜像，包括数据修改前的值、事务ID、SCN等
通过查询v$transaction视图，可以查看当前事务使用的UNDO块数（used_ublk）和UNDO记录数（used_urec）。这些值会随着事务修改数据量的增加而增加。

----------------------------------------------------------------------
理解SCN与读一致性
获取系统变更号（在未提交状态下）
Session A
select dbms_flashback.get_system_change_number as current_scn from dual;
CURRENT_SCN
-----------
3127398
Session B
select dbms_flashback.get_system_change_number as current_scn from dual;
CURRENT_SCN
-----------
3127403

Session A提交后，使用提交前的数据
SQL> commit;
Commit complete.
--当前数据：
SQL> select * from scott.test_undo;

ID NAME SALARY
---------- ---------- ----------
1 alice_upda 10000
ted
2 bob 6000
--5分钟前的数据
as of timestamp (systimestamp - interval '5' minute)
where id = 1;
ID NAME SALARY
---------- ---------- ----------
1 alice 5000

使用SCN进行闪回查询（替换为Session A查询到的SCN）
select * from scott.test_undo
as of scn 3127398
where id = 1;
ID NAME SALARY
---------- ---------- ----------
1 alice 5000
--使用SCN号同样可以查询到当前数据
SQL> select dbms_flashback.get_system_change_number as current_scn from dual;
CURRENT_SCN
-----------
3127523
select * from scott.test_undo
as of scn 3127523
where id = 1;
ID NAME SALARY
---------- ---------- ----------
1 alice_upda 10000
ted
SCN是Oracle数据库中的逻辑时间戳，用于标识数据库的变更顺序
一致性读使用SCN来确定数据版本：查询只会看到SCN小于或等于查询开始SCN的数据版本。
Flashback Query(闪回查询)利用UNDO数据来重建指定SCN或时间点的数据状态。

--------------------------------------------------------------------------
锁机制分析
Session C中查看锁信息
select
s.sid,
s.serial#,
s.username,
l.type as lock_type,
l.lmode as lock_mode,
l.request as lock_request,
o.object_name,
l.ctime as lock_hold_time
from v$lock l
join v$session s on l.sid = s.sid
left join dba_objects o on l.id1 = o.object_id
where s.username is not null
order by s.sid, l.type;
SID SERIAL# USERNAME LOCK LOCK_MODE LOCK_REQUEST OBJECT_NAM LOCK_HOLD_TIME
---------- ---------- ---------- ---- ---------- ------------ ---------- --------------
14 11 SYS AE 4 0 ORA$BASE 4613
125 11 SYS AE 4 0 ORA$BASE 2357
137 27 SYS AE 4 0 ORA$BASE 952
142 159 SYS AE 4 0 ORA$BASE 1394

模拟锁等待
Session A更新数据
update scott.test_undo set salary = 5000 where id = 1;
1 row updated.
Session B尝试更新同一条记录（挂起，无法进行）
update scott.test_undo set salary = 8000 where id = 1;
此时会话会挂起并生成表级锁(TM)和行级锁/事务锁(TX)，等待Session A释放锁
SID SERIAL# USERNAME LOCK LOCK_MODE LOCK_REQUEST OBJECT_NAM LOCK_HOLD_TIME
---------- ---------- ---------- ---- ---------- ------------ ---------- --------------
14 11 SYS AE 4 0 ORA$BASE 4846
125 11 SYS AE 4 0 ORA$BASE 2590
125 11 SYS TM 3 0 TEST_UNDO 37
125 11 SYS TX 6 0 37
137 27 SYS AE 4 0 ORA$BASE 1185
142 159 SYS AE 4 0 ORA$BASE 1627
142 159 SYS TM 3 0 TEST_UNDO 16
142 159 SYS TX 0 6 16

锁等待链
Session C中查看阻塞情况
select
w.sid as waiting_sid,
h.sid as holding_sid,
w.type as lock_type,
w.lmode as mode_held,
w.request as mode_requested,
o.object_name
from v$lock w, v$lock h, dba_objects o
where h.id1 = w.id1
and h.id2 = w.id2
and h.lmode > 0
and w.request > 0
and h.id1 = o.object_id(+)
and w.sid != h.sid;

WAITING_SID HOLDING_SID LOCK MODE_HELD MODE_REQUESTED OBJECT_NAM
----------- ----------- ---- ---------- -------------- ----------
142 125 TX 0 6




模拟事务恢复
Session A中模拟异常断开
在操作系统级别找到Session A的SPID
select s.sid, s.serial#, p.spid
from v$session s, v$process p
where s.paddr = p.addr and s.sid = &session_A_sid;
SID SERIAL# SPID
---------- ---------- --------
125 11 2121
kill掉对应的操作系统进程
kill -9 2121

前后对比：
锁已经释放
SID SERIAL# USERNAME LOCK LOCK_MODE LOCK_REQUEST OBJECT_NAM LOCK_HOLD_TIME
---------- ---------- ---------- ---- ---------- ------------ ---------- --------------
137 27 SYS AE 4 0 ORA$BASE 2398
142 161 SYS AE 4 0 ORA$BASE 306
142 161 SYS TM 3 0 TEST_UNDO 298
142 161 SYS TX 6 0 180
被挂起的Session B可以继续完成更新事务
select s.sid, s.serial#, s.username,
t.start_time,
t.used_ublk as "undo blocks",
t.used_urec as "undo records",
t.start_ubafil as "undo file#",
t.start_ubablk as "undo block#"
from v$transaction t
join v$session s on s.saddr = t.ses_addr
where s.username = 'SYS';
SID SERIAL# USERNAME START_TIME undo blocks undo records undo file# undo block#
---------- ---------- ---------- ------------------------------------ ----------- ------------
125 21 SYS 10/21/25 19:33:40 1 1 3 2025
Session A会话断开后进行了回滚
SID SERIAL# USERNAME START_TIME undo blocks undo records undo file# undo block#
---------- ---------- ---------- ------------------------------------ ----------- ------------
142 161 SYS 10/21/25 15:49:59 1 1 3 1869
Oracle使用锁来保证事务的隔离性。当一行数据被更新时，会在该行上设置行级锁（TX锁），防止其他事务同时更新同一行。
当一个事务试图更新被另一个事务锁定的行时，它会等待直到锁被释放（或超时）。
通过查询v$lock视图，可以查看当前会话持有的锁和等待的锁。v$lock中的LMODE表示锁的模式，REQUEST表示请求的锁模式。

-------------------------------------------------
数据访问流程：
1. 查询开始，获取当前SCN
2. 读取数据块
3. 检查数据块头的ITL（事务槽）
4. 如果数据块SCN > 查询SCN，通过UNDO地址找到旧版本
5. 从UNDO段读取历史版本数据
6. 重复直到找到查询SCN时的数据版本
2. UNDO段的结构与管理
UNDO段组成：
UNDO表空间
↓
UNDO段（回滚段）
↓
UNDO区（Extent）
↓
UNDO块（Block）

事务在UNDO段中的工作：
每个活动事务占用一个UNDO段槽位
UNDO块中存储：前镜像数据、事务ID、SCN、操作类型
循环使用：当UNDO段满时，会扩展或重用空间

--SCN（系统变更号）的作用
SCN的层级结构：
数据库SCN → 数据文件SCN → 数据块SCN
SCN在一致性读中的角色：
查询开始时获取当前SCN
只读取SCN小于或等于查询SCN的数据版本
通过比较数据块SCN和查询SCN决定是否需要访问UNDO


--锁机制
行级锁（TX）：
模式：0=None, 3=Row Exclusive
作用：保护单行数据

表级锁（TM）：
模式：2=Row Share, 3=Row Exclusive, 4=Share, 5=Share Row Exclusive, 6=Exclusive
锁等待解决机制：
1. 会话a请求被会话b持有的锁
2. 会话a进入等待状态，记录在v$session_wait
3. 等待超时（默认无限等待）
4. 死锁检测：每3秒检查一次，发现死锁立即回滚其中一个事务
5. 事务恢复原理
被阻塞事务恢复过程：
1. 检测到异常断开的事务
2. 读取undo段中的事务信息
3. 应用undo数据回滚未提交的更改
4. 释放事务持有的锁
5. 清理事务槽位


关键视图
监控UNDO和事务：
v$transaction：当前活动事务
v$rollstat：UNDO段统计信息
v$undostat：UNDO表空间使用历史

监控锁和等待：
v$lock：当前持有的锁和等待的锁
v$session_wait：会话等待事件
v$enqueue_stat：队列锁统计