Proper use of serialization with psycopg2

Question

I am accessing a postgresql table with serialization transaction isolation. I am doing something like this (with an existing psycopg2 connection conn and cursor in that connection, cur:

while True:
  try:
    cur.execute(query)
    break
  except TransactionRollbackError:
    [sleep a little]
    continue
  except Exception:
    [handle error here]

The point of this is to retry in case of serialization contention. Now, this works fine much of the time. But often I get this error after it has spent one iteration in the TransactionRollbackError trap:

current transaction is aborted, commands ignored until end of transaction block. Apparently, spinning this way to avoid serialization contention is not appropriate? Should I be doing this a different way?

Some notes: I am accessing the table with different processes (that are all the same and doing the same things: selecting, incrementing, and updating / inserting into the table.) Each of these processes has their own connection conn, they are not sharing a connection.

Another note: it seems like after going through the TransactionRollbackError exception block once, in the next spin of the while loop it ends up in the Exception exception block instead.

Still another note: The number of processes running at the same time has a direct effect on the frequency of errors, in that more processes tends to produce more errors. So there is some kind of contention going on. I am under the impression that using serialized transaction isolation with retries (as in my demo code) would fix this.

Do you [`rollback` the connection](http://initd.org/psycopg/docs/connection.html#connection.rollback) in the `except TransactionRollbackError` branch? — musically_ut, Aug 01 '15 at 18:59
@musically_ut - I do not, I think that I shouldn't. Am I wrong? — Ken - Enough about Monica, Aug 01 '15 at 19:01
`conn.rollback()` is the way I recover from errors to continue using my cursors after I commit a typo in `cur.execute`'s argument. A cursory reading of the error message suggests that the transaction ought to be aborted as well and [this post](http://stackoverflow.com/a/13007379/987185) suggests doing something similar. Why do you think you **should not** rollback? — musically_ut, Aug 01 '15 at 19:11
@musically_ut - My (maybe dumb) assumption was that since it's an error due to serialization contention, that I could simply retry without rolling back. I will try to make a savepoint and rollback / recover. — Ken - Enough about Monica, Aug 01 '15 at 19:16
You don't need to create a save-point explicitly. Each independent `execute` is run inside a transaction and it is that transaction that your connection gets trapped in if the statement runs into problems. Just doing a `conn.rollback()` in the correct branch should suffice. — musically_ut, Aug 01 '15 at 19:18
@musically_ut - The reason I want to use savepoints is that by the time I get to this problematic code, I may have already executed a few SQL statements that I don't want to rollback. In that case I should be using savepoints, correct? — Ken - Enough about Monica, Aug 01 '15 at 19:26
Yes, unless you put the connection in autocommit mode. However, your other code should also be wrapped inside a `try/catch`. More details here: http://initd.org/psycopg/docs/usage.html#transactions-control — musically_ut, Aug 01 '15 at 19:33
@musically_ut - I have reasons for not using autocommit (specifically, if something in the chain of sql commands fails, I want them all to fail.) Would you please put your solution above in an answer so that I can accept it? — Ken - Enough about Monica, Aug 01 '15 at 19:35
It would be better if you can confirm that it works first. :) — musically_ut, Aug 01 '15 at 19:36
@musically_ut - Yes, it solves the problem I was having. Thanks. Now I've run into another problem that I'll put in another question: http://stackoverflow.com/questions/31765494/serialization-contention-in-postgresql-bringing-my-process-to-a-grind — Ken - Enough about Monica, Aug 01 '15 at 19:41

score 5 · Accepted Answer · answered Aug 01 '15 at 19:45

You should perform a rollback in the except TransactionError: branch to recover from the error state:

while True:
  try:
    cur.execute(query)
    break
  except TransactionRollbackError:
    conn.rollback()
    [sleep a little]
    continue
  except Exception:
    [handle error here]

This is the recommendation made the FAQ as well.

Note that it will rollback all the SQL commands thus far unless you have been doing your own transaction control or have the connection in autocommit mode.

Proper use of serialization with psycopg2

1 Answers1