`
sd6292766
  • 浏览: 102081 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

转载:关于oracle事物锁级别Isolation Level

阅读更多
今天在查看oracle官方文档lock的那一部分的时候发现一个新的概念,isolation
level (数据隔离级别),虽然以前学过,但是忘的差不多了。
隔离级别(isoation leve)

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

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

ANSI/ISO SQ92标准定义了一些数据库操作的隔离级别:


    未提交读(read uncommitted)
    提交读(read committed) 
    重复读(repeatabe read) 
    序列化(seriaizabe)


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


    更新丢失(ost update):当系统允许两个事务同时更新同一数据是,发生更新丢失。 
    脏读(dirty read):当一个事务读取另一个事务尚未提交的修改时,产生脏读。
    非 重复读(nonrepeatabe
    read):同一查询在同一事务中多次进行,由于其他提交事务所做的修改或删除,每次返回不同的结果集,此时发生非重复读。(A transaction rereads
    data it has previousy read and finds that another committed transaction has
    modified or deeted the data. )
    幻 像(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 additiona
    rows that satisfy the condition. )

























Dirty Read NonRepeatabe Read Phantom Read
Read uncommitted Possible Possible Possible
Read committed not possible Possible Possible
Repeatabe read not possible not possible Possible
Seriaizabe not possible not possible not possible



ORACE的隔离级别

ORACE提供了SQ92标准中的read committed和seriaizabe,同时提供了非SQ92标准的read-ony。



read committed:

    这是ORACE缺省的事务隔离级别。
    事务中的每一条语句都遵从语句级的读一致性。
    保证不会脏读;但可能出现非重复读和幻像。


seriaizabe:(串行执行事务,并发性最小)

    简单地说,seriaizabe就是使事务看起来象是一个接着一个地顺序地执行。
    仅仅能看见在本事务开始前由其它事务提交的更改和在本事务中所做的更改。
    保证不会出现非重复读和幻像。
    Seriaizabe隔离级别提供了read-ony事务所提供的读一致性(事务级的读一致性),同时又允许DM操作。


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

If 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。

ORACE在数据块中记录最近对数据行执行修改操作的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.

The 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.

read-ony:

遵从事务级的读一致性,仅仅能看见在本事务开始前由其它事务提交的更改。
不允许在本事务中进行DM操作。
read ony是seriaizabe的子集。它们都避免了非重复读和幻像。区别是在read ony中是只读;而在seriaizabe中可以进行DM操作。

    Export with CONSISTENT = Y sets the transaction to read-ony.

read committed和seriaizabe的区别和联系:

事务1先于事务2开始,并保持未提交状态。

事务2想要修改正被事务1修改的行。事务2等待。

如果事务1回滚,则事务2(不论是read committed还是seriaizabe方式)进行它想要做的修改。

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

当事务2是seriaizabe方式时,失败并报错“Cannot seriaize access”,因为事务2看不见事务1提交的修改,且事务2想在事务1修改的基础上再做修改。即seriaizabe不允许存在事务嵌套



具体见英文:

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.

read committed和seriaizabe可以在ORACE并行服务器中使用。

关于SET TRANSACTION READ WRITE:read write和read committed
应该是一样的。在读方面,它们都避免了脏读,但都无法实现重复读。虽然没有文档说明read write在写方面与read committed一致,但显然它在写的时候会加排他锁以避免更新丢失。在加锁的过程中,如果遇到待锁定资源无法锁定,应该是等待而不是放弃。这与 read committed一致。

语句级的读一致性

    ORACE保证语句级的读一致性,即一个语句所处理的数据集是在单一时间点上的数据集,这个时间点是这个语句开始的时间。
    一个语句看不见在它开始执行后提交的修改。
    对于DM语句,它看不见由自己所做的修改,即DM语句看见的是它本身开始执行以前存在的数据。


事务级的读一致性

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


设置隔离级别

设置一个事务的隔离级别

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET TRANSACTION READ ONLY;

设置增个会话的隔离级别

    ATER SESSION SET ISOLATION_LEVE SERIALIZABLE;
    ATER SESSION SET ISOLATION_LEVE READ COMMITTED;

Choice of Isolation Level

Application designers and developers should choose an isolation level based
on application performance and consistency needs as well as application coding
requirements.

For environments with many concurrent users rapidly submitting transactions,
designers must assess transaction performance requirements in terms of the
expected transaction arrival rate and response time demands. Frequently, for
high-performance environments, the choice of isolation levels involves a
trade-off between consistency and concurrency.

Read Committed Isolation

For many applications, read committed is the most appropriate
isolation level. Read committed isolation can provide considerably more
concurrency with a somewhat increased risk of inconsistent results due to
phantoms and non-repeatable reads for some transactions.

Many high-performance environments with high transaction arrival
rates require more throughput and faster response times than can be achieved
with serializable isolation. Other environments that supports users
with a very low transaction arrival rate also face very low risk of incorrect
results due to phantoms and nonrepeatable reads. Read committed isolation is
suitable for both of these environments.

两种情况:(1)在事务量大、高性能的计算环境,需要更高的吞吐量和响应时间;(2)事务数少,并且发生幻影和不可重复读的几率的比较低

Oracle read committed isolation provides transaction set consistency for
every query. That is, every query sees data in a consistent state. Therefore,
read committed isolation will suffice for many applications that might require a
higher degree of isolation if run on other database management systems that do
not use multiversion concurrency control.

Read committed isolation mode does not require application logic to trap the
"Cannot serialize access" error and loop back to restart a transaction. In most
applications, few transactions have a functional need to issue the same query
twice, so for many applications protection against phantoms and non-repeatable
reads is not important. Therefore many developers choose read committed to avoid
the need to write such error checking and retry code in each transaction.

Serializable Isolation

Oracle's serializable isolation is suitable for environments where there is a
relatively low chance that two concurrent transactions will modify the same rows
and the long-running transactions are primarily read-only. It is most suitable
for environments with large databases and short transactions that update only a
few rows.

(1)适合于很少存在两个事务同时修改同一条记录的情况



(2)长事务以只读为主



(3)大型数据库并且每个短事务只修改很少的记录

Serializable isolation mode provides somewhat more consistency by protecting
against phantoms and nonrepeatable reads and can be important where a read/write
transaction executes a query more than once.

Unlike other implementations of serializable isolation, which lock blocks for
read as well as write, Oracle provides nonblocking queries and the fine
granularity of row-level locking, both of which reduce write/write contention.
For applications that experience mostly read/write contention, Oracle
serializable isolation can provide significantly more throughput than other
systems. Therefore, some applications might be suitable for serializable
isolation on Oracle but not on other systems.

All queries in an Oracle serializable transaction see the database as of a
single point in time, so this isolation level is suitable where multiple
consistent queries must be issued in a read/write transaction. A report-writing
application that generates summary data and stores it in the database might use
serializable mode because it provides the consistency that a READ
ONLY transaction provides, but also allows INSERT,
UPDATE, and DELETE.
分享到:
评论

相关推荐

    oracle lock and isolation at

    在Oracle中,锁(Lock)和隔离级别(Isolation Level)是确保多用户环境下数据一致性和完整性的关键组成部分。本篇文章将深入探讨这两个概念及其在实际应用中的重要性。 一、Oracle锁机制 1. **锁定类型**: - **...

    TRANSACTION ISOLATION LEVEL

    RANSACTION ISOLATION LEVEL

    Oracle中使用锁进行并发控制

    在Oracle中,可以使用`SET TRANSACTION ISOLATION LEVEL`语句来显式地设置事务的隔离级别。例如: ```sql SET TRANSACTION ISOLATION LEVEL READ COMMITTED; ``` 或者在会话级别设置: ```sql ALTER SESSION SET ...

    oracle死锁故障分析和诊断解决

    - 合理设置`ISOLATION LEVEL`,以减少并发冲突。 4. **使用Oracle提供的死锁检测机制**: - Oracle自身具备一定的死锁自动检测和恢复功能,可以在一定条件下自动解除死锁。 #### 五、案例分析 假设在一个多用户...

    ORACLE锁定机制

    2. 在事务开始时设置适当的隔离级别,如`SET TRANSACTION ISOLATION LEVEL READ COMMITTED`。 3. 避免长时间持有锁,尤其是在高并发环境中,尽快完成事务操作以释放锁资源。 总结,Oracle锁定机制是保证数据库稳定...

    asp.net 中使用oracle数据库事务

    - **Serializable**:最高级别的隔离,可以避免所有并发问题,但可能会导致更多的锁等待,降低性能。 #### 四、总结 本文介绍了在 ASP.NET 应用程序中如何使用 OracleTransaction 对象来管理数据库事务。通过使用 ...

    DB2和 Oracle的并发控制

    并发控制通过引入【锁】技术来防止事务间的冲突,保证ACID特性得以实现,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。 【锁类型】主要有两种基本类型的锁:排他锁...

    Oracle事务与锁定

    锁定类型包括共享锁(读锁)、排他锁(写锁)和其他多种类型的行级、表级和模式级别的锁。当一个事务持有锁时,其他事务必须等待锁被释放才能进行相应的操作。这样既保证了数据一致性,也实现了并发访问的安全性。 ...

    Oracle事务、锁表查询及相关实用查询SQL语句

    ### Oracle事务、锁表查询及相关实用查询SQL语句 #### 一、Oracle事务管理 在Oracle数据库中,事务是作为一组逻辑操作单元处理的一系列数据库操作。这些操作要么全部成功,要么全部失败,确保了数据的一致性和完整...

    Oracle Concepts 10g R2 pdf 中英文对照版

    - **并发控制**:Oracle使用多种机制来确保多个用户同时访问数据时的数据一致性,如行级锁、乐观锁等。 - **一致性读**:Oracle提供了一种称为“一致性读”的特性,使得用户可以在不阻塞其他事务的情况下读取数据。 ...

    KFC系列之 - Oracle DBA入门Mysql(中)

    - **tx_isolation**:用于设置当前会话的隔离级别,可以设置为`READ-UNCOMMITTED`、`READ-COMMITTED`、`REPEATABLE-READ`或`SERIALIZABLE`。 - **innodb_lock_wait_timeout**:设置在等待锁时的超时时间,单位为秒。...

    oracle 事务实验

    SET TRANSACTION [ISOLATION LEVEL level] [READ ONLY | READ WRITE]; ``` - **示例**: - 设置事务为只读: ```sql SQL> set transaction readonly; ``` - 设置事务为读写,默认情况: ```sql SQL> set ...

    事物处理与并发控制

    标题和描述概述的知识点主要集中在Oracle数据库的事物处理与并发控制方面,这涉及到数据库操作的一致性、隔离性和并发管理。下面将详细展开这些知识点。 ### 事务处理 #### 定义与特性 事务(Transaction)是...

    维护项目oracle常用语句

    ` 这是默认级别,而`set transaction isolation level serializable;` 设置为串行化级别,提供了最高级别的隔离,防止脏读、不可重复读和幻读。另外,`read only` 表示只读事务。 4. **利用游标更新数据**: 游标...

    浅析Oracle和Microsoft SQL Server事务处理的异同.pdf

    - SQL Server同样支持保存点的概念,使用SET TRANSACTION ISOLATION LEVEL语句可以设置事务的隔离级别,控制事务的并发行为。 4. 锁机制: - Oracle使用行级锁和表级锁,在高并发环境下,能够较好地保证数据的...

    【Oracle 并发】

    "ORACLE.txt"文件可能包含了关于Oracle并发控制更具体的配置、优化技巧或案例分析。通过阅读和分析这个文件,我们可以获得更深入的理解,例如如何调整Oracle的并发参数以提升系统性能,或者如何解决死锁问题。 总的...

    Oracle的事务及锁.docx

    Oracle数据库的事务管理和锁机制是确保数据库稳定性和数据一致性的重要组成部分。事务是数据库操作的基本逻辑单元,具有四个关键特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持续性...

Global site tag (gtag.js) - Google Analytics