sql server - C# Nested transaction in a loop, but rolling back if any of them fail -

the issue i'm trying solve follows:

i have process ingests data file , inserts things in multiple tables, done 1 transaction, large data sets rollback or commit time out , fail, no matter set time out (at least far attempts have shown me). decided need rewrite functionality "chop" task up. stands pseudo code current code looks (pseudo code used avoid unnecessary information)

variable = functionthatreadsfromafile();  itransactionmanager transactionobject = new transactionmanager(); idbtransaction dbtransaction = transactionobject.get();  writetofirsttable(variable ,dbtransaction); writetosecondtable(variable ,dbtransaction); writetothirdtable(variable ,dbtransaction);  if(!error) transactionobject.commit(dbtransaction); else transactionobject.rollback(dbtransaction); 

like said, works fine smaller data sets, when file has more particular amount of rows (dependent on time out) fails on commit or rollback.

i can't change time out 10,000 seconds example, in fact due way program structured can't change time out @ beyond testing purposes. i'm trying have program work on 100 rows @ time instead of entire file @ once, committing them , rolling if 1 of "sets of hundred" fail, i've understood can done nested transactions, doing ;

using (transactionscope outtertransaction = new transactionscope()) {      while(file.read())      {          using (transactionscope innertransaction = new transactionscope())          {          variable = getnexthundredorlessrows(file); //100 rows @ time          writetofirsttable(variable ,innertransaction );          writetosecondtable(variable ,innertransaction );          writetothirdtable(variable ,innertransaction);           if(!error)         innertransaction.complete();         else         innertransaction.rollback();          }      }      if(!error)         outtertransaction.complete();         else         outtertransaction.rollback();  } 

isn't working, idea i'm doing wrong?

thank in advance taking time attempt me out.

edit: right track take solve issue? i've read nested transactions join scope of outer transaction , still run same issue on .complete?

i believe need commit transaction @ bottom of loop (this cause other problems need consider, ie how rollback). if wait til you're outside of inner transaction loop transactions bundled , committed @ once means performance far worse performance of first example.

edit: noted causes problem if 1 set of inserts fails cannot rollback changes. solve problem think simplest solution create "rollbackstack". inside while loop push object whatever data necessary roll onto stack. add flag indicate if fails. if there failure, switch flag , break loop. pop stack undoing changes until stack empty. know db in state before started. after can retry insertion group 1. alternatively can add retry logic continue group x x group @ failed before doing roll back. if after set number of retries fail complete full insert go rollback method pop stack til empty.