Optimistic vs Pessimistic Locking: Handling the Data Fight in a Database

A thousand people tap Buy Now in the same second, fighting over the last ticket. What happens on your server? Here is pessimistic against optimistic locking.

Read in Indonesian

A thousand people tap the "Buy Now" button in the same second, all fighting over the last concert ticket. What do you think happens on the server? ๐Ÿค”

Without a proper concurrency safeguard, your system can hit a disaster called a Race Condition or the Lost Update Problem. The ticket sells to 5 people at once, while the stock in the database goes negative. Chaos, right? ๐Ÿ˜…

In backend and database work, two transactions updating the same row at the same time have to be managed carefully. The two most common approaches to this data fight are Pessimistic Locking and Optimistic Locking.

Let's take apart the difference, plus when to pick which.

Key takeaways

  • Race Conditions and the Lost Update Problem appear when many users edit the same row at the same time.
  • Pessimistic Locking locks the data physically at the database level through SELECT ... FOR UPDATE, suited to high contention like a ticket flash sale.
  • Optimistic Locking works on a version or timestamp column without locking the row, suited to high read traffic with low conflict frequency.
  • Choosing the right strategy keeps data integrity without sacrificing application throughput.

How Do People Edit the Same Document Together?

To picture it, think of those two transactions as two people editing the same document in an office.

The first style is pessimistic: a staff member takes the physical document from the filing cabinet, then locks the cabinet with a padlock and keeps the key. Anyone who wants to read or edit has to stand in line at the cabinet until that staff member is done. The philosophy is roughly: "Ah, someone will probably change it at the same time, better to lock it from the start."

The second style is optimistic: the document gets photocopied and handed to everyone, each sheet marked with a version number. Everyone edits freely at their own desk. Only when saving to the central archive does anyone check. "Still version 1.0? If so, save it and bump it to 1.1. If someone already saved 1.1, your revision is rejected, grab the latest copy!" This style is relaxed at the start, firm at the end.

What Is Pessimistic Locking?

Pessimistic Locking works by locking a row (row-level lock) or a table directly in the database. The feature comes from relational transactions in databases like PostgreSQL or MySQL.

When the transaction begins, the query reads the data with a SELECT ... FOR UPDATE clause:

SQL
BEGIN;

-- Lock the product row with ID 42 so no other transaction can change it
SELECT id, stock, price
FROM products
WHERE id = 42
FOR UPDATE;

-- Do the calculation in the application
-- Update the stock if it is still available
UPDATE products
SET stock = stock - 1
WHERE id = 42;

COMMIT; -- Lock released, other transactions may enter now

The upside is clear: safety. You are guaranteed 100% that no data overlaps, because other transactions are forced to queue (blocking).

But there is a price. Throughput can drop sharply when many requests queue on the same row. And if you are careless about the locking order, the system can hit a Deadlock, where two transactions wait on each other forever.

And How Does Optimistic Locking Work?

Unlike the previous one, Optimistic Locking locks nothing physically. This approach leans on application logic, helped by a version marker column like version INT or updated_at TIMESTAMP.

The flow goes roughly like this:

  1. Read the data along with its current version.
    SQL
    SELECT id, stock, version FROM products WHERE id = 42;
    -- Result: stock = 10, version = 1
  2. Compute in the application, then save only if the version has not changed.
    SQL
    UPDATE products
    SET stock = stock - 1, version = version + 1
    WHERE id = 42 AND version = 1;
  3. Check the number of rows affected. If the result is 1, it worked and the version moves to 2. If the result is 0, another transaction saved first, and the application can cancel or retry.

The upside is speed: nothing gets blocked (non-blocking). Perfect for applications with a heavy read load (high read throughput).

The downside: when contention is high, say a fight over 1 concert ticket, most transactions fail. Those repeated retries are what eventually burn your CPU. ๐Ÿ˜…

A Code Example in Python / SQLAlchemy

Here is the comparison using SQLAlchemy Concurrency:

PYTHON
from sqlalchemy.orm import Session
from sqlalchemy import select
from models import Product

 # 1. Pessimistic Locking implementation
def buy_product_pessimistic(db: Session, product_id: int, quantity: int):
    # Use with_for_update() to trigger 'SELECT ... FOR UPDATE'
    product = db.execute(
        select(Product)
        .where(Product.id == product_id)
        .with_for_update()
    ).scalar_one()

    if product.stock < quantity:
        raise ValueError("Stok nggak mencukupi!")

    product.stock -= quantity
    db.commit()
    return product

 # 2. Optimistic Locking implementation
def buy_product_optimistic(db: Session, product_id: int, quantity: int):
    # Read the data without a physical lock
    product = db.execute(
        select(Product).where(Product.id == product_id)
    ).scalar_one()

    if product.stock < quantity:
        raise ValueError("Stok nggak mencukupi!")

    current_version = product.version

    # Conditional update matching the version number
    result = db.query(Product).filter(
        Product.id == product_id,
        Product.version == current_version
    ).update({
        "stock": product.stock - quantity,
        "version": current_version + 1
    })

    if result == 0:
        db.rollback()
        raise ValueError("Bentrokan data terdeteksi! Silakan ulangi transaksi.")

    db.commit()
    return product

In the optimistic version, the main lock is the WHERE id = 42 AND version = 1 clause. If the version already changed first, the update affects no row at all, and result is 0. That check is what replaces a physical lock.

When Do You Use Which?

The easy way to decide: look at how often the data conflicts.

Criterion Pessimistic Locking Optimistic Locking
Contention Level High (many fighting over 1 row). Low to medium.
Traffic Characteristics Financial transactions, flash sales, booking. User profiles, blog edits, general catalogs.
Cost of Failure Waiting queue (blocking latency). Compute cost for retry.
Mechanism Physical database lock (FOR UPDATE). version / timestamp column in application logic.

So for high contention that demands absolute integrity, use pessimistic. For read-dominated traffic with rare conflicts, optimistic is far lighter.

So, Which One Do We Pick?

There is no single perfect solution for every case. What matters is understanding the character of the transactions in your own application.

Use Pessimistic Locking when absolute integrity with no tolerance for retry is genuinely required under high contention. On the other side, use Optimistic Locking to keep the system nimble on transactions where the risk of conflict is low.

Pick wrong and it is not only the data that falls apart. Your CPU cries too. Hehe.

Happy locking, and may your transactions always survive the fight. ๐Ÿ‘‹

Inva

Writer

Get better at shipping code.

Join the other developers getting tech updates, tips, and relaxed tutorials in their inbox every week.

Stay in the loop

Tech updates, coding tips, and relaxed insights straight to your inbox every week.

No spam. Unsubscribe anytime.

Read next

See all

For developers who learn by struggling

ยฉ 2026 Inva.dev.