14

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?

  • Are you trying to avoid the exception just to increase performance, or are you unable to repeat the transaction? If the latter, you just have to use explicit locking or change isolation level. – Mikko Rantalainen Mar 21 '20 at 09:22
  • Related "when it happens" question: https://stackoverflow.com/questions/7705273/what-are-the-conditions-for-encountering-a-serialization-failure – Ciro Santilli OurBigBook.com Dec 02 '21 at 11:36

3 Answers3

7

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.

DB140141
  • 411
  • 4
  • 8
  • Cool. I completely forgot about that issue. But it is still a problem for me. I am going to check it as soon as possible. Thanks a lot! –  Aug 20 '18 at 17:04
  • Hey. I checked it. SKIP LOCKED doesnt work for me. But NOWAIT does. My sql code: UPDATE sessionstore SET valid_until = %s WHERE sid = (SELECT sid FROM sessionstore WHERE sid = %s FOR UPDATE NOWAIT ); –  Aug 23 '18 at 11:59
  • 1
    Not so fast. NOWAIT will throw an exception if you don’t get the lock. You do want SKIP for that reason. Please explain why you don’t think it works - and read the docs carefully. – DB140141 Aug 24 '18 at 20:40
  • OK. Actually it didn't work. I made a mistake in my configuration, thats why I got a false positive. So I still have the issue. Unfortunately FOR UPDATE SKIP LOCKED doesnt' work for me. I put the code to try: except statement, but I am getting postgres errors in my logs. Do you have any other idea of handling it? –  Aug 27 '18 at 09:52
  • Look at my updated answer. You are using too high of a transaction isolation level, in my opinion, based on the fact that you simply want to ignore a double-update. I don't know what language you are using, but in SQL, you want: `SET TRANSACTION ISOLATION LEVEL READ COMMITTED;` at transaction start, and then you will not get any more serialization errors. This is the DEFAULT isolation level and you should always use it unless you really have a specific reason to use otherwise. – DB140141 Aug 28 '18 at 21:32
1

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

blueberry
  • 325
  • 2
  • 8
0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • The point is that I don't want to do any ROLLBACK. Scenario like that would be great <3 Me: Hey. Postgres UPDATE that row. Postgres: OOps. I can't :P :P :P xD Me: Ok. Then go further and just ignore it. –  Jun 11 '18 at 12:38
  • 1
    I'm not sure if I understood you right, but that is what my answer suggests, right? – Laurenz Albe Jun 11 '18 at 12:41
  • ROLLBACK takes time and resources. I can't afford it. –  Jun 11 '18 at 12:42
  • 1
    Then you'll have to accept the serialization error and retry the transaction. – Laurenz Albe Jun 11 '18 at 13:04
  • But this query is not so important. It is ok even if it is not executed. When I am getting error postgres is making a huge rollback. For a while server "stops" and my users are getting "disconnect" information. –  Jun 11 '18 at 13:18
  • 1
    Even a huge rollback only takes very little time on PostgreSQL, and it certainly won't disconnect users. – Laurenz Albe Jun 11 '18 at 14:03
  • 2
    Why are you using REPEATABLE READ or SERIALIZABLE if you don't want to pay the price? Use READ COMMITTED and SELECT ... FOR UPDATE and you'll never get a serialization error. – Laurenz Albe Jun 11 '18 at 14:04
  • Can you write more about? Can you explain? I am a noobie. –  Jun 11 '18 at 15:42
  • 2
    There is nothing much to explain. If you use `READ COMMITTED` isolation level, you cannot get a serialization error. If you want to prevent a concurrent transaction to change a row after you have read it, read it with `SELECT ... FOR UPDATE`. – Laurenz Albe Jun 11 '18 at 19:16