PL/SQL Application Locking Framework

PL/SQL Application Locking is a core PL/SQL framework that implements logical locking of objects at application level for applications that use Oracle databases. It can be translated for Microsoft, Postgres or other databases.

Objects

The framework consists in two tables: process runs and application locks and two packages: processing and application locking. You can read more technical details about the objects in the objects page on wiki and in the sections below.

Process Runs Table

The process runs table stores process runs identified uniquely by a run id and having as attributes start time, end time and a run status that can be running, completed successfully or failed.

Application Locks Table

The application locks table is the core of the framework and stores the logical locks set by the client. A lock is identified uniquely by a lock id, is set on a certain object, by a certain process run id and can have three modes: shared, write exclusive or full exclusive.

Processing Package

The processing package has two procedures: start process that starts a process with status running and end process that completes a process run and updates its status to successful or failed.

Application Locking Package

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.

Demo

The demo contains multiple flows of using the PL/SQL Application Locking framework. You can read more technical details about the flows in the demo page on wiki and in the sections below.

Acquire Lock

In the acquire lock page we will see how we start two process runs, 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.

Release Locks

In the release locks page page we will see how we complete one process run successfully and release its locks, while the second process will fail and will not release its locks, leaving orphan locks in the system.

Release Orphan Locks

In the release orphan locks page 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.