What is the expected change in behavior from having done start/work/commit inside the loop, vs just doing the work without using a transaction? If the outer transaction is committed, obviously the inner work will be kept, just as it would be even if that work were not inside its own transaction. If the outer transaction is rolled back, I assume you intend for the inner work to be discarded (even though it was done inside a successfully committed transaction).
Perhaps obviously, if you want the inner work to be kept even if the outer transaction is rolled back, don't start the outer transaction until the inner work is done. My notion of the behavior of "nested transactions" is that rolling back the outer transaction will _always_ return things to the way they were when the outer transaction started. One reason you could want to do this is that you want to use the same block of code that does the start/work/commit sequence in another situation, without necessarily having started an outer transaction. What I've done in that situation is to have an "are we inside a transaction" boolean flag, and the (equivalent of your) inner code doesn't start a transaction if one was already going on (and it keeps track of whether it started one). If the inner code needs to roll back, that rolls back everything done since the outer transaction started; if all is ok and it wants to commit, it only does so if it had actually started the transaction (but not if it saw one as being in process already). If your reason for doing it this way is that you want the locks taken by the inner transaction to be cleared, how this interacts with the "transaction isolation level" setting of other processes is going to be very database dependent. (Are you using Oracle?) As you can see, I think it matters why you want to do this; I'm sure it's complex but so is what you're (thinking of) asking the database to do for you. As Peter asked, have you tried it? The code I described above was written when having the re-used inner code start a new transaction resulted in a "transaction already started" error -- so I added the "in transaction" flag and made the inner code work differently if it was set. Good luck... At 02:47 PM 1/4/2006, Franklin Gray wrote >Can ado.net 1.1 support nested transactions? I would like to have the >ability to rollback all changes at the end but also have a start and >commit trans in the loop. Note: this is not the exact logic but a simple >example to demonstrate what I want to accomplish so please don't ask why >because that's too hard to explain. > >IE >Start trans > >begin loop > start trans > do work > commit trans >end loop >commit trans (or rollback depending on business rule) J. Merrill / Analytical Software Corp =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
