[jira] [Commented] (CAY-2954) Don't wrap selecting queries in transactions

["Andrus Adamchik (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CAY-2954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18088762#comment-18088762 ] 

Andrus Adamchik commented on CAY-2954:
--------------------------------------

Here is a raw JDBC benchmark (outside Cayenne) on the effect of "autoCommit=true" vs "autoCommit=false;c.commit()" (current behavior). The result is quiet 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)