public inbox for [email protected]
help / color / mirror / Atom feedCould not serialize access due to read/write dependencies among transactions
2+ messages / 2 participants
[nested] [flat]
* Could not serialize access due to read/write dependencies among transactions
@ 2023-02-23 21:18 michele curioni <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: michele curioni @ 2023-02-23 21:18 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Could not serialize access due to read/write dependencies among transactions
@ 2023-02-23 22:10 Tom Lane <[email protected]>
parent: michele curioni <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-02-23 22:10 UTC (permalink / raw)
To: michele curioni <[email protected]>; +Cc: [email protected]
michele curioni <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-02-23 22:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-23 21:18 Could not serialize access due to read/write dependencies among transactions michele curioni <[email protected]>
2023-02-23 22:10 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox