• Register
0 votes
1k views

Problem :

I want to save on the DB the log of a multi threaded processor but I am facing the following error:

new transaction is not allowed because there are other threads running in the session.

In each tread I have following function :

internal bool WriteMyTrace(IResult myresult, string mymessage, byte mytype)
{
SPC_SENDING_TRACE mytrace = new SPC_SENDING_TRACE(mymessage,Parent.currentLine.CD_LINE,mytype,Parent.currentUser.FULLNAME,Parent.guid);
Context.SPC_SENDING_TRACE.AddObject(mytrace);
if (Context.SaveChanges(myresult) == false)
            return false;
        return true;
}

Here Context is different for each of the threads, but there connection with my DB is always in the same way.

Anyone have solution on my problem?

7.5k points

Please log in or register to answer this question.

2 Answers

0 votes

Solution :

I guess you must create the context for each of the transaction and then just dispose it, you can do that in following way:

using(var myctx = new MyContext()) {
    //do your transaction here
}

After the closed bracket your context is disposed.

You should also try to add the .ToList() to the every LINQ query that you have. When you try to iterate over your LINQ result, you must not make any changes until the iteration is finished. Also check if you have something similar to that. Hope my solution helped you this time in resolving your issues.

 

38.6k points
0 votes

Solution:

After much pulling out of hair I discovered that the foreach loops were the culprits. What requires to occur is to call EF however return it into an IList<T> of that target type then loop on the IList<T>.

Example:

IList<Client> clientList = from a in _dbFeed.Client.Include("Auto") select a;
foreach (RivWorks.Model.NegotiationAutos.Client client in clientList)
{
   var companyFeedDetailList = from a in _dbRiv.AutoNegotiationDetails where a.ClientID == client.ClientID select a;
    // ...
}

As you've meanwhile defined, you cannot save from within a foreach that is still drawing from the database through an active reader.

Calling ToList() or ToArray() is fine for small data sets, however at the time you have thousands of rows, you will be consuming a large amount of memory.

It's better to load the rows in chunks.

public static class EntityFrameworkUtil
{
    public static IEnumerable<T> QueryInChunksOf<T>(this IQueryable<T> queryable, int chunkSize)
    {
        return queryable.QueryChunksOfSize(chunkSize).SelectMany(chunk => chunk);
    }

    public static IEnumerable<T[]> QueryChunksOfSize<T>(this IQueryable<T> queryable, int chunkSize)
    {
        int chunkNumber = 0;
        while (true)
        {
            var query = (chunkNumber == 0)
                ? queryable 
                : queryable.Skip(chunkNumber * chunkSize);
            var chunk = query.Take(chunkSize).ToArray();
            if (chunk.Length == 0)
                yield break;
            yield return chunk;
            chunkNumber++;
        }
    }
}

Given the above extension process, you can write your query like this:

foreach (var client in clientList.OrderBy(c => c.Id).QueryInChunksOf(100))
{
    // do stuff
    context.SaveChanges();
}

The queryable object you call this method on should be ordered. This is since Entity Framework just supports IQueryable<T>.Skip(int) on ordered queries, which creates sense at the time you consider that multiple queries for diverse ranges need the ordering to be stable. In case the ordering isn't important to you, only order by primary key as that's likely to have a clustered index.

This version will query the database in batches of 100. Note that SaveChanges() is named for each entity.

In case you want to improve your throughput dramatically, you must call SaveChanges() less frequently. Employ code like this instead:

foreach (var chunk in clientList.OrderBy(c => c.Id).QueryChunksOfSize(100))
{
    foreach (var client in chunk)
    {
        // do stuff
    }
    context.SaveChanges();
}

This results in 100 times less database update calls. Naturally every of those calls takes longer to complete, however you still come out way ahead in the end. Your mileage may vary, however this was worlds faster for me.

And it gets around the exception you were viewing.

EDIT I revisited this question after running SQL Profiler and updated a some things to enhance performance. For anyone who is devoted, here is some sample SQL that displays what is made by the DB.

The first loop doesn't require to skip anything, hence is easier.

SELECT TOP (100)                     -- the chunk size 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
FROM [dbo].[Clients] AS [Extent1]
ORDER BY [Extent1].[Id] ASC

Subsequent calls require to skip previous chunks of results, so introduces usage of row_number:

SELECT TOP (100)                     -- the chunk size
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
FROM (
    SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], row_number()
    OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
    FROM [dbo].[Clients] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 100   -- the number of rows to skip
ORDER BY [Extent1].[Id] ASC

We have present posted an official response to the bug opened on link. The workarounds we recommend are as pursues:

This error is due to Entity Framework making an implicit transaction at the time the SaveChanges() call. The best method to perform around the error is to use a different pattern (for example not saving while in the midst of reading) or by bluntly declaring a transaction. Here are three possible solutions:

// 1: Save after iteration (recommended approach in most cases)
using (var context = new MyContext())
{
    foreach (var person in context.People)
    {
        // Change to person
    }
    context.SaveChanges();
}

// 2: Declare an explicit transaction
using (var transaction = new TransactionScope())
{
    using (var context = new MyContext())
    {
        foreach (var person in context.People)
        {
            // Change to person
            context.SaveChanges();
        }
    }
    transaction.Complete();
}

// 3: Read rows ahead (Dangerous!)
using (var context = new MyContext())
{
    var people = context.People.ToList(); // Note that this forces the database
                                          // to evaluate the query immediately
                                          // and could be very bad for large tables.

    foreach (var person in people)
    {
        // Change to person
        context.SaveChanges();
    }
} 

Actually you cannot save changes within a foreach loop in C# using Entity Framework.

context.SaveChanges() process acts like a commit on a regular database system (RDMS).

Only make all changes (which Entity Framework will cache) and then save all of them right away calling SaveChanges() after the loop (outside of it), like a database commit command.

31.7k points
edited by