Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is transaction possible when one query is dependent on previous query result?

 
Sijohn Thomas
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have three queries.

Query 1 - Inserts a row in table1.
Query 2 - Fetches the primary key id for the inserted record (by Query 1) in table1.
Query 3 - Uses the id fetched by the second query as foreign key to insert values in table 2.

Can I put all 3 queries in one transaction?

If I write like

conn.setAutoCommit(false);

execute all three queries

conn.commit();

The problem I would be facing is if the third query will fail as the commit happens at last. (As it requires the id by query 2). Please help.



 
Saikat Mukherjee
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This can be achieved using a stored procedure if i am not wrong
 
Jayesh A Lalwani
Rancher
Posts: 2756
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes you can put all SQL statements in one transaction, and each SQL will see the changes done by other sqls before it.

Actually, isolation level changes this behavior a bit. A transaction will always see its own changes. If you use read uncommitted, transactions can see other transactions changes. If you use read committed or repeatable read, transaction will not be able to see other transactions changes unless the transaction is committed.
 
Sijohn Thomas
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jayesh. Would appreciate if you could elaborate on transactions with read uncommited with an example. Thanks a lot.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic