CODEWITHSUNDEEP
postgresqlloggingtransactionslocking
Karachun
Karachun

Reputation: 3

How to detect what caused a short time lock after it was released in PostgreSQL

In Java application I frequently see such errors: org.springframework.r2dbc.UncategorizedR2dbcException: executeMany; SQL [select * from table1 where id=:id and column1 <> :a for update]; could not serialize access due to concurrent update; nested exception is io.r2dbc.postgresql.ExceptionFactory$PostgresqlTransientException: [40001] could not serialize access due to concurrent update

Transaction with query select * from table1 where id=:id and column1 <> :a for update was rollbacked. Transaction isolation level - REPEATABLE READ. How can I see what has locked this row? Lock is very short (milliseconds).

I see no helpful information in Postgres log and application log.

Upvotes: 0

Views: 419

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246568

The problem here is not a concurrent lock, but a concurrent data modification. It is perfectly normal to get that error, and if you get it, you should simply repeat the failed transaction.

There is no way to find out which concurrent transaction updated the row unless you log all DML statements.

If you get a lot of these errors, you might consider switching to pessimistic locking using SELECT ... FOR NO KEY UPDATE.

Upvotes: 1

Related Questions