Why set Autocommit to true?

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 开发者_StackOverflow中文版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?

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.

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.

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.

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.

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.)

http://download.oracle.com/javase/tutorial/jdbc/basics/transactions.html

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!

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.