【Oracle入门到删库跑路-09】核心技能：事务和锁机制

事务基础概念

事务是数据库操作的基本单位，它是一系列数据库操作的逻辑集合，这些操作要么全部执行成功，要么全部不执行。

事务的ACID特性

1. 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成
2. 一致性（Consistency）：事务执行前后，数据库必须保持一致性状态
3. 隔离性（Isolation）：并发执行的事务之间不能相互干扰
4. 持久性（Durability）：事务一旦提交，对数据库的改变就是永久性的

事务控制语句

-- 开始事务（隐式开始）
BEGIN
  -- 执行数据库操作
  INSERT INTO employees VALUES (207, 'John', 'Doe', 'JDOE@EXAMPLE.COM', 
                               '555-1234', SYSDATE, 'IT_PROG', 7000, NULL, 103, 60);
  UPDATE departments SET manager_id = 207 WHERE department_id = 60;
  DELETE FROM job_history WHERE employee_id = 207;
END;
/

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;

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

-- 释放保存点
RELEASE SAVEPOINT sp1;

事务示例

-- 银行转账事务示例
BEGIN
  -- 从账户A扣除金额
  UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A001';
  
  -- 向账户B增加金额
  UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B001';
  
  -- 记录转账历史
  INSERT INTO transfer_history (from_account, to_account, amount, transfer_date)
  VALUES ('A001', 'B001', 1000, SYSDATE);
  
  -- 提交事务
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Transfer completed successfully');
EXCEPTION
  WHEN OTHERS THEN
    -- 回滚事务
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Transfer failed: ' || SQLERRM);
END;
/

5.2 锁机制

锁是数据库管理系统用来控制并发访问共享资源的机制，确保数据的一致性和完整性。

锁的类型

按锁的级别分类

1. 行级锁（Row-Level Lock）：锁定表中的特定行
2. 表级锁（Table-Level Lock）：锁定整个表
3. 页级锁（Page-Level Lock）：锁定数据页

按锁的模式分类

1. 共享锁（Share Lock, S）：允许多个事务同时读取同一资源
2. 排他锁（Exclusive Lock, X）：阻止其他事务读取或修改资源

行级锁

-- 自动行级锁（DML操作时自动获得）
UPDATE employees SET salary = 8000 WHERE employee_id = 100;
-- 此时对employee_id=100的行获得排他锁

-- 手动行级锁
SELECT * FROM employees WHERE employee_id = 100 FOR UPDATE;
-- 对指定行获得排他锁，直到事务结束

-- 带等待时间的行级锁
SELECT * FROM employees WHERE employee_id = 100 FOR UPDATE WAIT 10;
-- 等待最多10秒获取锁，超时则报错

-- 不等待的行级锁
SELECT * FROM employees WHERE employee_id = 100 FOR UPDATE NOWAIT;
-- 不等待，如果无法立即获得锁则报错

-- 跳过锁定行
SELECT * FROM employees WHERE department_id = 60 FOR UPDATE SKIP LOCKED;
-- 跳过已经被其他事务锁定的行

表级锁

-- 行共享锁（RS）：允许并发访问表，但阻止表结构修改
LOCK TABLE employees IN ROW SHARE MODE;

-- 行排他锁（RX）：允许读取表，但阻止其他事务获取排他锁
LOCK TABLE employees IN ROW EXCLUSIVE MODE;

-- 共享锁（S）：允许读取表，但阻止修改表
LOCK TABLE employees IN SHARE MODE;

-- 共享行排他锁（SRX）：允许读取表，但阻止其他事务获取排他锁
LOCK TABLE employees IN SHARE ROW EXCLUSIVE MODE;

-- 排他锁（X）：完全锁定表，阻止其他任何访问
LOCK TABLE employees IN EXCLUSIVE MODE;

-- 释放锁
COMMIT;  -- 或 ROLLBACK;

锁监控

-- 查看当前锁信息
SELECT sid, serial#, username, lockwait, status
FROM v$session
WHERE lockwait IS NOT NULL;

-- 查看锁等待情况
SELECT s1.username || '@' || s1.machine || ' (SID=' || s1.sid || ') is blocking ' ||
       s2.username || '@' || s2.machine || ' (SID=' || s2.sid || ')' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid = l1.sid AND s2.sid = l2.sid
AND l1.BLOCK = 1 AND l2.request > 0
AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;

-- 查看对象锁信息
SELECT do.object_name, lo.session_id, lo.locked_mode
FROM v$locked_object lo, dba_objects do
WHERE lo.object_id = do.object_id;

-- 查看锁持有者和等待者
SELECT lo.session_id AS holder_sid,
       wo.session_id AS waiter_sid,
       do.object_name
FROM v$locked_object lo,
     dba_objects do,
     v$session wo,
     v$session ho
WHERE lo.object_id = do.object_id
AND lo.session_id = ho.sid
AND wo.blocking_session = ho.sid;

5.3 死锁处理

死锁是指两个或多个事务相互等待对方持有的资源，形成循环等待的情况。

死锁示例

-- 会话1执行
BEGIN
  UPDATE employees SET salary = 8000 WHERE employee_id = 100;
  DBMS_LOCK.SLEEP(5);  -- 模拟处理时间
  UPDATE employees SET salary = 9000 WHERE employee_id = 101;
  COMMIT;
END;
/

-- 会话2执行（几乎同时）
BEGIN
  UPDATE employees SET salary = 9000 WHERE employee_id = 101;
  DBMS_LOCK.SLEEP(5);  -- 模拟处理时间
  UPDATE employees SET salary = 8000 WHERE employee_id = 100;
  COMMIT;
END;
/

死锁检测和处理

-- 查看死锁信息
SELECT *
FROM v$lock
WHERE block = 1;

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

-- 查看死锁相关的会话
SELECT s.sid, s.serial#, s.username, s.status, s.sql_id
FROM v$session s, v$lock l
WHERE s.sid = l.sid AND l.block = 1;

-- 终止会话（谨慎使用）
ALTER SYSTEM KILL SESSION 'sid,serial#';

避免死锁的策略

-- 1. 按固定顺序访问资源
-- 总是按employee_id从小到大的顺序更新记录
BEGIN
  IF p_emp_id1 < p_emp_id2 THEN
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = p_emp_id1;
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = p_emp_id2;
  ELSE
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = p_emp_id2;
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = p_emp_id1;
  END IF;
  COMMIT;
END;
/

-- 2. 使用较低的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 3. 减少事务持有锁的时间
BEGIN
  -- 尽快完成操作并提交
  UPDATE employees SET last_updated = SYSDATE WHERE employee_id = 100;
  COMMIT;
END;
/

-- 4. 使用绑定变量减少硬解析
-- 避免在循环中使用动态SQL

5.4 并发控制

并发控制是确保多个事务并发执行时数据一致性的机制。

隔离级别

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;     -- 读已提交（默认）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;      -- 串行化
SET TRANSACTION ISOLATION LEVEL READ ONLY;         -- 只读

-- 查看当前会话的隔离级别
SELECT s.sid, s.serial#, s.username, 
       t.used_ublk, t.used_urec,
       CASE t.isolation_level
         WHEN 1 THEN 'READ COMMITTED'
         WHEN 2 THEN 'SERIALIZABLE'
         WHEN 3 THEN 'READ ONLY'
       END AS isolation_level
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr(+)
AND s.sid = SYS_CONTEXT('USERENV','SID');

读一致性

-- Oracle通过多版本并发控制（MVCC）实现读一致性
-- 读操作不会阻塞写操作，写操作也不会阻塞读操作

-- 示例：在事务中读取数据
BEGIN
  -- 事务开始时创建读一致性视图
  FOR emp_rec IN (SELECT * FROM employees WHERE department_id = 60) LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
    
    -- 即使其他事务在此期间修改了数据，当前事务仍能看到开始时的数据
    DBMS_LOCK.SLEEP(10);
  END LOOP;
END;
/

快照隔离

-- 使用闪回归档实现长时间运行查询的一致性视图
CREATE FLASHBACK ARCHIVE DEFAULT fb_archive
TABLESPACE users
RETENTION 1 YEAR;

-- 为表启用闪回归档
ALTER TABLE employees FLASHBACK ARCHIVE fb_archive;

-- 查询历史数据
SELECT * FROM employees AS OF TIMESTAMP SYSDATE - 1;  -- 一天前的数据
SELECT * FROM employees AS OF SCN 123456;            -- 特定SCN的数据

-- 使用版本查询查看行的历史版本
SELECT versions_starttime, versions_endtime, versions_xid, versions_operation, salary
FROM employees VERSIONS BETWEEN TIMESTAMP SYSDATE-1 AND SYSDATE
WHERE employee_id = 100;

5.5 事务监控和优化

事务监控

-- 查看长时间运行的事务
SELECT s.sid, s.serial#, s.username, s.status, 
       t.start_time, t.used_ublk, t.used_urec,
       (SYSDATE - t.start_date) * 24 * 60 AS duration_minutes
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND (SYSDATE - t.start_date) * 24 * 60 > 10  -- 超过10分钟的事务
ORDER BY duration_minutes DESC;

-- 查看未提交的事务
SELECT s.sid, s.serial#, s.username, s.program, 
       t.start_date, t.used_ublk
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.username IS NOT NULL;

-- 查看回滚段使用情况
SELECT r.segment_name, r.status, r.tablespace_name,
       r.initial_extent, r.next_extent,
       s.sid, s.serial#, s.username
FROM dba_rollback_segs r, v$transaction t, v$session s
WHERE r.segment_id = t.xidusn
AND t.addr = s.taddr(+);

事务优化

-- 1. 批量操作减少事务开销
BEGIN
  -- 批量插入
  FORALL i IN 1..emp_array.COUNT
    INSERT INTO employees VALUES emp_array(i);
  COMMIT;
END;
/

-- 2. 合理使用自治事务
CREATE OR REPLACE PROCEDURE log_error(p_error_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (error_time, error_message)
  VALUES (SYSDATE, p_error_msg);
  COMMIT;
END log_error;
/

-- 3. 避免长事务
-- 将大事务分解为多个小事务
DECLARE
  CURSOR emp_cursor IS SELECT employee_id FROM employees;
  TYPE emp_id_array IS TABLE OF NUMBER;
  v_emp_ids emp_id_array;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor BULK COLLECT INTO v_emp_ids LIMIT 1000;
    EXIT WHEN v_emp_ids.COUNT = 0;
    
    -- 处理一批数据
    FORALL i IN 1..v_emp_ids.COUNT
      UPDATE employees SET last_updated = SYSDATE 
      WHERE employee_id = v_emp_ids(i);
    
    COMMIT;  -- 每批数据提交一次
  END LOOP;
  CLOSE emp_cursor;
END;
/

5.6 本章小结

本章详细介绍了Oracle数据库中的事务和锁机制，包括事务的基本概念、ACID特性、锁的类型和管理、死锁处理、并发控制以及事务监控和优化等内容。理解和掌握这些知识对于开发高质量的数据库应用程序至关重要。

练习题

1. 编写一个银行转账的事务程序，确保转账操作的原子性
2. 模拟死锁场景，并演示Oracle如何自动检测和处理死锁
3. 创建一个长时间运行的事务监控脚本，定期报告运行时间超过阈值的事务
4. 实现一个批量更新程序，使用合理的事务控制策略
5. 配置和使用闪回归档功能，查询表的历史数据版本
6. 编写一个自治事务过程，用于记录应用程序日志