Whenever we develop application with the database, we can’t avoid dealing with database transaction, normally the application can define a datasource, pointing to a database. In the code, say java, you can get a connection from the datasource.

If you don’t explicitly specify whether the autocommit property of the connection, the default value will be true in most of the datasource.

It is a valid and in most cases you don’t want to change this value. What this property does is, for each and every sql statement your application is going to execute, it all automatically commit them.

However, you may want to control and wrap a few sql statement in one transaction, either commit all of them, or rollback all of them. Meaning once you change this property to false, you will need to take care of the commit behaviour and rollback behaviour by yourself, also make sure you change the property back to true. Otherwise it won’t be committing any future db changes.

Here are a few traps you probably need to be aware.

1. In some datasource implementations, programmatically changing autocommit property is not well supported.
2. Code to manually switching on and off this autocommit need to be carefully
3. The connection object you acquire to perform the property change must be the same one you are performing sql statement on.

Thanks for joining me!

Good company in a journey makes the way seem shorter. — Izaak Walton