What is the effect of disabling auto-commit on a database connection?

Question

If I set a connection to manual commit does it mean my process will lock the database? If I have to execute multiple SQL queries that might probably take 4-5 hours to execute, does it mean during that time no other user will get to access my database?

I am taking scenario of web application where there are thousands of users accessing same database.

connection.setAutoCommit(false);
//multiple sql query that will probably take 4-5 hours to be executed

connection.commit();

Answer 1

Setting autocommit to false will usually have no effect on reads, so if that's the kind of queries you're running, shouldn't be a problem.

What autocommit=false guarantees is that you'll be executing your queries (inserts, deletes, updates), in a transaction, which means that they'll either all succeed (with a commit at the end), or fail, and be rolled back.

When you're inserting, updating, or doing a select ... for update, some rows will be locked, and this is harder to predict, since it depends on your engine, Mysql version, isolation level, etc.

If, for instance, autocommit=false and two users need to update the same rows at the same time, then one will be locked waiting for the first one to complete, with either a commit or a rollback.

Let's say USER1 triggers an update on your database that will target 10 rows.

You begin the transaction, do the update, do a couple more queries...

Before the commit/rollback, USER2 triggers the same update, or an update that will target one or more of the same rows that USER1 is updating.

USER2 will be locked, waiting for USER1 to either commit or rollback, before the update can be executed.

One way of testing this is opening two different connections to the database, for instance, via command line or some other client, and simulating this behavior. Set the autocommit to false, do an update on one client, do the same on the second, and see it hang there until you commit or rollback on the first client.

This technique can be very handy to help you understand what goes on behind the scenes.

Answer 2

connection.setAutoCommit(false); means that you are starting a transaction on this connection. All changes you will do to the DB tables in this connection will be saved on commit or reverted on rollback (or disconnect without commit). It does not mean that you lock the whole DB. Whether other users will be locked trying to access the tables your transaction uses will depend on the operations your transaction is doing and transaction isolation level.