Decoding Pessimistic Locking: Ensuring Data Integrity in Concurrent Applications

The previous post discussed the “Optimistic Logic” and how Hibernate implements it.

This new post will discuss the “Pessimistic Logic” technique and how to implement it using Hibernate.

What is the Pessimistic Locking?

Locking is a critical concept in the world of databases and transaction management. As the name suggests, Pessimistic Locking takes a conservative approach to handling concurrent data access. Here, we’ll delve deep into Pessimistic Locking, comparing it to its counterpart, Optimistic Locking.

In a Pessimistic Locking approach, a record (or row) in the database is locked as soon as someone starts modifying it, preventing others from accessing the same record for updates until it is released. This strategy is called ‘pessimistic’ because it assumes the worst—that conflicts will arise—and takes steps to prevent them.

Pessimistic Locking in raw SQL

The way to achieve Pessimistic Locking in SQL can differ based on the RDBMS used. Let’s delve into both MySQL and PostgreSQL for a clearer understanding.

Pessimistic Write Lock

A Pessimistic Write Lock is used when a transaction intends to update or delete data and wants to ensure that no other transactions can read, update, or delete the same data until it’s done. This lock type is crucial for maintaining data integrity during critical update operations.

MySQL

START TRANSACTION;
SELECT * FROM table_name WHERE condition FOR UPDATE;
-- Update or delete operations here
COMMIT;

In MySQL, a Pessimistic Write Lock is acquired using the FOR UPDATE clause in a SELECT statement. When a row is locked with FOR UPDATE, other transactions can neither modify nor acquire a write lock on the same row until the lock is released. However, they can still read the data unless they attempt to acquire a write lock or use a locking read themselves.

PostgreSQL

BEGIN;
SELECT * FROM table_name WHERE condition FOR UPDATE;
-- Update or delete operations here
COMMIT;

PostgreSQL also uses the FOR UPDATE clause to acquire a Pessimistic Write Lock. Similar to MySQL, this lock prevents other transactions from acquiring read locks (with FOR SHARE) or write locks (with FOR UPDATE) on the locked rows.

Pessimistic Read Lock

A Pessimistic Read Lock is used when a transaction needs to read data and ensures that other transactions cannot modify the data until it is complete. However, other transactions can still read the locked data, unlike write locks.

MySQL

START TRANSACTION;
SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE;
-- Further read operations
COMMIT;

In MySQL, the LOCK IN SHARE MODE clause is used to implement a Pessimistic Read Lock. This allows multiple transactions to read the same data without interfering with each other while preventing any of them from modifying the data.

PostgreSQL

BEGIN;
SELECT * FROM table_name WHERE condition FOR SHARE;
-- Further read operations
COMMIT;

PostgreSQL uses the FOR SHARE clause for acquiring a Pessimistic Read Lock. This lock allows other transactions to read the locked rows but prevents them from obtaining a write lock on these rows.

Choosing between these locks (or combining them) depends on your specific application requirements, including the level of data integrity and concurrency needed. It’s also important to consider the performance implications of locking strategies, as excessive Locking can lead to bottlenecks and reduced application throughput.

Concurrency Effects of Pessimistic Locking Strategies

Creating a table illustrating what actions other transactions can perform when a transaction has acquired a Pessimistic Read or Write lock provides a clear, comparative view of the concurrency behaviours permitted by these locking strategies. This understanding is crucial for designing applications that require transactional integrity without sacrificing performance. The table below generalizes the actions based on typical SQL database behaviours, such as those found in PostgreSQL and MySQL:

This table offers a generalized view, and the exact behaviour can vary based on the database system, configuration, and transaction isolation levels. Always refer to your specific database’s documentation for precise behaviour.

Pessimistic Locking in Hibernate

Hibernate offers various LockMode options to handle different levels of data locking, which can be crucial for maintaining data integrity in your application. Understanding each lock mode and its use case is essential for practical Hibernate usage. Let’s explore these modes with more clarity and provide examples where applicable.

LockMode.WRITE

• Description: This lock mode is automatically acquired by Hibernate when it updates or inserts a row in the database. It’s more of an internal mechanism than something you’d manually set in your code.
• Example Usage: There’s no need to use it explicitly LockMode.WRITE in your code, as Hibernate handles this automatically when performing insert or update operations.

LockMode.UPGRADE

• Description: This mode is used to explicitly acquire a Pessimistic Write Lock on a database row, equivalent to a SELECT ... FOR UPDATE SQL statement. It’s useful when you want to lock a row for updates and ensure no other transactions can be updated or deleted until your current transaction is completed.
• Example Usage:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

Book book = session.get(Book.class, 1, LockMode.UPGRADE);
// Modifications to the book entity

tx.commit();
session.close();

LockMode.UPGRADE_NOWAIT

• Description: Similar to UPGRADE, but it tells the database to throw an error immediately if the requested lock cannot be obtained rather than waiting for the lock to become available. This mode prevents your application from hanging in scenarios where lock contention is high. Note that this mode is specific to databases that support the NOWAIT directive, such as Oracle.
• Example Usage: (Assuming the database supports NOWAIT)

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

try {
    Book book = session.get(Book.class, 1, LockMode.UPGRADE_NOWAIT);
    // Modifications to the book entity
} catch (PessimisticLockException e) {
    // Handle lock acquisition failure
}

tx.commit();
session.close();

LockMode.READ

• Description: This lock mode is acquired automatically by Hibernate when reading data under the Repeatable Read or Serializable isolation levels to ensure that the read data remains consistent throughout the transaction. The user can explicitly request it.
• Example Usage: Explicit usage might look like this, though relying on Hibernate’s automatic handling under the appropriate isolation levels is more common.

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

Book book = session.get(Book.class, 1, LockMode.READ);
// Read operations on the book entity

tx.commit();
session.close();

LockMode.NONE

• Description: Indicates the absence of a lock. All entities are switched to this lock mode at the end of a transaction. Additionally, entities associated with the session via update() or saveOrUpdate() start in this lock mode.
• Example Usage: Generally, you don’t need to set it explicitly with LockMode.NONE, as it’s the default state. However, if you need to downgrade a lock for some reason, you can do so:

session.lock(book, LockMode.NONE);

Hibernate LockMode and SQL Comparison

Understanding and correctly applying Hibernate’s LockMode options can significantly influence your application’s consistency, performance, and reliability. While LockMode.WRITE and LockMode.READ are generally managed automatically based on your transaction and isolation settings, LockMode.UPGRADE and LockMode.UPGRADE_NOWAIT can be explicitly used to manage locking behaviour for specific entities, offering a finer degree of control over concurrent data access.

Creating a table to compare Hibernate lock modes with native SQL’s locking clauses (like FOR UPDATE and FOR SHARE) offers a clear understanding of the correspondence between high-level ORM behaviour and underlying database operations. This comparison assumes a general understanding that database-specific SQL syntax might vary slightly, but the concepts remain broadly applicable across RDBMS like PostgreSQL, MySQL, Oracle, etc.

This table serves as a guideline to understand how Hibernate’s LockMode options translate to SQL locking mechanisms, bridging object-relational mapping concepts and database-level locking strategies.

Advantages and Disadvantages of Pessimistic Locking

Pessimistic vs. Optimistic Locking

Optimistic Locking doesn’t lock records immediately. Instead, it checks for conflicts when attempting to commit. A conflict arises if another transaction has updated the same data, typically resulting in an exception.

Conclusion

Choosing between Pessimistic and Optimistic Locking requires carefully evaluating your specific use case, system requirements, and potential for data contention. Pessimistic Locking offers a straightforward approach in scenarios with known contention, while optimistic Locking shines in highly concurrent systems due to its scalability. Proper understanding and testing under realistic conditions will guide you to the right choice for your application.

Thanks for reading. Stay tuned!

Learn. Grow. Teach.