TransactionAbortedException Occurs Randomly
in [Programming SQL]
|
From: Scott on 23 Jul 2008 15:41 Somewhere around the 50th time the method shown below is called a TransactionAbortedException is thrown. The inner exception is "The requested operation cannot be completed because the connection has been broken". Does anyone know what would cause this to happen? protected void Insert() { using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew)) { using (SqlConnection connA = new SqlConnection(connStringA)) { using (SqlConnection connB = new SqlConnection(connStringB)) { connA.Open(); connB.Open(); // Call table adapters Update taTableA.Update(myDataTableA); taTableB.Update(myDataTableB); taTableC.Update(myDataTableC); scope.Complete(); connB.Close(); connA.Close(); } } } }
From: Andrew J. Kelly on 23 Jul 2008 17:18 It appears as if you are starting a new transaction with each loop. Why are you doing that? That would nest all the transactions and eventually I would think you would hit a limit although I don't know what that is. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Scott" <Scott(a)discussions.microsoft.com> wrote in message news:F865EF73-1EAF-4BA6-B7BD-74222892F951(a)microsoft.com... > Somewhere around the 50th time the method shown below is called a > TransactionAbortedException is thrown. > The inner exception is "The requested operation cannot be completed > because > the connection has been broken". > Does anyone know what would cause this to happen? > > protected void Insert() > { > using (TransactionScope scope = new > TransactionScope(TransactionScopeOption.RequiresNew)) > { > using (SqlConnection connA = new SqlConnection(connStringA)) > { > using (SqlConnection connB = new SqlConnection(connStringB)) > { > connA.Open(); > connB.Open(); > > // Call table adapters Update > taTableA.Update(myDataTableA); > taTableB.Update(myDataTableB); > taTableC.Update(myDataTableC); > > scope.Complete(); > > connB.Close(); > connA.Close(); > } > } > } > } >
From: Scott on 24 Jul 2008 08:24 I don't understand what you mean. What loop are you reffering to? There is no loop in the code snippet that nests transactions. scope.Complete() is called followed by exiting the "using" statement for the TransactionScope which then exits the method. The method Insert() is not called in a tight loop either. "Andrew J. Kelly" wrote: > It appears as if you are starting a new transaction with each loop. Why are > you doing that? That would nest all the transactions and eventually I would > think you would hit a limit although I don't know what that is. >
From: Scott on 24 Jul 2008 08:36 Note: Added a second call to a method which cascades the transaction. Not sure if this would be a problem but thought it was worth mentioning. protected void Insert() { using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew)) { using (SqlConnection connA = new SqlConnection(connStringA)) { using (SqlConnection connB = new SqlConnection(connStringB)) { connA.Open(); connB.Open(); // Call table adapters Update taTableA.Update(myDataTableA); taTableB.Update(myDataTableB); taTableC.Update(myDataTableC); SaveSecond(connection); // Cascades transaction scope.Complete(); connB.Close(); connA.Close(); } } } } public bool SaveSecond(SqlConnection connection) { using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required)) { tableAdapterA.Connection = connection; tableAdapterB.Connection = connection; tableAdapterC.Connection = connection; tableAdapterA.Update(myDataTableA); tableAdapterB.Update(myDataTableB); tableAdapterC.Update(myDataTableC); scope.Complete(); } }
From: Andrew J. Kelly on 24 Jul 2008 10:13
I was assuming you were calling the INSERT() method 50 times, is that not the case? I was also assuming the TransactionScope scope = new line of code started an Explicit transaction. But what I didn't catch the first time is that you also have this at the end of the method scope.Complete();. I don't know what that does but now I again assume it issues a COMMIT TRAN. If these assumptions are correct each call should begin a tran, Issue the updates and then commit the tran. Normally that would be fine and there should be no issues. But if you issued a Begin tran before you called the first Insert() all of those would be wrapped in one big nested transaction with none of the inner ones actually committing until the outmost one is committed. If you have Implicit Transactions turned on for that connection you might see this behavior. I would run a trace at the statement level to see what is going on. Also if you select @@TRANCOUNT in your Insert() method you can see if the level is where you expect it to be. One other note is that since you appear to be updating thru two connections at the same time you are most likely also wrapped in a distributed transaction as well. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Scott" <Scott(a)discussions.microsoft.com> wrote in message news:3FBFE936-F670-40D8-B08E-43513B9CF5F9(a)microsoft.com... >I don't understand what you mean. What loop are you reffering to? > There is no loop in the code snippet that nests transactions. > scope.Complete() is called followed by exiting the "using" statement for > the > TransactionScope which then exits the method. > The method Insert() is not called in a tight loop either. > > "Andrew J. Kelly" wrote: > >> It appears as if you are starting a new transaction with each loop. Why >> are >> you doing that? That would nest all the transactions and eventually I >> would >> think you would hit a limit although I don't know what that is. >> > |