This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes setAutoCommit() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "setAutoCommit()" Watch "setAutoCommit()" New topic
Author

setAutoCommit()

Cameron Park
Ranch Hand

Joined: Apr 06, 2001
Posts: 371
Hi, I am a bit confused about setAutoCommit(). What happens when we set it to true? Whatever commands that is in its Statement will be executed right a way(immediately commits)?
What happens when we set it to false?
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
auto commit means that any updates to the database will be committed when they are executed. If auto commit is off, you can do a rollback, and the changes will be removed. If you commit after the execution, the updates are kept.
Dan
Cameron Park
Ranch Hand

Joined: Apr 06, 2001
Posts: 371
So, even after execute, if we do not commit the db is unchanged? This only applies to "update" (Update, Insert, Delete) right?
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Yes, when I say "update" the database, I mean update, insert, delete.
Yes, I believe that if you have autocommit off and do not do a commit, the changes are not kept in the database.
When your working in TRANSACTIONS, multiple tables are "updated" to reflect a single type of transaction. For example, adding an employee may mean inserting into the employee, department, payroll tables. Depending upon how you wish your database to function, you may wish to rollback all inserts if the payroll table insert failed. On the other hand, you may commit after inserting into the employee and department table knowing your system can clean up after the payroll insert failure. Or your cleanup routine can restart at the point of failure.
These are strategic points, and are very important. You also have to realize that commits are expensive, so you may try and get in as much as you can between the commit points. As with all IT issues, there are tradeoffs you have to deal with.
Dan
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Daniel Dunleavy:
I believe that if you have autocommit off and do not do a commit, the changes are not kept in the database.

Depends on what you mean by "kept". Your changes are being processed and stored by the database. When you read your data, you will read the modified data even before you committed it. Depending on the transaction isolation level your database supports, others may be able to see your modified data as well (dirty reads). But the data is not kept in the sense that, if you rollback (or if power gets cut), it will disappear from the database.
When your working in TRANSACTIONS, multiple tables are "updated" to reflect a single type of transaction. For example, adding an employee may mean inserting into the employee, department, payroll tables. Depending upon how you wish your database to function, you may wish to rollback all inserts if the payroll table insert failed. On the other hand, you may commit after inserting into the employee and department table knowing your system can clean up after the payroll insert failure. Or your cleanup routine can restart at the point of failure.

NO! I could not disagree more. No cleanup. Why did the insert fail in the first place? The JVM may have crashed. The network may be out. The power may be cut. When something goes wrong, there is every chance that you won't be able to clean up.
The bottom line is really, really simple. When you start with a transaction, you start with an internally consistent database. Then you perform a couple of co-ordinated actions, during which your data won't be consistent anymore (eg, you have an employee, but no payroll yet). When you are finished, your data is consistent again. That is when you commit.
No earlier.
You never, ever, commit work on an inconsistent database which would require cleaning up.
These are strategic points, and are very important. You also have to realize that commits are expensive, so you may try and get in as much as you can between the commit points.

Again I have to disagree. A database has to maintain buffers to store uncommitted data and rollback information. When you query your data, the database has to look in the committed data and your uncommitted data, which is typically less efficient. With some transaction isolation levels, the database effectively maintains a snapshot of the data as it was when you started the transaction. When you finally commit the transaction, it has to move your data to a permanent resting place and clear rollback information. All these things take disk space, memory, and processing time proportional to the length of your transactions. Not committing work regularly can be at least as expensive as committing every single teeny-weeny little update.
As a rule, you would commit work every time you had consistent data. If the units of work are very small, you would use JDBC to batch them, and commit after every successful batch.
- Peter

[This message has been edited by Peter den Haan (edited May 04, 2001).]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: setAutoCommit()
 
Similar Threads
Clarification required related to some transaction doubts
do i need to set setAutoCommit to true in the final block when i use both pooling and transaction
Set content -length
No Transaction
insert problem in oracle