Database Reference
In-Depth Information
sandra's excellent support for UUIDs, we might as well use one and get the added advant-
age of knowing when a column was last updated:
ALTER TABLE "users" ADD "version" timeuuid;
Now we need to give each existing row a version. This can be done easily enough with a
WHERE…IN
update query:
UPDATE "users"
SET "version" = NOW()
WHERE "username" IN (
'alice', 'bob', 'carol', 'dave', 'eve', 'frank', 'gina',
'happycorp'
);
This will give every user profile the same initial version UUID, but that's fine; we just
need to make sure version numbers are unique over time within a single row.
Optimistic locking in action
Now it's time to implement actual optimistic locking. When we initially display the profile
to the two HappyCorp employees for editing, we'll keep track of the version that we dis-
played. Then, when the first employee goes to save their update, we'll perform a condi-
tional update to make sure that we're updating the same version that the user thinks we
are:
UPDATE "users"
SET "location" = 'New York, NY', "version" = NOW()
WHERE "username" = 'happycorp'
IF "version" = ec0c1fb7-321f-11e4-8eeb-5f98e903bf02;
The
IF
clause at the end of the query makes this a conditional update, another type of
lightweight transaction. You'll notice that, just like conditional inserts, conditional updates
give us feedback on whether the update was applied:
