Darren, On Sun, Oct 21, 2012 at 11:01 PM, Darren Duncan <dar...@darrenduncan.net> wrote: > Igor Korot wrote: >> >> Hi, ALL, >> Is it possible to have transaction inside transaction? Will it be >> handled correctly? >> >> What I mean is: crate transaction 1 by issuing "BEGIN", create >> transaction 2 by issuing "BEGIN". >> Close transaction 2 by issuing either "COMMIT" or "ROLLBACK". >> If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1. >> If "COMMIT" is issued, continue transaction 1. >> >> Thank you. > > > Didn't you mean to say, "regardless of how (inner) transaction 2 ends, > continue transaction 1"? > > Being able to rollback part of a main transaction without rolling back the > whole thing is the whole point of nested transactions. (Likewise for > "savepoints", which are different syntax but the same functionality.) > > If you want a rollback on the inner to rollback the outer unconditionally, > you never needed a nested transaction in the first place, but rather just > the one outer transaction 1, or you might have wanted an autonomous > transaction, which is different, depending on what you want.
OK, consider the following scenario: table1_master( id integer primary key, <more fields follows> ) table2_slave( slave_id integer primary key, table1_master_id foreign key, <more fields follows> ) table3_slave( slave_id integer primary key, table1_master_id foreign key, <more fields follows> ) With an insert everything is OK. I have one transaction starting from bottom up to satisfy foreign key constraints. But if I want to edit the data my algorithm is: 1. Query all tables to grab the data according to table1_master.id 2. Present the data on screen and let the user edit the data. Delete the data retrieved in step 1. 3. Grab all the data in the appropriate class. 4. Start transaction 1 to insert the data. 5. Start transaction 2 to delete the data from tableN_slave 6. If 5 fails, rollback transaction 2, then transaction 1 (i.e. don't go with tr.1) 7. If 5 succeeds continue with transaction 1. What would be the best approach from sqlite point of view? Thank you. > > -- Darren Duncan > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
