Autocommit false mode java

Question

Hi All,

I am not very sure about the auto commit mode of java JDBC, I am making a connection whose auto commit mode is set to false. After that I fire a select query on database, which works fine, I am under impression that commit has to be called only for insert, update, delete statements.

Now do I need to commit the connection for Select query? If not will that table be locked for other transactions?

Thanks, Rohit.

Answer 1

It depends on your isolation level. If you use READ COMMITTED, the SELECT only creates locks if you specify SELECT FOR UPDATE. If you use REPEATABLE READ or higher, every SELECT could create a lock (depends on your database model).

Answer 2

I am not very sure about the auto commit mode of java JDBC, I am making a connection whose auto commit mode is set to false. After that I fire a select query on database, which works fine, I am under impression that commit has to be called only for insert, update, delete statements.

That's depends of your application. If there are simple updates, inserts, deletes you can leave autocommit=on. Generaly, it's recomended to off autocommit. That configuration gives you more flexibility and power in applicaiton. You can use complex transactions and you can decide when transaction begins and when ends.

Now do I need to commit the connection for Select query? If not will that table be locked for other transactions?

No, select statment doesn't begin transaction (insert, update, delete does) so there is no need to commit/rollback after select statment. There is one special case of select which lock selected rows and need transaction - with for update clause