Article courtesy of author Tong Xuan Hoai
problem
Locking records while reading or updating data is a fairly common occurrence. The classic example for this case is the money transfer problem between A and B: As A transfers to B the amount x, we need to check A’s balance, if there is enough money, then subtract it. x dong to the account and then add x dong to B’s account. Make sure that all additions and subtractions must be successful for the transaction to be complete, because if an error occurs while subtracting A’s money, it is not added to B or vice versa will cause a serious problem with the accuracy of the program.
For example, the command to execute queries in this case in PostgreSQL would be like:
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 in transactions. That is, start a transaction and make sure that the subtraction and the addition are successful, then it will recognize the successful transfer and save it in the database. If unfortunately one of the two is wrong, the transaction will fail and no money will be deducted or added in an unreasonable manner.
But in the case that A can make several money transfers and “almost at the same time” another problem arises. It is in command SELECT first for the purpose of checking the balance, due to the high probability of several orders SELECT may come out balance of A before the check balance is reached, then they are all satisfied a > x and what if they all continue to execute UPDATE Later?
There are many ways to solve this problem. One of them locked the current record SELECT again by asking SELECT FOR UPDATEif the following query is met SELECT to user A, it will need to queue until the first query completes. This method is called Record Locking, in addition we have another method, Optimistic Locking. In today’s article, I will talk about two methods of data locking to see how they work, what are the advantages and disadvantages, and in what cases to use.
Record locking (pessimistic locking)
Record Locking is a database concurrency management technique where records are locked to ensure consistency and prevent transactions from accessing the same record at the same time.
When a transaction wants to update or read data from a record, it asks the database to lock that record to prevent other transactions from accessing it. After the transaction completes, it is unlocked so that other transactions can continue to access it.
For example in PostgreSQL, SELECT FOR UPDATE is a way to lock the records it queries. By changing to:
SELECT balance FROM accounts WHERE user = 'A';
wall:
SELECT balance FROM accounts WHERE user = 'A' FOR UPDATE;
The first transaction immediately locks the record with user = ‘A’ and following transactions cannot continue reading but must wait for an instruction. COMMIT o ROLLBACK If successful, you can continue.
Record Locking technique ensures data consistency, however it can lead to deadlock situation. Therefore, the use of Record Locking should be carefully considered to ensure the performance and data consistency of the database system. For a better understanding of locks and deadlocks, I recommend reading the article Explicit Locking in PostgreSQL.
Check out the attractive Oracle jobs at TopDev
Optimistic Locking
Optimistic Locking is a method where transactions will not lock any records but will let them execute normally. As the optimistic name suggests, the idea behind Optimistic Locking is the assumption that transactions accessing the same record will not update data at the same time, and only one of them will complete on updating.
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 updating. If the state of the record has not been changed by other transactions, it will update that record. Otherwise, if it detects that the state of the record has changed, it will need to abort the update.
To implement Optimistic Locking, we need to add a field to mark the update, it can be version, updated_at… or any data field so that every successful update of the record will also update it.
Example in the table accounts more schools updated_at is the time when the record was successfully updated. We don’t need it SELECT FOR UPDATE more but rather will do SELECT still out updated_at.
SELECT balance, updated_at FROM accounts WHERE user = 'A';
Then perform a “conditional” update of the updated_at:
UPDATE accounts SET balance = balance - x, updated_at = now() WHERE user = 'A' AND updated_at = updated_at;
In updated_at is the result of the query SELECT transaction.
To explain the principle is very simple, because UPDATE The record is locked while updating data so only one transaction is allowed to update at a time. Subsequent transactions when trying to update data in updated_at old, absolutely no matching records. Then we can proceed with this case as a failed transaction.
Optimistic Locking is simple and effective in situations where data update transactions do not occur very frequently. However, it does not guarantee data consistency or risk more errors because it is usually handled at the application layer through programming code.
In what case does this apply?
Still the old saying, first of all, the choice of using Record Locking or Optimistic Locking depends on each specific problem, because each method has its own advantages and disadvantages and its own context. However, you can rely on some of the suggestions below to increase your decision making skills.
Use Record Locking if:
- Data consistency is a top priority.
- Transactions that update data frequently or have multiple transactions update the same record at the same time.
Use Optimistic Locking if:
- Data consistency is not a requirement and the application needs to increase performance and speed of transactions.
- Transactions that don’t update data frequently, or don’t have multiple transactions updating the same record at the same time.
In addition, in some cases it is possible to combine both approaches to achieve an optimal solution. For example, use Optimistic Locking for transactions that read data, and use Record Locking for transactions that update data. This helps to optimize performance and data consistency for the database system.
summary
In database systems, there are two common methods of data locking: Optimistic Locking and Record Locking. While Optimistic Locking assumes that transactions accessing the same record will not update data at the same time, Record Locking “quickly” locks the records to ensure that the first transaction has access. and prevent other access transactions. Each method has its own advantages and disadvantages, so it must be applied appropriately to each specific problem.
Original article posted on 2coffee.dev
See more:
Find more attractive IT jobs on TopDev
