[ 
https://issues.apache.org/jira/browse/CAMEL-27057?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Claus Ibsen resolved CAMEL-27057.
---------------------------------
    Resolution: Fixed

> 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
>            Assignee: Federico Mariani
>            Priority: Major
>             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