Locks

Two users load the same book, both change it, both save: without a lock the second save silently overwrites the first. The starter offers the two ways Spring Data does: optimistic locking with a @Version property, which detects the conflict at save time, and pessimistic locking with @Lock on a finder, which holds the rows until the transaction ends.

Why

Example

@Table('stock')
class Stock {
  Stock({this.id, required this.bookId, required this.available});

  @Id()
  int? id;
  int bookId;
  int available;

  @Version()
  int version = 0;        // raised at each save
}

abstract interface class StockRepository implements ListCrudRepository<Stock, int> {
  // FOR UPDATE: the row stays locked until the transaction ends.
  @Lock(LockMode.pessimisticWrite)
  Future<Stock?> findByBookId(int bookId);

  // A work queue: skip the rows other workers hold, wait at most 2 seconds.
  @Lock(LockMode.pessimisticWrite, timeout: Duration(seconds: 2), skipLocked: true)
  Future<List<Stock>> findTop10ByAvailableGreaterThan(int available);
}

@Service()
class Checkout {
  Checkout(this.stock, this.orders);

  final StockRepository stock;
  final OrderRepository orders;

  @Transactional()          // a @Lock finder works only inside a transaction
  Future<void> place(int bookId) async {
    final row = (await stock.findByBookId(bookId))!;
    row.available--;
    await stock.save(row);
    await orders.save(PurchaseOrder(bookId: bookId));
  }
}

@Version

A conflict is a ConcurrencyFailureException, the type retry code catches. In a web application it is answered with a 409 (see In a web application).

try {
  await books.save(edited);
} on OptimisticLockingFailureException {
  // someone saved the book after we loaded it: reload and show the user
}

@Lock

Attribute Default Meaning
value (required) LockMode.pessimisticWrite (exclusive, FOR UPDATE) or LockMode.pessimisticRead (shared, FOR SHARE: others may read, not change).
timeout null The longest wait for the lock; null for the store's default, Duration.zero to fail at once (NOWAIT).
skipLocked false Leave out rows another transaction holds (SKIP LOCKED), for work queues.

How each store locks

PostgreSQL SQLite In memory
Granularity rows the whole database rows
pessimisticWrite FOR UPDATE BEGIN IMMEDIATE exclusive row lock
pessimisticRead FOR SHARE BEGIN IMMEDIATE shared row lock
timeout: Duration.zero NOWAIT ignored: the busy timeout applies fails at once
longer timeout SET LOCAL lock_timeout before the query ignored: the busy timeout applies the given wait
default wait the server's spring.datasource.sqlite.busy-timeout (5000 ms) spring.data.memory.lock-timeout (10s)
skipLocked SKIP LOCKED (wins over a zero timeout) ignored (the database is held whole) skips held rows
Deadlocks DeadlockLoserDataAccessException (40P01) detected

In Spring (Java)

@Lock(LockMode.PESSIMISTIC_WRITE)
Optional<Stock> findByBookId(long bookId);

timeout and skipLocked are extensions here; Spring sets them with query hints. A @Lock query outside a transaction is an error here.

See also