Definition
- Deadlock -- a circular wait where two or more transactions each hold a lock and wait for a lock held by the other. Neither can proceed, so the database detects the cycle and kills one transaction.
- Consistent lock ordering -- the fundamental fix. If all threads always lock rows in the same deterministic order (for example, sorted by primary key), no circular wait can form.
- Optimistic locking -- uses a
@Versioncolumn on the entity. No database locks are held during the transaction. At write time, Hibernate checks the version and throwsOptimisticLockExceptionif another transaction changed the row. - @Retryable -- a Spring Retry annotation that auto-retries a method when a specified exception is thrown, with configurable backoff delay between retries.
- SKIP LOCKED -- a SQL clause (
SELECT ... FOR UPDATE SKIP LOCKED) that skips rows already locked by other transactions instead of waiting. Ideal for concurrent queue processing.
At the National Bank of India, four strategies handle teller conflicts. Lock ordering: all tellers always grab account files in alphabetical order. Optimistic locking: tellers do not grab files at all -- they work on copies and check for changes when they save. Retry: if a teller's work is undone by a conflict, they simply try again after a short wait. SKIP LOCKED: when processing a queue of pending transfers, a teller skips any file currently being used by another teller and picks the next available one.
Understanding the Concepts
What causes a parallel transaction deadlock?
A deadlock is a circular wait between two or more transactions. Each transaction holds a lock on one resource and waits for a lock on another resource held by the other transaction. Neither can proceed, and without intervention, they would wait forever.
Databases implement a deadlock detector that periodically checks for circular waits. When a cycle is found, the database picks a victim transaction and kills it. The victim receives an error (PostgreSQL: ERROR: deadlock detected; MySQL: ERROR 1213 (40001): Deadlock found when trying to get lock).
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class UnsafeTransferService {
private final AccountRepository accountRepository;
public UnsafeTransferService(AccountRepository accountRepository) {
this.accountRepository = accountRepository;
}
// WRONG: lock order depends on caller's input -> deadlock risk
@Transactional
public void transfer(String fromId, String toId, BigDecimal amount) {
var from = accountRepository.findByIdForUpdate(fromId).orElseThrow();
var to = accountRepository.findByIdForUpdate(toId).orElseThrow();
from.setBalance(from.getBalance().subtract(amount));
to.setBalance(to.getBalance().add(amount));
}
}
Deadlock scenario:
Thread-1: transfer("SAV2001", "CUR3001", 10000)
Lock SAV2001 -> waiting for CUR3001
Thread-2: transfer("CUR3001", "SAV2001", 5000)
Lock CUR3001 -> waiting for SAV2001
Circular wait: SAV2001 -> CUR3001 -> SAV2001
PostgreSQL detects the cycle after deadlock_timeout (1 sec)
One transaction is killed: ERROR: deadlock detected
How does consistent lock ordering prevent deadlocks?
The fundamental fix is to ensure all threads always acquire locks in the same deterministic order. If every thread locks the row with the smaller ID first, no circular wait can form.
For a transfer between two accounts, sort the account IDs before locking:
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class OrderedTransferService {
private final AccountRepository accountRepository;
public OrderedTransferService(AccountRepository accountRepository) {
this.accountRepository = accountRepository;
}
// CORRECT: consistent lock order by sorted ID
@Transactional
public void transfer(String fromId, String toId, BigDecimal amount) {
String firstId = fromId.compareTo(toId) < 0 ? fromId : toId;
String secondId = fromId.compareTo(toId) < 0 ? toId : fromId;
var first = accountRepository.findByIdForUpdate(firstId).orElseThrow();
var second = accountRepository.findByIdForUpdate(secondId).orElseThrow();
var from = first.getId().equals(fromId) ? first : second;
var to = first.getId().equals(toId) ? first : second;
if (from.getBalance().compareTo(amount) < 0) {
throw new InsufficientFundsException(fromId, amount);
}
from.setBalance(from.getBalance().subtract(amount));
to.setBalance(to.getBalance().add(amount));
}
}
With consistent ordering:
Thread-1: transfer(SAV2001, CUR3001)
sorted: CUR3001, SAV2001 (C < S)
Lock CUR3001 -> Lock SAV2001
Thread-2: transfer(CUR3001, SAV2001)
sorted: CUR3001, SAV2001 (same order!)
Lock CUR3001 -> WAITS (held by Thread-1)
No cycle: Thread-2 waits, Thread-1 finishes, Thread-2 proceeds.
How does optimistic locking avoid deadlocks?
Optimistic locking does not hold any database locks during the transaction. Instead, it adds a @Version column to the entity. When Hibernate issues an UPDATE, it includes WHERE version = ? in the statement. If another transaction already changed the row (incrementing the version), the UPDATE affects zero rows and Hibernate throws OptimisticLockException.
Because no row locks are held, there is nothing to create a circular wait. Conflicts are detected at write time, not at read time.
package com.bank.model;
import jakarta.persistence.*;
import java.math.BigDecimal;
@Entity
@Table(name = "accounts")
public class Account {
@Id
private String id;
private String holderName;
private BigDecimal balance;
@Version
private Long version; // optimistic lock column
public String getId() { return id; }
public void setId(String id) { this.id = id; }
public String getHolderName() { return holderName; }
public void setHolderName(String name) { this.holderName = name; }
public BigDecimal getBalance() { return balance; }
public void setBalance(BigDecimal balance) { this.balance = balance; }
public Long getVersion() { return version; }
}
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class OptimisticTransferService {
private final AccountRepository accountRepository;
public OptimisticTransferService(AccountRepository accountRepository) {
this.accountRepository = accountRepository;
}
// No FOR UPDATE -- relies on @Version
@Transactional
public void transfer(String fromId, String toId, BigDecimal amount) {
var from = accountRepository.findById(fromId).orElseThrow();
var to = accountRepository.findById(toId).orElseThrow();
if (from.getBalance().compareTo(amount) < 0) {
throw new InsufficientFundsException(fromId, amount);
}
from.setBalance(from.getBalance().subtract(amount));
to.setBalance(to.getBalance().add(amount));
// At flush:
// UPDATE accounts SET balance=..., version=2
// WHERE id='SAV2001' AND version=1
// If version changed: 0 rows affected -> OptimisticLockException
}
}
Optimistic locking timeline:
Thread-1: read SAV2001 (version=1), debit 10000
Thread-2: read SAV2001 (version=1), debit 5000
Thread-1: UPDATE ... WHERE id='SAV2001' AND version=1
-> 1 row affected, version set to 2. SUCCESS.
Thread-2: UPDATE ... WHERE id='SAV2001' AND version=1
-> 0 rows affected (version is now 2)
-> OptimisticLockException thrown
-> Thread-2 must RETRY
No locks held. No deadlock possible.
Conflict detected at write time, not read time.
How does @Retryable auto-recover from deadlocks?
@Retryable from Spring Retry automatically retries a method when a specified exception is thrown. Combined with consistent lock ordering (to prevent most deadlocks) or optimistic locking (to detect conflicts), retryable provides automatic recovery.
package com.bank.service;
import org.springframework.dao.CannotAcquireLockException;
import org.springframework.orm.ObjectOptimisticLockingFailureException;
import org.springframework.retry.annotation.Backoff;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class RetryableTransferService {
private final AccountRepository accountRepository;
public RetryableTransferService(AccountRepository accountRepository) {
this.accountRepository = accountRepository;
}
@Retryable(
retryFor = {
CannotAcquireLockException.class,
ObjectOptimisticLockingFailureException.class
},
maxAttempts = 3,
backoff = @Backoff(delay = 100, multiplier = 2)
)
@Transactional
public void transfer(String fromId, String toId, BigDecimal amount) {
String firstId = fromId.compareTo(toId) < 0 ? fromId : toId;
String secondId = fromId.compareTo(toId) < 0 ? toId : fromId;
var first = accountRepository.findByIdForUpdate(firstId).orElseThrow();
var second = accountRepository.findByIdForUpdate(secondId).orElseThrow();
var from = first.getId().equals(fromId) ? first : second;
var to = first.getId().equals(toId) ? first : second;
if (from.getBalance().compareTo(amount) < 0) {
throw new InsufficientFundsException(fromId, amount);
}
from.setBalance(from.getBalance().subtract(amount));
to.setBalance(to.getBalance().add(amount));
}
}
Retry behavior:
Attempt 1: CannotAcquireLockException (deadlock detected)
-> wait 100ms
Attempt 2: CannotAcquireLockException (deadlock again)
-> wait 200ms (100 * 2)
Attempt 3: SUCCESS (no contention this time)
-> transfer committed
If all 3 attempts fail:
-> exception propagates to caller
-> caller handles the permanent failure
How does SKIP LOCKED enable concurrent queue processing?
SELECT ... FOR UPDATE SKIP LOCKED is a SQL clause that skips rows already locked by other transactions instead of waiting for them. This is ideal for queue-style processing where multiple workers drain a table of pending items concurrently.
Each worker picks the next unlocked item, processes it, and commits. No contention, no waiting, no duplicate processing.
package com.bank.repository;
import com.bank.model.PendingPayment;
import jakarta.persistence.LockModeType;
import jakarta.persistence.QueryHint;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;
import java.util.Optional;
public interface PendingPaymentRepository
extends JpaRepository<PendingPayment, Long> {
@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints(@QueryHint(
name = "jakarta.persistence.lock.timeout",
value = "-2" // SKIP LOCKED hint for Hibernate
))
@Query("SELECT p FROM PendingPayment p "
+ "WHERE p.status = 'PENDING' "
+ "ORDER BY p.createdAt ASC")
Optional<PendingPayment> findNextPending();
}
package com.bank.service;
import com.bank.model.PendingPayment;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class PaymentQueueProcessor {
private final PendingPaymentRepository pendingRepo;
private final AccountRepository accountRepository;
public PaymentQueueProcessor(PendingPaymentRepository pendingRepo,
AccountRepository accountRepository) {
this.pendingRepo = pendingRepo;
this.accountRepository = accountRepository;
}
@Scheduled(fixedDelay = 100) // poll every 100ms
@Transactional
public void processNextPayment() {
pendingRepo.findNextPending().ifPresent(payment -> {
var account = accountRepository.findByIdForUpdate(
payment.getAccountId()).orElseThrow();
account.setBalance(
account.getBalance().subtract(payment.getAmount()));
payment.setStatus("PROCESSED");
});
}
}
SQL generated:
SELECT * FROM pending_payments
WHERE status = 'PENDING'
ORDER BY created_at ASC
FOR UPDATE SKIP LOCKED
LIMIT 1
With 4 workers:
Worker-1: picks payment #1 (locked)
Worker-2: skips #1, picks payment #2 (locked)
Worker-3: skips #1, #2, picks payment #3 (locked)
Worker-4: skips #1, #2, #3, picks payment #4 (locked)
Zero waiting. Zero deadlocks. Zero duplicate processing.
Each worker processes a different payment.
What is the comparison between all four strategies?
| Strategy | Locks held | Deadlock risk | Conflict handling | Best for |
|---|---|---|---|---|
| Consistent ordering | Yes (FOR UPDATE) | None (if ordering is correct) | Second thread waits | Transfers, balance updates |
| Optimistic (@Version) | No | None (no locks) | Retry on OptimisticLockException | Low-contention updates, profile edits |
| @Retryable | Depends on inner strategy | Safety net | Auto-retry with backoff | Any strategy as a fallback |
| SKIP LOCKED | Yes (FOR UPDATE) | None (skips locked rows) | Skip and process next | Job queues, batch processing |
Decision guide:
Hot rows (settlement account): pessimistic + consistent ordering
Rare conflicts (profile update): optimistic + @Retryable
Queue processing (pending payments): SKIP LOCKED
Mixed: consistent ordering as baseline + @Retryable as safety net
What are common mistakes with deadlock handling?
The most common mistake is catching the deadlock exception and not retrying. The database killed the transaction, so the work was not done. The second mistake is retrying inside the same transaction boundary, which does not work because the transaction is already rolled back.
package com.bank.service;
import org.springframework.dao.CannotAcquireLockException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class MistakeTransferService {
private final AccountRepository accountRepository;
public MistakeTransferService(AccountRepository accountRepository) {
this.accountRepository = accountRepository;
}
// WRONG: catch deadlock without retry -- transfer silently lost
@Transactional
public void transferWrong(String fromId, String toId, BigDecimal amount) {
try {
var from = accountRepository.findByIdForUpdate(fromId).orElseThrow();
var to = accountRepository.findByIdForUpdate(toId).orElseThrow();
from.setBalance(from.getBalance().subtract(amount));
to.setBalance(to.getBalance().add(amount));
} catch (CannotAcquireLockException e) {
// WRONG: swallowed! Transfer never happened.
System.out.println("Deadlock, skipping: " + e.getMessage());
}
}
// WRONG: retry inside the same transaction
@Transactional
public void transferWrongRetry(String fromId, String toId,
BigDecimal amount) {
for (int i = 0; i < 3; i++) {
try {
var from = accountRepository.findByIdForUpdate(fromId)
.orElseThrow();
var to = accountRepository.findByIdForUpdate(toId)
.orElseThrow();
from.setBalance(from.getBalance().subtract(amount));
to.setBalance(to.getBalance().add(amount));
return;
} catch (CannotAcquireLockException e) {
// WRONG: TX is already rolled back by the DB
// Retrying inside the same TX does not help
// The persistence context is in an unknown state
}
}
}
}
WRONG (swallow deadlock):
Deadlock detected -> catch -> "skipping"
Transfer never happened. Customer thinks money moved.
WRONG (retry inside TX):
Deadlock detected -> TX rolled back by DB
Retry inside same TX -> persistence context is broken
May throw additional exceptions or produce corrupt data
CORRECT (@Retryable on a separate method):
@Retryable creates a NEW transaction for each attempt
Each retry starts fresh with a clean persistence context
What about combining SKIP LOCKED with native query?
Some Hibernate versions have limited support for SKIP LOCKED hints via the JPA query hint. For full control, use a native query:
package com.bank.repository;
import com.bank.model.PendingPayment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import java.util.Optional;
public interface PendingPaymentNativeRepository
extends JpaRepository<PendingPayment, Long> {
@Query(value = "SELECT * FROM pending_payments "
+ "WHERE status = 'PENDING' "
+ "ORDER BY created_at ASC "
+ "FOR UPDATE SKIP LOCKED "
+ "LIMIT 1",
nativeQuery = true)
Optional<PendingPayment> findNextPendingNative();
@Modifying
@Query(value = "UPDATE pending_payments "
+ "SET status = 'PROCESSING' "
+ "WHERE id = ("
+ " SELECT id FROM pending_payments "
+ " WHERE status = 'PENDING' "
+ " ORDER BY created_at ASC "
+ " FOR UPDATE SKIP LOCKED "
+ " LIMIT 1"
+ ") RETURNING *",
nativeQuery = true)
Optional<PendingPayment> claimNextPayment();
}
Native SKIP LOCKED with atomic claim:
SELECT + UPDATE in one statement
Atomically claims the next pending payment
Other workers see it as 'PROCESSING' and skip it
Even more efficient than separate SELECT + UPDATE
What should a senior engineer know?
- Deadlock detection cost -- PostgreSQL checks for deadlocks after
deadlock_timeout(default 1 second). MySQL's InnoDB checks on every lock wait. Frequent deadlocks add detection overhead. - Lock wait timeout vs deadlock timeout -- lock wait timeout (
innodb_lock_wait_timeoutin MySQL,lock_timeoutin PostgreSQL) is how long a transaction waits for a lock before giving up. Deadlock timeout is how long before the database checks for circular waits. They are different settings. - @Retryable must be on a different bean -- if @Retryable and @Transactional are on the same method, the retry happens outside the transaction proxy, which is correct. But if @Retryable is on a method that calls another @Transactional method via self-invocation, the retry does not create a new transaction.
- SKIP LOCKED portability -- SKIP LOCKED is supported in PostgreSQL 9.5+, Oracle 11g+, and MySQL 8.0+. It is not supported in H2 (common in tests), so integration tests may need a real database.
- Metrics -- track deadlock count (
pg_stat_database.deadlocks), lock wait time, and retry count to identify hot contention points.
# application.yml: retry configuration
spring:
retry:
enabled: true
# Custom retry template for fine-grained control
# @Bean
# RetryTemplate retryTemplate() {
# return RetryTemplate.builder()
# .maxAttempts(3)
# .exponentialBackoff(100, 2, 1000)
# .retryOn(CannotAcquireLockException.class)
# .retryOn(ObjectOptimisticLockingFailureException.class)
# .build();
# }
Senior checklist:
1. Baseline: consistent lock ordering on all FOR UPDATE queries
2. Low contention: optimistic @Version + @Retryable
3. Queue processing: SKIP LOCKED
4. Safety net: @Retryable(maxAttempts=3, backoff exponential)
5. Monitor: deadlock count, lock wait time, retry count
6. Test: integration tests with concurrent threads + CountDownLatch
What are common interview follow-up questions?
- Can a deadlock occur with a single row? No. A deadlock requires at least two resources locked in different orders. A single row causes a lock wait, not a deadlock.
- Does optimistic locking work for all scenarios? No. For high-contention rows (settlement account touched by every transfer), optimistic locking causes too many retries. Use pessimistic locking with consistent ordering instead.
- How does SKIP LOCKED handle an empty queue? The SELECT returns no rows. The worker does nothing and polls again on the next cycle.
- Can you combine optimistic and pessimistic locking? Yes, on different entities in the same transaction. Use pessimistic for the hot row (account balance) and optimistic for the less-contested row (transfer metadata).
Visual Overview
┌─────────────────────────────────────────────────────────┐ │ Strategy 1: Consistent Lock Ordering │ │ │ │ Both threads lock CUR3001 first, then SAV2001 │ │ │ │ Thread-1: LOCK CUR3001 ──► LOCK SAV2001 ──► COMMIT │ │ Thread-2: LOCK CUR3001 (waits) ──────────────► │ │ ──► LOCK SAV2001 ──► COMMIT │ │ │ │ No cycle. Thread-2 waits, Thread-1 finishes first. │ └─────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────┐ │ Strategy 2: Optimistic Locking (@Version) │ │ │ │ Thread-1: READ (v=1) ──► UPDATE WHERE v=1 ──► OK (v=2) │ │ Thread-2: READ (v=1) ──► UPDATE WHERE v=1 ──► FAIL! │ │ ──► OptimisticLockException ──► RETRY │ │ ──► READ (v=2) ──► UPDATE WHERE v=2 ──► OK │ │ │ │ No locks held. No deadlock possible. │ └─────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────┐ │ Strategy 4: SKIP LOCKED (queue processing) │ │ │ │ pending_payments: [P1, P2, P3, P4, P5] │ │ │ │ Worker-1: SELECT ... SKIP LOCKED ──► P1 (locked) │ │ Worker-2: SELECT ... SKIP LOCKED ──► P2 (skips P1) │ │ Worker-3: SELECT ... SKIP LOCKED ──► P3 (skips P1,P2) │ │ │ │ Zero waiting. Zero deadlocks. Max parallelism. │ └─────────────────────────────────────────────────────────┘
Key Takeaway
Parallel transaction deadlocks are handled with four complementary strategies. Consistent lock ordering is the fundamental defense: if all threads always lock rows in the same deterministic order (sorted by primary key), no circular wait can form. Optimistic locking with @Version avoids database locks entirely by detecting conflicts at write time and throwing OptimisticLockException for the loser to retry. @Retryable provides automatic recovery with exponential backoff for any locking-related failure. SKIP LOCKED enables concurrent queue processing where multiple workers drain a table of pending items without contention or waiting.
In Rajesh's payment system at the National Bank of India, the transfer service uses consistent lock ordering (sort account IDs, lock the smaller one first) combined with @Retryable as a safety net. For low-contention operations like profile updates, optimistic locking with @Version gives the best throughput because no database locks are held during the transaction. For the payment queue, multiple processor instances use SKIP LOCKED to pick up pending payments in parallel, each worker processing a different payment with zero overlap.
Pankaj, the architect, combines all four strategies: consistent ordering as the baseline discipline on all FOR UPDATE queries, optimistic locking for rare-conflict scenarios, @Retryable for edge cases that slip through, and SKIP LOCKED for queue-style processing. The key insight is that these strategies are not competing alternatives -- they address different contention profiles and can be layered.
Remember one sentence: prevent deadlocks with consistent lock ordering, detect conflicts with @Version, auto-recover with @Retryable, and process queues with SKIP LOCKED.
---