事务处理(学习笔记)

事务:

一种机制，一个操作序列，是数据库工作的逻辑单元

一个或者多个完成一组相关行为的SQL语句组成

一个不可分割的工作逻辑单元

事务的概念:

一个不可分割的工作逻辑单元，

保证数据库的完整性

事务的特性：简称ACID属性

原子性:(Atomicity): 事务是一个完整的操作，事务的各步操作是不可分的（原子的），要么都执行，要么都不执行

 

一致性(Consistency):在事务操作前后，数据必须处于一致状态

 

隔离性(Isolation): 对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

 

持久性(Durability):事务完成后，它对数据库的悠被永久保持

创建一张表:

CREATE TABLE acc(
    ID  NUMBER(19),
    NAME VARCHAR2(20)    NOT NULL,
    bal   NUMBER(19,3)    NOT NULL,
    CONSTRAINT pd_id PRIMARY KEY(ID),
    CONSTRAINT ck_bal   CHECK(bal>=0)
);
SELECT * FROM acc;
INSERT INTO acc VALUES(1001,'张三',3000);
INSERT INTO acc VALUES(1002,'张三',1);

 

 

1）原子性(Atomicity)

事务是一个完整的操作，事务的各步操作是不可分的（原子的），要么都执行，要么都不执行

模拟银行转账

BEGIN
  UPDATE acc SET bal=bal-4000 WHERE ID=1001;            --转出4000，但余额只有3000;
    UPDATE acc SET bal=bal-4000 WHERE ID=1001;          --转入4000
    COMMIT;                                              --提交事务
    EXCEPTION                                           --1001账户转出时会有异常，
      WHEN OTHERS THEN                                  --捕获导常
        dbms_output.put_line('账户异常，转账失败!');      --提示信息
        ROLLBACK;                                       --将数据回滚到转账前
END;
--查询发现数据已经回到转账前
SELECT * FROM acc;

2)一致性(Consistency):

在事务操作前后，数据必须处于一致状态

模拟银行转账

DECLARE
  v_a                  acc.bal%TYPE;            --余额类型
  v_b                  acc.bal%TYPE; 
  v_sal                acc.bal%TYPE;            --计算总金额
BEGIN
  SELECT bal INTO v_a FROM acc WHERE ID=1001;   --将账户A的余额查出
  SELECT bal INTO v_b FROM acc WHERE ID=1002;   --将账户B的余额查出
  dbms_output.put_line('转账前A的余额: '||v_a);
  dbms_output.put_line('转账前B的余额: '||v_b);
  dbms_output.put_line('转账前A和B的总余额: '||(v_a+v_b));
  
  --开始转账
  UPDATE acc SET bal=bal-2000 WHERE ID=1001;
  UPDATE acc SET bal=bal+2000 WHERE ID=1002;
  COMMIT;                                                      --提交
   SELECT bal INTO v_a FROM acc WHERE ID=1001;        --将账户A的余额查出
  SELECT bal INTO v_b FROM acc WHERE ID=1002;         --将账户B的余额查出
  dbms_output.put_line('转账后A的余额: '||v_a);
  dbms_output.put_line('转账后B的余额: '||v_b);
  dbms_output.put_line('转账后A和B的总余额: '||(v_a+v_b));
  
  EXCEPTION 
    WHEN OTHERS THEN
    dbms_output.put_line('账户异常，转账失败');
     --回退到转账前
    ROLLBACK;         
                                      
            --                         失败时账户余额  
  SELECT bal INTO v_a FROM acc WHERE ID=1001;        --将账户A的余额查出
  SELECT bal INTO v_b FROM acc WHERE ID=1002;         --将账户B的余额查出
  dbms_output.put_line('失败时A的余额: '||v_a);
  dbms_output.put_line('失败时B的余额: '||v_b);
  dbms_output.put_line('失败时A和B的总余额: '||(v_a+v_b));
END;

3)隔离性(Isolation):

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

模拟银行转账

--首先转账但不提交
DECLARE
BEGIN
--开始转账
  UPDATE acc SET bal=bal-2000 WHERE ID=1001;
  UPDATE acc SET bal=bal+2000 WHERE ID=1002;
END;

--此时第二个人开始为1001账户存款
UPDATE acc SET bal=bal+1000 WHERE ID=1001;
--发现一直处于等待状态，
--因为首先转账没有完成，不对同时对一个事务进行处理
--多个用户同时处理同一事务时，要么得到的是处理前的，要么得到处理后的状态
--这里返回转账并提交
DECLARE
BEGIN
--开始转账
  UPDATE acc SET bal=bal-2000 WHERE ID=1001;
  UPDATE acc SET bal=bal+2000 WHERE ID=1002;
  COMMIT;
END;
--再返回第二个人存钱窗口发现已经完成，可以提交
UPDATE acc SET bal=bal+1000 WHERE ID=1001;
COMMIT;

读取事务异常：

　　1、脏读：一个事务读取了另一个事务未提交的数据。

　　2、不可重复读：一个事务再次读取之前曾经读取过的数据时，发现该数据已经被另一个已提交的事务修改。

　　3、幻读：一个事务根据相同的查询条件，重新执行查询，返回记录中包含了与前一次执行查询返回的记录不同的行。

事务的隔离级别：

ANSI SQL-92标准中定义的事务级别：

　　1、Read Uncommitted：最低等级的事务隔离，它仅仅保证了读取过程中不会取到非法数据。

　　2、Read Comitted：此级别的事务隔离保证了一个事务不会读到另一个并行事务已修改但未提交的数据，此级别的事务级别避免了”脏读“。

　　3、Repeatable Read：此级别的事务隔离避免了”脏读“和”不可重复读“异常现象的出现。一个事务不可能更新已经由另一个事务读取但未提交（回滚）的数据。

     4、Serializable:最高等级的隔离级别，提供了最高等级的隔离机制，三种异常情况都能避免。该事务以串行执行的方式执行。

 

隔离等级	脏读	不可重复读	幻读
Read Uncommitted	可能	可能	可能
Read Comitted	不可能	可能	可能
Repeatable Read	不可能	不可能	可能
Serializable	不可能	不可能	不可能

 

 

 

Oracle中提供的隔离级别：

　　1、Read Comitted：Oracle默认的隔离级别，此级别的事务保证了一个事务不会读到另一个并行事务已修改但未提交的数据，也就是说，此等级的事务避免了"脏读"。

　　2、Serializable：最高等级的隔离级别，提供了最高等级的隔离机制，三种异常情况都能避免。

      3、Read Only：Read Only是Serializable的子集，指事务中不能有任何修改数据库中数据的语句（DML），以及修改数据结构的语句（DDL）。只允许读不允许改。

事务:

在Oracle中不需要专门的语句来开始事务，隐含的事务会在修改数据的第一条语句处开始

结束事务:

1. commit语句显式终止一个事务
2. ROLLBACK语句回滚事务
3. 执行一条DDL语句，如果DDL语句前面已经有了DML语句，则ORACLE会把前面的DML语句作为一个事务提交
4. 用户断开与ORACLE连接。用户当前的事务将被自动提交
5. 用户 进程意外终止，这里用户当前的事务被回滚

事务控制语句：

　　1、COMMIT:提交事务，对数据库的修改进行保存。

　　2、ROLLBACK:回滚事务，取消对数据库所做的修改。

　　3、SAVEPOINT:在事务中创建存储点。

　　4、ROLLBACK TO <SAVEPOINT>:将事务回滚到存储点。

　　5、SET TRANSACTION:设置事务的属性

事务控制语句:

SET AUTOCOMMIT=OFF	取消自动提交
SET AUTOCOMMIT=ON	打开自动提交，
COMMIT	提交事务
ROLLBACK	回滚事务，取消对数据库所做的修改。
SAVEPOINT 事务保存点的名称	设置事务保存点
ROLLBACK() TO [回滚点]	回滚操作
SET TRANSACTION	设置事务的属性

COMMIT 和ROLLBACEK可以写为:COMMIT WORK, ROLLBACK WORK

SAVEPOINT 在事务中创建存储点

语法： SAVEPOINT[SAVEPOINT_NAME]名字可以不写

ROLLBACK TO< SAVEPOINT_NAME > 将事务回滚到指定的存储点

事务控制语句：

SET TRANSACTION:

• SET TRANSACTION语句必须是事务的第一条语句
•  指定事务的隔离级别
•  规定事务回滚事务时使用的存储空间

设置事务级别:

1.  SET TRANSACTION READONLY;
2.  SET TRANSACTION ISOLATION_LEVEL READ COMMITTED;
3.  SET TRANSACTION ISOLATION_LEVEL SERIALIZABLE;

设置存储点

--模拟银行转账：
SELECT * FROM acc;
DECLARE
BEGIN
  UPDATE ACC SET BAL = BAL + 2000 WHERE ID = 1001; --先存入2000但不提交
  SAVEPOINT ACC_ADD; --设置存储点
  UPDATE ACC SET BAL = BAL - 6000 WHERE ID = 1001; --转出6000，余额不足会出现导演
  UPDATE ACC SET BAL= BAL + 6000 WHERE ID = 1002; --另一账户转入6000
  COMMIT; --提交
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('数据异常，转账失败!');
    ROLLBACK TO ACC_ADD; --回滚到存完2000时设置的存储点
END;

--银行转账在过程中

 

SELECT * FROM acc;
--建立过程
create or replace procedure proc_tran(fromacc in number,toacc in number,sal in number) is
begin
  dbms_output.put_line('转账开始');
  UPDATE acc SET bal=bal-sal WHERE id=fromacc;
  UPDATE acc SET bal=bal+sal WHERE id=toacc;
  dbms_output.put_line('转账成功');
  COMMIT;
  EXCEPTION 
    WHEN OTHERS THEN
      dbms_output.put_line('账户异常，转账失败');
      ROLLBACK;
end proc_tran;
--调用过程
DECLARE
BEGIN
  proc_tran(1001,1002,3000);
END;