40001 error in PostgreSQL

Question

I have simple table "Counters":

"CounterId" SERIAL (PK)

"CounterName" VARCHAR(50) NOT NULL (UNIQUE INDEX)

"Value" BIGINT NOT NULL

When two serializable transactions (actually there are many transactions like this in same time) executing queries:

SELECT NULL 
FROM "Counters" 
WHERE "CounterName" = @Value FOR UPDATE

SELECT "CounterId", "CounterName", "Value" 
FROM "Counters" 
WHERE "CounterName" = @Value
LIMIT 2 

(this query executes by Entity Framework in same connection and transaction)

UPDATE "Counters" SET "Value" = @Value WHERE "CounterId" = @CounterId

One of transactions is rolled back with error 40001

could not serialize access due to read/write dependencies among transactions

I'm retrying error transactions (5 times), but still have this error occurs.

Maybe this caused by different predicates in first and third queries?

Answer 1

If two transactions like the one you describe above run concurrently, the following will happen:

1. Transaction 1 locks a row with SELECT ... FOR UPDATE.

2. Transaction 2 tries to lock the same row and is blocked.

3. Transaction 1 modifies the value and commits.

4. Transaction 2 is unblocked, and before it locks the row, it has to recheck if the row version it is about to lock is still the most current version. Because of the modification by Transaction 1, that is no longer the case, and a serialization error is thrown.

There is no way to avoid that problem if several transactions with isolation level REPEATABLE READ or higher are trying to modify the same rows. Be ready to retry often!

It seems like the transaction actually locks more rows than it modifies. That exacerbates the problem. Only lock those rows that you need to change!