DEV Community

Cover image for Prevent a Stale DynamoDB Update with a Conditional PartiQL Statement
miruky
miruky

Posted on

Prevent a Stale DynamoDB Update with a Conditional PartiQL Statement

#aws #dynamodb #database #tutorial

Introduction

Hi, I'm miruky.

Two writers can read the same Amazon DynamoDB item and then try to save different changes. If both writes are unconditional, the later write can overwrite a newer state without realizing that its copy was stale.

This Console run adds a numeric item_version attribute and includes the expected version in a PartiQL UPDATE statement. One update moves the item from version 1 to version 2, a deliberately stale version-1 update fails with ConditionalCheckFailedException, and a fresh version-2 update succeeds.

The table uses on-demand capacity and holds one synthetic item. Request and storage charges still follow the current DynamoDB pricing page, so review that page before leaving a table running or adapting the pattern to production traffic.

1. Create an on-demand table and seed version 1

DynamoDB tables and their data are Regional, so every table operation in this run stays in us-east-1. The first screenshot establishes the English Console and Regional context before any table exists.

The English DynamoDB Console shows United States (N. Virginia) on the Tables page.

The header confirms United States (N. Virginia) while the DynamoDB Console is in English. This fixes the Regional context before the table is created.

Open DynamoDB, choose Tables, and search for the exact generated name miruky-abwmppcmmfrhlnmh. The result should be empty before the validation creates a table.

The DynamoDB table search has no exact match for the generated table name.

The exact filter for miruky-abwmppcmmfrhlnmh reports 0 matched results and No tables found. That empty result establishes that the validation does not reuse an existing resource.

Choose Create table. Enter miruky-abwmppcmmfrhlnmh, set the partition key to item_id with type String, omit the sort key, and keep the default settings with on-demand capacity.

The create-table form shows the generated name, String partition key, and default settings.

The form combines miruky-abwmppcmmfrhlnmh, item_id, String, and the default table settings. No sort key is defined for this singleton-update exercise.

Wait until the table status is Active. The table needs no secondary index, stream, global-table replica, or sample data beyond the single item used for this comparison.

The table overview shows an Active table with item_id as its only primary-key attribute.

The overview shows Active and lists only item_id in the primary key. The next operation can now seed the single synthetic item.

Open PartiQL editor and run this singleton INSERT. The values are deliberately generic and contain no user, account, or application data.

INSERT INTO "miruky-abwmppcmmfrhlnmh" VALUE {
  'item_id': 'item-001',
  'item_state': 'draft',
  'item_version': 1
}
Enter fullscreen mode Exit fullscreen mode

The PartiQL editor inserts the synthetic item with draft state and item_version 1.

The editor reports a successful INSERT for the controlled item. A read is still needed to prove the stored attribute values.

Run a key-based SELECT. The result should contain one item with item_state set to draft and item_version set to 1.

SELECT * FROM "miruky-abwmppcmmfrhlnmh"
WHERE item_id = 'item-001'
Enter fullscreen mode Exit fullscreen mode

A key-based PartiQL SELECT returns the draft item at version 1.

The result shows item_state as draft and item_version as 1. This is the baseline that the first conditional update expects.

2. Apply a version-checked update

A writer expects version 1, so its condition matches the stored item. It changes the state and increments the version in the same single-item operation.

UPDATE "miruky-abwmppcmmfrhlnmh"
SET item_state = 'published'
SET item_version = 2
WHERE item_id = 'item-001' AND item_version = 1
RETURNING ALL NEW *
Enter fullscreen mode Exit fullscreen mode

The conditional UPDATE returns the item with published state and version 2.

The successful response returns published and version 2. Those post-update values establish why another writer still expecting version 1 is stale.

RETURNING ALL NEW * makes the successful post-update attributes visible in the response. The condition still resolves to exactly one primary-key value because the WHERE clause includes item_id = 'item-001'.

3. Reject a writer holding stale version 1

Now simulate another writer that read the original item but did not see the successful update. It still expects item_version = 1 and attempts to write a different state.

UPDATE "miruky-abwmppcmmfrhlnmh"
SET item_state = 'archived'
SET item_version = 3
WHERE item_id = 'item-001' AND item_version = 1
RETURNING ALL NEW *
Enter fullscreen mode Exit fullscreen mode

The stale PartiQL statement still expects version 1 while attempting to write version 3.

The editor visibly keeps item_version = 1 in the condition while requesting version 3. That mismatch is deliberate; the stored item is already at version 2.

Run the statement. DynamoDB should return ConditionalCheckFailedException because the stored item is already at version 2, so the complete WHERE condition is false.

The PartiQL editor reports ConditionalCheckFailedException for the stale version-1 update.

The editor reports ConditionalCheckFailedException rather than updated attributes. The rejected response alone does not prove the item stayed unchanged, so the next read checks the stored state.

The failed condition is the protection mechanism; it is not a partial success. Run the same key-based SELECT again and confirm that item_state remains published and item_version remains 2.

A follow-up key-based read shows that the rejected stale update changed neither state nor version.

The follow-up read still shows published and version 2. Neither protected attribute moved to the stale writer's requested values.

4. Retry from the current version

A client should not blindly repeat the stale statement. It should read the current item, reconcile its intended change, and submit a new condition using the version it actually observed.

For this controlled run, update the expectation to version 2 and advance the stored version to 3. The state change is otherwise the same as the rejected attempt.

UPDATE "miruky-abwmppcmmfrhlnmh"
SET item_state = 'archived'
SET item_version = 3
WHERE item_id = 'item-001' AND item_version = 2
RETURNING ALL NEW *
Enter fullscreen mode Exit fullscreen mode

The fresh conditional UPDATE returns archived state and item_version 3.

The fresh update returns archived and version 3 after its condition uses the current version. This contrasts with the rejected statement without changing the intended state transition.

Run one final key-based SELECT. The stored item should now contain archived and version 3, proving that the same logical change succeeds only after its expected version matches the current item.

The final key-based read returns the archived item at version 3.

The final read confirms archived and version 3 as the stored values. The three observed states now match the version-check sequence.

Wrap-up

The stale writer did not overwrite the newer DynamoDB item. Its version predicate failed, DynamoDB returned a condition error, and a read showed that both protected attributes remained at their version-2 values.

In an application, return a conflict to the caller or re-read and reconcile the latest item instead of hiding this failure in an unconditional retry. If several items must change together, use a DynamoDB transaction rather than treating multiple singleton PartiQL statements as one atomic unit.

Thanks for reading this far.

See you in the next one.

Disclosure: This article was written with AI assistance and independently verified against the linked primary sources and observed results.

References

Top comments (0)