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.

Use a LOCK counter in ACCESS EXCLUSIVE MODE; before calling UPDATE counter.
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.

Can you post the entire function definition, rather than just the one line that is experiencing the problem? — jmelesky
– jmelesky, Commented May 22, 2017 at 18:47
Just for my information, does this mean that my conclusion on the problematic line could be wrong? I have had zero problems until I added that single line in a stored procedure. The actual stored procedure call is fairly involved. There is an entry level method which gets called by the Java layer in serializable isolation mode set using spring JDBC. The entry level stored procedure in turn will call several other methods which act on 7 different tables. The Counter is a new functionality which did not exist previously and something I am attempting to add. — cplusplusrat
– cplusplusrat, Commented May 22, 2017 at 19:05
I'm not saying that's not the problematic line, just that there are lots of other things about the function that could be impacting it. For example, if the function is defined as STABLE, then that could have a significant effect on the behavior you see. Likewise if you reference the counter table elsewhere in the function. If it's too much code, then you could try to find a minimum function which exhibits this behavior, and paste that -- that would help us focus on the actual problematic parts. — jmelesky
– jmelesky, Commented May 22, 2017 at 19:40
Like I mentioned before, the table counter is not accessed anywhere else but exactly at one location. All methods are marked as VOLATILE. — cplusplusrat
– cplusplusrat, Commented May 22, 2017 at 19:44
Let me put it another way: I can create this table, and create a function which has that line in it, and not see this problem. Therefore, there's relevant information missing. Can you help us with that relevant information? — jmelesky
– jmelesky, Commented May 22, 2017 at 21:24

Daniel Vérité · Accepted Answer · 2017-05-22 23:47:46Z

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.

I have read that documentation. In fact, I do have code in place in my java layer which retries transactions. And yes, on retrying I do succeed about 35% of the times, but there is a bit of a snowballing effect. The passed transactions now overlap with other inline which now start failing. Let me post some more explanation in the original problem. I have tried to cut down the problem to bare minimum.

Collectives™ on Stack Overflow

postgres lock table to avoid read/write exception

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related