Migrating a database from Oracle to PostgreSQL comes with its set of challenges, and one of them is the migration of user-defined locks, which are essential for managing application-level concurrency. Oracle’s DBMS_LOCK package offers a mechanism for developers to implement custom locking strategies. However, moving to PostgreSQL presents challenges, as advisory locks in PostgreSQL have a different functionality. In response to this challenge, we are excited to introduce pg_dbms_lock, a powerful PostgreSQL extension designed to simplify the migration process by providing seamless compatibility with Oracle’s DBMS_LOCK package.
This extension emulates Oracle’s user-defined locks behavior in PostgreSQL, offering a consistent interface for managing advisory locks. With procedures like ALLOCATE_UNIQUE(), REQUEST(), RELEASE(), and SLEEP(), pg_dbms_lock ensures a smooth transition, enabling developers to maintain their existing lock management logic while embracing the benefits of PostgreSQL.
Through PG_DBMS_LOCK for PostgreSQL, you will be able to use the same syntax as Oracle DBMS_LOCK and simplify your migrations to PostgreSQL.
What are User-defined locks in Oracle?
User-defined locks are created and managed at the application level by developers to coordinate and control access to shared resources in a multi-user environment. In Oracle, DBMS_LOCK is a built-in PL/SQL package that helps us manage application-level locks. The package allows developers to implement custom locking mechanisms to prevent concurrent access or conflicting operations on specific data or application resources. These locks are not enforced in any meaningful way by the database, it’s up to application code to give them meaning.
Please refer DBMS_LOCK to learn about Oracle user-defined locks.
Do we have User-defined locks in PostgreSQL?
PostgreSQL provides advisory locks, which are user-defined locks similar to Oracle user-defined locks. Advisory locks allow you to manage application-level locking for specific resources using numeric keys. If we are migrating from Oracle to Postgres, we need to rewrite our application lock management code in Oracle to use advisory locks in PostgreSQL.
Please refer Advisory Locks and the available functions to get to know about advisory locks in postgres.
How to migrate Oracle User-defined locks to PostgreSQL?
Postgres does not provide the exact same set of lock modes which are present in Oracle’s DBMS_LOCK. PostgreSQL advisory locks are more straight-forward and offer only shared or exclusive lock modes. Also, there is no specific option for converting the lock as found in DBMS_LOCK. Additionally, PostgreSQL’s advisory locks don’t offer a built-in “sleep” function. In Oracle, if you choose to identify locks by name, you can use ALLOCATE_UNIQUE to generate a unique lock identification number for these named locks. You can use this number to request and release the lock. But PostgreSQL’s advisory locks do not have built-in support for named locks. Advisory locks are acquired using numeric identifiers, and there is no direct way to assign a name to them. There are also few other significant differences in Postgres advisory locks when compared to Oracle DBMS_LOCK.
Introducing pg_dbms_lock for Oracle DBMS_LOCK compatibility in PostgreSQL
We are thrilled to introduce pg_dbms_lock, a powerful PostgreSQL extension designed to emulate the behavior of Oracle’s DBMS_LOCK package, making advisory lock management easy. We would like to specially thank our CTO, Gilles Darold, for his contributions to this extension.
pg_dbms_lock leverages PostgreSQL advisory locks to replicate the functionality of Oracle DBMS_LOCK, including lock mode (exclusive or shared), timeout, and on-commit release settings. This extension fills the gap for users transitioning from Oracle to PostgreSQL, providing a consistent and intuitive interface for managing advisory locks.
Here is a list of routines included in the extension. These are similar to the routines available with Oracle DBMS_LOCK Package with same name and signature.
-
ALLOCATE_UNIQUE():
ALLOCATE_UNIQUE() in pg_dbms_lock generates a unique lock ID for a specified lock name, facilitating coordinated lock use among applications. The lock handle returned is crucial for subsequent REQUEST() and RELEASE() calls within the same session, ensuring seamless lock management. -
REQUEST():
The REQUEST() function in pg_dbms_lock allows users to request a lock with a specified mode, accepting either a user-defined lock identifier or the lock handle from ALLOCATE_UNIQUE(). Supporting exclusive and shared modes, it offers flexibility with optional parameters like timeout and automatic release on commit. Return values provide insights into success, timeouts, or errors. -
RELEASE():
The RELEASE() function in pg_dbms_lock allows explicit release of a previously acquired lock using the REQUEST() function. While locks are automatically released at session end, RELEASE() supports manual release and is adaptable to user-assigned lock identifiers or lock handles from ALLOCATE_UNIQUE(). Return values indicate success, parameter errors, or situations where the lock specified by id or lockhandle is not owned. -
SLEEP():
The SLEEP() procedure in pg_dbms_lock temporarily suspends the session for a specified duration, enhancing the flexibility of time-based operations
Use Cases
-
Exclusive Access
-
Ensure exclusive access to external devices or services, such as printers, by employing user locks managed by pg_dbms_lock.
-
-
Parallelized Applications
-
Coordinate and synchronize parallelized applications seamlessly, preventing conflicts and ensuring smooth execution.
-
-
Scheduled Execution
-
Disable or enable program execution at specific times, providing control over when certain tasks are performed.
-
-
Transaction Monitoring
-
Detect whether a session has ended with a COMMIT or ROLLBACK, allowing for informed decision-making based on transaction status.
-
Conclusion
Through this article, we have discussed how we can emulate the Oracle’s DBMS_LOCK functionality in PostgreSQL using the PG_DBMS_LOCK extension. Similarly, the team at HexaCluster has contributed to several tens of extensions to provide Oracle and SQL Server compatibility to PostgreSQL. If you are looking for options to migrate your Oracle databases seamlessly to PostgreSQL, contact us today and have a chat with our technical team. In addition to PostgreSQL services, we do offer Machine Learning services. You may also use the contact form below to reach out to us.
Subscribe to our Newsletters and stay tuned for more interesting topics.
[…] PostgreSQL extension to manage advisory locks in a way compatible with the Oracle DBMS_LOCK package. This extension […]