postgres 9.5 row level locks concurrency exception

Question

I have recently ran into postgres concurrency bug which I won't repeat here. The original post was can be found at this link.

I am still trying to better understand how postgres handles serializable concurrency. My situation is this. I have one stored procedure which reads a table and then inserts based on the output of the read. This stored procedure, if called by multiple clients results in the 40001 read/write dependencies exception.

The question is this. Lets assume that the stored procedure which reads a table and then inserts into it based on the read, only reads some rows. If it is guaranteed that every call to the stored procedure for reads-insert touches a different row, would the concurrency exception go away? Is postgres smart enough to keep track of which rows were read during a transaction so that it can accurately detect modification of those specific rows by a different transaction resulting in the exception? And if yes, how reliable is this mechanism? Can it be optimized away in some cases and postgres, just to be safe throws an exception on modification to any of the read tables?

Why don't you send your findings to the postgres mailing list? That's where your can find the real specialists and the one who wrote the code in the first place. postgresql.org/list — Frank Heikens
"I have one stored procedure which reads a table and then inserts based on the output of the read." Does it "read a table" to determine whether a row or rows exists? How much of a table does it read? — Mike Sherrill 'Cat Recall'
No. In fact the read spans across 3 tables requires an intermediate joint/count. This is not a simple case of check if a row exists and insert if it doesn't. That can be handled by the on conflict clause for insert. The read itself has an involved WHERE clause which will automatically limit the number of rows that read will touch. — cplusplusrat

Laurenz Albe Laurenz Albe · Accepted Answer · 2017-03-23T09:59:25

First, what you encountered in the link you give is not a bug, but intended and documented behaviour.

I gather that you are using transaction isolation level SERIALIZABLE.

In this mode, every row you read is locked with a special SIReadLock which doesn't block anything, but is used to determine if a serialization anomality may have occurred, in which case the transaction is interrupted with a serialization error.

Note that not only rows that are returned to you are locked in this fashion, but all rows in all tables that are accessed during the execution of your query. So if there is a sequential scan in your execution plan, all rows of the table will have a SIReadLock. Moreover, if there are too many of these locks on a table, they get escalated to page or table level locks.

So it is possible that rows are locked unnecessarily. In addition to that, the algorithm which is used to detect inconsistencies can report false positives (it would be computationally too expensive to be exact).

As a consequence, you may receive serialization errors in the case you describe, although I would not expect any as long as everything is kept simple and there are no sequential scans.

Serialization errors are normal and to be expected on the SERIALIZABLE isolation level. Your application must be ready to handle them by retrying the transaction. It is the price you have to pay for not having to worry about data consistency.

postgres 9.5 row level locks concurrency exception

1 Answers