0

I have facing issue about "transaction was deadlocked" in one of my WCF webserice method, which is used to updated about 8 tables in Server database. I am using LinqToSQL in my WCF service to perform all database related operation.

And my code is something like below...

//Write code to open connection
foreach (var item in _updateList)
{
      dataTrans = this.Context.Connection.BeginTransaction(
           System.Data.IsolationLevel.ReadUncommitted
       );
      try{
            //Save Changes to database
            this.Context.SubmitChanges();                
            dataTrans.Commit();
       }
       catch (Exception ex)
       {
           //RollBack Datatransaction if any Table has invalid argument
           if (dataTrans != null)
              dataTrans.Rollback();
       }
}

and found below error in my Exception log

Transaction (Process ID 71) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

.Net SqlClient Data Provider
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderBase`1.Read()
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Task.TaskMfssActionDtl.UpdateSqlToDevice(RequestSqlToDevice request)
usr
  • 168,620
  • 35
  • 240
  • 369
Tejas Vaishnav
  • 466
  • 5
  • 20
  • move try..catch code out the foreach loop. – Tim.Tang Aug 07 '14 at 07:14
  • 1
    What is happening around this loop? Do you have other threads / processes that are accessing the same database resources? – Brian O''Byrne Aug 07 '14 at 07:57
  • No, i cant remove try catch from my foreach loop, as if i got exception in any one iteration of loop, i need to continue for other iteration of loop need to be updated. – Tejas Vaishnav Aug 07 '14 at 09:07
  • And Before this loop begins nothing going to happens like threads/processes, just written some code to validate client request and just fetched some short of required data from database. and after the loop is completed i just write code to create PDF file and send that file as an attachment in email. and in inside the try catch block of the loop, i have write code to update my list of tables data to database, and transaction is required to rollback data from all this 8 tables, if any of the table is not get updated successfully, and the loop will continue to update rest of the loop data – Tejas Vaishnav Aug 07 '14 at 09:11
  • Check this response at http://stackoverflow.com/questions/13159326/implementing-retry-logic-for-deadlock-exceptions You can always can try catching this type of SQLException and retry it before throwing an error. – Matias Jul 28 '16 at 14:58

0 Answers0