Discussing two popular data locking methods: Record Locking and Optimistic Locking

Problem

Locking records while reading or updating data is a relatively common occurrence. A classic example for this case is a money transfer problem between A and B: While A is transferring an amount x to B, we need to check the balance of A, subtract x dollars from their account, and then add x dollars to B's account. It is essential to ensure that all these debit and credit processes are successful for the transaction to be completed. If an error occurs while deducting money from A without adding it to B or vice versa, it would result in a significant accuracy issue for the program.

The typical order to execute the queries in this case with PostgreSQL would be:

BEGIN TRANSACTION;

SELECT balance FROM accounts WHERE user = 'A';

// if a > x then...  

UPDATE accounts SET balance = balance - x WHERE user = 'A';
UPDATE accounts SET balance = balance + x WHERE user = 'B';

COMMIT;

Most of us know how to solve this problem using transactions. It involves initiating a transaction and ensuring that both the debit and credit processes are successful before considering the money transfer as a successful transaction and saving it in the database. If, unfortunately, one of the processes fails, nobody would be deducted or credited with money illogically.

However, in cases where A can perform multiple money transfer operations almost simultaneously, another issue arises. This issue relates to the first SELECT statement used to check the balance. It is highly possible that multiple SELECT statements return the balance of A before checking it. In this case, all those SELECT statements would satisfy the condition a > x and what if they all proceed with the subsequent UPDATE statements?

To address this issue, there are several approaches. One of them is to lock the record being selected using the SELECT FOR UPDATE query. If a subsequent query encounters the SELECT on user A, it has to wait in a queue until the first query completes. This technique is called Record Locking. Alternatively, we also have another approach called Optimistic Locking. In today's article, I will discuss these two data locking methods to see how they work, their pros and cons, and when to use them.

Record Locking

Record Locking is a concurrency control technique in databases, where records are locked to ensure consistency and prevent multiple transactions from accessing the same record simultaneously.

When a transaction wants to update or read data from a record, it requests the database to lock that record to prevent other transactions from accessing it. After the transaction completes, it releases the lock, allowing other transactions to access it.

For example, in PostgreSQL, SELECT FOR UPDATE is a way to lock the records being queried. By replacing:

SELECT balance FROM accounts WHERE user = 'A';

With:

SELECT balance FROM accounts WHERE user = 'A' FOR UPDATE;

The first transaction will immediately lock the record with user = 'A', and subsequent transactions cannot proceed with reading until a successful COMMIT or ROLLBACK command is executed.

Record Locking ensures data consistency, but it can lead to deadlock situations. Therefore, the use of Record Locking needs to be carefully considered to ensure database performance and data consistency. To understand more about locking types and deadlocks, I recommend reading the article Understanding Lock Types (Explicit Locking) in PostgreSQL (written in Vietnamese).

Optimistic Locking

Optimistic Locking is a method in which transactions do not lock any records, allowing them to perform normally. As the name suggests, the idea behind Optimistic Locking is to assume that transactions accessing the same record will not update it simultaneously, and only one of those transactions will complete the data update.

When a transaction wants to update data, it does not lock the record to prevent other transactions from accessing it. Instead, it checks the state of the record before the update. If the state of the record hasn't been changed by other transactions, it proceeds with the update. Otherwise, if the state of the record has been changed, it rolls back the update.

To implement Optimistic Locking, we need to add a field to mark the update, such as version, updated_at, or any other field to be updated alongside the record.

For example, in the accounts table, we can add an updated_at field representing the time when the record was successfully updated. We no longer need SELECT FOR UPDATE, and instead, we will select updated_at in addition to balance.

SELECT balance, updated_at FROM accounts WHERE user = 'A';

Then, we perform a "conditional" update on updated_at:

UPDATE accounts SET balance = balance - x, updated_at = now() WHERE user = 'A' AND updated_at = updated_at;

Here, updated_at is the result of the SELECT statement within the transaction.

To explain the principle simply, because the UPDATE locks the record during data update, only one transaction at a time is allowed to update it. Subsequent transactions trying to update the data with the old updated_at essentially find no matching records. At that point, we can handle this case as a failed transaction.

Optimistic Locking is simple and effective in scenarios where data update transactions do not occur too frequently. However, it does not guarantee data consistency or carry a higher risk of causing errors as it is often handled at the application layer through programming code.

When to apply?

As always, the choice between Record Locking and Optimistic Locking depends on specific use cases, as each method has its advantages, disadvantages, and specific contexts. However, you can rely on some guidelines below to increase your decision-making capability.

Use Record Locking when:

• Data consistency is the highest priority.
• Data update transactions occur frequently or there are multiple transactions updating the same record simultaneously.

Use Optimistic Locking when:

• Data consistency is not a strict requirement, and the application needs to prioritize performance and transaction execution speed.
• Data update transactions occur infrequently or there are no multiple transactions updating the same record simultaneously.

Furthermore, in some cases, it is possible to combine both techniques to achieve an optimal solution. For example, using Optimistic Locking for reading transactions and Record Locking for data update transactions. This helps optimize performance and data consistency in the database system.

Conclusion

In database systems, there are two popular data locking methods: Optimistic Locking and Record Locking. While Optimistic Locking assumes that transactions accessing the same record will not update data simultaneously, Record Locking "quickly" locks the records to ensure that the first transaction has the right to access and prevent other transactions from accessing. Each method has its own pros and cons, so its application should be tailored to the specific use case.