oracle解决了事务的隔离,ORACLE事务的隔离级别

隔离级别(isolation

level)

l隔离级别定义了事务与事务之间的隔离程度。

l隔离级别与并发性是互为矛盾的:隔离程度越高,数据库的并发性越差;隔离程度越低,数据库的并发性越好。

lANSI/ISO

SQL92标准定义了一些数据库操作的隔离级别:

l未提交读(read

uncommitted)

l提交读(read

committed)

l重复读(repeatable

read)

l序列化(serializable)

l通过一些现象,可以反映出隔离级别的效果。这些现象有:

l更新丢失(lost

update):当系统允许两个事务同时更新同一数据是,发生更新丢失。

l脏读(dirty

read):当一个事务读取另一个事务尚未提交的修改时,产生脏读。

l非重复读(nonrepeatable

read):同一查询在同一事务中多次进行,由于其他提交事务所做的修改或删除,每次返回不同的结果集,此时发生非重复读。(A

transaction rereads data it has previously read and finds that

another committed transaction has modified or deleted the

data. )

l幻像(phantom

read):同一查询在同一事务中多次进行,由于其他提交事务所做的插入操作,每次返回不同的结果集,此时发生幻像读。(A

transaction reexecutes a query returning a set of rows that

satisfies a search condition and finds that another committed

transaction has inserted additional rows that satisfy the

condition. )

ORACLE的隔离级别

lORACLE提供了SQL92标准中的read

committed和serializable,同时提供了非SQL92标准的read-only。

lread

committed:

l这是ORACLE缺省的事务隔离级别。

l事务中的每一条语句都遵从语句级的读一致性。

l保证不会脏读;但可能出现非重复读和幻像。

lserializable:

l简单地说,serializable就是使事务看起来象是一个接着一个地顺序地执行。

l仅仅能看见在本事务开始前由其它事务提交的更改和在本事务中所做的更改。

l保证不会出现非重复读和幻像。

lSerializable隔离级别提供了read-only事务所提供的读一致性(事务级的读一致性),同时又允许DML操作。

l如果有在serializable事务开始时未提交的事务在serializable事务结束之前修改了serializable事务将要修改的行并进行了提交,则serializable事务不会读到这些变更,因此发生无法序列化访问的错误。(换一种解释方法:只要在serializable事务开始到结束之间有其他事务对serializable事务要修改的东西进行了修改并提交了修改,则发生无法序列化访问的错误。)

lIf

a serializable transaction contains data manipulation language

(DML) that attempts to update any resource that may have been

updated in a transaction uncommitted at the start of the

serializable transaction, (并且修改在后来被提交而没有回滚),then

the DML statement fails. 返回的错误是ORA-08177: Cannot

serialize access for this transaction。

lORACLE在数据块中记录最近对数据行执行修改操作的N个事务的信息,目的是确定是否有在本事务开始时未提交的事务修改了本事务将要修改的行。具体见英文:Oracle

permits a serializable transaction to modify a data row only if it

can determine that prior changes to the row were made by

transactions that had committed when the serializable transaction

began. To make this determination efficiently, Oracle uses control

information stored in the data block that indicates which rows in

the block contain committed and uncommitted changes. In a sense,

the block contains a recent history of transactions that affected

each row in the block. The amount of history that is retained is

controlled by the INITRANS parameter of CREATE TABLE and ALTER

TABLE. Under some circumstances, Oracle may have insufficient

history information to determine whether a row has been updated by

a "too recent" transaction. This can occur when many transactions

concurrently modify the same data block, or do so in a very short

period. You can avoid this situation by setting higher values of

INITRANS for tables that will experience many transactions updating

the same blocks. Doing so will enable Oracle to allocate sufficient

storage in each block to record the history of recent transactions

that accessed the block.

lThe

INITRANS Parameter:Oracle stores control

information in each data block to manage access by concurrent

transactions. Therefore, if you set the transaction isolation level

to serializable, you must use the ALTER TABLE command to set

INITRANS to at least 3. This parameter will cause Oracle to

allocate sufficient storage in each block to record the history of

recent transactions that accessed the block. Higher values should

be used for tables that will undergo many transactions updating the

same blocks.

lread-only:

l遵从事务级的读一致性,仅仅能看见在本事务开始前由其它事务提交的更改。

l不允许在本事务中进行DML操作。

lread

only是serializable的子集。它们都避免了非重复读和幻像。区别是在read

only中是只读;而在serializable中可以进行DML操作。

lExport

with CONSISTENT = Y sets the transaction to read-only.

lread

committed和serializable的区别和联系:

l事务1先于事务2开始,并保持未提交状态。事务2想要修改正被事务1修改的行。事务2等待。如果事务1回滚,则事务2(不论是read

committed还是serializable方式)进行它想要做的修改。如果事务1提交,则当事务2是read

committed方式时,进行它想要做的修改;当事务2是serializable方式时,失败并报错“Cannot

serialize access”,因为事务2看不见事务1提交的修改,且事务2想在事务一修改的基础上再做修改。具体见英文:Both

read committed and serializable transactions use row-level locking,

and both will wait if they try to change a row updated by an

uncommitted concurrent transaction. The second transaction that

tries to update a given row waits for the other transaction to

commit or roll back and release its lock. If that other transaction

rolls back, the waiting transaction (regardless of its isolation

mode) can proceed to change the previously locked row, as if the

other transaction had not existed. However, if the other (blocking)

transaction commits and releases its locks, a read committed

transaction proceeds with its intended update. A serializable

transaction, however, fails with the error "Cannot serialize

access", because the other transaction has committed a change that

was made since the serializable transaction

began.

lread

committed和serializable可以在ORACLE并行服务器中使用。

l关于SET

TRANSACTION READ WRITE:read write和read

committed 应该是一样的。在读方面,它们都避免了脏读,但都无法实现重复读。虽然没有文档说明read

write在写方面与read

committed一致,但显然它在写的时候会加排他锁以避免更新丢失。在加锁的过程中,如果遇到待锁定资源无法锁定,应该是等待而不是放弃。这与read

committed一致。

l语句级的读一致性

lORACLE保证语句级的读一致性,即一个语句所处理的数据集是在单一时间点上的数据集,这个时间点是这个语句开始的时间。

l一个语句看不见在它开始执行后提交的修改。

l对于DML语句,它看不见由自己所做的修改,即DML语句看见的是它本身开始执行以前存在的数据。

l事务级的读一致性

l事务级的读一致性保证了可重复读,并保证不会出现幻像。

l设置隔离级别

l设置一个事务的隔离级别

lSET

TRANSACTION ISOLATION LEVEL READ COMMITTED;

lSET

TRANSACTION ISOLATION LEVEL SERIALIZABLE;

lSET

TRANSACTION READ ONLY;

l设置增个会话的隔离级别

lALTER

SESSION SET ISOLATION_LEVEL SERIALIZABLE;

lALTER

SESSION SET ISOLATION_LEVEL READ COMMITTED;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值