Oracle 事务与锁机制

事务与锁机制是 Oracle 数据库并发控制的核心，理解事务和锁的工作原理对于 DBA 进行性能调优、故障诊断和日常维护至关重要。

事务基础

事务的定义

事务是一个或多个 SQL 语句的逻辑单元，这些语句要么全部执行成功，要么全部失败。事务是数据库并发控制的基本单位。

ACID 特性

Oracle 事务具有 ACID 特性：

原子性（Atomicity）：事务是一个不可分割的工作单元，要么全部执行成功，要么全部失败回滚
一致性（Consistency）：事务执行前后，数据库从一个一致状态转换到另一个一致状态
隔离性（Isolation）：一个事务的执行不会被其他事务干扰
持久性（Durability）：事务一旦提交，其结果将永久保存在数据库中

事务的状态

活跃状态（Active）：事务正在执行
部分提交状态（Partially Committed）：事务的最后一条语句执行完毕，但结果尚未写入磁盘
提交状态（Committed）：事务执行成功，结果已写入磁盘
失败状态（Failed）：事务执行过程中出现错误，需要回滚
终止状态（Aborted）：事务已回滚，数据库恢复到事务开始前的状态

事务控制语句

基本事务控制

sql

-- 开始事务（隐式开始）
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date) 
VALUES (207, 'John', 'Doe', 'john.doe@example.com', SYSDATE);

-- 保存点
SAVEPOINT save1;

-- 继续执行事务
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 207;

-- 回滚到保存点
ROLLBACK TO save1;

-- 提交事务
COMMIT;

-- 回滚整个事务
ROLLBACK;

自动提交

sql

-- 查看自动提交设置
SHOW PARAMETER autocommit;

-- 启用自动提交
SET autocommit ON;

-- 禁用自动提交
SET autocommit OFF;

分布式事务

sql

-- 提交分布式事务
COMMIT FORCE '2PC_TRANSACTION_ID';

-- 回滚分布式事务
ROLLBACK FORCE '2PC_TRANSACTION_ID';

-- 查看分布式事务
SELECT * FROM dba_2pc_pending;

锁机制

锁的作用

保证数据一致性：防止多个事务同时修改同一数据导致数据不一致
实现事务隔离：确保事务的隔离性，防止脏读、不可重复读和幻读
协调并发访问：合理分配资源，提高并发性能

锁的类型

按锁的粒度分类

行级锁（Row-Level Lock）
- 粒度最小的锁，锁定单行数据
- 并发性能最高，冲突概率最低
- 自动获取，无需显式请求
- 适合 OLTP 环境
页级锁（Block-Level Lock）
- 锁定数据块，包含多行数据
- 粒度比行级锁大，并发性能较低
- Oracle 不直接使用页级锁，而是通过行级锁实现类似功能
表级锁（Table-Level Lock）
- 锁定整个表
- 粒度最大，并发性能最低
- 适合批量操作和数据维护
- 可以显式请求

按锁的模式分类

共享锁（Share Lock, S）
- 允许其他事务读取数据，但不允许修改
- 用于 SELECT FOR UPDATE 等语句
- 兼容其他共享锁，但不兼容排他锁
排他锁（Exclusive Lock, X）
- 不允许其他事务读取或修改数据
- 用于 INSERT、UPDATE、DELETE 等语句
- 不兼容任何其他锁
更新锁（Update Lock, U）
- 介于共享锁和排他锁之间
- 用于 UPDATE 语句的初始阶段
- 兼容共享锁，但不兼容其他更新锁和排他锁
意向锁（Intention Lock）
- 表示事务打算在更细粒度上获取锁
- 包括意向共享锁（IS）和意向排他锁（IX）
- 用于表级锁和行级锁之间的协调

Oracle 特定锁类型

行共享锁（Row Share, RS）
- 允许其他事务读取和修改表中的其他行
- 用于 SELECT FOR UPDATE 语句
行排他锁（Row Exclusive, RX）
- 允许其他事务读取表，但不允许排他锁定表
- 用于 INSERT、UPDATE、DELETE 等语句
共享行排他锁（Share Row Exclusive, SRX）
- 允许其他事务读取表，但不允许修改表
- 用于 CREATE INDEX 语句
表排他锁（Table Exclusive, X）
- 完全锁定表，不允许任何其他事务访问
- 用于 ALTER TABLE、DROP TABLE 等语句

锁的获取与释放

自动获取锁

SELECT 语句：默认不获取锁，使用一致性读
SELECT FOR UPDATE 语句：获取行级共享锁
INSERT、UPDATE、DELETE 语句：获取行级排他锁
CREATE INDEX 语句：获取表级共享行排他锁
ALTER TABLE、DROP TABLE 语句：获取表级排他锁

显式获取锁

sql

-- 显式获取表级共享锁
LOCK TABLE employees IN SHARE MODE;

-- 显式获取表级排他锁
LOCK TABLE employees IN EXCLUSIVE MODE;

-- 显式获取表级行排他锁
LOCK TABLE employees IN ROW EXCLUSIVE MODE;

-- 显式获取表级行共享锁
LOCK TABLE employees IN ROW SHARE MODE;

-- 显式获取表级共享行排他锁
LOCK TABLE employees IN SHARE ROW EXCLUSIVE MODE;

锁的释放

事务提交（COMMIT）后自动释放
事务回滚（ROLLBACK）后自动释放
会话断开后自动释放
显式解锁（LOCK TABLE ... IN SHARE MODE NOWAIT）

并发控制

并发访问问题

在并发环境下，多个事务同时访问数据库可能会导致以下问题：

脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据
不可重复读（Non-Repeatable Read）：一个事务对同一行数据多次读取，结果不一致
幻读（Phantom Read）：一个事务对同一范围的数据多次查询，结果集的行数不一致
丢失更新（Lost Update）：两个事务同时更新同一行数据，导致其中一个更新丢失

隔离级别

Oracle 支持以下隔离级别：

隔离级别	脏读	不可重复读	幻读	Oracle 默认
读未提交（READ UNCOMMITTED）	可能	可能	可能	不支持
读已提交（READ COMMITTED）	不可能	可能	可能	是
可重复读（REPEATABLE READ）	不可能	不可能	可能	不支持
串行化（SERIALIZABLE）	不可能	不可能	不可能	支持

隔离级别的设置

sql

-- 设置会话级隔离级别
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;

-- 设置事务级隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 查看当前隔离级别
SELECT s.sid, s.serial#, s.username, t.isolation_level
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr;

Oracle 读一致性

Oracle 采用多版本并发控制（MVCC）机制实现读一致性：

一致性读（Consistent Read）：SELECT 语句读取事务开始时的数据版本，不受其他事务影响
当前读（Current Read）：SELECT FOR UPDATE、INSERT、UPDATE、DELETE 等语句读取最新的数据版本
回滚段（Undo Segment）：存储数据的旧版本，用于实现读一致性和事务回滚

锁的监控与管理

锁的监控

sql

-- 查看当前锁信息
SELECT * FROM v$lock;

-- 查看锁等待情况
SELECT * FROM v$lock_waits;

-- 查看阻塞会话
SELECT 
  bl.session_id AS blocking_session,
  w.session_id AS waiting_session,
  bl.lock_type,
  bl.mode_held,
  bl.mode_requested,
  o.object_name,
  s1.username AS blocking_user,
  s2.username AS waiting_user
FROM 
  dba_lock_internal bl,
  dba_lock_internal w,
  dba_objects o,
  v$session s1,
  v$session s2
WHERE 
  bl.session_id = s1.sid AND
  w.session_id = s2.sid AND
  bl.lock_id1 = w.lock_id1 AND
  bl.lock_id2 = w.lock_id2 AND
  bl.mode_held != 'None' AND
  w.mode_requested != 'None' AND
  bl.block = 1 AND
  w.request = 1 AND
  bl.object_id = o.object_id(+);

-- 查看会话锁信息
SELECT s.sid, s.serial#, s.username, l.type, l.id1, l.id2, l.lmode, l.request
FROM v$session s, v$lock l
WHERE s.sid = l.sid;

锁等待事件

sql

-- 查看锁等待事件
SELECT event, COUNT(*) FROM v$session_wait GROUP BY event ORDER BY 2 DESC;

-- 查看具体等待的会话
SELECT s.sid, s.serial#, s.username, s.event, s.wait_time, s.seconds_in_wait
FROM v$session s
WHERE s.event LIKE '%lock%';

锁的管理

sql

-- 终止阻塞会话
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 强制释放锁
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

-- 使用 DBMS_LOCK 包管理锁
EXEC DBMS_LOCK.REQUEST(lockhandle => 'mylock', lockmode => DBMS_LOCK.X_MODE);
EXEC DBMS_LOCK.RELEASE(lockhandle => 'mylock');

死锁处理

死锁的定义

死锁是指两个或多个事务互相等待对方释放锁，导致所有事务都无法继续执行的状态。

死锁的产生条件

互斥条件：资源不能被共享，只能被一个事务使用
请求与保持条件：事务已经持有一个资源，又提出新的资源请求
不剥夺条件：已分配的资源不能被强行剥夺
循环等待条件：若干事务之间形成首尾相接的循环等待资源关系

死锁的检测与处理

Oracle 自动检测死锁，并采取以下措施：

死锁检测：Oracle 定期检查是否存在死锁
死锁处理：当检测到死锁时，Oracle 会选择一个代价最小的事务进行回滚
死锁日志：死锁信息会记录在告警日志中

死锁的监控

sql

-- 查看死锁历史
SELECT * FROM v$deadlock_history;

-- 查看最近的死锁信息
SELECT * FROM v$diag_history WHERE message LIKE '%deadlock%';

-- 查看告警日志中的死锁信息
-- Linux: tail -f $ORACLE_BASE/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log
-- Windows: 使用 Windows 事件查看器或查看 alert_ORCL.log 文件

死锁的避免

合理设计事务：尽量缩短事务的执行时间
合理设计索引：减少锁的竞争
使用合理的隔离级别：根据业务需求选择合适的隔离级别
避免长事务：长事务容易导致锁积累和死锁
使用 NOWAIT 选项：避免长时间等待锁
统一访问顺序：多个事务访问资源时，按照相同的顺序访问

事务与锁的性能优化

事务性能优化

缩短事务时间：尽量减少事务中的 SQL 语句数量，避免在事务中执行耗时操作
避免长事务：长事务会占用大量的回滚段空间，影响系统性能
合理使用索引：减少锁的竞争，提高查询性能
使用绑定变量：减少硬解析，提高事务执行速度
避免在事务中使用 DDL 语句：DDL 语句会隐式提交事务，影响事务的原子性

锁性能优化

减少锁的持有时间：尽量缩短事务的执行时间，减少锁的持有时间
减少锁的粒度：优先使用行级锁，避免使用表级锁
避免锁升级：合理设置锁的参数，避免行级锁升级为表级锁
使用 NOWAIT 选项：避免长时间等待锁，及时处理锁冲突
监控锁等待情况：定期监控锁等待情况，及时处理锁冲突
使用并行执行：对于批量操作，使用并行执行可以提高性能

回滚段优化

sql

-- 查看回滚段使用情况
SELECT segment_name, tablespace_name, r.status, r.blocks, r.extents
FROM dba_rollback_segs r, v$rollname n
WHERE r.segment_name = n.name;

-- 查看回滚段统计信息
SELECT * FROM v$rollstat;

-- 设置回滚段大小
ALTER ROLLBACK SEGMENT rbs01 ONLINE;
ALTER ROLLBACK SEGMENT rbs01 RESIZE 100M;

-- 创建大回滚段
CREATE ROLLBACK SEGMENT rbs_large TABLESPACE undo INITIAL 100M NEXT 100M MAXEXTENTS UNLIMITED;

版本差异

版本	事务与锁特性
Oracle 11g	增强了回滚段管理，引入自动 undo 管理
Oracle 12c	支持多租户环境的事务管理，增强了锁监控功能
Oracle 19c	优化了死锁检测算法，增强了并行事务性能
Oracle 23c	引入了新的并发控制机制，支持更细粒度的锁

常见问题（FAQ）

Q1: 如何查看当前的锁等待情况？

A1: 使用以下查询：

sql

SELECT 
  bl.session_id AS blocking_session,
  w.session_id AS waiting_session,
  bl.lock_type,
  bl.mode_held,
  bl.mode_requested,
  o.object_name,
  s1.username AS blocking_user,
  s2.username AS waiting_user
FROM 
  dba_lock_internal bl,
  dba_lock_internal w,
  dba_objects o,
  v$session s1,
  v$session s2
WHERE 
  bl.session_id = s1.sid AND
  w.session_id = s2.sid AND
  bl.lock_id1 = w.lock_id1 AND
  bl.lock_id2 = w.lock_id2 AND
  bl.mode_held != 'None' AND
  w.mode_requested != 'None' AND
  bl.block = 1 AND
  w.request = 1 AND
  bl.object_id = o.object_id(+);

Q2: 如何处理死锁？

A2: 处理死锁的步骤：

查看死锁信息：SELECT * FROM v$deadlock_history;
分析死锁原因：查看涉及的表、索引和 SQL 语句
终止阻塞会话：ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
优化应用程序：修改应用程序逻辑，避免死锁

Q3: 如何避免锁等待？

A3: 避免锁等待的方法：

缩短事务时间
减少锁的持有时间
使用合理的索引
使用 NOWAIT 选项
统一访问顺序
避免长事务

Q4: 如何设置隔离级别？

A4: 设置隔离级别的方法：

sql

-- 会话级
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;

-- 事务级
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Q5: 如何查看事务的隔离级别？

A5: 查看事务隔离级别的方法：

sql

SELECT s.sid, s.serial#, s.username, t.isolation_level
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr;

Q6: 如何优化回滚段？

A6: 回滚段优化的方法：

使用自动 undo 管理：ALTER SYSTEM SET undo_management = AUTO SCOPE=SPFILE;
合理设置 undo 表空间大小：ALTER SYSTEM SET undo_tablespace = undo SCOPE=SPFILE;
设置合理的 undo 保留时间：ALTER SYSTEM SET undo_retention = 3600 SCOPE=SPFILE;
监控回滚段使用情况：SELECT * FROM v$rollstat;

命令	用途
`COMMIT`	提交事务
`ROLLBACK`	回滚事务
`SAVEPOINT`	创建保存点
`ROLLBACK TO savepoint`	回滚到保存点
`LOCK TABLE`	显式锁定表
`ALTER SESSION SET ISOLATION_LEVEL`	设置隔离级别
`SET TRANSACTION ISOLATION LEVEL`	设置事务隔离级别
`SELECT * FROM v$lock`	查看锁信息
`SELECT * FROM v$lock_waits`	查看锁等待情况
`SELECT * FROM v$deadlock_history`	查看死锁历史
`ALTER SYSTEM KILL SESSION`	终止会话
`DBMS_LOCK.REQUEST`	请求锁
`DBMS_LOCK.RELEASE`	释放锁

总结

事务与锁机制是 Oracle 数据库并发控制的核心，理解事务和锁的工作原理对于 DBA 进行日常运维、性能调优和故障诊断至关重要。DBA 应该根据实际业务需求和系统资源，选择合适的隔离级别，优化事务设计，监控锁等待情况，及时处理死锁，以确保数据库的稳定高效运行。通过合理的事务设计和锁管理，可以提高数据库的并发性能，减少锁冲突，避免死锁，保证数据的一致性和完整性。

Oracle 事务与锁机制 ​

事务基础 ​

事务的定义 ​

ACID 特性 ​

事务的状态 ​

事务控制语句 ​

基本事务控制 ​

自动提交 ​

分布式事务 ​

锁机制 ​

锁的作用 ​

锁的类型 ​

按锁的粒度分类 ​

按锁的模式分类 ​

Oracle 特定锁类型 ​

锁的获取与释放 ​

自动获取锁 ​

显式获取锁 ​

锁的释放 ​

并发控制 ​

并发访问问题 ​

隔离级别 ​

隔离级别的设置 ​

Oracle 读一致性 ​

锁的监控与管理 ​

锁的监控 ​

锁等待事件 ​

锁的管理 ​

死锁处理 ​

死锁的定义 ​

死锁的产生条件 ​

死锁的检测与处理 ​

死锁的监控 ​

死锁的避免 ​

事务与锁的性能优化 ​

事务性能优化 ​

锁性能优化 ​

回滚段优化 ​

版本差异 ​

常见问题（FAQ） ​

Q1: 如何查看当前的锁等待情况？ ​

Q2: 如何处理死锁？ ​

Q3: 如何避免锁等待？ ​

Q4: 如何设置隔离级别？ ​

Q5: 如何查看事务的隔离级别？ ​

Q6: 如何优化回滚段？ ​

相关命令速查 ​

总结 ​