oracle 在过程中使用 FOR UPDATE 时应该何时提交？

When should I commit when using FOR UPDATE in a procedure?

提问by user1

If I use a FOR UPDATE clause in a stored procedure when should I "commit"? After closing the opened cursor or before closing the opened cursor? Below is the procedure im using, am i doing it in correct way?

如果我在存储过程中使用 FOR UPDATE 子句，我应该什么时候“提交”？关闭打开的游标之后还是关闭打开的游标之前？以下是我使用的程序，我是否以正确的方式进行？

CREATE OR REPLACE PROCEDURE Proc_UpdateCSClientCount(inMerid     IN  VARCHAR2,
                                                     outCliCount  OUT NUMBER,
                                                     outretvalue  OUT NUMBER)
AS
   CURSOR c1 IS
      SELECT CLIENT_COUNT
        FROM OP_TMER_CONF_PARENT
       WHERE MER_ID = inMerid
      FOR UPDATE OF CLIENT_COUNT;
BEGIN
   OPEN c1;
   IF SQL%ROWCOUNT = 1 THEN
      FETCH c1 INTO outCliCount;
      outCliCount := outCliCount + 1;
      UPDATE OP_TMER_CONF_PARENT
         SET CLIENT_COUNT = outCliCount
       WHERE CURRENT OF c1;
   END IF;
   outretvalue := 0;
   CLOSE c1;
   COMMIT;
EXCEPTION
   WHEN no_data_found THEN
      outretvalue := -1;
END;

回答by Vincent Malgrat

You should commit at the end of the transaction. I doubt you can find a reasonable case where the end of the transaction is in the middle of a FOR UPDATEloop.

您应该在事务结束时提交。我怀疑你能找到一个合理的情况，即事务的结束处于FOR UPDATE循环的中间。

Perhaps you have heard that committing frequently is a good thing. This is a false myth, this is totally wrong. The opposite is true in Oracle: committing involves extra work and therefore you should only commit when all the work is done, never before.

也许你听说过经常提交是一件好事。这是一个错误的神话，这是完全错误的。在 Oracle 中正好相反：提交涉及额外的工作，因此您应该只在所有工作完成后提交，而不要在此之前提交。

Furthermore, from a logical point of view, it is unimaginably easier to recover from an error if you can start from scratch instead of having half the work done.

此外，从逻辑的角度来看，如果您可以从头开始而不是完成一半的工作，那么从错误中恢复是难以想象的容易。

IMO, committing in a procedure should be excessively rare. The calling application should be the one that makes the necessary checks and finally decides if the data should be committed or not.

IMO，在程序中提交应该非常罕见。调用应用程序应该是进行必要检查并最终决定是否应该提交数据的应用程序。

In conclusion, you can't commit accross a FOR UPDATEloop (it will produce an ORA-01002: fetch out of sequence) and that is a good thing. Whenever you find yourself committing accross a normal loop, you should ask yourself if the commit is really necessary -- most likely it isn't.

总之，您不能跨FOR UPDATE循环提交（它会产生ORA-01002: fetch out of sequence），这是一件好事。每当您发现自己在正常循环中提交时，您应该问自己提交是否真的有必要——很可能不是。

If you really need to commit andonly fetch once, it doesn't matter if you commit before or after closing the cursor.

如果你真的需要提交并且只获取一次，那么在关闭游标之前或之后提交都没有关系。

---

---

Update following your code excerpt: there are many things that need correcting in your code (I suppose it is not directly production code but still):

按照您的代码摘录更新：您的代码中有很多需要更正的地方（我想它不是直接的生产代码，但仍然是）：

• The exception will never be raised: only implicit SELECT INTOcan produce NO_DATA_FOUND.
• SQL%ROWCOUNTis NULL if the preceding statement is a SELECT.
• You could use c1%ROWCOUNT, but this will only return the number of rows fetched: 0after the initial open.
• I mainly use FOR UPDATE NOWAITso that two sessions never block each other. If you only use FOR UPDATE, you might as well use a single UPDATEand not use SELECTbeforehand.
• This is a matter of preference, but return codes are prone to errors and exceptions are generally preferred. Let the error propagate. Why would anyone call this function on an idthat doesn't exist? This is probablya bug in the calling app/procedure so you should not catch it.

• 永远不会引发异常：只有隐式SELECT INTO可以产生NO_DATA_FOUND。
• SQL%ROWCOUNT如果前面的语句是 NULL，则为 NULL SELECT。
• 您可以使用c1%ROWCOUNT，但这只会返回获取的行数：0在初始open.
• 我主要使用FOR UPDATE NOWAIT这样两个会话永远不会相互阻塞。如果你只用FOR UPDATE，你还不如用单个UPDATE而不是SELECT预先使用。
• 这是一个偏好问题，但返回代码容易出错，通常首选异常。让错误传播。为什么有人会在一个id不存在的函数上调用这个函数？这可能是调用应用程序/过程中的错误，因此您不应捕获它。

So you could rewrite your procedure like this:

所以你可以像这样重写你的程序：

CREATE OR REPLACE PROCEDURE Proc_UpdateCSClientCount(inMerid     IN  VARCHAR2, 
                                                     outCliCount OUT NUMBER) AS
BEGIN
   -- lock the row, an exception will be raised if this row is locked
   SELECT CLIENT_COUNT + 1
     INTO outCliCount
     FROM OP_TMER_CONF_PARENT
    WHERE MER_ID = inMerid
   FOR UPDATE OF CLIENT_COUNT NOWAIT;
   -- update the row
   UPDATE OP_TMER_CONF_PARENT
      SET CLIENT_COUNT = CLIENT_COUNT + 1
    WHERE MER_ID = inMerid;
END;

回答by Florin Ghita

From Oracle Documentation:

从Oracle 文档：

All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction.Since the rows are no longer locked, you cannot fetch from a FOR UPDATE cursor after a commit.

当您打开游标时，所有行都被锁定，而不是在获取它们时锁定。 当您提交或回滚事务时，行将被解锁。由于行不再被锁定，因此您无法在提交后从 FOR UPDATE 游标中获取。

That's important. If you've done the task(finished fetch) is not important if you commit before or after closing the cursor.

这很重要。如果您在关闭游标之前或之后提交任务（完成提取）并不重要。

But if commit between fetchs is needed, as a workaround, use update with rowid, not where current of. Example from doc:

但是，如果需要在获取之间进行提交，作为一种解决方法，请使用带有 rowid 的更新，而不是where current of. 来自文档的示例：

DECLARE
   CURSOR c1 IS SELECT last_name, job_id, rowid FROM employees;
   my_lastname   employees.last_name%TYPE;
   my_jobid      employees.job_id%TYPE;
   my_rowid      UROWID;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_lastname, my_jobid, my_rowid;
      EXIT WHEN c1%NOTFOUND;
      UPDATE employees SET salary = salary * 1.02 WHERE rowid = my_rowid;
      -- this mimics WHERE CURRENT OF c1
      COMMIT;
   END LOOP;
   CLOSE c1;
END;
/

UPDATE(after edit of question): You can do that in a single sql, without cursor.

更新（编辑问题后）：您可以在单个 sql 中执行此操作，无需光标。

UPDATE OP_TMER_CONF_PARENT 
set CLIENT_COUNT = CLIENT_COUNT +1 
where MER_ID = inMerid;

UPDATE2. The code should be updated as following in order to work:

更新2。代码应更新如下才能工作：

...
open C1;
FETCH C1 into OUTCLICOUNT;
--dbms_output.put_line(' count:'||c1%rowcount);
IF c1%rowcount = 1 THEN
      outCliCount := outCliCount + 1;
...

That is: fetch should be done before counting the rows affected, and rows affected is c1%rowcount, not sql%rowcount. If you want to know if a row is updated or not, you should put an else to if and assign a special value to the outretvalue parameter.

也就是说：应该在计算受影响的行数之前完成 fetch，受影响的行数是c1%rowcount，而不是sql%rowcount。如果您想知道某行是否被更新，您应该在 if 中添加一个 else 并为 outretvalue 参数分配一个特殊值。

回答by Petr Pribyl

If you commit before closing cursor and after that you try to fetch again, you get an INVALID_CURSOR exception. I suggest to commit after closing cursor.

如果您在关闭游标之前提交然后再次尝试获取，则会收到 INVALID_CURSOR 异常。我建议在关闭游标后提交。