This is a simple tip for implement OCC directly on the database and so avoiding additional overhead that this can imply doing that at the application level.
The OCC mechanism mainly consists in:
- having an additional column called
versionby convention. - check if the incoming modification has the same version value.
- increment that number on successful modifications.
This can be done at the application level, but that will imply at least an additional query for retrieve the row for match the version number.
To implement that on the database (in this case PostgreSQL), let start defining a very simple trigger function that just checks the version and raise exception if version mismatch:
CREATE OR REPLACE FUNCTION handle_occ()
RETURNS TRIGGER AS $occ$
BEGIN
IF (NEW.version != OLD.version) THEN
RAISE EXCEPTION 'Version mismatch: expected % given %',
OLD.version, NEW.version
USING ERRCODE='P0002';
ELSE
NEW.version := NEW.version + 1;
END IF;
RETURN NEW;
END;
$occ$ LANGUAGE plpgsql;
Then, we should create a sample table with version field and attach
the appropriate trigger to it:
CREATE TABLE foobar (
id bigserial,
version bigint DEFAULT 0,
name text
);
CREATE TRIGGER foobar_occ_tgr BEFORE UPDATE ON foobar
FOR EACH ROW EXECUTE PROCEDURE handle_occ();
And we are done, let try that:
test=# INSERT INTO foobar (name) VALUES ('Yennefer');
INSERT 0 1
test=# SELECT * FROM foobar;
id | version | name
----+---------+----------
1 | 0 | Yennefer
(1 row)
test=# UPDATE foobar SET version=3, name='Ciri' WHERE id = 1;
ERROR: Version mismatch: expected 0 given 3
At the application level you can catch and handle this kind of errors
using the explicitly defined ERRCODE on the exception raised inside
the trigger function.
And finally, you don't need any additional concurrency control such as
using additional locks or more stronger isolation level for maintain
consistency. PostgreSQL by default acquires ROW EXCLUSIVE locks
for each row in UPDATE statements that already protects you from
possible race condition.