Announcement Announcement Module
Collapse
No announcement yet.
MySQL: set autocommit=0; set autocommit=1 Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL: set autocommit=0; set autocommit=1

    Hi,

    I'm using Hibernate and MySQL with spring managed transactions and for some reason these empty "SET autocommit=0; SET autocommit=1" entries keep getting logged.

    I've set defaultAutoCommit to false for dbcp, also tried appending &autoCommit=false to the JDBC URI. Everything works either way, just that the log files grow quite a few MB/sec because of this.

    Code:
                     158268 Query       SET autocommit=1
                     158268 Query       SET autocommit=1
                     158268 Query       SET autocommit=1
                     158268 Query       SET autocommit=0
                     158268 Query       commit
                     158268 Query       SET autocommit=1
                     158268 Query       SET autocommit=1
                     158268 Query       SET autocommit=1
                     158268 Query       SET autocommit=0
                     158268 Query       commit
    My guess is that Sprinig is starting a new TX every time because of PROPAGATION_REQUIRED (even though everything is already cached).

    Is there any way I could somehow specify that autocommit should be always off and doesn't have to be tuned every time?
    Would be nice to get rid of the unneeded commits also, but I guess that's normal.

    Here's the config for a sample spring service:

    Code:
        <bean id="furnitureService" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
            <property name="transactionManager">
                <ref bean="transactionManager"/>
            </property>
            <property name="target">
                <ref bean="furnitureServiceTarget"/>
            </property>
            <property name="transactionAttributes">
                <props>
                    <prop key="get*">PROPAGATION_REQUIRED,readOnly</prop>
                    <prop key="find*">PROPAGATION_REQUIRED,readOnly</prop>
                    <prop key="save*">PROPAGATION_REQUIRED</prop>
                </props>
            </property>
        </bean>
    Thanks,
    Tarmo

  • #2
    Well-spotted, that's a typical situation for a Hibernate-based read operation: If everything is in the cache, a read-only operation will just turn the auto-commit flag to false and commit at the end, because no actual data access is necessary.

    You can avoid the auto-commit switching by configuring your connection pool accordingly, i.e. defining default auto-commit as "false" there. The transaction handling will automatically detect that status and proceed without a setAutoCommit(false) call. You'll still get the commit at the end, though, which of course involves the overhead of synchronous database communication - without an actual need for it.

    The above is essentially a consequence of the way most JDBC drivers work: Every JDBC operation gets sent to the database, even if no actual operation is necessary...

    To address that issue, I've recently added the LazyConnectionDataSourceProxy class, which exposes Connection proxies that do not access the JDBC driver until actually necessary. They will simply gather setAutoCommit, setReadOnly, etc calls and apply them lazily on first attempt to create a JDBC Statement. Commit and rollback will be suppressed too if no Statements have been created.

    To set this up, you need a target DataSource definition and a LazyConnectionDataSourceProxy proxy definition. Your SessionFactory, your, transaction manager and all your DAOs should access the proxy, which is straightforward - the proxy implements the DataSource interface too, so can simply be passed in instead of the target DataSource.

    Code:
    <bean id="dataSourceTarget" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
      <property name="url"><value>jdbc&#58;mysql&#58;//localhost&#58;3306/imagedb</value></property>
      <property name="username"><value>admin</value></property>
      <property name="password"><value></value></property>
    </bean>
    
    <bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
      <property name="targetDataSource"><ref local="dataSource"/></property>
    </bean>
    LazyConnectionDataSourceProxy will be part of Spring 1.1.4, to be released by the end of this week. To give it an early try, feel free to grab a current nightly snapshot!

    Juergen

    Comment

    Working...
    X