TFB `updates` test: severe performance regression with `JdbcTransactionExposedTransactionProvider`, exhaustive analysis, and fix (rollback releases resources)

[ShreckYe]

This issue is drafted by Copilot.

Root Cause and Fix

A severe performance regression in the TFB updates test was traced to use of JdbcTransactionExposedTransactionProvider for statement preparation (introduced in PR #101), where a single JDBC transaction was shared across SQL preparations. While this approach was intended to optimize by reducing transaction creation overhead, it instead led to extreme throughput drops in real-world concurrent benchmarks: 90% slower on my device (2200 req/s for JDBC provider vs 27000 - 28000 req/s for Database provider).

Fix

Explicitly rolling back the shared transaction (jdbcTransaction.rollback()) releases held resources and resolves all contention, restoring expected throughput and behavior.

---

Investigation Path & Related Experiments (Cross Repo)

(1) Initial Analysis and Benchmarks

• TFB update test performance (FrameworkBenchmarks): Repeated test runs confirmed a consistent 90% drop.
• Profiling isolated benchmarks (exposed-vertx-sql-client PRs Fix TFB updates performance again by refactoring the benchmark for profiling (see #107 and #108 for more details) and eventually clearing accumulated state in statementPreparationExposedTransaction #104, Add profiling entrypoint for TFB batch update variants #106, Add profiling infrastructure and comparative analysis for TfbBatchUpdateBenchmark transaction providers #107, Profile TfbBatchUpdateBenchmark transaction providers with flame graphs #108): Flame graphs showed negligible difference (<1%) between providers, leading to further investigation.

(2) Possible Causes Considered & Eliminated

• ThreadLocal Serialization: Hypothesized overhead from withThreadLocalTransaction serializing parallel calls. Flame graphs showed this as a hotspot (blocking), but further prepareBatchSqlAndArgTuples analysis confirmed the batch SQL preparation logic is fully synchronous, ruling out interleaving/thread contention at the function level.
• Internal State Contention: Explored whether underlying properties of JdbcTransaction or Database held shared resources.
• Deadlocks: Considered possibility of deadlocks due to held JDBC transaction locks or connection state interfering on parallel event loops. No deadlocks were observed, only serialization/blocking.
• Memory Contention: The fact that SQL preparation throughput does not scale with cores lead me into thinking about this.

(3) Extensive Profiling and Experimentation

• Transaction Pooling: Implemented a pool of JDBC transactions (see Profile and compare Exposed transaction providers - Database provider 56% faster FrameworkBenchmarks#25: PooledJdbcTransactionProvider). Improved throughput by ~13%, but still far behind Database provider. Scaling the pool size further did not help.
• ThreadLocal Removal: Attempted to remove ThreadLocal transaction context (see experiment notes in Profile and compare Exposed transaction providers - Database provider 56% faster FrameworkBenchmarks#25), but this broke Exposed compatibility and reduced performance further.
• Async-Profiler (Wall-clock Profiling): Revealed blocking on withThreadLocalTransaction when using a single shared transaction under concurrent load.

(4) Final Finding & Fix

• Analysis: The single shared JDBC transaction, even if used only for SQL statement preparation, holds JDBC connection state, which blocks parallel preparation and execution under Vert.x event loops during high load.
• Fix: Calling jdbcTransaction.rollback() releases all held resources, removes blocking, and restores full parallelization. After rollback, benchmark results matched expectations, with throughput up from 2200 req/s to 27000 - 29000 req/s.

(5) Research Provenance

• Cross-repo experiments: See exposed-vertx-sql-client PRs: feat: share Exposed transaction for SQL preparation with a newly introduced StatementPreparationExposedTransactionProvider interface, and make misc improvements #101, Fix TFB updates performance again by refactoring the benchmark for profiling (see #107 and #108 for more details) and eventually clearing accumulated state in statementPreparationExposedTransaction #104, Investigate and document TfbBatchUpdateBenchmark 25% performance regression #105, Add profiling infrastructure and comparative analysis for TfbBatchUpdateBenchmark transaction providers #107, Profile TfbBatchUpdateBenchmark transaction providers with flame graphs #108
• FrameworkBenchmarks experiments: See FrameworkBenchmarks PRs: Profile transaction providers with exposed-vertx-sql-client 0.8.0-SNAPSHOT FrameworkBenchmarks#17, Profile and compare Exposed transaction providers - Database provider 56% faster FrameworkBenchmarks#25 (PooledJdbcTransactionProvider experiments, flame graphs, throughput/latency metrics), Add wrk-based benchmarking with Testcontainers for vertx-web-kotlinx FrameworkBenchmarks#23
• Branches: copilot/profile-exposed-transaction-providers, exposed-vertx-sql-client-align-with-vertx-web-kotlinx-fix-updates-performance, and all branches since commit 5a6c57f61a21fc655dcddcf21c3e4bfb7f096d44 in both repos were considered.

---

Key Takeaways

• Always roll back JDBC transactions after statement preparation.

---

Confirmed fixed with jdbcTransaction.rollback().

[ShreckYe]

Already fixed by #104. Don't know why is not closed.