What Happens When Multiple Users Hit the Same Database Record

Imagine an e-commerce website during a flash sale. Thousands of users try to purchase the last remaining iPhone at exactly the same moment.

Without proper concurrency control, multiple users could successfully buy the same item, leading to overselling, inconsistent data, and unhappy customers.

This is where locking mechanisms come into play.

In this article, we’ll explore how locking works in distributed systems and databases when multiple users try to access or update the same record simultaneously.

The Problem: Concurrent Access

Consider a product table:

Product ID -(101) Name-(iPhone 16) Stock-(1)

Two users, User A and User B, attempt to purchase the product at the same time.

Without Locking

1. User A reads stock = 1
2. User B reads stock = 1
3. User A updates stock = 0
4. User B updates stock = 0

Both purchases succeed even though only one item existed.

This is known as a race condition.

What Is a Lock?

A lock is a mechanism that temporarily prevents other transactions from modifying a resource while one transaction is using it.

Think of it as a bathroom door lock:

• One person enters and locks the door.
• Others must wait.
• When finished, the lock is released.

Databases use the same concept for records, rows, tables, or even distributed resources.

Pessimistic Locking

Pessimistic locking assumes conflicts are likely.

Before modifying a record, the transaction acquires a lock.

BEGIN;

SELECT * 
FROM products
WHERE id = 101
FOR UPDATE;

UPDATE products
SET stock = stock - 1
WHERE id = 101;

COMMIT;

What Happens?

Transaction A:

• Acquires lock
• Reads stock
• Updates stock
• Commits

Transaction B:

• Tries to acquire lock
• Waits until Transaction A finishes
• Reads latest value

Timeline

Time →

User A: Lock ---- Update ---- Commit

User B:      Waiting -------- Lock ---- Update

Only one transaction modifies the record at a time.

Pros

• Strong consistency
• Prevents race conditions

Cons

• Reduced throughput
• Increased waiting time
• Risk of deadlocks

Optimistic Locking

Optimistic locking assumes conflicts are rare.

Instead of locking, each record contains a version number.

Product
--------
id = 101
stock = 1
version = 5

User A Reads

stock = 1
version = 5

User B Reads

stock = 1
version = 5

Both users attempt an update.

User A:

UPDATE products
SET stock = 0,
    version = 6
WHERE id = 101
AND version = 5;

Update succeeds.

User B:

UPDATE products
SET stock = 0,
    version = 6
WHERE id = 101
AND version = 5;

Fails because version is now 6.

The application tells User B:

“The record has changed. Please retry.”

Pros

• Higher performance
• No waiting
• Scales well

Cons

• Retries required
• Not suitable for heavy contention

Database Row-Level Locks

Most relational databases support row-level locking.

Instead of locking an entire table, only the specific row is locked.

Products Table

Row 101 -> Locked
Row 102 -> Free
Row 103 -> Free

This improves concurrency because other rows remain accessible.

Examples:

• MySQL InnoDB
• PostgreSQL
• Oracle
• SQL Server

Distributed Locking

In microservices architectures, multiple application servers may try to update the same resource.

Server A
Server B
Server C

      ↓
 Shared Resource

Database locks alone may not be sufficient.

A distributed lock is used.

Popular implementations:

• Redis
• ZooKeeper
• etcd

Example using Redis:

SET lock:product101 unique_id NX EX 10

Meaning:

• Create lock only if it doesn’t exist
• Expire after 10 seconds

If another server tries to acquire the same lock, it fails until the lock is released.

Deadlocks

Deadlocks occur when two transactions wait for each other forever.

Example:

Transaction A:

Lock Product
Wait for Order

Transaction B:

Lock Order
Wait for Product

Result:

A waits for B
B waits for A

Neither can proceed.

Modern databases detect deadlocks automatically and terminate one transaction.

Real-World Example: Ticket Booking

Imagine booking a movie ticket.

Available seats:

A1
A2
A3

Thousands of users attempt to reserve seat A1.

With Locking

1. User A locks seat A1.
2. User B attempts reservation.
3. User B waits.
4. User A confirms booking.
5. Seat marked as booked.
6. User B receives “Seat already booked.”

This guarantees only one successful booking.

Companies such as airline reservation systems, railway booking platforms, and ticketing websites heavily rely on locking and transaction management.

Choosing the Right Locking Strategy

ScenarioRecommended ApproachBanking transactionsPessimistic LockingInventory managementPessimistic LockingUser profile updatesOptimistic LockingSocial media likesOptimistic LockingFlash salesDistributed Lock + Database TransactionTicket bookingPessimistic Locking

Key Takeaways

When thousands of users access the same record simultaneously, systems must prevent race conditions and maintain data consistency.

The most common approaches are:

1. Pessimistic Locking — lock first, then update.
2. Optimistic Locking — update only if the version matches.
3. Row-Level Locking — lock only the affected row.
4. Distributed Locking — coordinate updates across multiple servers.
5. Deadlock Handling — detect and resolve circular waits.

A good system design balances consistency, performance, and scalability. The right locking strategy depends on business requirements, traffic volume, and the acceptable level of contention.