Oracle 隔离级别

举报
snowofsummer 发表于 2020/11/24 07:39:39 2020/11/24
【摘要】 isolation_level默认隔离级别Read committed; ORACLE保证语句级的读一致性,即一个语句所处理的数据集是在单一时间点上的数据集,这个时间点是这个语句开始的时间。不可重复读:(同时操作,事务一分别读取事务二操作时和提交后的数据,读取的记录内容不一致)不可重复读是指在同一个事务内,两个相同的查询返回了不同的结果。===》》》同样的条件,你读取过的数...

isolation_level

默认隔离级别Read committed;

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


不可重复读:(同时操作,事务一分别读取事务二操作时和提交后的数据,读取的记录内容不一致)

不可重复读是指在同一个事务内,两个相同的查询返回了不同的结果。

===》》》同样的条件,你读取过的数据,再次读取出来发现值不一样了。


幻读:(和可重复读类似,但是事务二的数据操作仅仅是插入和删除,不是修改数据,读取的记录数量前后不一致)


参考手册:

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-B97790CB-DF82-442D-B9D5-50CCE6BF9FBDDatabase 

Concepts==Overview of Oracle Database Transaction Isolation Levels


Read Committed Isolation Level

In the read committed isolation level, every query executed by a transaction sees only data committed before the query—not the transaction—began.

This isolation level is the default. It is appropriate for database environments in which few transactions are likely to conflict.

A query in a read committed transaction avoids reading data that commits while the query is in progress. For example, if a query is halfway through a scan of a million-row table, and if a different transaction commits an update to row 950,000, then the query does not see this change when it reads row 950,000. However, because the database does not prevent other transactions from modifying data read by a query, other transactions may change data between query executions. Thus, a transaction that runs the same query twice may experience fuzzy reads and phantoms.


  • Dirty reads

    A transaction reads data that has been written by another transaction that has not been committed yet.

  • Nonrepeatable (fuzzy) reads

    A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data. For example, a user queries a row and then later queries the same row, only to discover that the data has changed.

  • Phantom reads

    A transaction reruns 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.

    For example, a transaction queries the number of employees. Five minutes later it performs the same query, but now the number has increased by one because another user inserted a record for a new hire. More data satisfies the query criteria than before, but unlike in a fuzzy read the previously read data is unchanged.



Preventable Read Phenomena by Isolation Level

Isolation Level Dirty Read Nonrepeatable Read Phantom Read

Read uncommitted

Possible

Possible

Possible

Read committed

Not possible

Possible

Possible

Repeatable read

Not possible

Not possible

Possible

Serializable

Not possible

Not possible

Not possible

Oracle Database offers the read committed (default) and serializable isolation levels. Also, the database offers a read-only mode.





【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。