[
https://issues.apache.org/jira/browse/CAY-2954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18088762#comment-18088762
]
Andrus Adamchik edited comment on CAY-2954 at 6/13/26 4:42 PM:
---------------------------------------------------------------
Here is a raw JDBC benchmark (outside Cayenne) on the effect of
"autoCommit=true" vs "autoCommit=false;c.commit()" (current behavior). The
result is quite dramatic!
h2. h2. PostgreSQL
{noformat}
┌───────────┬────────────┬────────────┬──────────────┬───────────────────┐
│ condition │ mode │ throughput │ mean latency │ client CPU/1k ops │
├───────────┼────────────┼────────────┼──────────────┼───────────────────┤
│ loopback │ autocommit │ 8379 ops/s │ 119 µs │ 11.0 ms │
├───────────┼────────────┼────────────┼──────────────┼───────────────────┤
│ loopback │ tx-commit │ 4293 ops/s │ 233 µs │ 22.0 ms │
├───────────┼────────────┼────────────┼──────────────┼───────────────────┤
│ +1ms RTT │ autocommit │ 401 ops/s │ 2489 µs │ 94 ms │
├───────────┼────────────┼────────────┼──────────────┼───────────────────┤
│ +1ms RTT │ tx-commit │ 201 ops/s │ 4971 µs │ 199 ms │
└───────────┴────────────┴────────────┴──────────────┴───────────────────┘
{noformat}
h2. MySQL
{noformat}
┌───────────┬────────────┬────────────┬──────────────┐
│ condition │ mode │ throughput │ mean latency │
├───────────┼────────────┼────────────┼──────────────┤
│ loopback │ autocommit │ 7819 ops/s │ 128 µs │
├───────────┼────────────┼────────────┼──────────────┤
│ loopback │ tx-commit │ 4089 ops/s │ 244 µs │
├───────────┼────────────┼────────────┼──────────────┤
│ +1ms RTT │ autocommit │ 379 ops/s │ 2638 µs │
├───────────┼────────────┼────────────┼──────────────┤
│ +1ms RTT │ tx-commit │ 182 ops/s │ 5494 µs │
└───────────┴────────────┴────────────┴──────────────┘
{noformat}
was (Author: andrus):
Here is a raw JDBC benchmark (outside Cayenne) on the effect of
"autoCommit=true" vs "autoCommit=false;c.commit()" (current behavior). The
result is quite dramatic!
PostgreSQL
{noformat}
┌───────────┬────────────┬────────────┬──────────────┬───────────────────┐
│ condition │ mode │ throughput │ mean latency │ client CPU/1k ops │
├───────────┼────────────┼────────────┼──────────────┼───────────────────┤
│ loopback │ autocommit │ 8379 ops/s │ 119 µs │ 11.0 ms │
├───────────┼────────────┼────────────┼──────────────┼───────────────────┤
│ loopback │ tx-commit │ 4293 ops/s │ 233 µs │ 22.0 ms │
├───────────┼────────────┼────────────┼──────────────┼───────────────────┤
│ +1ms RTT │ autocommit │ 401 ops/s │ 2489 µs │ 94 ms │
├───────────┼────────────┼────────────┼──────────────┼───────────────────┤
│ +1ms RTT │ tx-commit │ 201 ops/s │ 4971 µs │ 199 ms │
└───────────┴────────────┴────────────┴──────────────┴───────────────────┘
{noformat}
> Don't wrap selecting queries in transactions
> --------------------------------------------
>
> Key: CAY-2954
> URL: https://issues.apache.org/jira/browse/CAY-2954
> Project: Cayenne
> Issue Type: Task
> Reporter: Andrus Adamchik
> Assignee: Andrus Adamchik
> Priority: Major
>
> Cayenne surrounds every DB interaction with a transaction. This doesn't make
> sense for single select operations (the only case where it would is long
> running externally managed transactions, where selects should see uncommitted
> DB state produced by prior changes; though should be handled at the
> DataSource level).
> So let's bypass implicit transactions when we can. Since Cayenne has no idea
> whether the query requires a tx or not, the indicator should be provided by
> the query itself ({{q.getMetadata().isReadOnly()}}). Most queries know
> whether they are "read-only" or not. For those that don't (such as
> ProcedureQuery) it will default to "false".
> h2. Other considerations
> * Currently, we force "autoCommit=false" on every connection. Show we keep
> doing that for selects?
> * Transaction listeners should no long receive begin/commit/rollback events.
> That's a breaking change, but also looks like the correct thing to do.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
