In this section we will see how we start a third process run that needs to use a table which remained locked by another completed process run and how the third process run will release that orphan lock.

After failing to acquire a lock on the table already locked, the third process run will check the locks on that resource and release the orphan lock. After the orphan lock is released, the third process run will be able to acquire the lock on the table.

Step 8: start process run P3

We start process P3 using the procedure start_process in package processing.

A new record was inserted in the process runs table with run id 3, process name P3, run status running (0) and end date null.

Step 9: P3 tries to acquire FX lock on resource T2 but fails

We attempt to set a full exclusive application lock on table T2 with run id 3 for process run P3 using the procedure acquire_lock in package application_locking but the output parameter v_lock_id is returned null, meaning that the attempt failed.

No new record was inserted in the applications locks table because the acquire lock failed.

Step 10: P3 checks the locks on resource T2

We check the existing application locks on table T2 using the procedure get_locks_on_resource in package application_locking.

There is a record in the applications locks table with lock id 2 on table T2 of type shared (1) set by process run id 2 (P2).

Step 11: P3 releases the orphan lock on resource T2

We release the orphan locks on table T2 using the procedure release_orphan_locks_on_resource in package processing.

There are no more records in the applications locks table, meaning that the orphan lock was deleted.

Now, process run P3 can acquire the full exclusive application lock on table T2.