Prevent Concurrent Update in Read-Committed Transaction

To prevent concurrent update on table records, I simply just encapsulate upates inside Serializable Transaction. But, quoted from Annotated postgresql.conf and Global User Configuration (GUC) Guide (http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html):

Under a heavy multi-user load, setting “serializable” can impose a significant penalty as numerous transactions are forced to wait for the serialized transaction to complete.

I want to share with you my simple way to implement the feature in PostgreSQL default transaction isolation level: READ COMMITTED.

First, define the table:

CREATE TABLE inventory(
  id serial NOT NULL,
  item text NOT NULL,
  qty integer NOT NULL,
  ver integer NOT NULL DEFAULT 1,
  CONSTRAINT inventory_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

The last field ver is the focal point of the concurrent update protection.

Let’s create trigger function in PLSQL to be activated BEFORE INSERT and BEFORE UPDATE.

CREATE OR REPLACE FUNCTION trig_inventory()
RETURNS trigger AS
$BODY$
BEGIN
  IF (TG_WHEN = ‘BEFORE’ AND TG_OP = ‘INSERT’) THEN
    NEW.ver = 1; –force ver initiated with 1
    RETURN NEW;
  ELSIF (TG_WHEN = ‘BEFORE’ AND TG_OP = ‘UPDATE’) THEN
    IF OLD.ver > NEW.ver THEN –somebody has updated this record
      RAISE EXCEPTION ‘Concurrent Update Protection!’;
    END IF;
    NEW.ver = OLD.ver + 1; –increment UPDATE
    RETURN NEW;
  END IF;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;

Assign the trigger function to inventory table:

CREATE TRIGGER inventory_before_insert BEFORE INSERT
  ON inventory FOR EACH ROW EXECUTE PROCEDURE public.trig_inventory();
CREATE TRIGGER inventory_before_update BEFORE UPDATE
  ON inventory FOR EACH ROW EXECUTE PROCEDURE public.trig_inventory();

Now, let us try the feature (use psql):
First, insert record:

citra=# INSERT INTO inventory (item,qty) VALUES (‘ITEM_1′,50) RETURNING *;
id | item | qty | ver
—-+——–+—–+—–
2 | ITEM_1 | 50 | 1
(1 row)INSERT 0 1

Please note that field ver has been initialized to 1;
Now, update the record to check ver incremented:

citra=# BEGIN;
BEGIN
citra=# UPDATE inventory SET qty=qty+100 WHERE id=2 RETURNING *;
id | item | qty | ver
—-+——–+—–+—–
2 | ITEM_1 | 150 | 2
(1 row)UPDATE 1
citra=# COMMIT;

Finally, let’s simulate concurrent update protection. Besides current psql session, please open another session.
For simplicity, I assume that users on both session has queried the record and identify the ver equals 2. Please ensure that the ver field is included in update command.

session #1		session #2
citra=# BEGIN;		citra=# BEGIN;
citra=# UPDATE inventory SET qty=qty+100,ver=2 WHERE id=2 RETURNING *;		citra=# UPDATE inventory SET qty=qty+100,ver=2 WHERE id=2 RETURNING *;
citra=# COMMIT;		citra=# COMMIT;
		ERROR: Concurrent Update Protection!

The second session get the error message. The user must re-query the record to get the latest ver and use it in next update command.