Blog

Database Locks: The Silent Killer Of High-Load Systems

concurrency lives in database too

High-load systems often die quietly inside database lock queue.

Application logs say request timed out. Database knows truth: transactions waited, rows locked, deadlocks formed, and nobody designed concurrency.

Lost Update

Two transfers read same balance 100. Both subtract 80. Both save 20. Customer spent 160 and system thinks 80.

This is not edge case. This is normal traffic plus bad design.

Pessimistic Locking

Lock row before changing it.

select id, balance
from accounts
where id = ?
for update;

Then update in same transaction.

@Transactional
void debit(AccountId id, Money amount) {
    Account account = accountRepository.findForUpdate(id)
        .orElseThrow(() -> new UnknownAccountException(id));

    account.debit(amount);
    accountRepository.save(account);
}

Simple and safe for some flows. But locks reduce concurrency. Keep transaction short.

Optimistic Locking

Add version column.

update accounts
set balance = ?, version = version + 1
where id = ? and version = ?;

If updated rows = 0, someone changed account first. Retry or fail with conflict.

Optimistic locking works well when conflicts are rare.

Deadlock Pattern

Transfer A locks account 1 then 2. Transfer B locks account 2 then 1. Database picks victim.

Fix by deterministic lock order.

List<AccountId> ordered = Stream.of(from, to)
    .sorted(Comparator.comparing(AccountId::value))
    .toList();

accounts.lockInOrder(ordered);

Now transactions acquire locks in same order.

Do Not Hold Locks During Network Calls

Never lock account row, call external provider, wait two seconds, then update. You just converted database into waiting room.

Separate reservation, external call, confirmation. Use saga or state machine.

Monitor Locks

Track lock wait time, deadlocks, transaction duration, slow queries, and connection pool saturation. If app threads wait for database, your service latency explodes.

Final Rule

Concurrency is not only Java threads. Database is concurrent system with rules.

Know those rules before money moves through it.