Click here to Skip to main content
14,743,899 members
Please Sign up or sign in to vote.
4.90/5 (3 votes)
Hi All

I am getting the above exception in production environment. I've googled the exception, and the solution in most cases is to use MARS. But after adding the MARS in the connection string, I got the following exception:

System.Data.SqlClient.SqlException: The transaction operation cannot be performed because there are pending requests working on this transaction.


But currently I'm not looking for the solution, rather if any of you can help me replicate this issue. This is not getting replicated in any environment neither in house, nor at UAT. came suddenly out of nowhere. Until yesterday it was running perfectly.

The above exceptions are thrown when I commit the transaction.

public string Subscribe(DateTime date)
{
    ABCDataContext dataContext = new ABCDataContext();
    return Process(RAKBankDataAccess.GetRegistration(dataContext, date), dataContext);
}

private string Process(IQueryable<DeliveryRegistration> registrations,
                       ABCDataContext dataContext)
{
        System.Data.Common.DbTransaction trans = null;
        if (dataContext.Connection.State != System.Data.ConnectionState.Open)
            dataContext.Connection.Open();
        trans = dataContext.Connection.BeginTransaction();
        dataContext.Transaction = trans;
        dataContext.CommandTimeout = 0;
        foreach (DeliveryRegistration dr in registrations)
        {
            //perform some modifications
            //Add some new rows

            transactionCount++;
           if (transactionCount == 250)
            {
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
                if (dataContext.Connection.State!=
                    System.Data.ConnectionState.Open)
                    dataContext.Connection.Open();
                trans = dataContext.Connection.BeginTransaction();
                dataContext.Transaction = trans;

                transactionCount = 1;
            }

        }

        dataContext.SubmitChanges();
        dataContext.Transaction.Commit();

        return "";
}


DB server is Sql Server 2005
Thanks
Posted

Found the culprit; Lazy loading. Solved it with calling .ToList() method before iteration over the query results

Thanks all for your time :)
   
Comments
Sandeep Mewara 23-Jun-12 9:30am
   
Good to know. :thumbsup:
Code related to DataReader is not visible directly, but looks like being used in Data access layer. Somehow your datareader is still in use before you try to re-use in your current scenario. Sharing views on why today and not before would be difficult without seeing the exact code. You can try to handle it using proper code needed for datareader opening and closure.

Have a full detail on the same here: MSDN Blog: "There is already an open DataReader associated with this Command which must be closed first" explained[^]
   
Comments
Phan7om 22-Jun-12 3:01am
   
Hi Mewara,

I've checked this link before. But I'm not using DataReader anywhere in the whole project and only LINQ is used.

thanks for your time :)
Sandeep Mewara 22-Jun-12 3:23am
   
That's what I said, its not clear from the code above but based on error it is being used internally. Look for how 'ABCDataContext' internally works.
In the below code the problem is:

public string Subscribe(DateTime date)
{
ABCDataContext dataContext = new ABCDataContext();
return Process(RAKBankDataAccess.GetRegistration(dataContext, date), dataContext);
}

private string Process(IQueryable<deliveryregistration> registrations,
ABCDataContext dataContext)
{
System.Data.Common.DbTransaction trans = null;
if (dataContext.Connection.State != System.Data.ConnectionState.Open)
dataContext.Connection.Open();
trans = dataContext.Connection.BeginTransaction();
dataContext.Transaction = trans;
dataContext.CommandTimeout = 0;
foreach (DeliveryRegistration dr in registrations)
{
//perform some modifications
//Add some new rows

transactionCount++;
if (transactionCount == 250)
{
dataContext.SubmitChanges();
dataContext.Transaction.Commit();
trans = dataContext.Connection.BeginTransaction();
dataContext.Transaction = trans;

transactionCount = 1;
}

}
if(transactionCount % 250 != 0)
zz
return "";
}

Use the below code:

foreach (DeliveryRegistration dr in registrations)
       {
           //perform some modifications
           //Add some new rows

           transactionCount++;
          if (transactionCount == 250)
           {
               dataContext.SubmitChanges();
               dataContext.Transaction.Commit();
               if (dataContext.Connection.State!=
                   System.Data.ConnectionState.Open)
                   dataContext.Connection.Open();
               trans = dataContext.Connection.BeginTransaction();
               dataContext.Transaction = trans;

               transactionCount = 1;
           }

       }
       if(transactionCount % 250 != 0)
{
       dataContext.SubmitChanges();
       dataContext.Trans%action.Commit();
}

       return "";
   
Comments
Phan7om 23-Jun-12 6:17am
   
and how does it solves my problem ?
Somewhere used used sqldatareader n its not yet yet been closed.. You given an inappropriate code. just search SqlDataReader in your code then close it before executing any command..
   
Comments
Phan7om 23-Jun-12 6:17am
   
just for your info I've searched in the whole solutions and found only single instance of datareader which is closed. why can't people see LINQ tag, I'm using LINQ

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900