Federico Mariani created CAMEL-27025:
----------------------------------------

             Summary: camel-jpa - the consumer cannot bound its wait for the 
lock of an entity, so a transaction that does not end (lost connection) stops it
                 Key: CAMEL-27025
                 URL: https://issues.apache.org/jira/browse/CAMEL-27025
             Project: Camel
          Issue Type: Improvement
          Components: camel-jpa
            Reporter: Federico Mariani
             Fix For: 4.24.0


With the default {{consumeLockEntity=true}}, {{JpaConsumer.lockEntity}} locks 
each polled entity with {{entityManager.lock(entity, lockModeType)}} 
({{components/camel-jpa/src/main/java/org/apache/camel/component/jpa/JpaConsumer.java}},
 line 376 on main), {{PESSIMISTIC_WRITE}} by default, without a lock timeout. 
Only {{skipLockedEntity=true}} passes a hint: 
{{jakarta.persistence.lock.timeout=0}} (NOWAIT).

PostgreSQL has no lock timeout by default ({{lock_timeout=0}}), so a poll waits 
until the transaction that holds the row lock ends. If that transaction does 
not end, the consumer is stuck. This happens when a client (another consumer, 
or the same one) loses its connection without the database noticing, for 
example a network failure or a NAT timeout during a poll: PostgreSQL keeps the 
session {{idle in transaction}} with its row locks until TCP keepalive gives up 
on the client, about 2 hours with the Linux defaults, unless 
{{idle_in_transaction_session_timeout}} is set (default 0).

A socket timeout of the JDBC driver does not get the consumer out: the poll 
fails, but its database session goes on waiting for the lock, and each later 
poll leaves one more such session. Found with fault-injection tests (Toxiproxy 
silently dropping the connection of a consumer to a real PostgreSQL, Hikari and 
Agroal pools with a socket timeout): the waiting sessions grew from 1 to 6 in 
60 s, the consumer never recovered (readiness DOWN, rows left in the table).

There is no configuration to bound the wait of the consumer's lock. With 
Hibernate 7.4.15 a {{jakarta.persistence.lock.timeout}} property of the 
persistence unit is not applied to {{EntityManager.lock(entity, 
lockModeType)}}: {{SessionImpl.lock}} copies only the lock options of the 
session, which come from {{setProperty}}, not the defaults of the session 
factory.

h3. Steps to reproduce

PostgreSQL 17, Hibernate 7.4.15 set up through Spring's 
{{LocalContainerEntityManagerFactoryBean}}, a table with 3 entities, consumer 
{{jpa:Item?query=select x from Item x order by x.id}}. A second connection runs 
{{BEGIN; SELECT id FROM item WHERE id = 1 FOR UPDATE;}} and never ends its 
transaction.

h3. Actual (4.23.0 and 4.24.0-SNAPSHOT)

* Default options: the poll waits for the lock until the other transaction ends 
({{pg_stat_activity}}: one session with {{wait_event_type = 'Lock'}}; still 
waiting after 10 s in the test).
* JDBC URL with {{socketTimeout=2}}: each poll fails after 2 s, but its session 
stays on the database waiting for the lock: 1, 2, 3 waiting sessions after 3 
polls.
* {{jakarta.persistence.lock.timeout=1000}} as a property of the persistence 
unit: no effect, the poll still waits.
* {{skipLockedEntity=true}}: the lock fails at once ({{for no key update 
nowait}}). It does not skip the entity on PostgreSQL: the failed statement 
aborts the transaction, Hibernate reports a {{PessimisticLockException}} 
({{PostgreSQLDialect.causesRollback}} is true), {{lockEntity}} rethrows it, and 
the poll is rolled back. With the second entity locked, the first one is 
routed, its delete is rolled back, and every poll routes it again.
* Once the transaction holding the lock ends ({{pg_terminate_backend}}, which 
is what {{idle_in_transaction_session_timeout}} does), the next poll consumes 
every entity.

h3. Expected

A way to bound the wait, so that the poll fails (or skips the entity) instead 
of waiting, without leaving a session waiting on the database, and 
documentation of the case.

h3. Proposed fix

A consumer option {{lockTimeout}} (millis) given to the lock as the standard 
{{jakarta.persistence.lock.timeout}} hint (Hibernate on PostgreSQL then runs 
{{set local lock_timeout}} before the lock), and a section in the component 
documentation: the case above, {{lockTimeout}}, {{skipLockedEntity}}, and 
PostgreSQL's {{idle_in_transaction_session_timeout}}, which ends the 
transaction that holds the lock. The defaults do not change.

_Claude Code on behalf of Croway_



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

Reply via email to