shashank created CAMEL-26708:
--------------------------------
Summary: camel-sql - JdbcKeyValueRepository: two nodes can both
win putIfAbsent, and reading an expired entry deletes a live one
Key: CAMEL-26708
URL: https://issues.apache.org/jira/browse/CAMEL-26708
Project: Camel
Issue Type: Bug
Components: camel-sql
Reporter: shashank
Assignee: shashank
Two {{JdbcKeyValueRepository}} instances on one database (two nodes of a
cluster) can both "win" {{putIfAbsent}} for the same key, and reading an
expired entry can delete the entry another node has just stored for that key.
The repository was added in 4.23.0; the KeyValueRepository page lists it as
distributed-safe for clusters.
*putIfAbsent.* When the INSERT fails with a {{DuplicateKeyException}}, the
method marks its transaction rollback-only and reads the key again inside that
same transaction, returning the result. {{null}} means "the put succeeded"
({{KeyValueRepository}} javadoc), but the INSERT is rolled back, so nothing is
stored:
* Under REPEATABLE READ (the MySQL/MariaDB default; also H2) the re-read uses
the snapshot taken by the first SELECT, which does not show the winner's row.
Both callers get {{null}}.
* Under READ COMMITTED it returns {{null}} when the winner deletes its row
between the duplicate key and the re-read, for example because the Idempotent
Consumer removes the key of a failed message ({{removeOnFailure}}).
* On PostgreSQL the re-read fails, as the transaction is aborted after the
error.
{{KeyValueIdempotentRepository.add}} is {{putIfAbsent(...) == null}}, so the
same message is processed on both nodes; the {{camel-state-store}}
{{putIfAbsent}} operation reports the key acquired on both.
Short trace (REPEATABLE READ): A {{SELECT}} key: no row. B {{putIfAbsent}}:
{{SELECT}}, {{INSERT}}, commit, returns null. A {{INSERT}}: duplicate key,
re-read in its snapshot: no row, returns null. Two winners, B's value stored.
*Lazy expiry.* {{doGet}} reads the row, and if it has expired it runs {{DELETE
FROM CAMEL_KEYVALUE WHERE ITEM_KEY = ?}}. Between the SELECT and the DELETE
another node can store the key again ({{put}}, {{putIfAbsent}}); the DELETE
then removes that live entry. The writer was told its write succeeded, and a
third caller's {{putIfAbsent}} also returns {{null}}. Exposure needs entries
written with a TTL ({{camel-state-store}}, Cache EIP).
Both reproduce deterministically on the embedded H2 database with two
repositories and a {{JdbcTemplate}} that pauses one node's statement, and also
in a plain concurrent stress loop without pausing (hundreds of double wins per
20,000 races on H2).
Suggested fix:
* {{putIfAbsent}}: let the duplicate key roll back the transaction and read the
key again in a new transaction (return the stored value, or insert again if it
was deleted), with a small bound on the attempts. When it joined a transaction
of the caller, rethrow the duplicate key.
* {{doGet}}: delete the expired row with the expiry condition in the WHERE
clause ({{... WHERE ITEM_KEY = ? AND EXPIRES_AT > 0 AND EXPIRES_AT <= ?}}, as
{{deleteExpiredString}} already does for the whole table), as a new
configurable statement.
_Filed with Claude Code on behalf of allthingssecurity._
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
