Federico Mariani created CAMEL-27057:
----------------------------------------

             Summary: camel-jpa - skipLockedEntity does not skip a locked 
entity on PostgreSQL: the poll is rolled back and its entities are routed again
                 Key: CAMEL-27057
                 URL: https://issues.apache.org/jira/browse/CAMEL-27057
             Project: Camel
          Issue Type: Bug
          Components: camel-jpa
            Reporter: Federico Mariani
             Fix For: 4.24.0


{{skipLockedEntity=true}} is documented as "use NOWAIT on lock and silently 
skip the entity. This allows different instances to process entities at the 
same time but not processing the same entity" 
({{JpaConsumer.setSkipLockedEntity}}). {{JpaConsumer.lockEntity}} locks each 
entity with {{entityManager.lock(entity, lockModeType, 
Map.of("jakarta.persistence.lock.timeout", 0))}}, and skips the entity when the 
lock fails, unless the failure is a {{PessimisticLockException}} or an 
{{OptimisticLockException}}, which it rethrows because the transaction cannot 
go on.

On PostgreSQL the failed {{FOR UPDATE NOWAIT}} aborts the transaction, so the 
lock cannot fail without failing the poll: Hibernate 7.4.15 reports a 
{{PessimisticLockException}} ({{PostgreSQLDialect.causesRollback}} is true), 
{{lockEntity}} rethrows it, the final flush fails with {{current transaction is 
aborted}}, and the poll is rolled back. The entities that the poll routed 
before the locked one are not consumed, and the next poll routes them again, 
for as long as the other transaction holds the lock. So two instances that 
compete for the same table, the case the option exists for, route the same 
entities several times instead of skipping each other's.

A second case, on every database: an entity that another instance consumed 
(deleted) after the poll query listed it fails the lock with an 
{{OptimisticLockException}} (Hibernate: "No rows were returned from JDBC query 
for versioned entity"), which marks the transaction rollback only, so the poll 
is rolled back in the same way. Seen with Hibernate on PostgreSQL and H2, and 
with OpenJPA on PostgreSQL: the poll routes entity 1, stops at entity 2, and 
its delete of entity 1 is rolled back; the next poll routes entity 1 again.

Found with fault-injection tests against a real PostgreSQL, while working on 
CAMEL-27025.

h3. Steps to reproduce

PostgreSQL 17, Hibernate 7.4.15 (Spring 
{{LocalContainerEntityManagerFactoryBean}}), 3 entities, consumer 
{{jpa:Item?query=select x from Item x order by x.id&skipLockedEntity=true}}. A 
second connection runs {{BEGIN; SELECT id FROM item WHERE id = 2 FOR UPDATE;}} 
and keeps its transaction open.

h3. Actual (4.24.0-SNAPSHOT)

* Each poll routes entity 1, then the lock of entity 2 fails ({{for no key 
update of ... nowait}}), the poll fails with {{PersistenceException: ... 
current transaction is aborted, commands ignored until end of transaction 
block}} and is rolled back: after 3 polls entity 1 was routed 3 times, entity 3 
never, and the table still has 3 rows.
* When the other transaction ends, the next poll routes 1, 2 and 3: entity 1 
was routed 4 times in all.

h3. Expected

Entity 2 is skipped: the poll routes and consumes 1 and 3 once, later polls 
skip 2 while it is locked, and a poll after the lock is released consumes 2.

h3. Notes

* Hibernate takes the lock timeout {{-2}} 
({{org.hibernate.Timeouts.SKIP_LOCKED}}) as {{SKIP LOCKED}} for a query, where 
the database supports it: a select of the entity with {{SKIP LOCKED}} returns 
no row for a locked entity, without an error, so the transaction goes on. 
{{EntityManager.lock}} rejects {{-2}} ({{IllegalArgumentException: Skip-locked 
is not valid option for #lock}}).
* OpenJPA 4.1.1 rejects {{-2}}, and has no NOWAIT: it applies the lock timeout 
as a JDBC query timeout, where {{0}} means no timeout. With OpenJPA on 
PostgreSQL, {{skipLockedEntity=true}} waits for the lock, as without it.

_Claude Code on behalf of Croway_




--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

Reply via email to