[Feature Request] Optimistic Locking

Discussed in https://github.com/SeaQL/sea-orm/discussions/819

^{Originally posted by eratio08 June 21, 2022}

Motivation

Optimistic locking allows to handle competing/concurrent modifications of a row.

Optimistic locking assumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect. Before committing, each transaction verifies that no other transaction has modified its data. If the check reveals conflicting modifications, the committing transaction rolls back.

This approach guarantees some isolation, but scales well and works particularly well in read-often-write-sometimes situations.

source: Hibernate Docs

Proposed Solutions

let res: UpdateResult = fruit.update(db).await?;

Should generate the SQL statement

UPDATE fruite SET opt_lock = opt_lock + 1 ... WHERE id = <id> AND opt_lock = <opt_lock>; 

If effected row would be 0 the result be would be a DbError::ConcurrentModification

A version column could be derived by

pub struct Model {
  ...
  #[sea_orm(version)]
  pub opt_lock: usize,
}

Additional Information

An advanced version could also allow Timestamps as data type for the opt-lock. The opt_lock would be updated via now()/currentTimestamp to avoid time shift issues of clients. Using a timestamp would allow to merge opt_lock and modified_at/updated_at columns into a single one.

Start by tracing the Model update(db) API and its UpdateResult, then inspect how generated UPDATE statements and DbError values are represented. Define the version-column behavior, affected-row conflict handling, and #[sea_orm(version)] model support; done means concurrent updates produce the proposed SQL behavior and expose the specified error.