Hi Sumedha, Nuwan, Yes, I understand that we shouldn't be using auto commit if we need to rollback multiple transactions. In Metrics reporter, it just does a batch insert and it has only one transaction. So, there were no issues with auto commit yet.
I thought of asking question as we also let the user to set default auto commit value from the data source configuration. For example, in Metrics scenario, if the data source config had default auto commit set to false, the reporter will not insert any data to the database table. So, the Metrics reporter depends on having default auto commit set to true. When we want to use transactions, we can set auto commit to false after getting a connection. Then we will not depend on the "default auto commit" from the data source. I also think it is a good practice to always set auto commit to false when getting a connection when we want to use transactions. i.e. con.setAutoCommit(false) [1]. The API Manager code also follows the same practice [2]. So, in summary,* it's recommended to use transactions in the code and avoid depending on auto commit.* I will change the metrics code. Thanks for your replies and helping me to clarify. Best Regards, [1] https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html [2] https://github.com/wso2/carbon-apimgt/blob/v2.0.1/components/apimgt/org.wso2.carbon.apimgt.impl/src/main/java/org/wso2/carbon/apimgt/impl/dao/ApiMgtDAO.java#L863 On Mon, Nov 23, 2015 at 4:59 PM, Sumedha Rubasinghe <[email protected]> wrote: > Isuru, > By allowing autocommit = true, your giving control of your JDBC logic > completely to database engine. > This will be problematic when you want to perform a rollback resulted by > multiple SQL statements. > > For example: > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > with autocommit=true > > insertOrder() > //order saved to database > insertPayment() > //payment save to database > Here no way of rollbacking Order if payment is not successful. > > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > with autocommit=false > beginTxn { > insertOrder() > insertPayment() > } finally{ > commitTxn() > } error { > rollback() > } > > rollback operation can remove both payment and order records as they are > part of a single txn. > > > > > > On Mon, Nov 23, 2015 at 4:03 PM, Isuru Perera <[email protected]> wrote: > >> Hi, >> >> When we write JDBC code, is it okay if we depend on auto commit in the >> connection we get from the DataSource? >> >> The JDBC Reporter for Metrics [1] depends on auto commit "true" value set >> in the DataSource configuration. The JDBC reporter doesn't have any complex >> transactions and I thought it should be okay to depend on auto commit. >> >> WDYT? >> >> Thanks! >> >> Best Regards, >> >> [1] >> https://github.com/wso2/carbon-metrics/blob/v1.1.1/components/org.wso2.carbon.metrics.jdbc.reporter/src/main/java/org/wso2/carbon/metrics/reporter/JDBCReporter.java#L233-L258 >> >> -- >> Isuru Perera >> Associate Technical Lead | WSO2, Inc. | http://wso2.com/ >> Lean . Enterprise . Middleware >> >> about.me/chrishantha >> Contact: +IsuruPereraWSO2 <https://www.google.com/+IsuruPereraWSO2/about> >> > > > > -- > /sumedha > m: +94 773017743 > b : bit.ly/sumedha > -- Isuru Perera Associate Technical Lead | WSO2, Inc. | http://wso2.com/ Lean . Enterprise . Middleware about.me/chrishantha Contact: +IsuruPereraWSO2 <https://www.google.com/+IsuruPereraWSO2/about>
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
