Oracle数据库控制 —— 事务与并发控制详解
一、环境准备：Oracle 安装简要说明（延续第13章）
本章依赖已安装的 Oracle Database（如 21c XE），无需额外安装组件。
若尚未安装，请参考第13章“环境准备”部分完成 Oracle 安装并确保可使用sqlplus连接。
验证安装：
sqlplus sys/your_password@localhost:1521/XE as sysdba创建测试用户（用于后续案例）：
-- 以 SYSDBA 身份执行CREATEUSERapp_user IDENTIFIEDBYAppPass123;GRANTCREATESESSION,CREATETABLE,UNLIMITEDTABLESPACETOapp_user;二、核心语法知识点详解与案例
1. 事务概述（Transaction）
概念：
- 事务是一组逻辑操作单元，具有ACID特性：
- Atomicity（原子性）：全部成功或全部失败。
- Consistency（一致性）：事务前后数据满足业务规则。
- Isolation（隔离性）：并发事务互不干扰。
- Durability（持久性）：提交后结果永久保存。
Oracle 事务特点：
- 自动开启：执行第一条 DML（INSERT/UPDATE/DELETE）时自动开始。
- 显式结束：通过
COMMIT或ROLLBACK结束。 - 隐式提交：执行 DDL（如 CREATE TABLE）或退出会话时自动提交。
2. 操作事务
2.1 提交事务（COMMIT）
COMMIT[WORK][COMMENT'text'];WORK可选（兼容 SQL 标准）。COMMENT用于分布式事务（极少用）。
案例：
-- 开启事务（自动）INSERTINTOemployees(id,name)VALUES(101,'Alice');UPDATEdepartmentsSEThead='Alice'WHEREdept_id=10;-- 提交更改COMMIT;-- 所有修改永久生效2.2 回滚事务（ROLLBACK）
ROLLBACK[WORK][TOSAVEPOINTsavepoint_name];案例：
-- 插入数据INSERTINTOemployeesVALUES(102,'Bob');-- 设置保存点SAVEPOINTsp1;-- 再插入INSERTINTOemployeesVALUES(103,'Charlie');-- 回滚到保存点（仅撤销 Charlie 的插入）ROLLBACKTOsp1;-- 完全回滚（撤销 Bob 和 Charlie）ROLLBACK;✅
SAVEPOINT允许部分回滚，提高事务灵活性。
2.3 自动回滚
- 会话异常断开（如网络中断） → Oracle 自动回滚未提交事务。
- 执行 DDL（如
CREATE TABLE） → 隐式提交当前事务。
3. 并发控制与锁机制
3.1 为何加锁？
- 防止多个事务同时修改同一数据导致不一致。
- 保证隔离性（Isolation）。
常见并发问题：
| 问题 | 说明 |
|---|---|
| 脏读 | 读到未提交的数据 |
| 不可重复读 | 同一事务内多次读取结果不同 |
| 幻读 | 新插入的行“凭空出现” |
🔒 Oracle 默认隔离级别为READ COMMITTED，可避免脏读，但可能出现不可重复读和幻读。
4. 加锁的方法
Oracle 自动管理大多数锁（DML 锁），但也支持手动控制。
4.1 行级锁（Row-Level Locking）
- 执行
UPDATE/DELETE时自动对涉及行加排他锁（X 锁）。 - 其他会话可读，但不能修改被锁定的行。
案例（会话 A）：
-- 会话 AUPDATEemployeesSETsalary=8000WHEREid=101;-- 此时 id=101 的行被锁定（未 COMMIT 前）会话 B 尝试修改同一行：
-- 会话 B（将阻塞，直到会话 A COMMIT 或 ROLLBACK）UPDATEemployeesSETsalary=9000WHEREid=101;⏳ 会话 B 会等待（默认无超时），可通过
ALTER SESSION SET ddl_lock_timeout = 10;设置 DDL 等待时间（对 DML 无效）。
4.2 表级锁（Table Locks）
- 手动加表锁（较少用，通常由 Oracle 自动升级）。
语法：
LOCKTABLEtable_nameINlock_modeMODE[NOWAIT];常见模式：
| 模式 | 说明 |
|---|---|
| ROW SHARE | 允许多个会话并发读写（默认 DML 行为） |
| SHARE | 禁止其他会话修改表（用于只读场景） |
| EXCLUSIVE | 禁止任何其他 DML/DDL |
案例：
-- 锁定 employees 表为共享模式（禁止结构修改）LOCKTABLEemployeesINSHAREMODE;-- 锁定为排他模式（禁止任何其他访问）LOCKTABLEemployeesINEXCLUSIVEMODENOWAIT;-- NOWAIT：不等待，直接报错❗
EXCLUSIVE模式会阻塞所有其他 DML，慎用！
4.3 SELECT FOR UPDATE（显式加锁）
- 在查询时锁定选中行，防止其他会话修改。
语法：
SELECT...FROMtable_nameWHERE...FORUPDATE[OFcolumn_list][NOWAIT|WAIT n];案例：
-- 锁定部门 10 的所有员工记录SELECT*FROMemployeesWHEREdept_id=10FORUPDATE;-- 仅锁定 salary 列（语义上，实际仍锁整行）SELECTid,salaryFROMemployeesWHEREid=101FORUPDATEOFsalary NOWAIT;✅ 常用于“先查后改”场景，避免竞态条件。
5. 死锁（Deadlock）
5.1 死锁的产生
- 两个或多个事务互相等待对方释放锁，形成循环等待。
经典场景：
- 会话 A：更新
emp(id=1)→ 尝试更新dept(id=1) - 会话 B：更新
dept(id=1)→ 尝试更新emp(id=1) - 双方都在等对方释放锁 → 死锁！
5.2 Oracle 如何处理死锁？
- Oracle自动检测死锁（通过等待图）。
- 终止其中一个事务（报错
ORA-00060），让另一个继续。
模拟死锁（需两个会话）：
会话 A：
UPDATEemployeesSETsalary=7000WHEREid=101;-- 不提交UPDATEdepartmentsSETbudget=100000WHEREdept_id=10;会话 B：
UPDATEdepartmentsSETbudget=110000WHEREdept_id=10;-- 不提交UPDATEemployeesSETsalary=7500WHEREid=101;→ 其中一个会话将报错：
ORA-00060: deadlock detected while waiting for resource5.3 死锁的预防
✅最佳实践：
- 按固定顺序访问表：如总是先
employees后departments。 - 减少事务持有锁的时间：尽快
COMMIT。 - 避免交互式事务：不要在事务中等待用户输入。
- 使用
SELECT FOR UPDATE NOWAIT：快速失败而非无限等待。
三、综合性实战案例
场景：银行转账系统（高并发安全）
业务需求：
- 用户 A 向用户 B 转账 500 元。
- 必须保证：A 扣款成功 ⇨ B 加款成功（原子性）。
- 高并发下不能超扣或重复转账。
表结构：
CREATETABLEaccounts(account_id NUMBERPRIMARYKEY,balance NUMBER(10,2)NOTNULL);INSERTINTOaccountsVALUES(1,1000.00);-- AINSERTINTOaccountsVALUES(2,2000.00);-- BCOMMIT;安全转账存储过程（推荐方式）
CREATEORREPLACEPROCEDUREtransfer_funds(p_from_idINNUMBER,p_to_idINNUMBER,p_amountINNUMBER)ASv_from_bal NUMBER;v_to_bal NUMBER;BEGIN-- 1. 显式锁定两账户（按 ID 升序，避免死锁）SELECTbalanceINTOv_from_balFROMaccountsWHEREaccount_id=p_from_idFORUPDATE;-- 锁定转出账户SELECTbalanceINTOv_to_balFROMaccountsWHEREaccount_id=p_to_idFORUPDATE;-- 锁定转入账户-- 2. 检查余额IFv_from_bal<p_amountTHENRAISE_APPLICATION_ERROR(-20001,'Insufficient funds');ENDIF;-- 3. 执行转账UPDATEaccountsSETbalance=balance-p_amountWHEREaccount_id=p_from_id;UPDATEaccountsSETbalance=balance+p_amountWHEREaccount_id=p_to_id;-- 4. 提交（自动释放锁）COMMIT;DBMS_OUTPUT.PUT_LINE('Transfer successful.');EXCEPTIONWHENOTHERSTHENROLLBACK;RAISE;END;/✅ 关键点：
- 使用
FOR UPDATE显式加锁。- 按
account_id升序访问（统一顺序防死锁）。- 异常时回滚。
多会话并发测试
会话 1：
EXECtransfer_funds(1,2,500);会话 2（同时执行）：
EXECtransfer_funds(2,1,300);→ Oracle 会串行执行，不会出现余额错误或死锁（因访问顺序一致）。
四、监控与诊断工具
1. 查看当前锁信息
-- 查看被阻塞的会话SELECTsid,serial#, username, blocking_sessionFROMv$sessionWHEREblocking_sessionISNOTNULL;-- 查看锁详情SELECTs.sid,s.serial#, s.username, l.type, l.lmode, l.request, o.object_nameFROMv$locklJOINv$sessionsONl.sid=s.sidLEFTJOINdba_objects oONl.id1=o.object_idWHEREl.typeIN('TM','TX');2. 强制终止会话（解决长时间阻塞）
-- 终止 SID=123, SERIAL#=456 的会话ALTERSYSTEMKILLSESSION'123,456';五、总结与最佳实践
| 主题 | 建议 |
|---|---|
| 事务 | 尽量短小，及时 COMMIT/ROLLBACK |
| 锁 | 优先依赖 Oracle 自动行锁，必要时用FOR UPDATE |
| 死锁 | 统一访问顺序 + 快速失败（NOWAIT） |
| 并发 | 避免长事务，合理设计索引减少锁范围 |
| 监控 | 定期检查v$lock、v$session |
💡 Oracle 的并发控制高度自动化，开发者应聚焦于事务边界设计和访问顺序规范，而非手动加锁。
✅ 本章覆盖 Oracle 事务控制、锁机制、死锁处理全流程，适用于 OLTP 高并发场景开发与调优。
