Concurrency and Row Versioning - Part 1

[Sascha]

Throughout my career, I’ve occasionally encountered a particularly tricky issue: debugging a broken application state. It often occurs when a user double-clicks a button, unintentionally sending multiple modification requests to the same resource. In other cases, two users might update the same record almost simultaneously.

Modern APIs are designed for scale, but when multiple clients modify the same data concurrently, you inevitably face the classic problem of concurrent updates.

These series will explore practical patterns to protect your system:

1. Concurrency and Row Versioning - Part 1 (you’re here)
2. Concurrency and Transactional Row Versioning - Part 2
3. Concurrency and API Resource Locks - Part 3
4. Concurrency and Queues - Part 4

Database row versioning (optimistic concurrency)


Each row in a database table that needs protection carries a version value. When you update a database row, you check that the version you read is current and that no one has modified it.

All modern databases include a

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

or a

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

column type out of the box: Postgres, MS SQL, Oracle DB, MySQL. Moreover, timestamps are also supported natively in document databases, such as MongoDB, GCP Firestore, and Supabase.

Row versioning in steps


Here are the steps with an SQL example to protect a row against concurrent updates.

1. 
   Read record data, including the row version
   
   SELECT version as OLD_VERSION FROM users WHERE ID=1
2. 
   Implement the required logic to prepare the updated data.
   
   
3. 
   Update the record only if the version has not changed.
   
   UPDATE users
   SET firstname = 'Edgar', lastname = 'Codd', version = $NEW_VERSION
   WHERE id = 1 AND version = $OLD_VERSION;

If another concurrent request updated the same row during step 2, then step 3 will fail. The database will reject it due to the condition conflict WHERE version = $OLD_VERSION.

This technique, also known as Optimistic Concurrency, has the main benefit of not requiring a database resource lock and being very light. I remember developers actively using this principle in SQL databases 15 years ago.

Firestore DB row versioning


Firestore DB, an example of a NoSQL database, supports row versioning using the special metadata.


// Step 1 - read
const userRef = db.collection('users').doc('1');
const doc = await userRef.get();
const oldVersion = doc.updateTime; // <-- get version metadata

// Step 2 - your logic

// Step 3 - conditional update
await userRef.update({
firstname: 'Edgar',
lastname: 'Codd'
},
{
lastUpdateTime: oldVersion // <-- conditional update
});




The important things here are to recognize:

1. Firestore automatically sets unique updateTime metadata during a document update, so we do not set it manually.
2. Firestore allows conditional update via the special
   
   

   Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

   
   
   check, which can be read as: WHERE lastUpdateTime = $oldVersion.

Broad Compatibility


Custom row versions can be used even if a database does not natively support timestamp or rowversion types. The only requirement is a conditional update.

Timestamp can be stored as an ISO string. That is how

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

manages to handle it.
For optimization and fewer bits storage, it is also common to use a small integer version, incrementing it by one during a row update: version = version + 1.

Источник:

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.