wood burning stoves 2.0*
The moose likes JDBC and the fly likes Transaction over multiple tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Transaction over multiple tables" Watch "Transaction over multiple tables" New topic
Author

Transaction over multiple tables

Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Can you maintain a Transaction over multiple Table inserts? I will be entering data into Table1 and retrieving the ID then entering that ID and other data into Table2 then possibly a 3rd Table. (Normalization, ha).
If any one of those inserts fail for any reason, I don't want any of them to happen. And I just didn't know if you could maintain a Transaction that would roll back inserts into multiple tables.
Thanks.


GenRocket - Experts at Building Test Data
Jason Steele
Ranch Hand

Joined: Apr 25, 2003
Posts: 100
Gregg,

Man, I don't think I've ever seen you stumped in here! And truth be told, I'm not sure I got the correct answer, but here it goes. I believe, and feel free to chime-in whenever, that once a transaction has begun and full rollback can be performed anytime to the last commit. Now if that is not correct, somebody please correct me. I would certainly like to know.


An egg is a chicken's house!
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Man, I don't think I've ever seen you stumped in here
You just haven't been around long enough.
I think, and this goes along with your thinking, that if I begin a transaction for 1 table and if there are no errors then I can go into a transaction for a second table, and then if there are no errors, I can commit both transactions at the end of the task.
This makes sense to me, but I will have to see. Now with that being said, I am beginning to use Hibernate for my project so I don't even know if this applies. But it will be good to know for future reference anyway.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60748
    
  65

Yes, you can perform multiple inserts in a single transaction. This is pretty much the whole purposes of transactions: to make sure that the database retains relational integrity.
bear


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Dana Hanna
Ranch Hand

Joined: Feb 28, 2003
Posts: 227
Originally posted by Gregg Bolinger:
Man, I don't think I've ever seen you stumped in here
You just haven't been around long enough.
I think, and this goes along with your thinking, that if I begin a transaction for 1 table and if there are no errors then I can go into a transaction for a second table, and then if there are no errors, I can commit both transactions at the end of the task.
This makes sense to me, but I will have to see. Now with that being said, I am beginning to use Hibernate for my project so I don't even know if this applies. But it will be good to know for future reference anyway.


A transaction is not begun on a table, it is begun on a connection. Anything that the connection does (with some limits ex. TRUNCATE), will be rolled back with a rollback call.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Transaction over multiple tables
 
Similar Threads
SEVERE: Duplicate entry '1' for key 'PRIMARY'
Transaction Isolation
Is there a limit on the number of sql statements in a single transaction?
comparing csv file values with database attributes.
bulk insert