Reliable Data Storage Using Optimistic Locking in Forge SQL

This article is a guest post by Vasyl Zakharchenko, an Atlassian developer community member.

Optimistic locking is a powerful technique for avoiding data corruption in applications that handle concurrent updates. In this article, we’ll demonstrate how to implement optimistic locking in Atlassian Forge using a simple but realistic example: a release readiness checklist.

👉 Example implementation is available here (written in Drizzle ORM): https://github.com/vzakharchenko/forge-sql-orm/tree/master/examples/forge-sql-orm-example-checklist

Default Checklist

Here’s the default checklist used when the issue is initialized:

export const defaultChecklist = [
  { label: "Feature flags verified", done: false },
  { label: "Support team notified", done: false },
  { label: "Release notes added", done: false },
  { label: "Linked issues closed", done: false },
  { label: "Changelog updated", done: false },
];

The Problem

Imagine your Forge app allows teams to manage a checklist for Jira issues before release.

Now imagine two users, Zoe and Marcus, open the same Jira issue page but at different times.

Zoe opens the issue in the morning and starts working through the checklist. She plans to mark:

• Release notes added
• Support team notified

But before she finishes, Marcus opens the same issue later that day and quickly marks:

• Feature flags verified
• Linked issues closed

Marcus submits his changes and sees a success message: “The checklist has been successfully updated.”

However, Zoe still sees the old version of the checklist (without Marcus’s updates). When she finishes her work and clicks Update, she also gets a success message — but unknowingly overwrites Marcus’s changes.

The final saved checklist only contains Zoe’s edits. Marcus’s updates are lost, even though they were valid and saved earlier.

This is a classic race condition: both users worked with outdated data, and the last save silently won.

The Solution: Optimistic Locking

Forge SQL offers a multi-tenant SQL solution, providing tenant isolation and stable query performance. Due to this, Forge SQL introduces an additional critical constraint:

• Only one query per .query() call — no chaining or batching.

Given this limitation, traditional transactional update flows (read → modify → write) aren’t feasible. Optimistic locking offers a practical and reliable alternative that works well within Forge SQL.

Instead of relying on in-transaction state, we treat the updated_at field as the version marker. Here’s how it works, using Zoe and Marcus’s scenario as an example:

1. Zoe opens the Jira issue first and receives the current checklist and updated_at timestamp from the backend.
2. Marcus later opens the same Jira issue and receives the current checklist (still unchanged) and the same initial updated_at timestamp.
3. Marcus quickly updates the checklist and successfully saves it with a new updated_at timestamp.
4. When Zoe finally tries to save her changes, her updated_at timestamp no longer matches the one in the database.

If no rows are affected during Zoe’s update, the system recognizes the conflict and notifies Zoe that Marcus has already updated the checklist. Thus, Marcus’s earlier changes are preserved, and Zoe is prompted to reload the data. This lets us enforce consistency in a single statement.

 How It Works

Schema Design

Forge SQL is backed by TiDB, a MySQL-compatible distributed database. Here’s the table definition using a DATETIME field as the versioning mechanism. The updated_at column is used for optimistic locking, while update_id and update_display_name store the identity of the last user who modified the record. This allows the UI to show who made the last change, which is helpful in collaborative environments:

CREATE TABLE `issue_check_list` (
  `issue_id` VARCHAR(255) NOT NULL,
  `check_list` JSON NOT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT (now()),
  `update_id` VARCHAR(255) NOT NULL,
  `update_display_name` VARCHAR(255) NOT NULL,
  CONSTRAINT `issue_check_list_issue_id` PRIMARY KEY(`issue_id`)
);

The updated_at column acts as the version field. Any update will include the value of updated_at from the original read. If it has changed, the update will be rejected.

Optimistic Locking Flow

1. Backend → Frontend: The app fetches the current checklist and the corresponding updated_at timestamp.

SELECT `issue_id`, `check_list`, `updated_at`, `update_id`, `update_display_name`
FROM `issue_check_list`
WHERE `issue_check_list`.`issue_id` = "COM-1";

2. Frontend: The user (e.g., Zoe) modifies the checklist in the UI.

3. Frontend → Backend: The updated checklist is submitted along with the original updated_at value (from the initial read). The server generates a new updated_at timestamp (e.g., the current server time) and performs an UPDATE, setting the new value, but only if the existing updated_at in the database still matches the old one. This ensures the update is accepted only if no one else (e.g., Marcus) has changed the checklist in the meantime.

UPDATE `issue_check_list`
SET `issue_id` = "COM-1",
    `check_list` = '[{"label":"Feature flags verified","done":true},{"label":"Support team notified","done":true},{"label":"Release notes added","done":true},{"label":"Linked issues closed","done":true},{"label":"Changelog updated","done":true}]',
    `updated_at` = "2025-05-18T12:12:00.000", -- new server-generated timestamp
    `update_id` = "marcus-account-id",
    `update_display_name` = "Marcus"
WHERE (`issue_check_list`.`issue_id` = "COM-1" AND `issue_check_list`
.`updated_at` = "2025-05-18T06:21:17.019"); -- previous timestamp from the original read

4. Backend → Frontend: If the UPDATE affects 0 rows, it means that the checklist was already modified by someone else (e.g., Marcus). The backend detects this as a version mismatch and returns a conflict response.

Successful update (Marcus):

Conflict detected (Zoe):

Depending on your app’s complexity, you have a few options to handle this:

• ❌ Show an error message — inform the user explicitly that someone (Marcus) modified the checklist. (This is the strategy used in the example app.)
• 🔄 Return both versions — allow the user to see both their attempted changes and the current version for manual resolution.
• 🔁 Auto-merge — implement backend logic to intelligently merge non-conflicting changes.

These strategies allow you to tailor conflict resolution effectively. Typically, explicitly warning the user (e.g., Zoe) and prompting a reload is the clearest option.

 Version Field Options

While this article uses a DATETIME field (updated_at) as the versioning mechanism, optimistic locking can be implemented using other types as well — as long as the value changes on every update and can be compared reliably.

 Option 1: updated_at (DATETIME)

• Must be NOT NULL
• Should be initialized with the current timestamp (e.g. DEFAULT (now()))
• Must include millisecond precision for consistency
• New timestamp should be generated server-side at each update
• Recommended: updated_at provides fewer opportunities for manual manipulation compared to numeric versions.

Option 2: version (INTEGER)

• Must be NOT NULL

• Starts from 0 or 1

• Should be incremented by +1 on every update

• You can use a TiDB sequence to generate version values automatically:

CREATE SEQUENCE version_seq;

CREATE TABLE issue_check_list (
issue_id VARCHAR(255) NOT NULL,
      check_list JSON NOT NULL,
version BIGINT NOT NULL DEFAULT (NEXTVAL(version_seq)),
  -- other fields...
  PRIMARY KEY(issue_id)
);

• Numeric versions can be more easily manipulated or incorrectly managed, making updated_at preferable for strict concurrency control.

In both cases, the version field should be passed from the frontend during the update and matched in the WHERE clause of the UPDATE query.

---

🚀 Running the Example

You can try this implementation locally using the example project (based on Drizzle ORM):

git clone https://github.com/vzakharchenko/forge-sql-orm.git
cd forge-sql-orm/examples/forge-sql-orm-example-checklist
npm install
forge register
forge deploy
forge install

Make sure you have the Forge CLI installed and authenticated.

---

Summary

The Zoe and Marcus example shows how important it is to consider situations where users might accidentally overwrite each other’s changes. Optimistic locking solves this by adding a simple version check — using either a timestamp (updated_at) or a number (version) — to every update. If the data has changed since it was last read, the update is rejected or handled safely.

This technique works especially well in Forge SQL, where you can’t use transactions or multi-step queries. A single UPDATE ... WHERE version = ? is enough to protect your data from race conditions.

Choose the versioning approach that works for you:

• Use updated_at for timestamp-based workflows
• Use version for numeric control and sequences

Either way, optimistic locking helps preserve the integrity of your users’ data — with minimal complexity.

P.S. Zoe and Marcus aren’t just example names — they’re inspired by my 7-year-old daughter and her best friend. Explaining race conditions with them in mind made this article a lot more fun to write