我可以使用Oracle PL/SQL同时持有两个锁吗？

Question

我的应用程序当前的锁概念假设获得2个锁并执行代码。但我总是得到一个错误，当我试图释放第一个锁把手。有没有办法做到这一点，或者我使用dbms_lock一次获得两个锁是错误的吗？

诚挚的问候!

DECLARE
  l_handle_1 VARCHAR2(128);
  l_handle_2 VARCHAR2(128);
  l_result NUMBER;
BEGIN

    -- >>> LOCK1
    dbms_lock.allocate_unique('lock_1', l_handle_1);
    l_result := dbms_lock.request(l_handle_1, dbms_lock.x_mode, 10, true);
    BEGIN

      -- >>> LOCK2
      dbms_lock.allocate_unique('lock_2', l_handle_2);
      l_result := dbms_lock.request(l_handle_2, dbms_lock.x_mode, 10, true);
      BEGIN

        /*
         * PLSQL-Code with both locks held
         */

        -- LOCK-2 release
        l_result := dbms_lock.release(l_handle_2);
        IF (l_result > 0) THEN
          dbms_output.put_line('Fail 2');
        END IF;

        -- LOCK-1 release
        l_result := dbms_lock.release(l_handle_1);
        IF (l_result > 0) THEN
          dbms_output.put_line('Fail 1');
        END IF;

      EXCEPTION
        WHEN OTHERS THEN
          l_result := dbms_lock.release(l_handle_2);
          IF (l_result > 0) THEN
            dbms_output.put_line('Fail 3');
          END IF;
          RAISE;          
      END;

    EXCEPTION
      WHEN OTHERS THEN
        l_result := dbms_lock.release(l_handle_1);
        IF (l_result > 0) THEN
          dbms_output.put_line('Fail 4');
        END IF;
        RAISE;
    END;
END;

Answer 1

对于第一个锁句柄，使用release_on_commit = TRUE调用dbms_lock.request，然后调用allocate_unique。allocate_unique执行commit，因此释放第一个锁。

如果您按如下方式更改代码，则不会出现错误：

DECLARE
  l_handle_1 VARCHAR2(128);
  l_handle_2 VARCHAR2(128);
  l_result NUMBER;
BEGIN


    dbms_lock.allocate_unique('lock_1', l_handle_1);
    dbms_lock.allocate_unique('lock_2', l_handle_2);

    -- >>> LOCK1
    l_result := dbms_lock.request(l_handle_1, dbms_lock.x_mode, 10, true);
    BEGIN

      -- >>> LOCK2
      -- dbms_lock.allocate_unique('lock_2', l_handle_2);
      l_result := dbms_lock.request(l_handle_2, dbms_lock.x_mode, 10, true);
      BEGIN

        /*
         * PLSQL-Code with both locks held
         */

        -- LOCK-2 release
        l_result := dbms_lock.release(l_handle_2);
        IF (l_result > 0) THEN
          dbms_output.put_line('Fail 2');
        END IF;

        -- LOCK-1 release
        l_result := dbms_lock.release(l_handle_1);
        IF (l_result > 0) THEN
          dbms_output.put_line('Fail 1');
        END IF;

      EXCEPTION
        WHEN OTHERS THEN
          l_result := dbms_lock.release(l_handle_2);
          IF (l_result > 0) THEN
            dbms_output.put_line('Fail 3');
          END IF;
          RAISE;          
      END;

    EXCEPTION
      WHEN OTHERS THEN
        l_result := dbms_lock.release(l_handle_1);
        IF (l_result > 0) THEN
          dbms_output.put_line('Fail 4');
        END IF;
        RAISE;
    END;
END;