SELECT updated rows during concurrent updates

Question

Suppose I have a set of code that creates a transaction, selects, deletes, and re-creates a resource, and commits, in order to update it:

1. BEGIN
2. SELECT * FROM "product" WHERE (code = 'A') FOR UPDATE
3. DELETE FROM "product" WHERE "product"."id" = 1
4. INSERT INTO "product" ("id","code","price") VALUES (1,'A',3000) // Updated price
5. COMMIT

Now, imagine that this code is being run twice, concurrently, and trying to modify the same row:

TX1 - BEGIN
TX1 - SELECT product
TX1 - DELETE product

TX2 - BEGIN
TX2 - SELECT product - This blocks until TX1 has been committed

TX1 - INSERT updated product
TX1 - COMMIT

TX2 - SELECT product - Error occurs here...

If I use the transaction level ReadCommitted, I get 0 rows returned. Notice that TX2 was created after the product was DELETEd in TX1.

I can't use Serializable or RepeatableRead, since the row has changed, I get pq: could not serialize access due to concurrent update.

Is there any way to have the SELECT in TX2 block until TX1 has been completed, and then SELECT the new updated row? Am I using SELECT...FOR UPDATE incorrectly?

Answer 1

The behaviour and the reasons on how the isolation levels works in PostgreSQL are greatly summarized in their documentation: https://www.postgresql.org/docs/9.5/transaction-iso.html#XACT-READ-COMMITTED). With

What's important, from my perspective, is that the PostgreSQL uses the optimistic locking for transaction processing. When the transaction is started a snapshot is taken and it's worked with that. The manual use of the SELECT ... FOR UPDATE means using the pessimistic lock on the row.

Now, when you use the RepeatableRead the database promises the transaction isolation is guaranteed.

When TX1 pessimistically locks the row the TX2 waits what's next. If the TX1 rolls back then the TX2 may continue. But as the TX1 commits then the TX2 has no other possibilities than rollback and throw the exception to meet the promise of the isolation level.

From here it's up to you to rerun the SQL query which will see the updated version of state of the database.

So for the response to your question

Is there any way to have the SELECT in TX2 block until TX1 has been completed, and then SELECT the new updated row?

Yes, use the RepeatableRead and when the query of TX2 fails rerun your query once again.