【PL/SQL】マルチセッション環境での排他・同時実行制御の高度設計

-- ロック中ならすぐORA-00054にする
SELECT id
FROM task_queue
WHERE id = :id
FOR UPDATE NOWAIT;

DECLARE
  CURSOR c_task IS
    SELECT id
    FROM task_queue
    WHERE status = 'READY'
    ORDER BY priority DESC, id
    FOR UPDATE SKIP LOCKED;

  v_id task_queue.id%TYPE;
BEGIN
  OPEN c_task;
  FETCH c_task INTO v_id;

  IF c_task%FOUND THEN
    UPDATE task_queue
       SET status = 'RUNNING',
           picked_at = SYSTIMESTAMP
     WHERE CURRENT OF c_task;
  END IF;

  CLOSE c_task;
  COMMIT;
END;
/

ALTER TABLE customers ADD (row_ver NUMBER DEFAULT 0 NOT NULL);

UPDATE customers
   SET email = :new_email,
       row_ver = row_ver + 1
 WHERE id = :id
   AND row_ver = :expected_ver;

IF SQL%ROWCOUNT = 0 THEN
  RAISE_APPLICATION_ERROR(-20001, 'version conflict');
END IF;

CREATE TABLE key_locks (
  lock_key VARCHAR2(200) PRIMARY KEY
);

BEGIN
  INSERT INTO key_locks(lock_key) VALUES (:key);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(-20010, 'key busy');
END;

-- クリティカルセクション

DELETE FROM key_locks WHERE lock_key = :key;
COMMIT;

DECLARE
  v_handle VARCHAR2(128);
  v_result PLS_INTEGER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('JOB:DAILY_AGG', v_handle);

  v_result := DBMS_LOCK.REQUEST(
                lockhandle        => v_handle,
                lockmode          => DBMS_LOCK.X_MODE,
                timeout           => 0,
                release_on_commit => TRUE);

  CASE v_result
    WHEN 0 THEN
      NULL; -- success
    WHEN 1 THEN
      RAISE_APPLICATION_ERROR(-20000, 'lock timeout');
    WHEN 2 THEN
      RAISE_APPLICATION_ERROR(-20001, 'lock deadlock');
    WHEN 3 THEN
      RAISE_APPLICATION_ERROR(-20002, 'parameter error');
    WHEN 4 THEN
      RAISE_APPLICATION_ERROR(-20003, 'already own lock');
    ELSE
      RAISE_APPLICATION_ERROR(-20004, 'illegal lock handle');
  END CASE;

  -- クリティカルセクション
  COMMIT; -- release_on_commit=TRUEならここで解放
END;
/

DECLARE
  v_count PLS_INTEGER := 0;
BEGIN
  FOR r IN (
    SELECT id
    FROM targets
    WHERE processed = 'N'
    ORDER BY id
  ) LOOP
    SAVEPOINT sp_row;
    BEGIN
      UPDATE targets
         SET processed = 'Y',
             processed_at = SYSTIMESTAMP
       WHERE id = r.id;

      v_count := v_count + 1;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK TO sp_row;
    END;

    IF v_count >= 5000 THEN
      COMMIT;
      v_count := 0;
    END IF;
  END LOOP;

  COMMIT;
END;
/

-- 読み取り専用の一貫したスナップショット
SET TRANSACTION READ ONLY;

SELECT ...
FROM report_source;

COMMIT;

INSERT INTO outbox(event_key, event_type, payload, created_at)
VALUES (:event_key, 'ORDER_CREATED', :json_payload, SYSTIMESTAMP);

-- 配送ワーカー側
SELECT id
FROM outbox
WHERE sent_at IS NULL
ORDER BY id
FOR UPDATE SKIP LOCKED;

DECLARE
  resource_busy       EXCEPTION;
  deadlock_detected   EXCEPTION;
  cannot_serialize    EXCEPTION;

  PRAGMA EXCEPTION_INIT(resource_busy,     -54);   -- ORA-00054
  PRAGMA EXCEPTION_INIT(deadlock_detected, -60);   -- ORA-00060
  PRAGMA EXCEPTION_INIT(cannot_serialize,  -8177); -- ORA-08177

  v_try PLS_INTEGER := 0;
BEGIN
  LOOP
    v_try := v_try + 1;
    BEGIN
      -- 衝突しやすい更新
      UPDATE targets
         SET processed = 'Y'
       WHERE id = :id;

      COMMIT;
      EXIT;
    EXCEPTION
      WHEN resource_busy OR deadlock_detected OR cannot_serialize THEN
        ROLLBACK;
        IF v_try < 5 THEN
          DBMS_LOCK.SLEEP(POWER(2, v_try) * 0.2);
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
END;
/

-- Concurrency待機中のセッション
SELECT sid,
       serial#,
       username,
       event,
       seconds_in_wait,
       blocking_session
FROM v$session
WHERE state = 'WAITING'
  AND wait_class = 'Concurrency';

-- ロック待ちと保有者の対応
SELECT waiter.sid  AS waiter_sid,
       holder.sid  AS holder_sid,
       waiter.type,
       waiter.id1,
       waiter.id2
FROM v$lock waiter
JOIN v$lock holder
  ON waiter.type = holder.type
 AND waiter.id1  = holder.id1
 AND waiter.id2  = holder.id2
WHERE waiter.request > 0
  AND holder.block = 1;