[
https://issues.apache.org/jira/browse/CAMEL-27057?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Work on CAMEL-27057 started by Federico Mariani.
------------------------------------------------
> 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)