Oracle 事务探索与实例(一)
更多精彩内容尽在www.leonarding.com《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
会话二
$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 whererownumupdate leo8 set cost=20000where cost=10000;
1 row updated.
LEO1@LEO1>commit;
Commit complete.
当我们交钱的时候发现tiger升值了，哎呦我去~ 这不打劫嘛！
LEO1@LEO1>select * from leo8 whererownumdrop 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 whererownumupdate leo8 set cost=20000where cost=10000; 已经更新
1 row updated.
LEO1@LEO1>commit;
Commit complete.
到了较赎金的时候，我们又查询了一次
LEO1@LEO1>select * from leo8 whererownumcreate 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事务隔离等级作用域是一个事务，超出这个事务就无效了。
事务属性 Phantom Read Non-repeatableRead Read committed Serializable
Leonarding
2013.5.24
北京&spring
分享技术~成就梦想
Blog：www.leonarding.com
页:
[1]