Postgresql – SELECT FOR UPDATE (NOWAIT) on auxilliary table for serializing updates

lockingpostgresqlserialization

Background:

I have an inventory tracking application which needs to avoid the following sort of situation where the same inventory might be used twice due to lack of serialization. The constraint is that each item of inventory from a pool of inventory must in use only once at any given time (much simplified example):

CREATE TABLE inventory_use (id serial, ref varchar);

Transaction 1:
BEGIN;
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T1 insert');

Transaction 2:
BEGIN;
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T2 insert');

Transaction 1:
COMMIT;

Transaction 2:
COMMIT; -- fails (as desired) at SERIALIZABLE, commits (not desired) at REPEATABLE READ

SERIALIZABLE prevents this from occurring by causing the last commit to fail (and REPEATABLE READ does not). However, even though my application does not involve many concurrent writes, I'm reluctant to go for SERIALIZABLE because my understanding of its performance characteristics is weak: I'm not yet confident I know how to predict for example what the effect of concurrent reads or of (read or write) throughput will be on performance, perhaps especially on concurrent transactions that aren't connected directly with inventory tracking and probably are not critical to the application. Probably I should educate myself more about SERIALIZABLE since it looks very useful, but I'd like to understand the alternatives too.

So this question is about alternative approaches to SERIALIZABLE for this kind of problem, in particular using SELECT FOR UPDATE NOWAIT to serialize inventory tracking updates through row locking while running at the READ COMMITTED isolation level.

What I've tried:

I implemented FOR UPDATE NOWAIT locking on the table that records inventory users. That seems to work fine.

However, that may cause trouble because it can block some other updates to that table that need not be serialized. This may well not be critical in my application, but it led me to see if I could use a separate table, created just for the purpose of locking, to do the FOR UPDATE locking. To implement that, before the SELECT FOR UPDATE, I would begin by checking whether an INSERT is needed to represent the pool of inventory whose use needs to be serialized — perhaps via INSERT ... ON CONFLICT DO NOTHING — in order to ensure that there is a suitable row that can be locked to serialize the updates.

Question:

I've not been able to find other people using SELECT FOR UPDATE in this style in which:

  1. there is an auxiliary table whose only function is FOR UPDATE row locking, not to store any application data.
  2. FOR UPDATE row locking on existing rows is being used to serialize the creation of new rows

That makes me wonder whether this is a bad way to solve this problem.

I've seen other suggested approaches that are similar, for example using REPEATABLE READ with an auxiliary table and using an UPDATE to that table to implicity acquire a lock: https://peter.grman.at/postgres-repeatable-read-vs-serializable/

Is this (the SELECT FOR UPDATE style I describe above) a sensible technique for solving a problem of this kind, and if so, how should it be done? I don't have specific "how should it be done" issues in mind other than to benefit from experience here.

Best Answer

You could use transaction level advisory locks to serialize your operations:

BEGIN;
-- take an advisory lock for the duration of the session
SELECT pg_advisory_xact_lock(4711);
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T1 insert');
COMMIT;

The number 4711 is an arbitrary constant.

Then the second transaction using the same code will block until the first one has committed.