Optimistic Offline Lock Implementer

Use when offline-concurrency-strategy-selector (or your team) has chosen Optimistic Offline Lock and you need to implement it correctly end-to-end. Handles:...

Security Scan

This instruction-only skill is internally consistent with its stated goal (implementing optimistic offline locking); it asks the agent to read and edit the codebase and produce design/test artifacts and does not request unexpected credentials, installs, or network endpoints.

Purpose & Capability

Name/description claim implementing Optimistic Offline Lock; the instructions, reference checklists, migration examples, ORM snippets, and required tools (Read/Grep/Edit/Write) align with that purpose. It sensibly expects access to the persistence-layer source and schema files. There are no unrelated requirements (no cloud creds, unrelated binaries, or external services).

Instruction Scope

SKILL.md scopes runtime behaviour to analyzing and modifying the codebase, producing schema/ORM/config changes, and drafting UX/test plans. The instructions reference grep/SQL/ORM patterns and recommend re-querying for modified_by/modified_at on collisions. It does not instruct reading unrelated system files, exfiltrating secrets, or calling unknown external endpoints. It does require the agent to read and potentially edit repository files — which is appropriate for an implementer skill.

Install Mechanism

Instruction-only skill with no install spec and no code files executed on install; lowest-risk install posture.

Credentials

The skill declares no required environment variables, credentials, or config paths. That is proportionate: implementing DB/ORM changes requires codebase access (declared) and a human will supply any DB credentials needed for manual testing; the skill itself does not ask for unrelated secrets.

Persistence & Privilege

always:false (normal). The skill asks for Read/Edit/Write access to the repository (appropriate for making implementation changes). If you allow autonomous invocation or automatic edits, review generated changes before committing. No evidence it modifies other skills or system-wide agent settings.

Assessment

This skill appears coherent and focused: it will analyze and modify your persistence layer and produce an implementation plan, migration snippets, ORM config, collision-UX guidance, and tests. Before installing or granting it write permissions, ensure you: 1) keep your normal code review/PR process (do not let the agent push changes directly into production branches), 2) run the generated DB migrations and tests in a sandbox or CI, and 3) provide any required DB credentials only to trusted humans/tools when performing live testing. If you plan to let the agent run autonomously with repository write access, require approvals or review hooks so changes are inspected before merging.

When to Use

This skill implements the Optimistic Offline Lock pattern after offline-concurrency-strategy-selector (or your team) has confirmed it is the right concurrency strategy. The pattern applies when a business transaction spans multiple HTTP requests — user loads a record, edits for seconds or minutes, then saves — and the collision frequency is low enough that detecting conflicts at commit time is acceptable.

Do not use if conflicts are frequent or rework cost is unacceptably high (use pessimistic locking instead). Do not use if the entire workflow fits in a single request/transaction (use database isolation levels).

Context & Input Gathering

Collect this before proceeding. Grep the codebase or ask the user directly.

Required:

1. Stack and ORM — Java/Hibernate, C#/EF Core, Python/SQLAlchemy, Ruby on Rails, Node.js/Knex, or hand-rolled SQL?
2. Entities needing protection — which tables/domain classes have concurrent-edit risk?
3. Schema mutability — can version columns be added, or is the schema frozen (legacy)?
4. Client round-trip — does a web/mobile client need to hold the version between GET and PUT/POST?
5. Unit of Work present? — is there an explicit UoW or does each repository method own its transaction?
6. Inconsistent read risk? — are there reads whose correctness the commit depends on (not just writes)?

Observable from codebase:

• Existing version or lock_version columns → implementation may be partially in place; audit for correctness
• @Version, [ConcurrencyCheck], version_id_col annotations → ORM-native path
• UPDATE statements without WHERE version clause → lost-update vulnerability
• API response DTOs → check whether version field is included

Sufficiency: If ORM and entity list are known, proceed. If schema is frozen, plan the all-fields-WHERE fallback (see References).

---

Process

Step 1 — Confirm strategy selection

Verify that offline-concurrency-strategy-selector chose Optimistic Offline Lock for this workflow.

• IF the selector skill was not run → invoke offline-concurrency-strategy-selector first, OR ask: "Is collision frequency low AND is rework cost acceptable if a save is rejected?" If yes, Optimistic applies.
• IF Pessimistic was chosen → use pessimistic-offline-lock-implementer instead.

WHY: This skill implements Optimistic-specific mechanics. Applying it to a high-collision, high-rework-cost workflow produces a poor user experience — users lose significant work on every collision.

---

Step 2 — Identify entities and design the version column

List every entity (table) that participates in concurrent-edit business transactions.

For each entity:

Integer version column (primary approach):

ALTER TABLE {table} ADD COLUMN version INTEGER NOT NULL DEFAULT 0;
ALTER TABLE {table} ADD COLUMN modified_by VARCHAR(255);
ALTER TABLE {table} ADD COLUMN modified_at TIMESTAMP;

ORM-native equivalents (preferred when ORM is in use):

ORM	Annotation / Config	Exception thrown
Hibernate / JPA	@Version on int version field	OptimisticLockException
EF Core (.NET)	[ConcurrencyCheck] on field, or [Timestamp] (rowversion)	DbUpdateConcurrencyException
SQLAlchemy	__mapper_args__ = {"version_id_col": version_col}	StaleDataError
Rails ActiveRecord	lock_version INTEGER DEFAULT 0 column (auto-detected)	ActiveRecord::StaleObjectError
Django (no built-in)	Manual: filter on version + check updated_count, or django-concurrency package	Custom exception

WHY: Using an integer version is deterministic and monotonically increasing. Timestamps are "simply too unreliable, especially if you're coordinating across multiple servers" (Fowler) — clock skew causes missed conflicts and false collisions. The modified_by and modified_at columns are not used in the WHERE clause; they provide context for the error message shown to the user.

If the schema cannot be changed → fall back to the all-fields WHERE clause approach (see References).

---

Step 3 — Plumb version capture on read

Every place the entity is loaded must capture and carry the version.

Domain object / entity: The version field must be part of the object, not a separate variable. This ensures the Identity Map returns the same version consistently within a business transaction.

// Java domain object base class
class DomainObject {
    private int version;
    private String modifiedBy;
    private Timestamp modifiedAt;
    // setSystemFields() called by mapper after load — NOT constructor
}

API/DTO round-trip (for web/mobile clients): If a client loads the record in one HTTP request and saves in another, the version must travel to the client and back.

// GET /customers/42
{ "id": 42, "name": "Smith", "version": 7, ... }

// PUT /customers/42
{ "id": 42, "name": "Smythe", "version": 7, ... }

The server extracts version from the request body and uses it in the WHERE clause.

WHY: If the version is not round-tripped, the server has no way to know what version the client read. It would either have to reject all saves (too strict) or skip the check (defeats the pattern). The client must be a faithful carrier of the version it received.

Identity Map check: In the persistence layer, find(id) must check the Identity Map before querying the DB. Loading the same record twice at different version values within one business transaction produces undefined behavior in version checks.

---

Step 4 — Implement version-conditioned writes

UPDATE (modify):

UPDATE customer
SET name = ?, modified_by = ?, modified_at = ?, version = ?
WHERE id = ? AND version = ?
-- Parameters: (newName, currentUser, now, oldVersion+1, id, oldVersion)

DELETE:

DELETE FROM customer WHERE id = ? AND version = ?
-- Parameters: (id, oldVersion)

Check row count immediately:

int rowCount = stmt.executeUpdate();
if (rowCount == 0) {
    throwConcurrencyException(object);  // see Step 5
}

For ORM stacks: Enable the built-in version field (Step 2 annotation). The ORM generates version-conditioned SQL automatically and throws its native exception. Catch that exception at the service/controller boundary (Step 5).

WHY: The WHERE clause atomically validates the version AND applies the data change in a single SQL statement. This is the only reliable way to ensure "nobody changed this between my read and my write" without holding a database lock across user think-time. A separate SELECT + UPDATE pair has a TOCTOU race condition.

The version increment (version = oldVersion + 1) is essential: it marks the row as changed so any concurrent session holding the old version will see row count 0 on their save.

---

Step 5 — Implement collision detection and error context

When row count = 0, determine WHY before throwing:

protected void throwConcurrencyException(DomainObject obj) {
    // Re-query to differentiate "modified" from "deleted"
    ResultSet rs = execute("SELECT version, modified_by, modified_at FROM customer WHERE id=?", obj.getId());
    if (rs.next()) {
        int dbVersion = rs.getInt("version");
        String who = rs.getString("modified_by");
        Timestamp when = rs.getTimestamp("modified_at");
        throw new ConcurrencyException(
            "Customer " + obj.getId() + " was modified by " + who +
            " at " + format(when) + ". Please reload and re-apply your changes."
        );
    } else {
        throw new ConcurrencyException(
            "Customer " + obj.getId() + " was deleted by another session."
        );
    }
}

WHY: A bare "concurrency error" leaves the user confused and helpless. Informing them WHO changed the record and WHEN lets them make an informed decision: was it a conflicting edit or just a background sync? The re-query is safe here because it runs inside the same system transaction that is about to be rolled back — it reads the committed DB state.

---

Step 6 — Design collision handling UX

Catching the exception at the controller/service boundary is not enough — the user must understand what happened and have a meaningful path forward.

Minimum acceptable UX (abort + inform):

"This record was modified by Alice at 2:34 PM. Your changes could not be saved. Please reload the record and re-apply your changes." [Reload] [Cancel]

Better UX (show conflict): Show the user's proposed changes alongside the current DB state side by side. Let them choose which values to keep field by field.

Advanced UX (merge or force-save):

• Merge: auto-merge non-overlapping field changes (works when two users edited different fields — similar to how source control merges non-conflicting lines).
• Force-save: user explicitly accepts that their version will overwrite the current DB state. Requires a deliberate action (not the default). Load the latest version, then re-apply the user's changes on top.

API semantics: Return HTTP 409 Conflict with a body that includes currentVersion, modifiedBy, modifiedAt, and the conflicting field values.

WHY: "A proper application will tell when the record was altered and by whom" (Fowler). Silent failure or a bare error code is the most common implementation mistake — users lose work and don't know why. The quality of the collision UX often determines whether Optimistic Locking is tolerable in practice.

---

Step 7 — Integrate with Unit of Work commit

If the system uses a Unit of Work pattern, the commit sequence must:

checkConsistentReads()   ← version-check read-set objects (if inconsistent read protection needed)
insertNew()
deleteRemoved()
updateDirty()            ← version-conditioned UPDATEs live here

On ANY ConcurrencyException during commit: rollback the system transaction before re-throwing.

public void commit() {
    try {
        checkConsistentReads();
        insertNew();
        deleteRemoved();
        updateDirty();
    } catch (ConcurrencyException e) {
        rollbackSystemTransaction();  // CRITICAL — do not forget
        throw e;
    }
}

Inconsistent read protection (optional but recommended): if the commit's correctness depends on a value that was READ but not WRITTEN (e.g., customer address used for tax calculation), register that object in a read-set and version-check it at commit:

public void registerRead(DomainObject obj) { reads.add(obj); }

public void checkConsistentReads() {
    for (DomainObject obj : reads) {
        obj.getVersion().increment();  // forces version check even for read-only objects
    }
}

WHY: Without rollback, partial writes enter the database — some records updated, others not — leaving data in an inconsistent state. The UoW is the natural integration point because it already owns the system transaction boundary and the commit sequence.

If the Unit of Work skill (unit-of-work-implementer) is available, reference its commit sequence and add version-conditioned writes in the updateDirty() and deleteRemoved() stages. If that skill has not been run, implement the commit loop directly in the repository or service class.

---

Step 8 — Audit for common anti-patterns

Run this checklist against the codebase before marking implementation complete:

• Every UPDATE and DELETE on versioned tables includes AND version=? — grep for UPDATE {table} and verify WHERE clause
• Version is incremented on every successful save — verify version = version+1 (or ORM equivalent), not version = version
• Row count is checked after every UPDATE/DELETE — no unchecked executeUpdate() calls
• Collision error message includes who and when — not just "concurrency error"
• Version is included in API responses and request bodies (for web clients) — check DTO classes and OpenAPI spec
• No timestamp used as version substitute — search for modified_at or updated_at in WHERE clauses
• Retry logic (if any) reloads the object first — stale in-memory object with old version must not be reused directly
• No raw SQL UPDATE paths that bypass ORM version mechanism — grep for raw SQL on ORM-managed tables
• System transaction is rolled back on ConcurrencyException — not just re-thrown

WHY: Each of these is a known failure mode. Missing the WHERE clause is the most dangerous — it silently permits Lost Updates, the exact problem this pattern exists to prevent. Non-incremented version means subsequent writers see no new version and can overwrite without conflict. Stale retry immediately throws another collision without making progress.

---

Step 9 — Write the two-writer concurrency test

A test that exercises the actual collision scenario:

# Pseudocode — adapt to your framework's test infrastructure
def test_optimistic_lock_collision():
    record = create_record(name="original", version=0)

    # Session A loads at version 0
    session_a_record = load(record.id)  # version=0

    # Session B loads at version 0, edits, saves first
    session_b_record = load(record.id)  # version=0
    session_b_record.name = "session_b_edit"
    save(session_b_record)  # succeeds, version becomes 1

    # Session A tries to save — version 0 no longer matches DB version 1
    session_a_record.name = "session_a_edit"
    with raises(ConcurrencyException):
        save(session_a_record)  # must raise — row count = 0

    # Verify DB holds session B's value, not session A's
    current = load(record.id)
    assert current.name == "session_b_edit"
    assert current.version == 1

WHY: Unit tests on individual UPDATE SQL are insufficient. The collision scenario requires two independent sessions reading the same version and one writing first. Without this test, it is easy to ship an implementation that increments correctly in isolation but fails to detect collisions in practice (e.g., version not in WHERE clause but version correctly incremented).

---

Step 10 — Produce the implementation plan artifact

Output the Optimistic Offline Lock Implementation Plan (see Outputs section).

WHY: A written plan consolidates all decisions made in Steps 1–9 into a reviewable artifact. It serves as the spec for implementation (or review checklist for existing code), makes the anti-pattern audit explicit, and gives the team a shared reference for schema changes, ORM config, and UX behavior.

Inputs

• Entity/table list requiring concurrent-edit protection
• Stack, ORM, and language (determines implementation path)
• Schema mutability (can version columns be added?)
• API contract (does client need to round-trip version?)
• Existing persistence code (mapper, repository, or ORM entities)
• Unit of Work implementation (if present)

Outputs

Optimistic Offline Lock Implementation Plan

## Optimistic Offline Lock Implementation Plan: [Feature/Entity Name]

**Date:** YYYY-MM-DD | **Stack:** [ORM / language] | **Entities:** [list]

### 1. Schema Changes
- [ ] ALTER TABLE {table} ADD COLUMN version INTEGER NOT NULL DEFAULT 0;
- [ ] ALTER TABLE {table} ADD COLUMN modified_by VARCHAR(255);
- [ ] ALTER TABLE {table} ADD COLUMN modified_at TIMESTAMP;

### 2. ORM Config
[@Version / [ConcurrencyCheck] / version_id_col / lock_version]

### 3. Version Round-Trip Path
- GET /resource/{id} → includes "version" in response
- PUT /resource/{id} → client sends "version" back in body
- Server extracts version from: [request.body.version / session state]

### 4. Write Implementation
- UPDATE: WHERE id=? AND version=? with version=oldVersion+1
- DELETE: WHERE id=? AND version=?
- Row-count check: rowCount == 0 → throwConcurrencyException()

### 5. Collision Handling
- Exception: [ConcurrencyException / OptimisticLockException / custom]
- Re-query: SELECT version, modified_by, modified_at WHERE id=?
- UX: [error+reload / diff view / force-save] | API: 409 Conflict

### 6. Unit of Work Integration
- Commit: checkConsistentReads → insertNew → deleteRemoved → updateDirty
- Rollback on ConcurrencyException: [yes / repository-per-request N/A]

### 7. Anti-Pattern Checklist
[Completed from Step 8]

### 8. Test Plan
- [ ] Two-writer test: session B saves first → session A gets ConcurrencyException
- [ ] Delete collision: record deleted → "deleted" message
- [ ] Retry: reload + re-save succeeds after collision

Key Principles

1. The version WHERE clause is the lock — it must be in every UPDATE and DELETE. A version column stored and incremented but absent from the WHERE clause provides zero protection. The atomic "validate + write" in a single SQL statement is what makes the pattern work without holding a DB connection across user think-time. Any write path missing the version WHERE clause silently permits Lost Updates.

2. Integer version, not timestamp — always. System clocks are unreliable across servers. Sub-millisecond updates can share a timestamp. An integer version is monotonically increasing and immune to clock skew. Fowler: "system clocks are simply too unreliable, especially if you're coordinating across multiple servers."

3. Round-trip the version — client must carry it faithfully. The version must travel from the GET response to the PUT/POST request body. A server that re-reads the version from DB at save time defeats the pattern — it always gets the latest version and never detects conflicts.

4. The collision message must name who and when. "Your save failed" is not acceptable. Users need to know WHO changed the record and WHEN. Store modified_by and modified_at and re-query on collision. Implementing only the technical check without actionable UX is a half-implementation.

5. Roll back the system transaction on collision — without exception. If any write fails the version check, roll back the system transaction before re-throwing. Partial commits leave data inconsistent (some records written, others not). Fowler: "Do not forget this step!"

6. Stale retry fails again — always reload before retry. Catching ConcurrencyException and re-saving the same domain object immediately fails again (it still holds the old version). Reload the object from DB first, re-apply changes, then save.

7. Move version mechanics into the abstract mapper — Implicit Lock prevents gaps. One developer omitting a version check on one entity silently breaks the scheme for that entity. The abstract AbstractMapper supertype (or ORM base config) makes version-conditioned writes mandatory — concrete mappers cannot accidentally skip them.

Examples

Example 1: Java/Spring Data JPA — CRM Customer Entity

Scenario: CRM where 15 sales reps edit customer records, 2–5 min sessions, low collision rate. Optimistic chosen.

Trigger: "Lost updates on customer records when two reps edit the same customer."

Process:

• Step 2: Add @Version private int version; to Customer entity. Hibernate auto-generates version-conditioned SQL.
• Step 3: Include "version": 7 in CustomerDTO. Controller extracts it from PUT body.
• Step 4: customerRepository.save(customer) → Hibernate issues UPDATE customer SET ... WHERE id=? AND version=?.
• Step 5: Catch OptimisticLockException in @ExceptionHandler. Re-query for modifiedBy/modifiedAt. Return HTTP 409.
• Step 6: UI shows: "This customer was updated by Bob at 3:12 PM. Please reload and re-apply your changes."
• Step 7: @Transactional handles rollback automatically.

Output: @Version on Customer entity, DTO round-trips version, 409 handler with who/when, two-writer test.

---

Example 2: Ruby on Rails — Inventory Management

Scenario: Warehouse staff edit product records via Rails admin. 1–3 min sessions.

Trigger: "Two staff sometimes update the same product simultaneously — add optimistic locking."

Process:

• Step 2: rails generate migration AddLockVersionToProducts lock_version:integer. Rails auto-detects lock_version.
• Step 3: Hidden field in form: <%= f.hidden_field :lock_version %>. Permit in strong params.
• Step 4: product.update!(params) → Rails: UPDATE products SET ... WHERE id=? AND lock_version=?.
• Step 5: rescue ActiveRecord::StaleObjectError in controller. Re-query for updated_by/updated_at.
• Step 6: Flash: "Someone else updated this product. Here's what changed — please review and re-submit."

Output: Migration, hidden field, rescue StaleObjectError, diff-style conflict display.

---

Example 3: Node.js + Knex — Custom Repository

Scenario: Node.js SaaS, hand-rolled Knex repositories, no ORM. Entities: contracts, line_items.

Trigger: "Add version-based optimistic locking to our contract editing API — no concurrency protection today."

Process:

• Step 2: ALTER TABLE contracts ADD COLUMN version INTEGER NOT NULL DEFAULT 0, modified_by, modified_at.
• Step 3: GET response includes version. PUT body sends it back. Repository receives claimedVersion.
• Step 4: knex('contracts').where({ id, version: claimedVersion }).update({ ...fields, version: claimedVersion+1 }) → check count === 0.
• Step 5: ConcurrentModificationError re-queries modified_by/modified_at and attaches to error.
• Step 6: Express error handler returns 409 { error: "Conflict", modifiedBy, modifiedAt }.
• Step 7: Multi-table commits use knex.transaction() with rollback on ConcurrentModificationError.

Output: Migration, version-conditioned repository, ConcurrentModificationError, 409 handler, two-writer test.

References

• Version Column SQL and ORM Config Reference — SQL DDL for version columns, ORM-specific annotations and config, all-fields WHERE fallback for frozen schemas
• Collision UX Patterns — abort+inform, diff view, merge, force-save — with UI copy templates and API 409 response schema
• Anti-Pattern Detection Checklist — grep patterns and code audit queries for each of the 9 anti-patterns, with example buggy code and correct fix
• Unit of Work Integration Guide — commit sequence, read-set registration, rollback wiring, and Java example from the book

License

This skill is licensed under CC-BY-SA-4.0. Source: BookForge — Patterns of Enterprise Application Architecture by Martin Fowler, David Rice, Matthew Foemmel, Edward Hieatt, Robert Mee, Randy Stafford.

Related BookForge Skills

Install related skills from ClawhHub:

• clawhub install bookforge-offline-concurrency-strategy-selector
• clawhub install bookforge-unit-of-work-implementer
• clawhub install bookforge-transaction-isolation-level-auditor

Or install the full book set from GitHub: bookforge-skills