Acquire Lock - lucienlazar/plsql-application-locking GitHub Wiki

In this section we will see how we start two process runs and how we set different application locks on two tables for these two processes.

We will see how only one process can set a write exclusive application lock on a table at a time to write in it and how two parallel processes can set shared application locks on the same table at the same time to read from it.

Step 1: start process run P1

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

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

Step 2: process run P1 acquires WX lock on resource T1

We set a write exclusive application lock on table T1 with run id 1 for process run P1 using the procedure acquire_lock in package application_locking.

A new record was inserted in the applications locks table with lock id 1 on table T1 of type write exclusive (2) set by process run id 1 (P1).

Step 3: process run P1 acquires S lock on resource T2

We set a shared application lock on table T2 for process run P1 using the procedure acquire_lock in package application_locking.

A new record was inserted in the applications locks table with lock id 2 on table T2 of type shared (1) set by process run id 1 (P1).

Step 4: start process run P2

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

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

Step 5: process run P2 acquires S lock on resource T2

We set a shared application lock on table T2 for process run P2 using the procedure acquire_lock in package application_locking.

A new record was inserted in the applications locks table with lock id 3 on table T2 of type shared (1) set by process run id 2 (P2).