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.

CodePudding user response：

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.