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

Question

如果我在存储过程中使用 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;

Answer 1

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

也许您听说过经常提交是一件好事。这是一个错误的神话，这是totally wrong .在 Oracle 中情况恰恰相反:提交涉及额外的工作，因此您应该只在所有工作完成后才提交，而绝不能之前提交。

此外，从逻辑的角度来看，如果可以从头开始而不是事半功倍，那么从错误中恢复起来会容易得难以想象。

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

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

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

---

根据您的代码摘录进行更新:您的代码中有很多地方需要更正(我想它不是直接的生产代码，但仍然是):

• 永远不会引发异常:仅隐式 SELECT INTO可生产NO_DATA_FOUND .
• SQL%ROWCOUNT如果前面的语句是 SELECT，则为 NULL .
• 你可以使用 c1%ROWCOUNT ，但这只会返回获取的行数:0在初始 open 之后.
• 我主要使用 FOR UPDATE NOWAIT这样两个 session 就不会互相阻塞。如果你只使用 FOR UPDATE , 你不妨使用一个 UPDATE并且不使用 SELECT事先。
• 这是一个偏好问题，但返回码容易出错并且 exceptions are generally preferred .让错误传播。为什么有人会在 id 上调用此函数那不存在？这可能是调用应用程序/过程中的错误，因此您不应捕获它。

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

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;