none
SubmitChanges carries over from a failed transaction to the next RRS feed

  • Question

  • I have a problem that with a single dbcontext, submitchanges doesn't completely roll back transactions when a submit fails:

    Basically, I have code that looks like this:

    using(DbContext db = ...)
    {
    	try
    	{
    		using(TransactionContext trans = ...)
    		{
    			db.Insert( something that fails for any reason ) // (1)
    			db.Insert( something that has a FK on the previous object ) // (2)
    			db.SubmitChanges();
    			trans.Complete();
    		}
    	}
    	catch(Exception)
    	{ }
    
    	using(TransactionContext trans = ...)
    	{
    		db.Insert( something completely unrelated ) // (3)
    		db.SubmitChanges(); //throws an error about (2) failing.
    		trans.Complete();
    	}
    
    }

    How can I prevent this?

    Preferably without having to kill the connection and reconnect, because that's expensive.

     

    Thursday, June 3, 2010 8:43 AM

Answers

  • @KristoferA: That's strange, because we have seen quite a performance improvement when we started to reuse our DataContexts rather than re-creating them in each function...


    In that case, I would recommend looking closer at what is going on and where the time is spent. A new DC instance doesn't mean a new [ADO or transport layer] SQL connection. The performance difference between one or two DCs, based on the sample code in your original post should be almost negligible under normal circumstances (assuming nothing significant was left out in the sample).

    If for some reason you see a performance issue related to sql server connections, you may want to create a SqlConnection explicitly and pass it to the DataContext constructor.


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Monday, June 7, 2010 1:54 PM
    Answerer
  • 	
    DataContext db = new DataContext();
    using(TransactionContext trans = ...)
    	{
    		try
    		{
    			db.Insert( something that fails for any reason ) // (1)
    			db.Insert( something that has a FK on the previous object ) // (2)
    			db.SubmitChanges();
    			trans.Complete();
    		}
    		catch(Exception)
    		{
    			db = new DataContext();
    		}
    	}
    
    	using(TransactionContext trans2 = ...)
    	{
    
    	}


    You can try this. 
    Monday, June 7, 2010 2:24 PM

All replies

  • Hello,

    You could using a TransactionScope and call Trans.RollBack() in Catch block to handle the exception. You could take a look at a sample here:
    http://blogs.msdn.com/b/wriju/archive/2007/08/06/linq-to-sql-using-transaction.aspx

    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Monday, June 7, 2010 7:54 AM
  • The last sentence ("Preferably without having to kill the connection and reconnect, because that's expensive.") looks like it is based on a misconception.

    Creating a datacontext instance is not very expensive, and connections are separate from DC instances. Connections are pooled & cached by the underlying ADO.net infrastructure anyways, so creating a new dc instance doesn't mean you have to create a new database connection. If two separate and unrelated units of work, give them a datacontext instance each...


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Monday, June 7, 2010 8:42 AM
    Answerer
  • @Roahn Luo: Actually, it seems that it's already a TransactionScope that I use ( I typed TransactionContext from memory, sorry). In theory, it should be rollbacked automatically when disposing (at the end of the using block) since trans.Complete() was not called, shouldn't it?

    @KristoferA: That's strange, because we have seen quite a performance improvement when we started to reuse our DataContexts rather than re-creating them in each function...

    Monday, June 7, 2010 12:51 PM
  • @KristoferA: That's strange, because we have seen quite a performance improvement when we started to reuse our DataContexts rather than re-creating them in each function...


    In that case, I would recommend looking closer at what is going on and where the time is spent. A new DC instance doesn't mean a new [ADO or transport layer] SQL connection. The performance difference between one or two DCs, based on the sample code in your original post should be almost negligible under normal circumstances (assuming nothing significant was left out in the sample).

    If for some reason you see a performance issue related to sql server connections, you may want to create a SqlConnection explicitly and pass it to the DataContext constructor.


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Monday, June 7, 2010 1:54 PM
    Answerer
  • 	
    DataContext db = new DataContext();
    using(TransactionContext trans = ...)
    	{
    		try
    		{
    			db.Insert( something that fails for any reason ) // (1)
    			db.Insert( something that has a FK on the previous object ) // (2)
    			db.SubmitChanges();
    			trans.Complete();
    		}
    		catch(Exception)
    		{
    			db = new DataContext();
    		}
    	}
    
    	using(TransactionContext trans2 = ...)
    	{
    
    	}


    You can try this. 
    Monday, June 7, 2010 2:24 PM
  • Thanks. I'll try.
    Monday, June 7, 2010 5:42 PM
  • Any luck? Can you please share solution/workaround for this issue?

     

    I am running into same issue...

    Friday, May 13, 2011 3:07 PM