数据库事务与隔离级别示例oracle与sql server对比Word文件下载.docx

数据库事务与隔离级别示例oracle与sql server对比Word文件下载.docx

《数据库事务与隔离级别示例oracle与sql server对比Word文件下载.docx》由会员分享，可在线阅读，更多相关《数据库事务与隔离级别示例oracle与sql server对比Word文件下载.docx（20页珍藏版）》请在冰豆网上搜索。

[Fname] 

[nvarchar]（20）,

[Lname] 

[Address] 

[nvarchar]（50）,

[City] 

[State] 

[nchar]

（2） 

DEFAULT 

（'

CA'

）,

[Zip] 

[nchar]（5） 

[Phone] 

[nchar]（10）

）

insert 

into 

customer 

values（1, 

'

Gary'

Mckee'

111 

Main'

Palm 

Springs'

94312, 

7605551212）

values（2, 

Tom'

Smith'

609 

Geogia'

Fresno'

JP'

33045, 

5105551212）

values（3, 

Jams'

bond'

ST 

Geogie 

21'

Washington'

NY'

20331, 

4405551864）

ORACLE

Customer（

CustID 

int 

Fname 

nvarchar2（20）,

Lname 

Address 

nvarchar2（50）,

City 

State 

nchar

（2） 

Zip 

nchar（5） 

Phone 

nchar（10）

）;

7605551212）;

5105551212）;

4405551864）;

1。

Sqlserver与oracle单条语句处理对比

SERVER单条语句默认自动提交，即单条语句自动作为一个事务处理；

而oracle的原则是尽量延后提交，除非遇到显式提交命令或者DDL语句。

打开事务1:

运行：

select 

* 

from 

customer

可以看到表有3条记录

values（4, 

Hello'

world'

paradise 

road 

01'

heaven'

XY'

00001, 

1234564321）

转到事务2：

可以看到事务1中刚插入的custid为4的记录。

打开事务1，运行：

customer;

可以看到表有3条记录，运行：

1234564321）;

转到事务2，运行：

能看到的还是3条记录，事务1中刚插入的一条记录未自动提交，看不到。

转到事务1，运行：

commit;

现在能看到4条记录了。

2. 

丢失更新

Sqlserver完全兼容ANSI 

92标准定义的4个隔离级别。

它的默认隔离级别是提交读（read 

committed），在该级别下，可能会有丢失更新的问题。

Oracle的默认情形也一样。

故不再重复。

打开事务1运行：

set 

level 

read 

committed

begin 

tran

--看到3条记录

现在切换到事务2，此时事务1还未结束。

在事务2中运行：

--看到3条记录，和事务1中相同

现在假设事务1事务继续运行，修改数据并提交：

update 

state 

= 

TK'

where 

3

commit

回到事务2，事务2根据先前查询到的结果修改数据：

99999 

结果因为事务1已经修改了事务2的where条件数据，事务2未成功修改数据（其实准确的说应该算是幻象读引起的更新失败。

不过若满足条件的记录数多的话，事务2的update可能更新比预期的数量少的记录数，也可算“丢失”了部分本应完成的更新。

个人认为只要明白实际上发生了什么即可，不必过分追究字眼）。

丢失更新还可能有别的情形，比如事务2也是

KO'

两个事务都结束后，事务2的结果反映到数据库中，但事务1的更新丢失了，事务2也不知道自己覆盖了事务1的更新。

3．脏读演示

sqlserver的默认隔离级别是提交读（read 

committed），当手工将其改为未提交读时，事务可以读取其它事务没提交的数据；

oracle由于自身特殊实现机制，可以理解为自身基础的隔离级别就是可重复读（与ANSI标准还是有区别的，后面例子会说明）。

TN'

uncommitted

此时看到的数据是事务1已经更新但还未提交的（3号记录state值TN）。

而如果事务1发觉数据处理有误，转到事务1，进行回滚：

Rollback

此时事务2如根据刚读取的数据进一步处理，会造成错误。

它读取的数据并未更新到数据库，是“脏”的。

ANSI定义未提交读（read 

uncommitted）级别本意不是为了故意引入错误，而是提供一种可能的最大并发程度级别，即一个事务的数据更新不影响其它事务的读取。

Oracle从内核层面实现了更新数据不阻塞读。

可以说它提供未提交读级别的兼容，但没有脏读问题。

（详情参考对应PPT文档）故oracle没有手工设置read 

uncommitted级别的语句。

4．不可重复读

Sql 

server的默认级别没有脏读问题，但存在不可重复读问题。

Oracle默认级别也是提交读，不过它因为自身特殊机制，在语句一级不存在不可重复读问题。

也就是说当运行时间较长的查询时，查询结果是与查询开始时刻一致的（即使查询过程中其它事务修改了要查询的数据），而SQL 

SERVER就存在问题（sql 

server 

2005新特性提供了可选的语句一级一致性支持，叫做行版本机制，实际上可以说是照着oracle的多版本来的，大体原理差不多）。

由于语句一级的事务一致性难以演示，下面例子是事务一级，提交读隔离级别下发生的不可重复读现象：

可以得到1条记录，这个时候事务2中运行：

Customer 

事务2插入一条记录并提交。

回到事务1，事务1继续运行，此时它再次相同的查询，并借此作进一步修改，却发现读取到的数据发生了变化。

--2次读取不一致，之后的数据处理应该取消。

否则不正确

city 

garden'

读取未能获得记录。

也就是说在同一事务中两次相同的查询获得了不同的结果，产生读取不可重复现象。

尽管oracle在默认隔离级别下提供了语句级的事务读一致性，但在事务级仍然是会出现不可重复读现象。

和sql 

server一样，故不再重复。

5．幻像读

当sqlserver的隔离级别设置为可重复读（repeatable 

read），可以解决上面例子出现的问题。

其内部是通过事务期间保持读锁来实现的。

开始事务1，修改事务级别为可重复读，执行：

repeatable 

read

和上例一样得到1条记录，这个时候事务2中运行：

会发现事务2一直等待，并不结束。

返回事务1，运行：

--2次读取结果一致

事务2成功结束后，再返回事务1，发现事务1也完成了。

通过锁机制阻塞其它事务的修改，保持了事务期间读取的一致性。

然而，如果是插入数据，则还是会出现问题：

得到1条记录，这个时候事务2中运行：

hellow'

001'

00000, 

1119995555）

发现事务2立刻提交并正常结束了。

会发现得到了2条记录。

这种现象就叫做幻像读。

由于自身特殊的机制，oracle没有提供一致读隔离级别的选项，想要获得一致读的效果，实际上需要将事务提升到串行化等级，即serializable。

6.串行化级别不同数据库实现

在这个级别，可以认为事务中的数据无论何时都是一致的，此级别使它显得好像没有其它用户在修改数据，数据库在事务开始时候被“冻结”（至少，对于本事务涉及的数据如此）。

然而在不同数据库中，其实现机制也不同。

开始事务1，运行：

serializable

会得到1条记录，这时事务2开始运行：

会发现事务2挂起，它在等待事务1结束。

回到事务1，继续：

在片刻的等待以后，事务1得到类似以以下格式消息：

消息1205，级别13，状态56，第1 

行

事务（进程ID 

51）与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。

请重新运行该事务。

而事务2更新了数据并正常结束。

这是因为两个事务都设置成了串行化级别，当遇到冲突时候，sql 

server根据一定的规则选择牺牲掉其中一个事务，来保证事务的串行性。

上面的例子，如果将事务2的隔离级别改为提交读，那么事务2会等待事务1完成，之后自己正常完成（因为事务2没有串行需求，不会有死锁）。

在oracle中，通过多版本，可以在一定程度上避免死锁。

serializable;

;

--set 

tran语句隐式开始事务

得到1条记录，然后事务2开始运行：

1119995555）;

可以发现事务2立刻完成，没有阻塞。

回到事务1继续：

事务1中的第二次查询和事务开始时刻一致，就好像事务2已经完成的提交不存在。

事务最终正常更新完毕，并保持了“事务开始”时刻的数据一致性。

然而，如果事务1，2修改同样的数据行，也会有错误，

出现错误信息：

第 

1 

行出现错误:

ORA-08177:

无法连续访问此事务处理

总的来说，oracle利用多版本的方式实现串行化级别更少造成死锁，除非两个事务修改了相同的数据行，一般也不会造成冲突。

7．不同隔离级别的相互影响

前面的例子基本都是两个相同隔离级别事务的情况。

如果不同隔离级别的事务发生冲突，会有什么不同吗？

实际上，对于一个事务来说，其它事务的隔离级别对它来说是未知的，更进一步，甚至数据库中有没有其它事务，有多少事务也不知道。

影响事务访问数据就两方面因素：

该事务自身的隔离级别，该事务要访问的数据上面锁的状态。

事务1的查询获得1条记录，转到事务2，运行：

事务2查询获得3条记录，回到事务1，运行：

切换到事务2，运行：

因为事务2隔离级别为未提交读，因此事务1中刚作的修改可以立刻从查询看到，即使事务1还未结束。

进一步的update因为事务1对记录加了独占锁，因此事务2挂起。

回到事务1将其提交：

Commit

事务1正常结束，独占锁释放，从而让事务2得以继续修改数据，并最终完成。

Oracle数据库的隔离级别设置语句只有read 

committed和serializable（read 

only暂不讨论），加上其特殊锁机制，不同隔离级别事务间的影响除了上例（例6）中两个都为serializable的情况，其它都可视为互不阻塞。

8．页锁与行锁（限sql 

server）

server的锁可以到行一级。

然而它又存在自动的锁定扩大，锁定转换。

因此存在一些意想不到的情况。

下面是演示：

理论上来说，在提交读级别下，上面的update语句只是在state值为CA的数据行上加了独占锁，表中其它数据应该可以被其它事务更新，然而，如下事务2开始：

发现事务2陷入阻塞状态。

尽管它们更新的不是同一条记录。

回到事务1，运行：

事务1结束后事务