DBMS_LOCK revisited

In my earlier post regarding DBMS_LOCK I failed to note an important point about the ALLOCATE_UNIQUE routine; this routine commits the current transaction:

SQL>CREATE TABLE t1 (id NUMBER)
  2  /

Table created.

SQL>INSERT INTO t1 VALUES (1)
  2  /

1 row created.

SQL>DECLARE
  2     l_job_identifier        VARCHAR2(128) := 'job_101';
  3     l_lock_handle           VARCHAR2(128);
  4  BEGIN
  5     dbms_lock.allocate_unique (lockname        => l_job_identifier
  6                               ,lockhandle      => l_lock_handle
  7                               ,expiration_secs => 864000);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>ROLLBACK
  2  /

Rollback complete.

SQL>SELECT *
  2  FROM   t1
  3  /

        ID
----------
         1

The solution to this problem is to simply wrap the call to ALLOCATE_UNIQUE in an autonomous transaction:

SQL>CREATE TABLE t1 (id NUMBER)
  2  /

Table created.

SQL>INSERT INTO t1 VALUES (1)
  2  /

1 row created.

SQL>DECLARE
  2     l_job_identifier        VARCHAR2(128) := 'job_101';
  3     l_lock_handle           VARCHAR2(128);
  4
  5     PROCEDURE alloc_unique (p_lockname        IN  VARCHAR2
  6                            ,p_lockhandle      OUT VARCHAR2
  7                            ,p_expiration_secs IN  NUMBER DEFAULT 864000)
  8     AS
  9        PRAGMA AUTONOMOUS_TRANSACTION;
 10     BEGIN
 11        dbms_lock.allocate_unique (lockname        => p_lockname
 12                                  ,lockhandle      => p_lockhandle
 13                                  ,expiration_secs => p_expiration_secs);
 14        COMMIT;
 15     END alloc_unique;
 16  BEGIN
 17     alloc_unique (p_lockname        => l_job_identifier
 18                  ,p_lockhandle      => l_lock_handle
 19                  ,p_expiration_secs => 864000);
 20  END;
 21  /

PL/SQL procedure successfully completed.

SQL>ROLLBACK
  2  /

Rollback complete.

SQL>SELECT *
  2  FROM   t1
  3  /

no rows selected

Advertisement