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.
OCC mechanism mainly consists in:
- having an additional column called
- 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.