Oracle笔记（十一）：事务与锁 (Transaction Processing & Lock)

 

一、事务处理概述

        事务（Transaction）是Oracle中的基本工作单元，是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。

        属性： 一个工作单元必有四个属性ACID

• 原子性：(Atomic) 事务必须是原子工作单元；对于数据修改，要么全部执行，要么全都不执行。
• 一致性：(Consistent) 事务完成时，必须所有的数据都保持一致状态，结束时，结构都必须正确。
• 隔离性：(Isolated) 由并发事务做的修改必须与其他并发事务所做的修改隔离。不存在中间状态。
• 持久性：(Durable) 事务完成后，对系统影响永久，即使系统故障，修改也会保持。

       

二、开启与提交事务

        当执行一组 SQL 语句的时候，Oracle 会自动帮我们开启一个事务。

        通过 SET TRANSACTION 语句手动开启一个事务

BEGIN
 -- 手动开启事务 tran
SET TRANSACTION NAME 'tran';
--SET TRANSACTION READ WRITE NAME 'tran'; -- 同上语句，表明它是一个读写事务
INSERT INTO tb_test VALUES ('Scott');
COMMIT; -- 提交
 
END;
/

        SET TRANSACTION 还有许多其他参数，可以对事务进行更精准的控制。

-- 我们可以设置事务为只读事务，这在生成报告，账单等时特别有用
SET TRANSACTION READ ONLY NAME 'tran';
SELECT * FROM TEST;
COMMIT; -- 提交事务，只读事务也需要提交的哦
 
 
-- 我们还可以指定事务的隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE NAME 'tran';
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED NAME 'tran'; -- 事务默认的隔离级别是 READ COMMITTED
SELECT * FROM TEST;
COMMIT; -- 提交事务
 
-- 我们还可以指定当事务失败时，将事务回滚到指定的回滚段
-- Oracle 不推荐我们这样做，尽量使用自动回滚
SET TRANSACTION USE ROLLBACK SEGMENT test NAME 'tran';
INSERT INTO TEST VALUES ('Scott');
ROLLBACK; -- 回滚事务

 

         Commit提交事务

INSERT INTO TEST VALUES ('Scott');
COMMIT; -- 提交事务
COMMIT COMMENT 'test'; -- COMMENT 语句为事务添加注释，但Oracle不推荐我们使用它，它的存在是为了向后兼容。
COMMIT WORK; -- COMMIT 和 COMMIT WORK 完全等价
COMMIT WRITE WAIT IMMEDIATE; -- 这条语句和上面的语句完全相同
COMMIT WRITE WAIT BATCH;
COMMIT WRITE NOWAIT BATCH;
COMMIT WRITE NOWAIT IMMEDIATE;

 

三、保存点的运用

回滚点
Oracle 使用 SAVEPOINT 来设置回滚点。

INSERT INTO TEST VALUES ('Scott');
SAVEPOINT sp1;
INSERT INTO TEST VALUES ('Tom');
ROLLBACK TO SAVEPOINT sp1; -- 回滚到sp1，'Tom' 记录会被回滚
COMMIT;

 

四、锁概述

        Oracle 支持多用户共享同一数据库，但是当多个用户对同一个库进行修改时，会产生并发问题。使用锁就可以解决多用户存取数据的问题。从而保持数据库的完整性和一致性。

        锁，是在共享资源中访问控制的一种机制。若不使用锁，就会引发以下若干问题: 

【脏读】

        假设 事务A读取的记录是事务B的一部分时，若A正常完成，没毛病；若A出现问题，而B未完成，就变为脏读。

        例如，某员工工资为5900，事务A将其改为8900，但未提交确认；此时事务B读取员工工资为8900；事务A由于某些原因，执行RollBack回滚，取消了对工资的更改，但事务B已经将8900读走了。

        解决：增加行级锁，事务A修改时封锁该行，事务B只能等待。

【幻读】

        当某数据行执行INSERT / DELETE 操作，而该数据行恰好属于某事务读取的范围时，就发生幻读现象。

        假设，现在对员工表涨工资，将所有低于2000的月薪涨到3000，事务A使用UPDATE，事务B同时读取这批数据，但在其中插入了几条低于2000的数据，此时，事务A去查看表，会发现自己UPDATE 后还是有小于2000的记录！幻读事件就是在某个凑巧的条件下发生的。简而言之，就是在UPDATE的期间使用了INSERT ，没有被锁定，所以插入了新记录行，且能正常运行。

【非重复性读取】

        若事务A不止一次地读取相同的记录，但在两次读取之间有事务B刚好改了数据，则两次读取会出现差异，此时出现非重复性读取。

        假设事务S和事务K都读取一条工资为8000的数据行，若S将工资修改为3000并提交，而事务K使用的还是8000.

        解决方法：添加共享锁，在查询事务未结束前，不允许修改当前数据。

 

【丢失更新】

        一个事务更新数据库后，另一个事务再次对数据库更新，此时系统只能保留最后一个数据的更改。

        假设对一个员工表tb_employee的修改, 事务A将工资改为8000，而之后事务B又将工资改为3000，导致事务A的修改丢失。

        使用锁可以避免上述情况导致的数据不一致问题

 

五、锁的分类

按模式分类：

【排他锁 (X锁)】用于数据修改，（如 INSERT, UPDATE, DELETE）确保同一资源不被多重更新

【共享锁 (S锁)】用于读取数据操作，（如 Select）允许多事务查询，但不允许其他事务修改当前数据。

 

按操作对象分类：

【DDL锁】保护模式中对象的结构。

【DML锁】亦称为：数据锁，主要为了保护数据。

【内部闩锁】完全自动调用，主要保护自身数据库的内部结构

 

 

六、手动加锁

        通常，Oracle 会根据需要自动锁定表或行，如果我们要对一个表的大部分记录进行操作，锁定每一行会严重消耗系统资源，这个时候我们可以通过 LOCK TABLE 语句手动锁定整个表。例如下面的语句表示使用 EXCLUSIVE 模式锁定 TEST 表，如果其他事务已经锁定了该表，立刻返回。
 

-- EXCLUSIVE 表示使用独占模式锁定表，其他事务只允许读表。
-- NOWAIT 表示如果其他事务已经锁定了该表，立刻返回。
LOCK TABLE TEST IN EXCLUSIVE MODE NOWAIT;

 

七、SELECT FOR UPDATE

        如果我们查询某些记录是为了更新它，那么我们可以给 SELECT 语句加上 FOR UPDATE 子句，这样 Oracle 会返回结果的同时锁定它，从而防止别人更新这些记录, 下面是一个简单的例子。

DECLARE
 CURSOR upd_sly IS
SELECT name, salary
  FROM tb_employee 
   FOR UPDATE OF salary;
BEGIN
  NULL;
END;
/

八、自主事务

        通常，如果子程序 A 调用 B，那么 A 和 B 将在同一个事务中，A 或 B 中的任意一个 COMMIT 语句将会使 A 和 B 所做的所有更改全部提交。

        有时候，这不是我们想要的结果，我们想让 B 在自己的事务中，无论 A 成功或失败都不影响 B，怎么办？很简单，我们只需在 B 的声明部分加上 PRAGMA AUTONOMOUS_TRANSACTION 语句即可

 

-- 存储过程 B    
CREATE OR REPLACE PROCEDURE PRO_INSERT_LOG (      
  MESSAGE VARCHAR2    
)      
IS    
-- 表示该存储过程是一个自主事务存储过程    
PRAGMA AUTONOMOUS_TRANSACTION;    
BEGIN    
  INSERT INTO tb_log VALUES (MESSAGE);    
  COMMIT;    
END;   
  
-- 存储过程 A  
CREATE OR REPLACE PROCEDURE PRO_INSERT_EMPLOYEE (    
  FIRST_NAME         VARCHAR2,    
  LAST_NAME          VARCHAR2,     
  SALARY             NUMBER  
)    
IS    
BEGIN  
  -- 开启事务  
  COMMIT;  
  SET TRANSACTION NAME 'tran';  
    
  -- 设置回滚点  
  SAVEPOINT sp1;  
    
    -- 调用存储过程  
    PRO_INSERT_LOG('Inserting EMPLOYEES');  
  
    INSERT INTO tb_employees VALUES (NULL, FIRST_NAME, LAST_NAME, SALARY, CURRENT_TIMESTAMP);  
      
    EXCEPTION    
    WHEN OTHERS THEN    
      DBMS_OUTPUT.PUT_LINE('ERROR CODE:' || SQLCODE || ', ERROR MESSAGE: ' || SQLERRM);    
      ROLLBACK TO sp1; -- 回滚  
END;  
  
-- 测试  
DECLARE  
  FIRST_NAME    VARCHAR2(20) := 'Scb';  
  LAST_NAME    VARCHAR2(20) := 'Leonardo';   
  SLY          NUMBER(7,2) := 8888.88;
BEGIN    
  PRO_INSERT_EMPLOYEE(FIRST_NAME, LAST_NAME, SLY);      
END;

不管事务A执行成功/ 失败，都不会影响B事务。

部分代码来源于https://blog.csdn.net/shangboerds/article/details/43282115

 

十、锁等待与死锁(补充知识)  

        由排他锁的机制可知，当一个会话正在修改某记录，会对该记录进行加锁，若此时另一个会话也进来修改记录，会因为等不到排他锁释放而一直等待。数据库长时间无响应，直到第一个会话将事务提交，释放排他锁后，第二个事务才能对数据进行操作。

 

死锁的原因

        在俩个或以上的任务中，若每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。简而言之，两个事务，都锁定不同资源，同时申请对方锁定的资源，但两不相让，都不放资源，永久等待。

 

死锁四个必要条件：

• 请求与保持：获取资源的进程，同时申请新资源；
• 非剥夺条件：已经分配的资源不能从进程中被剥夺；
• 循环与等待：多进程形成环路，且每个进程都在等待相邻进程正在占用的资源；
• 互斥条件：资源只能被一个进程使用。

 

可能造成死锁的资源

        以下类型的资源可能造成阻塞，并最终导致死锁：

• 锁：
• 工作线程：
• 内存等待释放：
• 并行查询执行的相关资源：

 

减少死锁的策略

• 在所有事务中以相同次序使用资源；
• 让事务尽可能简短且在一个批处理中；
• 为死锁超时参数设置合理范围，如5分钟，超时则自动放弃本次操作，避免进程挂起；
• 避免事务内和用户交互，减少资源锁定时间。