Atomic optimistic locking on a PostgreSQL table doesn’t fail if clause not satifisfied

Let’s say I have the table definition below:

CREATE TABLE dummy_table (
    id        SERIAL PRIMARY KEY ,
    version   INT NOT NULL,
    data      TEXT NOT NULL);

INSERT INTO dummy_table(version, data)
VALUES (1, 'Stuff');

UPDATE dummy_table
SET version = 2
WHERE id = 1
AND version = 1;

Which basically gives a table in the state below:

id     version     data
1      2           'Stuff'

Now if several optimistic locking update statements are received by the database engine, how to make sure that they fail if the version is not the current one, for instance:

UPDATE dummy_table
SET version = 1
WHERE id = 1
AND version = 1;

If the conditions in the clause cannot be satisfied, the update will not happen. The problem is that, there is actually no error given as feedback when executing that statement.

I tried the solutions available here but I’m not sure the solutions given are actually atomic:

UPDATE dummy_table
SET version = 1
WHERE id = 1
AND version = 1
RETURNING id;

Does not return anything and does not throw any exception if the clause is not satisfied.

DO $$
BEGIN
    UPDATE dummy_table 
    SET version = 1 
    WHERE id = 1;
    IF NOT FOUND THEN RAISE EXCEPTION 'Record not found.';
    END IF;
END $$;

Works but not sure it’s actually atomic.

Is there any solution that would make an actual (atomic) optimistic locking update fails if the condition in the UPDATE statement cannot be satisfied?

Answer

Both solutions are fine and not subject to a race condition.

If the UPDATE changes no rows, RETURNING will return an empty result set, and FOUND will be set to FALSE.

Advertisement

Answer