File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Transaction Management in JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Transaction Management in JDBC" Watch "Transaction Management in JDBC" New topic
Author

Transaction Management in JDBC

Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
Hi,
could any body tell me how to manage transactions using jdbc. Please find the details below.
I want to insert 100 rows in a table and at 51 row an exception is thrown. in this case what exactly happens to the transaction.
how can i ensure that all the 50 rows, which are already inserted are saved in the table.
Any help would be highly appreciated.

Thanks.
--Deepika
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Deepika,
The phrase "auto-commit" is what you are looking for. If it is set to true (which is the default), the 50 successful inserts will be committed to the database. If it is set to false, a commit is not done until you explicitly request one - effectively rolling back the 50 inserts.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
Thanks Jeanne. That clarified my doubt.
--Deepika
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874


could any body tell me how to manage transactions using jdbc. Please find the details below.
I want to insert 100 rows in a table and at 51 row an exception is thrown. in this case what exactly happens to the transaction.
how can i ensure that all the 50 rows, which are already inserted are saved in the table.
Any help would be highly appreciated.


If 100 is part of a transaction , and you want 50 to be committed even if 51th fails , how could that 100 belongs to same transaction .
You requirement is wrong and better check out what is a transaction in a database context .
Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
Hi sadasivam,
This is not a requirement but a doubt in JDBC . As per Jeanne's reply, i suppose that each insertion is a single transaction and on successful insertion that would be committed. we can make all 100 records insertion as a single transaction by making the whole job as a batch job.

Jeanne, please correct me if i am wrong.
--Deepika
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39478
    
  28
I think you have misunderstood auto-commit. If you simply set auto-commit to false, then you can roll back all your transactions at any time, until you send a commit instruction. If you have auto-commit set true, then each update is committed immediately it is finished.
But those are not necessarily transactions; there might be another action taking place simultaneously, so these actions might not be "ACID." By using auto-commit = true, you would ensure the 50 insertions are maintained in the database, and the 51st would not because of the Exception. You can make each insertion a transaction in its own right, in which case nos 1 2 3 ... 50 52 53 ... 100 will be maintained permanently as ACID transactions, or you can bundle all 100 together as a single transaction in which case (I think) none will be maintained after the exception.
Or you can try locking the tables involved, inserting all the updates, then commit and unlock the tables.

I think that is correct; others please check whether I am mistaken.
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874



i suppose that each insertion is a single transaction and on successful insertion that would be committed. we can make all 100 records insertion as a single transaction by making the whole job as a batch job.


Yes you are right.


Or you can try locking the tables involved, inserting all the updates, then commit and unlock the tables.


Yes that's taken care by the transaction isolation level set by the driver by default or changed programmatically.

Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39478
    
  28
Balu Sadhasivam wrote:


i suppose that each insertion is a single transaction and on successful insertion that would be committed. we can make all 100 records insertion as a single transaction by making the whole job as a batch job.


Yes you are right.
That doesn't sound quite right. It may be the way you are expressing it, but if you have a batch of 100 transactions, then 99 of them will be executed and no 51 will be rolled back. If you want a single transaction you would have to put all 100 updates into the same transaction. I don't know whether you can nest transactions inside transactions, but "nested transactions" sounds very peculiar.

Or you can try locking the tables involved, inserting all the updates, then commit and unlock the tables.
Sorry, I wasn't clear. I meant that as an alternative to using a transaction.
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874



That doesn't sound quite right. It may be the way you are expressing it, but if you have a batch of 100 transactions, then 99 of them will be executed and no 51 will be rolled back. If you want a single transaction you would have to put all 100 updates into the same transaction



that's partially right. Here the auto commit comes to picture. since you have already made auto commit as false , now after one failed record (51) , you can still rollback all 99 successful transactions or commit 99 as such..
But again this all depends on the driver behavior that you use , whether it proceeds after failed transaction or not.


Sorry, I wasn't clear. I meant that as an alternative to using a transaction.


Transaction isolation level are what is used to control such behaviors in concurrency execution.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39478
    
  28
Thank you. I think we are actually in agreement, just phrasing things awkwardly.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Transaction Management in JDBC