'Postgres could not serialize access due to concurrent update

I have an issue with "could not serialize access due to concurrent update". I checked logs and I can clearly see that two transactions were trying to update a row at the same time.

my sql query

UPDATE sessionstore SET valid_until = %s WHERE sid = %s;

How can I tell postgres to "try" update row without throwing any exception?

sqlpostgresql


Solution 1:[1]

There is a caveat here which has been mentioned in comments. You must be using REPEATABLE READ transaction isolation or higher. Why? That is not typically required unless you really have a specific reason.

Your problem will go away if you use standard READ COMMITTED. But still it’s better to use SKIP LOCKED to both avoid lock waits and redundant updates and wasted WAL traffic.

As of Postgres 9.5+, there is a much better way to handle this, which would be like this:

UPDATE sessionstore
SET valid_until = %s
WHERE sid = (
    SELECT sid FROM sessionstore
    WHERE sid = %s
    FOR UPDATE SKIP LOCKED
);

The first transaction to acquire the lock in SELECT FOR UPDATE SKIP LOCKED will cause any conflicting transaction to select nothing, leading to a no-op. As requested, it will not throw an exception.

See SKIP LOCKED notes here: https://www.postgresql.org/docs/current/static/sql-select.html

Also the advice about a savepoint is not specific enough. What if the update fails for a reason besides a serialization error? Like an actual deadlock? You don’t want to just silently ignore all errors. But these are also in general a bad idea - an exception handler or a savepoint for every row update is a lot of extra overhead especially if you have high traffic. That is why you should use READ COMMITTED and SKIP LOCKED both to simplify the matter, and any actual error then would be truly unexpected.

Solution 2:[2]

The canonical way to do that would be to set a checkpoint before the UPDATE:

SAVEPOINT x;

If the update fails,

ROLLBACK TO SAVEPOINT x;

and the transaction can continue.

Solution 3:[3]

The default isolation level is "read committed" unless you need to change it for any specific use case. You must be using the "repeatable read" or "serializable" isolation level. Here, the current transaction will roll-back if the already running transaction updates the value which was also supposed to be updated by current transaction. Though this scenario can be easily handled by the "read_commit" isolation level where the current transaction accepts an updated value from other transaction and perform its instructions after the previous transaction is committed

ALTER DATABASE SET DEFAULT_TRANSACTION_ISOLATION TO 'read committed';

Ref: https://www.postgresql.org/docs/9.5/transaction-iso.html

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Laurenz Albe
Solution 3 blueberry

Related Questions