• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Transaction Management in JDBC

 
Deepika Saxena
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34218
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Deepika Saxena
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne. That clarified my doubt.
--Deepika
 
Balu Sadhasivam
Ranch Hand
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you. I think we are actually in agreement, just phrasing things awkwardly.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic