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)