Take the 2-minute tour ×

I have wondered for a long time why the JDBC API provides an autocommit mode (java.sql.Connection.setAutocommit()). It seems like an attractive nuisance that just lures people into trouble. My theory is it was only added to JDBC in order to simplify life for vendors who wanted to create tools for editing and running SQL using JDBC. Is there any other reason to turn on autocommit, or is it always a mistake?

share|improve this question

7 Answers 7

up vote 13 down vote accepted

Only reasonable reason I can see is to get rid of the connection.commit() and connection.rollback() boilerplate in simple single-query transactions in small applications. JDBC in raw form requires by itself already a lot of boilerplate. Every line less makes JDBC less scary to starters.

share|improve this answer

Unfortunately, using autocommit is database specific (as is transaction behavior). I think if you don't have a global, programmatic transaction strategy, autocommit is probably better than just hoping everyone properly closes/rolls back transactions.

Speaking for MySQL, you can leave autocommit=true on by default, and it will automatically turn that off when you BEGIN a transaction. The only reason to set autocommit=false is if you want to force an error if someone tries to start a transaction without a BEGIN.

For simplicity in a typical Java + MySQL application today, I would more or less ignore the auto-commit setting, use an open-session-in-view pattern and call it good.

I would strongly discourage explicit RDBMS row locks and use optimistic locks instead. Hibernate offers built-in support for optimistic locks, but it's an easy pattern to adopt even for hand-rolled code and offers better performance.

share|improve this answer
To clarify the typical Java + MySQL application: leave auto-commit set to true. Open a session (and begin a transaction) once, at the start of the incoming request (either a web page or a REST/JSON). If the request processes normally, commit the transaction at the end of the request processing. Then, add a roll back to your 500 page rendering for the request. This is basically how the Play Framework handles things, for example. It's intuitive for the user and the developer. –  Will Iverson Jan 11 '13 at 18:32
This is a good answer -- "web request" should pretty much correlate with the "DB transaction" in a webapp. Thus we need to rely on optimistic, not DB locking. It helps to use OpenSessionInView to hold a session open thru the entire request, incl view rendering, and then flush/commit at the end of the request. –  Thomas W Nov 21 '13 at 9:52

Auto-commit is convenient; but with changes to the JDBC 3 spec, has become much less useful.

Since JDBC 3connections in "auto-commit" mode can't have more than one Statement open. Executing another statement, will close the first -- including any ResultSet.

Thus, looping within a SELECT and issuing UPDATEs (or even nested SELECTs) will tend to fail. Apparently it's a crime, to actually want to do something with the results of your outer SELECT!

Anyway, depends on the specific driver & version.. but in general the JDBC 3 spec appears to mandate this unhelpful behaviour. Upgrading drivers can also unhelpfully 'discover' this behaviour.

Why use auto-commit? Originally, it was helpful & convenient. As other answers say, JDBC requires copious amounts of GUFF and HANDLING to call correctly.. JDBC is not really a well-designed API :(

These days, you'd be better using Hibernate or Spring's JdbcTemplate.. And if you're doing servlets/web apps, place your transaction management (begin/end) or Hibernate session (bind it to a thread-local) at the boundaries of the "User Request".

eg, get bind your connection/transaction at the start of the ServletRequest; and return it at the end.

You can use a javax.servlet.Filter or similar, and bind it to a thread-local, make a static helper to get it or require it, etc.

share|improve this answer

Commit-mode changes the way the db holds locks.

It is advisable to disable the auto-commit mode only during the transaction mode. This way, you avoid holding database locks for multiple statements, which increases the likelihood of conflicts with other users.


To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access by others to the data that is being accessed by the transaction. (Note that in auto-commit mode, where each statement is a transaction, locks are held for only one statement.)


share|improve this answer
Given that even mySQL and DB2 are multi-versioning these days, that tutorial is becoming somewhat anachronistic. :( –  Affe Dec 15 '10 at 19:40
Snapshot isolation levels don't come for free: blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/… You gain concurrency with snapshot isolation, but you add overhead. –  hythlodayr Dec 15 '10 at 19:50
Understanding the way you acquire locks is always relevant. –  nate c Dec 15 '10 at 20:01
Of course, I just think the matter-of-fact tone of the tutorial wrt "You should use auto-commit to prevent database lock contention." is disingenuous. It's an oversimplified blanket claim, that in 2010 has likely already become a minority use case. (But as an answer to the actual question that was asked, does provide good insight to why the (aging) API was designed that way!) –  Affe Dec 15 '10 at 20:11
Autocommit is pretty old. I think the answer is a lot more insightful on the background of why it exists than saying something like 'Well you don't have to type commit ...' –  nate c Dec 15 '10 at 20:15

I almost always run with autocommit=true. 99% of the time, my updates are atomic. Sure, there are cases where if you write the debit and then fail trying to write the credit you want to rollback. But in my experience, these are relatively rare. Usually each record that I write stands on its own. In that case, not needing to bother with doing a commit after each write is convenient. It save a line of code here and there. It may save more than that if, given the structure of the program, it means I don't need an additional try/catch block or that I don't need to pass a connection object between functions. It saves on annoying bugs where someone forgot to do a commit.

The only way I see that it could "lure someone into trouble" is he decides that turning autocommit off and doing the commit or rollback is too much trouble and so he does updates that should be within a transaction individually. Then everything apears to work fine as long as nothing happens that should abort the transaction. If test scenarios are inadequate, I could imagine this slipping into production.

But you could say the same about almost any feature of a language. Like, suppose you write a program that process numbers that 90% of the time will fit in a long, but every now and then may be bigger. Faced with this situation, the right thing to do is to use BigInteger or create a new class to handle the bigger numbers. A lazy programmer might be lured into using long because it will usually work and other alternatives are too much trouble. Would you therefore conclude that Java should not include long's (or int's) because someone might be lured into using them when they are not appropriate?

If in your programs the majority of updates have to be done within a transaction context, then turn autocommit off. Its presence doesn't hurt you any. It's there for when it's convenient, but when it's not you can turn it off.

share|improve this answer
Since the JDBC 3 spec, connections in "auto-commit" mode can't have more than one Statement open. Thus, nested UPDATEs or even SELECT within an outer SELECT loop will cause problems. –  Thomas W Jun 5 '12 at 0:36

95% of the codebase I'm working with now involves single updates where having autocommit on is perfectly reasonable. So, we default to it on. Only turning it off long enough to do the sections of code that need to be a transaction, then autocommit goes right back on!

share|improve this answer

Well there are certain conditions that needs careful look while enabling the “auto-commit” at global level:

a.) The transaction management at query level will be left to user, for an instance if one needs bunch of queries to either succeed or fail together then it needs to be wrapped under BEGIN and commit Transaction.

b.) Remember, there’s no rollback when “auto-commit” is enabled.

c.) There’s also an overhead of writing (committing) each and every transaction.

d.) For read only queries there’s no explicit need of “auto-commit” but by enabling “auto-commit” it is automatically enforced for all queries.

If table locking is the only concern for enabling the auto-commit then it may not be a good idea, rather one may resort to lower lock timeouts.

share|improve this answer

Your Answer


