自动提交打开时是否可以进行明确的提交? [英] Are explicit commits okay when autocommit is on?

问题描述

我已经搜索了一段时间，却找不到.我正在使用Oracle，并且有一个类似于以下内容的For循环:

I've been searching for a while and couldn't find this. I'm working with Oracle and have a For loop similar to:

BEGIN
  FOR YEARIDs IN (SELECT DISTINCT YEARID From MyTable)
  LOOP
    UPDATE (
              SELECT    ......
            )
    SET     MyFlag = 1;
    COMMIT;  -- Added
  END LOOP;
END;

AutoCommit已打开，但似乎直到整个FOR循环完成后才进行提交.因此，我在上面的代码中添加了Commit语句.这会导致任何意外结果，还是违反任何最佳做法? (即，打开AutoCommit时，我是否不应该进行显式调用来进行提交?)

AutoCommit is turned on, but it appears that the commit doesn't happen until the entire FOR loop completes. Therefore, I have added the Commit statement in the above code. Is this going to cause any unexpected results, or does this violate any best practices? (i.e. should I not make explicit calls to commit when AutoCommit is turned on?)

谢谢， 斯科特

糟糕，我正在使用Oracle 11g和Oracle SQL Developer作为客户端.

Oops... I'm using Oracle 11g and Oracle SQL Developer as the client.

到目前为止，感谢您的答复.在查询运行的时间点，正在生成数据&；调整.没有其他连接可以尝试访问数据.至于为什么这么频繁地提交，为什么在开发期间，我对一部分数据运行查询，而查询运行得很好.该表包含约1400万条记录，而我正在测试约10万条记录.该查询相当复杂，并且针对该子集运行大约5分钟.当我针对整个表运行它时，查询将运行14个小时以上，并且无法更新任何记录.我的理论是，持有大量撤消信息可能会消耗开发服务器上的所有可用资源.如果我频繁提交，则可以释放和重用撤消信息.是的，很慢.但是，如果查询实际上将完成，即使需要花费一整夜的时间，那么也可以将其移至测试服务器. (并且可以在以后进行性能调整.)截止日期早已过去. (错过最后期限后，我被邀请来帮忙.我的专业领域不在Oracle.)

Thank you for the responses, so far. At the point in time where the query is running, the data is being generated & tweaked. No other connections should be attempting to access the data. As to why I'm committing so often, during development, I run the query against a subset of the data and the query runs just fine. The table holds about 14 million records and I'm testing against about 100k. The query is fairly complex, and runs in about 5 minutes against this subset. When I move to run it against the whole table, the query runs for over 14 hours and fails to update any records. My theory is that holding that much undo information may be consuming all of the available resources on the development server. And if I make frequent commits, that undo information can be released and reused. Yes, it's slow. But if the query will actually complete, even if it takes all night, then it can be moved to the test server. (And performance tuning can be done at a later date.) The deadline for this has long since passed. (I was brought in to help out after the deadline was missed. And my area of expertise is not with Oracle.)

推荐答案

在循环内部进行提交通常不是一个好主意(因此允许任何工具自动提交).

Committing inside a loop is generally a bad idea (so is allowing any tool to automatically commit).

在循环内进行提交会使编写可重新启动的代码变得更加困难.如果您在3次迭代后遇到错误，该怎么办?现在，您已经成功提交了2个UPDATE语句的结果.大概，您需要然后找出要更新的行并编写代码以撤消更新，或者您必须添加代码来避免尝试为这两个成功的yearid值更新数据.当然有可能.但这涉及编写大量代码来跟踪您的进度，并且通常会使您的代码复杂得多.

Committing inside a loop makes it much harder to write restartable code. What happens if you encounter an error after 3 iterations? You've now successfully committed the results of 2 UPDATE statements. Presumably, you'd need to then either figure out which rows were updated and write code to reverse the updates or you would have to add code that avoids attempting to update the data for those two successful yearid values. That's certainly possible. But it involves writing a bunch of code to track your progress and generally makes your code much more complex.

在循环内提​​交会使代码变慢.提交通常是相当昂贵的操作.因此，循环执行通常是一个坏主意.如果您只有几十次循环迭代，那么问题就不大了.但是，如果您有数百或数千次迭代，则很容易最终花费大量时间进行提交.

Committing inside a loop makes your code much slower. Committing is generally a rather expensive operation. Doing it in a loop, therefore, is generally a bad idea. It's less of a problem if you only have a few dozen loop iterations. But if you have hundreds or thousands of iterations, you can easily end up spending the vast majority of your time committing.

在循环内部进行提交会大大增加引发ORA-01555错误的风险.针对MyTable的查询需要读取一致的数据视图.但是，如果您在循环内提​​交，则会告诉Oracle，您的会话不再需要较早的UNDO数据.如果Oracle碰巧清除了循环的后续迭代所需的UNDO数据，则会出现错误.然后，您又返回了不可重启动的代码，在该代码中，您已经成功进行了N次迭代，但是您不知道已经处理了哪些年或需要处理哪些年.

Committing inside a loop substantially increases the risk that you'll cause an ORA-01555 error. Your query against MyTable needs a read consistent view of the data. If you commit inside the loop, however, you're telling Oracle that your session no longer needs older UNDO data. If Oracle happens to purge UNDO data that you need for a subsequent iteration of the loop, you'll get an error. And then you're back dealing with non-restartable code where you'e successfully gone through N iterations but you don't know which years have been processed or which need to be processed.

在循环内提​​交可能会导致数据一致性问题.例如，如果某个其他会话正在运行报告，则这些报告很容易看到部分更新的数据，这通常意味着数据将不一致.如果3年的数据发生了变化，而其他年份没有变化，则很难理解报告的含义，而人员(或流程)很容易做出错误的决定.

Committing inside a loop can create data consistency issues. If some other session is running reports, for example, it is easy for those reports to see partially updated data which will often mean that the data will be inconsistent. If the data for 3 years has changed but other years have not, it can be very difficult to make sense of the reports and people (or processes) can easily make incorrect decisions.

在循环内提​​交还可以减少代码的可重用性.如果您的代码包含提交(或回滚，而不是到您在块内建立的保存点的回滚)，则任何不希望其事务提交的其他代码都无法调用它.这导致人们尝试在没有事务控制的情况下重新实现您的逻辑，或者错误地违反了事务完整性，这不可避免地导致他们构建引入数据一致性问题的应用程序.

Committing inside a loop also makes your code less reusable. If your code includes commits (or rollbacks other than to a savepoint you established inside the block), it cannot be called by any other piece of code that doesn't want its transaction committed yet. That leads people to try to re-implement your logic without the transaction control or to incorrectly violate transactional integrity which inevitably leads them to build applications that introduce data consistency issues.

这篇关于自动提交打开时是否可以进行明确的提交?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！