[Feature Request] Optimistic Locking

[eratio08]

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.

[billy1624]

Hey @eratio08, welcome!! I like the idea of constructing optimistic locking on top of updated_at column. However, we don't have support for created_at & updated_at columns just yet. So, we might want to implement that before working on the support of optimistic locking. Thoughts?

[eratio08]

@billy1624 sounds great.

The updated at can be implemented with the before_update hook but optlock would be need to adjust the query and I was not able to archive this with only the hooks. I tried to implementing the optlock using Update-many with an optlock filter clause, using this construct sadly does not call the before_update hook anymore.

[billy1624]

Please track the progress at #827

[billy1624]

Let me brainstorm the API for optlock after we got #827 landed

[hamedai]

@billy1624 any news on this?

[boringplay]

@billy1624 any news on this?

[boringplay]

Is there any progress?

[alvissraghnall]

fair. Co-ask.

[Huliiiiii]

Converted to issue #2897