Why doesn't MySQL JDBC use "Start transaction" query to start transactions?

Question

I have a Java app which uses JTA(Apache Geronimo) to manage transactions. The database in use is MySQL. App has a lot of begin and commit methods. However looking at the MySQL general log I could not find a single "Start transaction" query/command. The log is full of SET autocommit=1 and SET autocommit=0 with commit and rollback. Due to this, looking at logs I am unable to identify at what point the transaction began. I am not a Java guy and I could not find any helping resource on this either.

Answer 1

MySQL's JDBC driver implements the Java JDBC API. The java.sql.Connection interface does not have a method to start a transaction.

A transaction begins implicitly when you execute an SQL query.

If the driver is in autocommit mode, the transaction is committed automatically once the SQL query finishes.

If the driver is not in autocommit mode, the transaction started by your query remains active until you call Connection.commit() or Connection.rollback().

See also How to start a transaction in JDBC?

Answer 2

Start transaction and commit statements are used in MySql InnoDB. But In MySQL MyISAM, these commands are not valid so you need to use set autocommit = 0 instead of Start Transaction and set autocommit = 1 in place of commit; InnoDB allows both the ways but MyISAM allows only set autocommit. Also, note that these commands perform somewhat similar work but they are not identical. And the use of set autocommit is not recommended in InnoDB. For more information refer this question in StackOverflow.