Pessimistic Locking

Overview

Pessimistic locking is a strategy for protecting data integrity in applications where multiple users may attempt to update records concurrently. This application provides a simple implementation of pessimistic locking that can be easily used to add this feature to your applications.

Before adopting a pessimistic locking strategy, consider utilizing alternative design patterns - see the Appian Playbook Play on Data Locking Strategies.

Key Features & Functionality

Dependencies:

• Database: MySQL, MariaDB, MS SQL, Oracle
• Plugins: None
• Version: Appian 22.2+

Deployment:

• Run the SQL script for your RDBMS version.
• If your data source is not “jdbc/Appian”, update the constant in the properties file (LCK) Object Locking.properties.
•  Deploy the application (LCK) Object Locking.zip, along with the properties file (LCK) Object Locking.properties.
• For Oracle, deploy the Oracle patch "Oracle Patch - (LCK) Object Locking.zip".

Usage:

• Use the “Get Lock” sub-process to obtain a lock.
• Entity Type and Entity Id can be any identifiers, as long as, when combined, they are unique across the applications using this app. For example, you might choose “ABC_CUSTOMER” as the Entity Type and the relevant customer primary key as the Entity Id when getting a lock for a particular customer for the ABC app. You may use an integer or a string for the Entity Id field.
• Check the sub-process variables to establish whether lock a was successful (pv!lockSuccessful) or, if not, who already has the lock (pv!lockedBy). This information can be fed back to the end user, if there is one, to inform them.
• Use the “Release Lock with Retries” sub-process to release a lock. This model handles the edge case scenario where releasing the lock fails and retries the release to ensure that the lock is eventually released.
• Always use an exception flow to automatically release a lock after a given period. For example, when locking before a UI Task, configure a task timeout after a period that will subsequently release the lock.