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

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

一、事务处理概述

  1.     事务（Transaction）是Oracle中的基本工作单元，是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。
  3.     属性： 一个工作单元必有四个属性ACID
  • 原子性：(Atomic) 事务必须是原子工作单元；对于数据修改，要么全部执行，要么全都不执行。
  • 一致性：(Consistent) 事务完成时，必须所有的数据都保持一致状态，结束时，结构都必须正确。
  • 隔离性：(Isolated) 由并发事务做的修改必须与其他并发事务所做的修改隔离。不存在中间状态。
  • 持久性：(Durable) 事务完成后，对系统影响永久，即使系统故障，修改也会保持。

二、开启与提交事务

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

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

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

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

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

Commit提交事务

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

三、保存点的运用

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

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

四、锁概述

  1.     Oracle 支持多用户共享同一数据库，但是当多个用户对同一个库进行修改时，会产生并发问题。使用锁就可以解决多用户存取数据的问题。从而保持数据库的完整性和一致性。
  3.     锁，是在共享资源中访问控制的一种机制。若不使用锁，就会引发以下若干问题:

【脏读】

  1.     假设 事务A读取的记录是事务B的一部分时，若A正常完成，没毛病；若A出现问题，而B未完成，就变为脏读。
  3.     例如，某员工工资为5900，事务A将其改为8900，但未提交确认；此时事务B读取员工工资为8900；事务A由于某些原因，执行RollBack回滚，取消了对工资的更改，但事务B已经将8900读走了。
  5.     解决：增加行级锁，事务A修改时封锁该行，事务B只能等待。

【幻读】

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

【非重复性读取】

  1.     若事务A不止一次地读取相同的记录，但在两次读取之间有事务B刚好改了数据，则两次读取会出现差异，此时出现非重复性读取。
  3.     假设事务S和事务K都读取一条工资为8000的数据行，若S将工资修改为3000并提交，而事务K使用的还是8000.
  5.     解决方法：添加共享锁，在查询事务未结束前，不允许修改当前数据。

【丢失更新】

  1.     一个事务更新数据库后，另一个事务再次对数据库更新，此时系统只能保留最后一个数据的更改。
  3.     假设对一个员工表tb\_employee的修改, 事务A将工资改为8000，而之后事务B又将工资改为3000，导致事务A的修改丢失。
  5.     使用锁可以避免上述情况导致的数据不一致问题

五、锁的分类

按模式分类：

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

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

按操作对象分类：

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

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

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

六、手动加锁

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

七、SELECT FOR UPDATE

  1.     如果我们查询某些记录是为了更新它，那么我们可以给 SELECT 语句加上 FOR UPDATE 子句，这样 Oracle 会返回结果的同时锁定它，从而防止别人更新这些记录, 下面是一个简单的例子。
  3. DECLARE
  4.  CURSOR upd_sly IS
  5. SELECT name, salary
  6.   FROM tb_employee 
  7.    FOR UPDATE OF salary;
  8. BEGIN
  9.   NULL;
  10. END;
  11. /

八、自主事务

  1.     通常，如果子程序 A 调用 B，那么 A 和 B 将在同一个事务中，A 或 B 中的任意一个 COMMIT 语句将会使 A 和 B 所做的所有更改全部提交。
  3.     有时候，这不是我们想要的结果，我们想让 B 在自己的事务中，无论 A 成功或失败都不影响 B，怎么办？很简单，我们只需在 B 的声明部分加上 PRAGMA AUTONOMOUS\_TRANSACTION 语句即可  
  7. -- 存储过程 B    
  8. CREATE OR REPLACE PROCEDURE PRO_INSERT_LOG (      
  9.   MESSAGE VARCHAR2    
  10. )      
  11. IS    
  12. -- 表示该存储过程是一个自主事务存储过程    
  13. PRAGMA AUTONOMOUS_TRANSACTION;    
  14. BEGIN    
  15.   INSERT INTO tb_log VALUES (MESSAGE);    
  16.   COMMIT;    
  17. END;   
  19. -- 存储过程 A  
  20. CREATE OR REPLACE PROCEDURE PRO_INSERT_EMPLOYEE (    
  21.   FIRST_NAME         VARCHAR2,    
  22.   LAST_NAME          VARCHAR2,     
  23.   SALARY             NUMBER  
  24. )    
  25. IS    
  26. BEGIN  
  27.   -- 开启事务  
  28.   COMMIT;  
  29.   SET TRANSACTION NAME 'tran';  
  31.   -- 设置回滚点  
  32.   SAVEPOINT sp1;  
  34.     -- 调用存储过程  
  35.     PRO_INSERT_LOG('Inserting EMPLOYEES');  
  37.     INSERT INTO tb_employees VALUES (NULL, FIRST_NAME, LAST_NAME, SALARY, CURRENT_TIMESTAMP);  
  39.     EXCEPTION    
  40.     WHEN OTHERS THEN    
  41.       DBMS_OUTPUT.PUT_LINE('ERROR CODE:' || SQLCODE || ', ERROR MESSAGE: ' || SQLERRM);    
  42.       ROLLBACK TO sp1; -- 回滚  
  43. END;  
  45. -- 测试  
  46. DECLARE  
  47.   FIRST_NAME    VARCHAR2(20) := 'Scb';  
  48.   LAST_NAME    VARCHAR2(20) := 'Leonardo';   
  49.   SLY          NUMBER(7,2) := 8888.88;
  50. BEGIN    
  51.   PRO_INSERT_EMPLOYEE(FIRST_NAME, LAST_NAME, SLY);      
  52. END;

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

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

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

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

死锁的原因

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

死锁四个必要条件：

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

可能造成死锁的资源

  1.     以下类型的资源可能造成阻塞，并最终导致死锁：
  • 锁：
  • 工作线程：
  • 内存等待释放：
  • 并行查询执行的相关资源：

减少死锁的策略

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

发表评论

表情：
评论列表 （有 0 条评论，211人围观）

还没有评论，来说两句吧...

相关阅读