postgres lock table to avoid read/write exception

Question

I have a simple table counter which is as below.

CREATE TABLE counter
(
  id text NOT NULL,
  total integer NOT NULL,
  CONSTRAINT pk_id PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

The counter.id has a fixed set of 20 values and I have manually set the initial total to 0 for all the 20 counter.id. In one of the stored procedures, I added the following line

UPDATE counter SET total = total + 1 WHERE id = 'SomeID'; and now I see a large number of could not serialize access due to read/write dependencies among transactions postgres exceptions. If I comment that line out, the problem goes away. The table counter is not being updated/read anywhere else concurrently but this line.

I am using an ISOLATION level SERIALIZABLE in my transactions. The data access layer consists of Java Spring JDBC. I tried the following two approaches to resolve this issue.

1. Use a LOCK counter in ACCESS EXCLUSIVE MODE; before calling UPDATE counter.
2. Use PERFORM pg_advisory_xact_lock(1); right before calling UPDATE counter.

I am astonished that both the approaches did not solve the problem. From the documentation, the LOCK should give one thread an exclusive access to the table counter which should have prevented serializable exceptions. But it does not appear to work.

Any suggestions as to what I am doing wrong here?

---

UPDATE: So here is my attempt to reproduce the problem in a bit more simplified setting. I have have a single stored procedure which is as below.

CREATE OR REPLACE FUNCTION public.testinsert() RETURNS void AS
$BODY$
BEGIN
    LOCK counter IN ACCESS EXCLUSIVE MODE;
    INSERT INTO counter("id", "total")
    VALUES('SomeID', 0) ON CONFLICT(id)
    DO UPDATE SET total = counter.total + 1 where counter.id = 'SomeID';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.testinsert()

Now I attempt the following in two separate psql consoles.

Console1: begin transaction isolation level serializable;
Console2: begin transaction isolation level serializable;
Console1: select testInsert();
Console2: select testInsert();
Console1: commit;

At this point Console2 throws an exception could not serialize access due to concurrent update. This clearly tells me that the lock counter does not work when placed inside the stored procedure. Any ideas why?

If I try a variation of this with Console1 and Console2 both doing a lock counter right after begin transaction, followed by calling the stored procedure, the code works just fine as Console2 now waits on the lock.

I have tried replacing the lock counter with PERFORM pg_advisory_xact_lock(1) and encountered similar problems.

Answer 1

Consider this sentence from the doc at https://www.postgresql.org/docs/current/static/transaction-iso.html , about SERIALIZABLE:

Applications using this level must be prepared to retry transactions due to serialization failures.

It looks like you're ignoring that part, but you shouldn't.

See also https://wiki.postgresql.org/wiki/SSI for various examples of serialization failures that a session can have to deal with.

That these failures occur is the point of this isolation level. If you don't want them at all, you should use a less strict isolation, or avoid concurrency by locking out explicitly other sessions, like with your pg_advisory_xact_lock(1), but at the very beginning of the whole transaction.

The UPDATE you added might just change the timing of execution of concurrent transactions, because of the lock it creates (some transactions now stop when they wouldn't before). That's enough to trigger a serialization error. The root cause is that your concurrent transactions read/write data simultaneously at about the same locations (simultaneously meaning: transactions overlap, not necessarily that the writes occur at the same clockwall time exactly).

These hints at the bottom of the above-linked page may also help to reduce the probability of serialization failure :

For optimal performance when relying on Serializable transactions for concurrency control, these issues should be considered:

• Declare transactions as READ ONLY when possible.

• Control the number of active connections, using a connection pool if needed. This is always an important performance consideration, but it can be particularly important in a busy system using Serializable transactions.

• Don't put more into a single transaction than needed for integrity purposes.

• Don't leave connections dangling "idle in transaction" longer than necessary.

• Eliminate explicit locks, SELECT FOR UPDATE, and SELECT FOR SHARE where no longer needed due to the protections automatically provided by Serializable transactions.

• When the system is forced to combine multiple page-level predicate locks into a single relation-level predicate lock because the predicate lock table is short of memory, an increase in the rate of serialization failures may occur. You can avoid this by increasing max_pred_locks_per_transaction.

• A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures. It may be helpful to encourage the use of index scans by reducing random_page_cost and/or increasing cpu_tuple_cost. Be sure to weigh any decrease in transaction rollbacks and restarts against any overall change in query execution time.