The application locking package contains the logic of the framework encapsulated in four procedures: acquire lock that adds a logical lock to a certain object by a certain run id, respecting an algorithm that allows upgrading certain lock modes, release locks that deletes the locks at the end of a process, get locks that lists the locks on a certain object and release orphan locks that deletes orphan locks on a certain resource left by completed process runs.

Procedure acquire_lock

Description

Procedure acquire_lock tries to acquire a certain type of lock on a certain resource. If successful, it will return the internal id of the lock in the output parameter. If lock cannot be acquired the output parameter will be null.

Parameters

pi_resource_name in varchar2 – name of the resource on which the lock will be set

pi_lock_mode in integer – lock mode: 1 = shared, 2 = write exclusive, 3 = full exclusive

pi_run_id in integer – internal id of the process run that sets the lock

po_lock_id out integer – internal id of the generated lock

Call example

declare l integer; begin acquire_lock('T1', 1, 1, l); end;

Acquire lock algorithm

You can read more details about the algorithm used for acquire lock on this page.

Procedure release_locks_for_run

Description

Procedure release_locks_for_run deletes the locks for a certain process run id. It is called after the process is completed to clean up its locks. It returns in output parameters the number of deleted locks and the number of locks for that process run id that remained.

Parameters

pi_run_id in integer – internal id of the process run that set the locks

po_released_locks out integer – number of deleted locks

po_remaining_locks out integer – number of remaining locks

Call example

declare a integer; b integer; begin release_locks_for_run(1, a, b); end;

Procedure get_locks_on_resource

Description

Procedure get_locks_on_resource returns a list of application locks for a certain resource. It is called after a process attempts and fails to acquire a lock on a resource. It returns a list of application locks and the process runs that set them, including their statuses.

Parameters

pi_resource_name in varchar2 – name of the resource to be checked for locks

po_locks out sys_refcursor – list of locks containing columns: lock id, lock mode, run id and run status.

Call example

declare c sys_refcursor; begin get_locks_on_resource('T1', c); end;

Procedure release_orphan_locks_on_resource

Description

Procedure release_orphan_locks_on_resource deletes the locks left by completed processes for a certain resource. It is called in case a process attempts to acquire a lock on a resource, but the resource is being locked by a process that is already completed and, because of unexpected issues, could not clean-up its locks.

Parameters

pi_resource_name in varchar2 – name of the resource to be checked for locks

po_released_locks out integer – number of locks that were removed

po_remaining_locks out integer – number of locks that remained.

Call example

declare r integer; l integer; begin release_orphan_locks_on_resource('T1', r, l); end;