转载

参考oracle官方文档关于脏读、一致性读、undo中已提交数据块的理解

脏读Dirty reads
A transaction reads data that has been written by another transaction that has not been committed yet.
一个事务读取另一个尚未提交的事务写入的数据。

Oracle Database never permits dirty reads, which occur when a transaction reads uncommitted data in another transaction
Oracle数据库永远不允许脏读,即不可能出现一个事务读取另一个事务中未提交的数据。

可以简单理解成ORACLE数据库没有脏读的概念,因为实际中oracle数据库永远不会出现脏读的现象。




Consistent mode
A consistent read get is a retrieval of a read-consistent version of a block. This retrieval may use undo data. For example, if an uncommitted transaction has updated two rows in a block, and if a query in a separate session requests the block, then the database uses undo data to create a read-consistent version of this block (called a consistent read clone) that does not include the uncommitted updates. Typically, a query retrieves blocks in consistent mode.
一致的读取获取块的读取一致版本。 这个检索可以使用撤消数据。 例如,如果未提交的事务更新了块中的两行,并且如果单独会话中的查询请求该块,则数据库使用撤销数据来创建此块的读一致版本(称为一致读取克隆)不包括未提交的更新。 通常,查询以一致模式检索块。
--具体理解:因为未提交,所以数据块上没有事务提交后的SCN,数据块上的SCN还是事务开始之前的SCN,这时select时刻点的SCN大于数据块的SCN,但是数据块对应事务没有提交,所以还是去回滚段中读取数据块的前镜像。

Oracle Database maintains data consistency by using a multiversion consistency model and various types of locks and transactions. In this way, the database can present a view of data to multiple concurrent users, with each view consistent to a point in time. Because different versions of data blocks can exist simultaneously transactions can read the version of data committed at the point in time required by a query and return results that are consistent to a single point in time.
Oracle数据库通过使用多版本一致性模型和各种类型的锁和事务来维护数据一致性。 通过这种方式,数据库可以向多个并发用户呈现数据视图,每个视图与时间点一致。 由于不同版本的数据块可以同时存在,因此事务可以读取在查询所需的时间点提交的数据的版本,并返回与单个时间点一致的结果。

In Oracle Database, each user must see a consistent view of the data, including visible changes made by a user's own transactions and committed transactions of other users. For example, the database must not permit a dirty read, which occurs when one transaction sees uncommitted changes made by another concurrent transaction.
Oracle Database always enforces statement-level read consistency, which guarantees that the data returned by a single query is committed and consistent with respect to a single point in time. Depending on the transaction isolation level, this point is the time at which the statement was opened or the time the transaction began. The Flashback Query feature enables you to specify this point in time explicitly.
The database can also provide read consistency to all queries in a transaction, known as transaction-level read consistency. In this case, each statement in a transaction sees data from the same point in time, which is the time at which the transaction began.
在Oracle数据库中,每个用户都必须看到数据的一致视图,包括用户自己的事务所做的可见更改以及其他用户的已提交事务。 例如,数据库不允许脏读,当一个事务看到另一个并发事务所做的未提交更改时,就会发生这种情况。
Oracle数据库始终强制执行语句级别的读取一致性,这保证了单个查询返回的数据已被提交并且与单个时间点一致。 根据事务隔离级别,这一点是打开语句的时间或事务开始的时间。闪回查询功能使您可以明确指定此时间点。
数据库还可以为事务中的所有查询提供读取一致性,称为事务级读取一致性。在这种情况下,交易中的每个陈述都会看到来自同一时间点(即交易开始的时间点)的数据。


A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.
SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time with respect to the database. Several events may share the same SCN, which means that they occurred at the same time with respect to the database.
Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.
Oracle Database increments SCNs in the system global area (SGA). When a transaction modifies data, the database writes a new SCN to the undo data segment assigned to the transaction. The log writer process then writes the commit record of the transaction immediately to the online redo log. The commit record has the unique SCN of the transaction. Oracle Database also uses SCNs as part of its instance recovery and media recovery mechanisms.
系统更改号(SCN)是Oracle数据库使用的逻辑内部时间戳记。 SCNs订购数据库内发生的事件,这是满足事务的ACID属性所必需的。 Oracle数据库使用SCN标记SCN,在此之前知道所有更改都在磁盘上(写入了online redo),以便恢复避免应用不必要的重做。数据库还使用SCN标记一组数据不存在重做的点,以便可以停止恢复。
SCN以单调递增的顺序发生。 Oracle数据库可以像使用时钟一样使用SCN,因为观察到的SCN指示了一个逻辑时间点,并且重复的观察返回相等或更大的值。如果一个事件的SCN低于另一个事件,那么它发生在相对于数据库更早的时间。几个事件可能共享同一个SCN,这意味着它们同时发生在数据库上。
每笔交易都有一个SCN。例如,如果一个事务更新了一行,那么数据库将记录发生此更新的SCN。这个事务中的其他修改具有相同的SCN当一个事务提交时,数据库为这个提交记录一个SCN
Oracle数据库在系统全局区域(SGA)中增加SCN。当事务修改数据时,数据库将新的SCN写入分配给该事务的撤销数据段。日志写入程序会立即将事务的提交记录写入在线重做日志。提交记录具有事务的唯一SCN。 Oracle数据库还使用SCN作为其实例恢复和媒体恢复机制的一部分。
--当事务修改数据时,数据库将新的SCN写入分配给该事务的撤销数据段:具体的理解就是当一个事务开始时,会记录事务开始的SCN和修改前的数据到UNDO中

As the database retrieves data blocks on behalf of a query, the database ensures that the data in each block reflects the contents of the block when the query began. The database rolls back changes to the block as needed to reconstruct the block to the point in time the query started processing.
The database uses a mechanism called an SCN to guarantee the order of transactions. As the SELECT statement enters the execution phase, the database determines the SCN recorded at the time the query began executing. In Figure 9–1, this SCN is 10023. The query only sees committed data with respect to SCN 10023.
In Figure 9–1, blocks with SCNs after 10023 indicate changed data, as shown by the two blocks with SCN 10024. The SELECT statement requires a version of the block that is consistent with committed changes. The database copies current data blocks to a new buffer and applies undo data to reconstruct previous versions of the blocks. These reconstructed data blocks are called consistent read (CR) clones.
In Figure 9–1, the database creates two CR clones: one block consistent to SCN 10006 and the other block consistent to SCN 10021. The database returns the reconstructed data for the query. In this way, Oracle Database prevents dirty reads.
当数据库以查询方式检索数据块时,数据库确保每个块中的数据反映的是查询开始时的内容。数据库根据需要回滚对块的更改,以将块重建为查询开始处理的时间点。
数据库使用称为SCN的机制来保证事务的顺序。当SELECT语句进入执行阶段时,数据库确定查询开始执行时记录的SCN。在图9-1中,这个SCN是10023,查询关于SCN 10023已提交的数据
在图9-1中,10023之后的SCN块表示更改的数据,如SCN 10024所示的两个块所示。SELECT语句需要与已提交的更改一致的块版本。数据库将当前数据块复制到一个新的缓冲区,并应用撤销数据来重建以前版本的数据块。这些重建的数据块被称为一致读(CR)克隆。
在图9-1中,数据库创建两个CR克隆:一个块与SCN 10006一致,另一个块与SCN 10021一致。数据库返回查询的重构数据。通过这种方式,Oracle数据库可以防止脏读。
--查询关于SCN 10023已提交的数据:具体的理解就是只能看到SCN 10023或之前SCN号已提交的数据
参考oracle官方文档关于脏读、一致性读、undo中已提交数据块的理解


The database uses information in the block header, also called an interested transaction list (ITL), to determine whether a transaction was uncommitted when the database began modifying the block. The block header of every segment block contains an ITL.
数据库使用块头中的信息,也称为感兴趣事务列表(ITL),来确定在数据库开始修改块时事务是否未提交。 每个segment块的块标题包含一个ITL。

Entries in the ITL describe which transactions have rows locked and which rows in the block contain committed and uncommitted changes. The ITL points to the transaction table in the undo segment, which provides information about the timing of changes made to the database.
In a sense, the block header contains a recent history of transactions that affected each row in the block. The INITRANS parameter of the CREATE TABLE and ALTER TABLE statements controls the amount of transaction history that is kept.
ITL中的条目描述哪些事务处于行锁定状态,哪些行包含已提交和未提交的更改。 ITL指向还原段中的事务表,该事务表提供有关对数据库所做更改的时间的信息。
从某种意义上说,块标题包含了影响块中每一行的事务的最近历史记录。 CREATE TABLE和ALTER TABLE语句的INITRANS参数控制保留的事务历史记录的数量。




The undo segments contain the old values of data that have been changed by uncommitted or recently committed transactions. Thus, multiple versions of the same data, all at different points in time, can exist in the database. The database can use snapshots of data at different points in time to provide read-consistent views of the data and enable nonblocking queries.
回滚段包含未提交或最近提交的事务更改的旧数据值。 因此,在数据库中可以存在多个版本的相同数据,所有这些版本都在不同的时间点。 数据库可以使用不同时间点的数据快照来提供数据的一致性视图并启用非阻塞查询。

In general, it is desirable to retain old undo data as long as possible. After a transaction commits, undo data is no longer needed for rollback or transaction recovery. The database can retain old undo data if the undo tablespace has space for new transactions. When available space is low, the database begins to overwrite old undo data for committed transactions
通常,尽可能保留旧的撤消数据是可取的。 事务提交后,撤销数据不再需要回滚或事务恢复。 如果撤消表空间有新的事务空间,数据库可以保留旧的撤消数据。 当可用空间不足时,数据库将开始覆盖已提交事务的旧撤消数据

ORA-01555: snapshot too old: rollback segment number string with name "string" too small
Cause: rollback records needed by a reader for consistent read are overwritten by other writers
Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments
ORA-01555:快照太老:回滚段名称字符串的字符串太小
原因:阅读器读取一致性所需的回滚记录被其他作者覆盖
操作:如果在“自动还原管理”模式下,增加undo_retention设置。 否则,使用更大的回滚段

ORA-01555:快照过旧错误,除了sql执行时间过长外,还可能是undo空间不够或undo_retention值过小或undo_retention值足够但是GUARANTEE没有启用


个人理解
这个块的scn小于sql进程本身的scn且未提交,则要去读取回滚段,这个时候不会报ORA-01555,因为回滚段里面没有提交的数据是不会被覆盖的
这个块的scn大于sql进程本身的scn,也要去回滚段读取,这个时候数据块一定是提交了(当一个事务提交时,数据库为这个提交记录一个SCN),则提交时间肯定是大于sql开始执行时间点小于执行到读取该块的时间点,所以sql执行时间小于undo_retention且GUARANTEE启用则没有问题不会报ORA-01555。

如果一个select查询耗时2小时时,从select开始到这个块被访问时,这个块已经被修改了5次并且都提交了
1、如果这5次记录都还在undo中,但是INITRANS只是4时(MAXTRANS是4则INITRANS最大也只能是4,默认情况下INITRANS to 1,MAXTRANS to 255),会报错。
2、如果只有4次记录还在undo中,一次被覆盖了,会报错ORA-01555

实验过,10G容量级别的大量insert操作了30分钟,但是不commit,会发现redo log不停的切换产生归档日志,且datafile不停增加。再直接shutdown abort,startup的时候发现很快,不需要30分钟。
前滚回滚过程应该是这样的:数据库记录了最新的SCN、增量checkpoint的SCN、redo log的最大SCN,通过增量checkpoint的SCN开始应用redo log直到redo log的最大SCN乃至最新的SCN,这样就完成了前滚,在回滚的时候直接读取undo中这个会话最初的scn和前镜像直接回滚,不会一个个数据块去undo,否则10G都已经写入数据文件那回滚得多久啊
正文到此结束
Loading...