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
