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

偏执的太偏执、 2022-04-23 02:32 138阅读 0赞

## 一、事务处理概述 ##

事务（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][https_blog.csdn.net_shangboerds_article_details_43282115]

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

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

### 死锁的原因 ###

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

### 死锁四个必要条件： ###

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

### 可能造成死锁的资源 ###

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

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

### 减少死锁的策略 ###