
# 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

- Use `@Version` when conflicts are rare: a user edits a record in a form, and a conflict is shown as "someone changed this, reload". Nothing is held while the user thinks.
- Use `@Lock` when a read and the write that follows must not interleave, inside one short transaction: taking an item from stock, a work queue, a counter.

## Example

```dart
@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 new entity (version `0`, or `null` for a nullable version) is inserted with version `1`.
- A loaded entity is updated with `... where id = ? and version = ?`, and its version raised. When no row matches (another save got in between, or the row was deleted), `save` throws an `OptimisticLockingFailureException`.
- `delete(entity)` checks the version the same way; `deleteById` cannot, it has no version.
- The children of the aggregate (`@MappedCollection`) are covered by the root's version.
- The type is `int`, `int?` or `DateTime?`. How the version decides between insert and update is on [Entities](entities.md).

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](web.md)).

```dart
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. |

- `@Lock` applies to derived `find` methods and to `findById`; it is a build error on a `@Query` method.
- **Only inside a transaction.** A `@Lock` method called outside a transaction of its store fails at once with `IllegalTransactionStateException`. It never runs unlocked. (Spring runs it in autocommit, where the lock is released at once.)
- A lock that cannot be had throws `CannotAcquireLockException`.

## 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 |

- SQLite has no row locks: a `@Lock` query takes the database's write lock when its transaction begins, so it must be the first statement of its transaction. PostgreSQL has no such rule.
- The in-memory store has row locks of its own (read committed, with deadlock detection), held by the transaction of the repository's store.

## In Spring (Java)

```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

- [Transactions](transactions.md)
- [Errors](errors.md)
- [Entities](entities.md)
