oracle 事务 第二弹

一 数据库版本

SYS@LEO1>select* from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

PL/SQLRelease 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS forLinux: Version 11.2.0.1.0 - Production

NLSRTLVersion 11.2.0.1.0 - Production

 

二 事务那些事儿

1.什么是事务：事务是一组操作序列，这些操作要么都完成，要么都不完成，它是一个不可分割的操作单元。关系型数据库最核心的价值体现。说白了为了完成一件事而做的n个步骤，这n个步骤是有前后顺序的，必须按照A->B->C->D的逻辑顺序来执行，事务是数据库维护数据一致性的单位，例如多个会话同时读取同一数据的问题。

关系型数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

场景：银行转账 结算购物 订票系统

非关系型数据库中，一个事务可以是一个操作，但操作之间没有关系，相互独立的。

比如：推荐系统  日志分析  网站搜索

 

三 用例子说明事务的四个属性和自动提交功能

事务的属性ACID

原子性（Atomicity）：一个事务是一个不可分割的整体，事务中的操作要么都完成，要么都失败。

实例

SYS@LEO1>conn leo1/leo1

Connected.

LEO1@LEO1>drop table leo1 purge;                  清理环境

Table dropped.

LEO1@LEO1>create table leo1 (x number,ynumber);     创建表

Table created.

LEO1@LEO1>insert into leo1 values(1,1);               执行DML操作

1 row created.

LEO1@LEO1>update leo1 set x=2 where x=1;

1 row updated.

LEO1@LEO1>delete from leo1 where y=1;

1 row deleted.

LEO1@LEO1>commit;

Commit complete.

上面我们执行了三条sql语句，都属于一个事务，当commit的时候表示事务完成，这三条sql全部生效，如果rollback这三条sql全部失败，不存在有的完成有的失败，因为它们是一个不可再分割的整体。

一致性（Consistency）：指数据库的一种状态的一致性，具体来说不可违反约束，不可违反规则，所谓的一致性就是一种人为规则，例如定义一个主键，插入2条一样的数据就违反了一致性条件。

实例

LEO1@LEO1>drop table leo2 purge;                                            清理环境

Table dropped.

LEO1@LEO1>create table leo2 (x number,ynumber,constraint pk_leo2 primary key(x));  x列有主键

Table created.

LEO1@LEO1>insert into leo2 values(1,1);

1 row created.

LEO1@LEO1>insert into leo2 values(1,2);                     当插入的值一样时违反了一致性

insert into leo2 values(1,2)

*

ERROR at line 1:

ORA-00001: unique constraint (LEO1.PK_LEO2)violated

隔离性（isolation）：未提交的事务其他会话不可见。

实例

会话一

LEO1@LEO1>select * from leo2;               表里只有1条记录

        X          Y

---------- ------------------- ----------

        1          1

LEO1@LEO1>insert into leo2 values(2,2);        新插入一条

1 row created.

LEO1@LEO1>select * from leo2;               此时表里有2条记录，但未有提交

        X          Y

---------- ------------------- ----------

        1          1

        2          2

会话二

[oracle@leonarding1 flashback_area]$sqlplus leo1/leo1    连接新会话

LEO1@LEO1>select * from leo2;      只能看到已提交事务的数据，未提交的事务看不到

        X          Y

---------- ----------

        1          1

持久性（Durability）：事务一旦提交就不可更改，永久有效。

实例

LEO1@LEO1>drop table leo3 purge;

Table dropped.

LEO1@LEO1>create table leo3 (x number,ynumber);

Table created.

LEO1@LEO1>insert into leo3 values(1,1);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from leo3;

        X          Y

---------- ----------

        1          1

LEO1@LEO1>rollback;

Rollback complete.

LEO1@LEO1>select * from leo3;

        X          Y

---------- ----------

        1          1

事务一旦commit，你再rollback也是无效的，已经插进去了。

事务是以第一个DML语句作为开始

以下面其中之一作为结束

(1)    commit or rollback

(2)    DDL or DCL

(3) 用户session正常结束  退出sqlplus

(4) 系统正常结束or终止  

事务自动提交，我们可以设置oracle自动提交事务

实例

LEO1@LEO1>show autocommit;                  默认是不启动自动提交的

autocommit OFF

LEO1@LEO1>set autocommit on;                 手工启动

LEO1@LEO1>show autocommit;

autocommit IMMEDIATE

LEO1@LEO1>insert into leo3 values(2,2);           当插入数据的同时就提交

1 row created.

Commit complete.

LEO1@LEO1>select * from leo3;

        X          Y

---------- ----------

         1         1

        2          2

LEO1@LEO1>rollback;                           此时回滚已无效

Rollback complete.

LEO1@LEO1>select * from leo3;

        X          Y

---------- ----------

        1          1

        2          2

LEO1@LEO1>set autocommit off;                  我们不建议开启事务自动提交功能

 

四 在Oracle下演示Nonrepeatable Read 和PhantomRead的例子，给出SQL演示过程。

Non-repeatable Read不可重复读：在一个事务中，同样的数据被2次读取，得到不同的结果集

实例

Leo帮tiger一起买2张飞机表去肯尼亚看大象

LEO1@LEO1>drop table leo6 purge;

Table dropped.

LEO1@LEO1>create table leo6 (namevarchar2(10),ticket_type varchar2(20),price number);

Table created.

LEO1@LEO1>insert into leo6values('leo','plane_ticket',100);

1 row created.

LEO1@LEO1>insert into leo6values('tiger','plane_ticket',100);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from leo6;

NAME      TICKET_TYPE               PRICE

---------- -------------------- ----------

leo         plane_ticket                100

tiger        plane_ticket                100

飞机票的价格都是100$，2个人一共200$

Leo查询的价格

LEO1@LEO1>select sum(price) both_pricefrom leo6;

BOTH_PRICE

------------------

      200

在过行李安检的时候，发现tiger行李超重，原来tiger非常有爱心，给大象带了花生+毛豆，根据规定超重行李是要补交托运费的。

机场又加了100元托运费

LEO1@LEO1>update leo6 set price=200where name='tiger';

1 row updated.

LEO1@LEO1>select * from leo6;

NAME      TICKET_TYPE               PRICE

---------- -------------------- ----------

leo        plane_ticket                100

tiger       plane_ticket                200

LEO1@LEO1>commit;

Commit complete.

Leo再次查询价格时，总价变成300了

LEO1@LEO1>select sum(price) both_pricefrom leo6;

BOTH_PRICE

------------------

      300

不可重复读在数据库中是可能出现的，所以要注意一下，上面leo和机场是两个独立会话

Phantom Read幻读：在一个事务中，同样的sql被2次执行，得到不同的结果集。

实例

翻山越岭 跋山涉水 终于来到了广阔的非洲大草原真是大象遍地走 春风吹又生，tiger肴有兴致的数起了大象

Tiger 查询的结果

LEO1@LEO1>drop table leo7 purge;

Table dropped.

LEO1@LEO1>create table leo7 (namevarchar2(10),num number);

Table created.

LEO1@LEO1>insert into leo7values('a',1);

1 row created.

LEO1@LEO1>insert into leo7values('b',1);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select count(*) from leo7;                    第一天发现了2只

COUNT(*)

-----------------

        2

傍晚有一只小象跑了过来

LEO1@LEO1>insert into leo7values('c',1);

1 row created.

LEO1@LEO1>commit;

Commit complete.

第二天tiger又查询了一遍，这次发现了3只

LEO1@LEO1>select count(*) from leo7;

COUNT(*)

-----------------

       3

小结：上述两个生动的例子展现了Non-repeatableRead不可重复读和Phantom Read幻读的过程，大家一定要好好体会里面的含义，理解这两种不同的数据读取方式。

 

五 在Oracle设计一个场景，会导致Non-repeatableRead，然后选择一种事务隔离等级来避免它的发生，给出SQL演示过程。

场景：tiger在游玩非洲大草原之后，选择从海路回家，在走到索马里海岸的时候，有幸被海盗光顾，请他上岸吃海鲜，这次海盗拿出了不劳而获的敬业精神，赎金10000$，三天之内交换人质，一手交钱，一手交货。由于历来海盗的诚信记录并不是很光彩照人，我们模拟了一下可能的突发事件。

首先海盗提出条件，赎金10000$，还为此特意做了一个排行榜

LEO1@LEO1>create table leo8 (namevarchar2(20),cost int);          排行榜清单

Table created.

LEO1@LEO1>insert into leo8values('tiger',10000);                  插入tiger信息

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from leo8 whererownum<=1;                 我们看到tiger排在第一位

NAME                    COST

---------------------------------------------------------------

tiger                     10000

上面的信息是我们自己查询的情况，突然海盗得知tiger是中国知名的IT专家，国家的财富和人才。本着生意人的精明头脑，高价值的人当然换取的条件也水涨船高，海盗偷偷修改了赎金数，变成20000$

海盗

LEO1@LEO1>update leo8 set cost=20000where cost=10000;

1 row updated.

LEO1@LEO1>commit;

Commit complete.

当我们交钱的时候发现tiger升值了，哎呦我去~ 这不打劫嘛！

LEO1@LEO1>select * from leo8 whererownum<=1;         

NAME                    COST

---------------------------------------------------------------

tiger                     20000

为了防止这种事情发生，我们可以使用Oracle的Read-only事务隔离等级和Serializable事务隔离等级来避免这种事情发生，这两种事务隔离等级非常相似，因此我们选择较常用的Serializable来给大家演示。

Read-only事务隔离等级：只能看到事务开始时所有提交的改变，自身不允许DML操作。

Serializable事务隔离等级：只能看到事务开始时所有提交的改变和自身的改变。

当海盗告知我们赎金是10000$的时候，我们先开出了清单

LEO1@LEO1>drop table leo8 purge;

Table dropped.

LEO1@LEO1>create table leo8 (namevarchar2(20),cost int);     tiger清单

Table created.

LEO1@LEO1>insert into leo8values('tiger',10000);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>set transaction isolationlevel serializable;         我们设置好了隔离等级

Transaction set.

LEO1@LEO1>select * from leo8 whererownum<=1;             没错是10000$

NAME                    COST

---------------------------------------------------------------

tiger                     10000

海盗又故伎重演，想把价格提高到20000$，这是另一个会话

LEO1@LEO1>update leo8 set cost=20000where cost=10000;      已经更新

1 row updated.

LEO1@LEO1>commit;

Commit complete.

到了较赎金的时候，我们又查询了一次

LEO1@LEO1>select * from leo8 whererownum<=1;

NAME                    COST

---------------------------------------------------------------

tiger                     10000

good，完美封杀了海盗邪恶企图，数据没有变化，但并不是说数据没有被修改，由于启动事务隔离策略，其他事务所做的修改我们是看不到的。Serializable只对当前事务有效，不对另外事务有效。

小结：Oracle中所有事务隔离等级都是依赖undo实现的。什么时候使用Serializable隔离等级好呢，当修改操作比较短的事务，事务中存在多条sql需要数据从事务开始时就保持一致，这样的场景适合。

 

六 用示例比较Oracle的Read committed 和Serializable 事务隔离等级的区别，给出SQL演示过程。

Read committed读已提交事务隔离等级：Oracle 默认隔离等级，支持不可重复读和幻读。这两种数据读写方式在第四题中已经完全阐述了，这里不在重复。

Serializable 事务隔离等级：只能看到事务开始时所有提交的改变和自身的改变，不支持不可重复读和幻读，事务开始是什么样子，结果就是什么样子，其他用户影响不了这个事务。

会话A

LEO1@LEO1>create table leo9(x int,yint);                 创建表

Table created.

LEO1@LEO1>insert into leo9values(100,100);              插入一行

1 row created.

LEO1@LEO1>commit;                                  提交

Commit complete.

LEO1@LEO1>set transaction isolationlevel serializable;       启动Serializable 事务隔离等级

Transaction set.

LEO1@LEO1>select * from leo9;

        X          Y

---------- ----------------- ----------

       100       100

会话B，我们用另一个会话不管怎么修改leo9表，都不会影响会话A事务

LEO1@LEO1>insert into leo9values(200,200);      

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>insert into leo9values(300,300);

1 row created.

LEO1@LEO1>commit;

Commit complete.

会话A，还是一条数据

LEO1@LEO1>select * from leo9;

        X          Y

---------- ----------------- ----------

       100       100

会话A自己修改的内容在事务中是生效的

LEO1@LEO1>insert into leo9values(400,400);          我们插入一条记录

1 row created.

LEO1@LEO1>select * from leo9;                     在本事务中是可以看到的

        X          Y

---------- ----------

      400        400

      100        100

LEO1@LEO1>commit;                              一旦事务结束，Serializable 事务隔离等级就会失效

Commit complete.

会话A，又可以看到所有的记录了

LEO1@LEO1>select * from leo9;

        X          Y

---------- ----------

      400        400

      100        100

      200        200

      300        300

两种事务隔离等级的比较

Read committed                       V.S                      Serializable   Transactions

支持SQL92标准                                               支持SQL92标准

读取物化视图 维护语句级一致性                                维护事务级一致性

事务一致性 支持语句级                                        支持事务级

行级锁 支持                                                  支持

读锁定写 不支持                                              不支持

写锁定读 不支持                                              不支持

非行级锁 不支持                                              不支持

行级锁       支持                                                   支持

事务锁等待 支持                                              支持

小结：Serializable事务隔离等级作用域是一个事务，超出这个事务就无效了。