Concurrency and Row Versioning - Part 1

Throughout my career, I’ve occasionally encountered a particularly tricky issue: debugging a broken application state for only a few resources. 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 timestamp or a rowversion 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 lastUpdateTime precondition 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 Amazon DynamoDB 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.