Keeping data consistent is a major challenge when multiple users or processes can update the same information at the same time. In collaborative software environments, race conditions can occur where concurrent updates interfere with each other. One user’s changes might accidentally overwrite another’s, leading to lost data or corruption. For Atlassian Forge applications (which run in a multi-tenant cloud environment), this problem is especially pertinent because Forge’s SQL database allows only one query per call, making traditional multi-step transactions impossible. How do we ensure reliable data storage under these conditions? The answer is optimistic locking, a technique that checks for concurrent modifications and preserves data integrity without heavy locking mechanisms.
In this blog post, we’ll explore how optimistic locking works in the context of Atlassian Forge, using a simple scenario of a release readiness checklist in Jira. We’ll walk through the problem of concurrent updates, then demonstrate an optimistic locking solution step-by-step, including code snippets and practical implementation details. By the end, you’ll see how a small change, adding a version timestamp to each record, can prevent race conditions and keep your Forge app’s data reliable.
To illustrate the challenge, consider a scenario with two users (let’s call them Zoe and Marcus) managing a Jira issue’s release-readiness checklist. This checklist is stored in a Forge app’s database table and needs to be updated as items are completed. Now imagine the following sequence of events:
Zoe opens the issue first: In the morning, Zoe opens the Jira issue and loads the checklist. She starts checking off a couple of items (for example, "Release notes added" and "Support team notified") but hasn’t submitted her changes yet.
Marcus opens the issue later: Around midday, Marcus opens the same Jira issue. Since Zoe hasn’t saved her progress, Marcus sees the original, unmodified checklist. He quickly ticks off a few other items (say, "Feature flags verified" and "Linked issues closed") and submits his changes. The system saves Marcus’s updates and confirms with a success message.
Zoe continues unaware: Zoe, still with the issue open from earlier, doesn’t see Marcus’s new changes. She finishes her part of the checklist and clicks the Update button to save her progress. The app saves Zoe’s updates and shows a success message as well.
The outcome, a race condition: Because Zoe’s view was outdated (it didn’t include Marcus’s ticks), her save operation overwrote the checklist state with only her changes. Marcus’s earlier updates were lost entirely, even though his save was successful. Both users were operating on stale data, and the last writer wins: in this case, Zoe’s update clobbered Marcus’s work.
This scenario is a classic example of a concurrent update problem: each user unknowingly worked on an old copy of the data. Without safeguards, the last update will silently win, and any intermediate updates are overwritten. In a production environment, such lost updates can be frustrating for users and harmful to data integrity.
In traditional database systems, one way to handle concurrent updates is to use transactions or pessimistic locking (locking a record so only one user can modify it at a time). However, Forge SQL has a design constraint: you can’t perform multiple queries or multi-step transactions in a single request. This is because Forge’s serverless SQL (powered by TiDB under the hood) restricts each invocation to a single query for performance and multi-tenant isolation. Given this limitation, our Forge app can’t simply “BEGIN TRANSACTION; SELECT ... FOR UPDATE; UPDATE ...; COMMIT;” to guard against concurrent writes.
Optimistic locking is a practical alternative that fits well with Forge’s model. Instead of locking data in advance, optimistic locking allows concurrent reads and uses a version check at update time to detect conflicts. The core idea is to give each record a version identifier (often a timestamp or a version number) that changes every time the record is updated. When an update is attempted, the application includes the last known version. The database will only apply the update if the version matches what’s currently stored (meaning no one else has changed it in the meantime). If the version doesn’t match, the update is refused because another update happened since the data was read.
In our checklist example, optimistic locking will prevent Zoe’s stale update from overwriting Marcus’s changes. Here’s how: when Zoe and Marcus each load the checklist, they also retrieve a version marker (for example, an updated_at timestamp). Marcus saves first, and the system updates the checklist’s data and generates a new updated_at timestamp. When Zoe tries to save, the app notices that the updated_at she sent (the old value) does not match the current updated_at in the database (the new value from Marcus’s save). The update operation will then affect 0 rows: signaling a conflict. Instead of blindly overwriting, the app can catch this and inform Zoe that the data was updated by someone else, prompting her to refresh and merge changes if needed. In this way, optimistic locking turns a silent data loss into a detected conflict that can be handled gracefully.
To implement optimistic locking, we need to add a version field to our data schema. Forge’s SQL database is MySQL-compatible (TiDB), so we can use a typical table design with an extra column for versioning. In our release checklist example, the table might look like this:
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,
PRIMARY KEY(issue_id)
);
In this schema:
issue_id is the primary key (each Jira issue has one checklist).
check_list stores the checklist items in JSON format (each item has a label and a done status).
updated_at is our version field: a timestamp that records when the record was last updated. It’s set to the current time by default for new records (DEFAULT (now())), and Forge ensures millisecond precision for consistency.
update_id and update_display_name store who made the last change (the user’s account ID and name). These aren’t strictly required for optimistic locking, but they’re useful for showing in the UI who last updated the checklist.
The updated_at column will serve as the optimistic locking check. Every update will include a condition that the updated_at in the database must match a specific value (the one the client last saw) before it updates to a new value. If it doesn’t match, the update will not proceed.
How does the optimistic locking process actually work in our Forge app? Let’s break down the flow between the frontend and backend when updating the checklist:
Initial Read: When a user opens the issue, the Forge app’s backend sends the current checklist data along with the current updated_at timestamp. For example, Zoe opens the issue and receives a JSON checklist plus an updated_at like "2025-05-18T06:21:17.019" (which is stored in the database).
User Makes Changes: The user interacts with the checklist on the frontend (e.g., checking off items). This could be a single item or multiple items over some time. Importantly, the app still holds onto that original updated_at value from when the data was loaded.
Submit Update: When the user clicks the update/save button, the frontend sends the modified checklist data to the backend along with the original updated_at it was given. The backend now prepares an SQL UPDATE statement that includes a version check. It will attempt to set a new updated_at (the current server time) and update the checklist, but only where the row’s issue_id matches and updated_at still equals that original timestamp from step 1.
Database Update Attempt: The database executes the UPDATE ... WHERE ... query. Two outcomes are possible:
Success (No Conflict): If the row’s updated_at still matches the old value, it means no one else has updated the record in the meantime. The row is updated with the new checklist data, and updated_at gets a new timestamp. The query reports 1 row affected (one row changed).
Failure (Conflict Detected): If the row’s updated_at does not match (meaning another update occurred after our read), the WHERE clause fails to find a row and nothing is updated. The query reports 0 rows affected.
Handling the Outcome: The Forge backend checks how many rows were affected. If it’s 1, we return a success response to the user (and maybe include the new updated_at and who updated it). If it’s 0, we know a conflict happened: someone else saved changes first. In that case, the backend can respond with a special error or conflict message. The frontend should then alert the user that their update wasn’t saved because the data was updated by someone else, and perhaps prompt them to reload the latest checklist and retry their changes.
Here’s a simplified example of what the update query might look like on the server side for Marcus’s update, and then how it prevents Zoe’s conflicting update:
-- Marcus's update (no conflict, will succeed)
UPDATE issue_check_list
SET
check_list = '[{"label": "Feature flags verified", "done": true}, ...]',
updated_at = "2025-05-18T12:12:00.000", -- new timestamp generated by server
update_id = "marcus-account-id",
update_display_name = "Marcus"
WHERE issue_id = "COM-1"
AND updated_at = "2025-05-18T06:21:17.019"; -- matches the timestamp Marcus received earlier
-- Zoe's update (conflict, will affect 0 rows because updated_at no longer matches)
UPDATE issue_check_list
SET
check_list = '[{"label": "Feature flags verified", "done": true}, ...]',
updated_at = "2025-05-18T12:30:00.000", -- Zoe's new timestamp
update_id = "zoe-account-id",
update_display_name = "Zoe"
WHERE issue_id = "COM-1"
AND updated_at = "2025-05-18T06:21:17.019"; -- this no longer matches the current timestamp, so no rows update
In the above sequence, Marcus’s update succeeds and changes the updated_at to "2025-05-18T12:12:00.000". When Zoe’s update tries to run, it still expects the old "2025-05-18T06:21:17.019". The WHERE clause fails because the row’s timestamp is now different (due to Marcus’s save). This is the optimistic locking in action: the operation “optimistically” assumes no one else changed the data, but verifies that assumption with the WHERE check. Since the assumption was wrong in Zoe’s case, the update is safely aborted.
Detecting a conflicting update is only half the battle; we also need to decide how to handle it in the user experience. Optimistic locking lets us catch the conflict; from there, different applications might choose different conflict resolution strategies. Here are some common approaches when an update conflict is detected:
Display an Error and Reload: The simplest approach is to inform the user that their update couldn’t be saved because someone else modified the data. For example, Zoe might see a message: “Update failed: the checklist was changed by another user. Please reload and try again.” This makes it clear that her version was stale. The app can then refresh the checklist to show Marcus’s changes, and Zoe can reapply her changes on top if needed.
Present Both Versions for Merge: A more user-friendly (but complex) approach is to return both the user’s attempted changes and the latest saved version from the database. The UI could then help the user manually merge their changes with the other person’s changes. In our example, Zoe could be shown her version of the checklist alongside the current version (including Marcus’s ticks) and decide how to combine them.
Automatic Merge: In some cases, the app can intelligently merge changes without user intervention. This works best if updates are in different parts of the data and don’t directly conflict. For instance, if Zoe and Marcus checked off completely different items, the system might automatically merge their changes (since no item was marked by both). However, auto-merging requires careful logic and testing to avoid introducing new errors.
Each approach has its trade-offs. The first option (simple error and reload) is straightforward and unambiguous, which is often preferred for clarity; it ensures the user works off the latest data. The latter options improve convenience but add complexity. For many Forge apps, explicitly warning the user of a conflict and prompting a refresh is the safest route.
We’ve used a timestamp (updated_at) as the version indicator in our example, but it’s worth noting that any consistently incrementing value can serve as a version field. The key requirement is that the field changes reliably with each update and can be checked for equality. Here are two common options for version fields, with their pros and cons:
Option 1 Timestamp (updated_at): Using a date-time field that updates to the current time on each modification.
Must be NOT NULL and ideally defaults to the current timestamp (so new records get an initial value automatically).
Should include milliseconds (or higher precision) to ensure each update gets a unique timestamp, even if multiple updates occur in quick succession.
The new timestamp is generated server-side at update time (to avoid any client clock issues or duplicates).
Why use a timestamp? It’s hard to fake or guess and carries useful information (when the last update happened). Since it’s time-based, it’s straightforward and has a lower chance of misuse compared to a manual counter. It’s also human-readable for debugging or logging.
Option 2 Numeric Version Counter (version): Using an integer that increments with every update.
Must be NOT NULL and typically starts at 0 or 1.
Every time you update the record, increase this number by 1 (ensuring the new value is unique and greater than the old).
You can use database features to help auto-increment. For example, TiDB/MySQL can use a sequence or auto-increment column. One approach is to create a separate sequence and default the version to NEXTVAL(sequence), so it gets a new number on each insert/update.
Why use a numeric version? It’s a simple counter. However, numeric versions might be easier to accidentally reset or misuse (someone could, in theory, set a wrong version number in a manual query). They also don’t convey any temporal information. In practice, timestamps are often preferred for their simplicity and additional context.
Regardless of which approach you choose, the front-end must send the last seen version value with any update, and the back-end must include that in the WHERE clause of the update statement. Both methods achieve the same goal: detect if the record has been updated between read and write.
If you want to see optimistic locking in action, you can try out a Forge app example that implements this release checklist scenario. The example is open-source, and you can run it locally in your development environment. Here’s how to get started:
Clone the repository for the Forge checklist example (which uses Forge SQL with an ORM for convenience):
Navigate to the example project within the repository:
cd forge-sql-orm/examples/forge-sql-orm-example-checklist
Install dependencies for the Forge app:
npm install
Deploy and install the app to your development Jira instance using the Forge CLI:
forge register # if not already registered
forge deploy
forge install # select a site and install the app on a test project
Once installed, open a Jira issue and you should see the checklist module provided by the app. Try opening the same issue in two browser windows (to simulate two users) and reproduce the Zoe/Marcus scenario; you’ll see how the app handles the concurrent updates gracefully with optimistic locking.
(Make sure you have the Atlassian Forge CLI set up and you’re logged in with an Atlassian developer account before running the above commands.)
Data consistency in concurrent environments is crucial. Our Zoe and Marcus story highlights how easily a race condition can occur when two people unknowingly work on the same data. Optimistic locking offers a lightweight yet effective safeguard against these situations. By introducing a version check (whether a timestamp or a simple counter), we turned a potential data loss into a manageable event. In the context of Atlassian Forge, where we can’t use traditional database transactions, this technique is especially powerful: it lets a single SQL query both update data and enforce consistency.
By implementing optimistic locking in your Forge apps, you protect your users’ data from accidental overwrites without adding much complexity. The next time you design a Forge app that involves collaborative data editing, consider adding a version field and an update check. It’s a small change that makes your data storage far more reliable in the face of concurrent use.
Remember, whether you use a timestamp (updated_at) or a numeric version field, the principle is the same: “Check before you commit.” This way, your app will politely refuse to save stale data and will guide users to resolve conflicts instead of silently losing updates. That leads to happier teams and a more trustworthy app experience.