Fetching Across Commits: Where Is the Source of the Problem?

原创 2002年04月05日 09:19:00

Fetching Across Commits: Where Is the Source of the Problem?

By Boris Milrud

Fetching across commits is a common and accepted practice in the Oracle programming world. However, it is not an ANSI-supported operation. According to the ANSI standard, a cursor is invalidated whenever a commit is performed, which means it should be closed and reopened. Hence, you should be aware of potential ORA-01555 errors and the fact that you are relying on a cursor behavior that is not to the ANSI standard.

Oracle always enforces statement-level read consistency. This guarantees that the data returned by a single query is consistent with respect to the time when the query began. Therefore, a query never sees the data changes made by the transactions that commit during the course of a query's execution.

As I described in my previous 10-Minute Solution on this subject, Oracle uniquely identifies any given point in time by a set of numbers, called the System Change Numbers. To ensure read consistency, Oracle marks the current SCN as the query enters the execution phase. The query can see the snapshot of the records only as they were at the time that they were marked by, or assigned to, the SCN.

This is the situation when a query opens a cursor, then loops through fetching, changing, and committing the records on the same table. The cursor requires a "snapshot" of the data at the cursor's open time for read consistency. As data blocks are read on behalf of the query and each block contains uncommitted changes of other transactions or changed data with more recent SCNs, the data is then reconstructed using the saved snapshot from the rollback segments. During a long-running query, undo entries in the rollback segment may get overwritten by another transaction, even from the same query. In this case, the Oracle server would be unable to reconstruct the snapshot, resulting in the ORA-01555 error.

Here's an example: A cursor is opened at SCN=10. The execution SCN of the query is then marked as SCN=10. Every fetch by that cursor now needs to get the read-consistent data from SCN=10. Code in the stored procedure is fetching records from the cursor, changing them, and committing them. Let's say they were committed with SCN=20. If a later fetch happens to retrieve a record that's in one of the previously committed blocks, then the fetch will see that the current block SCN is 20. Because the fetch has to get the snapshot from SCN=10 it will try to find it in the rollback segments. If it can roll sufficiently back as previously explained, then it will be able to reconstruct the snapshot from SCN=10. If not, it will return the ORA-01555 error.

Committing less often, thus creating larger rollback segments, will reduce the probability of getting this error. The only drawback to this is that the developer has to contact the DBA and make sure the rollback segments can be extended, and that the rollback tablespace can accommodate them.

读这种文章还是有收获的。让你明白一些事情。

杭电OJ——1098 Ignatius's puzzle

Ignatius's puzzle Problem Description Ignatius is poor at math,he falls across a puzzle ...
  • lishuhuakai
  • lishuhuakai
  • 2012年10月17日 22:23
  • 1942

POJ 1318 Word Amalgamation (字符串 STL大水)

POJ 1318 Word Amalgamation (字符串 STL大水)
  • Tc_To_Top
  • Tc_To_Top
  • 2015年07月04日 00:34
  • 1589

安装gitlab

from: http://my.oschina.net/u/1169607/blog/344142 摘要 本文介绍了gitlab的安装,给出来安装过...
  • Real_Myth
  • Real_Myth
  • 2016年07月20日 09:55
  • 698

eclipse hibernate tool 报错 - Fetching children of Database

eclipse插件Hibernate tools与slf4j冲突,浏览Database出现错误: An internal error occurred during: "Fetching child...
  • u011948354
  • u011948354
  • 2015年12月16日 22:04
  • 1681

eclipse Hibernate Tools插件错误:Fetching children of Database

我遇到这个错误是因为最近安装了Spring Tools Suite插件,生成model类时,遇此问题。经过调查,发现里面的slf4j版本高于1.5.8导致这个问题,并且hibernate tools调...
  • haifengyouxi
  • haifengyouxi
  • 2014年03月20日 16:38
  • 3789

HDU 1098 Ignatius's puzzle(数论-其它)

HDU 1098 Ignatius's puzzle(数论-其它) 题目大意: 给定一个k,找到最小的a 使得 f(x)=5*x^13+13*x^5+k*a*x ,f(x)%65永远等于0 解题...
  • a1061747415
  • a1061747415
  • 2014年08月05日 14:10
  • 841

How to Ignore new commits for git submodule

the git occured serveral new commits like:Changes not staged for commit: (use "git add ..." to upd...
  • toyijiu
  • toyijiu
  • 2017年08月25日 10:41
  • 128

kafka 报错分析

kafka 更新版本之后报错如下:2015-12-02 15:14:12 [ pool-1-thread-2:4217697 ] - [ ERROR ] Failed to collate mes...
  • keep_learn
  • keep_learn
  • 2015年12月04日 14:47
  • 5106

Xcode encountered a problem. Source editor functionality is limited. Attempting to restore...

当xcode出现这样的问题的时候,去/Users/xxxx/Library/Developer/Xcode/里面删除DerivedData这个文件夹,问题就解决了...
  • wahaha13168
  • wahaha13168
  • 2016年04月14日 14:41
  • 1011

Squash Commits with Git

# switch to master branch git checkout master # ensure our master is up to date git pull remoteRepo...
  • velanjun
  • velanjun
  • 2014年10月27日 14:18
  • 3078
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Fetching Across Commits: Where Is the Source of the Problem?
举报原因:
原因补充:

(最多只允许输入30个字)