Thread: Could not serialize access due to read/write dependencies among transactions
Could not serialize access due to read/write dependencies among transactions
From
michele curioni
Date:
Hi,
we have a Spring Boot application that tries to update two separate rows in a table in 2 separate threads, and it uses the default Transaction Isolation level of READ_COMMITTED.
One of the two threads always succeeds, the other always fails with:
org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions [java] ERROR> Detail: Reason code: Canceled on identification as a pivot, during write. [java] ERROR> Hint: The transaction might succeed if retried.
I thought that error could only happen for SERIALIZABLE transactions, and moreover, the two threads are trying to update different rows.
The sql update has a where clause on the primary key:
update table set status='a' where column_a = {value1} and column_b={value2}
The table's primary key is a composition of column_a and column_b
thread1 and thread2 use different values for value1 and value2
Does anyone have any idea how the problem could happen?
Thanks,
Michele
Re: Could not serialize access due to read/write dependencies among transactions
From
Tom Lane
Date:
michele curioni <mic.curioni@googlemail.com> writes: > we have a Spring Boot application that tries to update two separate rows in > a table in 2 separate threads, and it uses the default Transaction > Isolation level of READ_COMMITTED. > One of the two threads always succeeds, the other always fails with: > org.postgresql.util.PSQLException: ERROR: could not serialize access > due to read/write dependencies among transactions > [java] ERROR> Detail: Reason code: Canceled on identification as a > pivot, during write. > [java] ERROR> Hint: The transaction might succeed if retried. > I thought that error could only happen for SERIALIZABLE transactions, Indeed. I think you're mistaken to claim that the transactions are running in READ COMMITTED. > moreover, the two threads are trying to update different rows. That part's not so hard to explain: serializable mode's tracking of which rows received updates is a bit lossy. It might be complaining just because the updated rows were on the same page. regards, tom lane