Optimistic Locking

Introduction

In contrast to Pessimistic Locking which I explained in the previous article, Optimistic Locking is suitable for use in cases where two people modifying the same data row rarely occurs
Therefore, it allows everyone to read and modify data freely and the check is only performed when executing an UPDATE
- If this row has not been modified by anyone, the UPDATE will proceed normally
- If someone has already modified this row beforehand, it will report an error and request a user retry
To implement Optimistic Locking, you do not use any row-level lock commands, but will instead implement it yourself in code via a version column (or an updated_at column) in the table

When you SELECT row data, you first retrieve the value of the version column
After performing the required business logic processing
When you UPDATE that row, you check whether the version matches the previously retrieved value

If no one has UPDATEd it yet, the version remains unchanged, you will get that row to UPDATE (and must also change the version value at the same time)
If someone has already UPDATEd it before, the version will change and you can no more get the old row to UPDATE

Comparison

Optimistic Locking: Uses a version column
- Locked by application logic, so the database remains completely free and consumes no locking resources
- Everyone can read data concurrently and an error is reported only if a conflict occurs during update
- Systems with low concurrency conflicts, or long-running transactions (such as editing a CMS article or updating a user profile)
Pessimistic Locking: Uses row-level locking
- Locked by the database, which maintains connections and occupies lock memory resources
- Subsequent users must wait until the previous user finishes
- Updates to the same row of data occur frequently and require high accuracy (such as money transfer services or movie ticket booking)

An important note is to choose the corresponding lock type for each scenario to ensure that unexpected deadlock issues do not occur

If you use Pessimistic Lock (FOR UPDATE) in an application where users open a form and save it a long time later, your database will be locked when many other users also want to view that content, because the lock is held for too long
On the other hand, if you use Optimistic Lock for a Flash Sale, subsequent customers making purchases will continuously receive a "Please try again" error, heavily impacting the user experience

Detail

First, create a table as follows, you just need to add a version column

CREATE TABLE products (
    id INT PRIMARY KEY,
    name TEXT,
    stock_quantity INT,
    version INT DEFAULT 1
);

You can see that the implementation of Optimistic Lock is very simple, when you use ORMs it can even automatically perform this for you, here I will use SQL to show you how it is actually processed in the database

-- Orders
SELECT stock_quantity, version FROM products WHERE id = 1;

UPDATE products
SET stock_quantity = 9, version = version + 1 
WHERE id = 1 AND version = 1;

You can see that we do not even need to add BEGIN...COMMIT to create a Transaction anymore, note that this is only suitable for this specific case, if you have multiple statements to execute and want them to COMMIT/ROLLBACK at the same time, you still need to create a separate transaction
After obtaining the current version, you just need to use UPDATE with that version along with increasing the version value
If a subsequent query also wants to update the same row but executes later, using the old version will fail to retrieve the row for update
Please note that this type of lock is only suitable for cases where conflicts occur very rarely, because if conflicts occur frequently, most user operations will report an error, forcing them to continuously retry