Oracle数据库控制 —— 事务与并发控制详解一、环境准备Oracle 安装简要说明延续第13章本章依赖已安装的 Oracle Database如 21c XE无需额外安装组件。若尚未安装请参考第13章“环境准备”部分完成 Oracle 安装并确保可使用sqlplus连接。验证安装sqlplus sys/your_passwordlocalhost:1521/XE as sysdba创建测试用户用于后续案例-- 以 SYSDBA 身份执行CREATEUSERapp_user IDENTIFIEDBYAppPass123;GRANTCREATESESSION,CREATETABLE,UNLIMITEDTABLESPACETOapp_user;二、核心语法知识点详解与案例1. 事务概述Transaction概念事务是一组逻辑操作单元具有ACID特性Atomicity原子性全部成功或全部失败。Consistency一致性事务前后数据满足业务规则。Isolation隔离性并发事务互不干扰。Durability持久性提交后结果永久保存。Oracle 事务特点自动开启执行第一条 DMLINSERT/UPDATE/DELETE时自动开始。显式结束通过COMMIT或ROLLBACK结束。隐式提交执行 DDL如 CREATE TABLE或退出会话时自动提交。2. 操作事务2.1 提交事务COMMITCOMMIT[WORK][COMMENTtext];WORK可选兼容 SQL 标准。COMMENT用于分布式事务极少用。案例-- 开启事务自动INSERTINTOemployees(id,name)VALUES(101,Alice);UPDATEdepartmentsSETheadAliceWHEREdept_id10;-- 提交更改COMMIT;-- 所有修改永久生效2.2 回滚事务ROLLBACKROLLBACK[WORK][TOSAVEPOINTsavepoint_name];案例-- 插入数据INSERTINTOemployeesVALUES(102,Bob);-- 设置保存点SAVEPOINTsp1;-- 再插入INSERTINTOemployeesVALUES(103,Charlie);-- 回滚到保存点仅撤销 Charlie 的插入ROLLBACKTOsp1;-- 完全回滚撤销 Bob 和 CharlieROLLBACK;✅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-- 会话 AUPDATEemployeesSETsalary8000WHEREid101;-- 此时 id101 的行被锁定未 COMMIT 前会话 B 尝试修改同一行-- 会话 B将阻塞直到会话 A COMMIT 或 ROLLBACKUPDATEemployeesSETsalary9000WHEREid101;⏳ 会话 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_id10FORUPDATE;-- 仅锁定 salary 列语义上实际仍锁整行SELECTid,salaryFROMemployeesWHEREid101FORUPDATEOFsalary NOWAIT;✅ 常用于“先查后改”场景避免竞态条件。5. 死锁Deadlock5.1 死锁的产生两个或多个事务互相等待对方释放锁形成循环等待。经典场景会话 A更新emp(id1)→ 尝试更新dept(id1)会话 B更新dept(id1)→ 尝试更新emp(id1)双方都在等对方释放锁 → 死锁5.2 Oracle 如何处理死锁Oracle自动检测死锁通过等待图。终止其中一个事务报错ORA-00060让另一个继续。模拟死锁需两个会话会话 AUPDATEemployeesSETsalary7000WHEREid101;-- 不提交UPDATEdepartmentsSETbudget100000WHEREdept_id10;会话 BUPDATEdepartmentsSETbudget110000WHEREdept_id10;-- 不提交UPDATEemployeesSETsalary7500WHEREid101;→ 其中一个会话将报错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_idp_from_idFORUPDATE;-- 锁定转出账户SELECTbalanceINTOv_to_balFROMaccountsWHEREaccount_idp_to_idFORUPDATE;-- 锁定转入账户-- 2. 检查余额IFv_from_balp_amountTHENRAISE_APPLICATION_ERROR(-20001,Insufficient funds);ENDIF;-- 3. 执行转账UPDATEaccountsSETbalancebalance-p_amountWHEREaccount_idp_from_id;UPDATEaccountsSETbalancebalancep_amountWHEREaccount_idp_to_id;-- 4. 提交自动释放锁COMMIT;DBMS_OUTPUT.PUT_LINE(Transfer successful.);EXCEPTIONWHENOTHERSTHENROLLBACK;RAISE;END;/✅ 关键点使用FOR UPDATE显式加锁。按account_id升序访问统一顺序防死锁。异常时回滚。多会话并发测试会话 1EXECtransfer_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.sids.sidLEFTJOINdba_objects oONl.id1o.object_idWHEREl.typeIN(TM,TX);2. 强制终止会话解决长时间阻塞-- 终止 SID123, SERIAL#456 的会话ALTERSYSTEMKILLSESSION123,456;五、总结与最佳实践主题建议事务尽量短小及时 COMMIT/ROLLBACK锁优先依赖 Oracle 自动行锁必要时用FOR UPDATE死锁统一访问顺序 快速失败NOWAIT并发避免长事务合理设计索引减少锁范围监控定期检查v$lock、v$session Oracle 的并发控制高度自动化开发者应聚焦于事务边界设计和访问顺序规范而非手动加锁。✅ 本章覆盖 Oracle 事务控制、锁机制、死锁处理全流程适用于 OLTP 高并发场景开发与调优。