Question

I got the follow OnUpgrade implementation:

    private override OnUpgrade()
    {
        IQueryable<Person> persons = from pers in Query.All<Person>()
                                     select pers;
        int count = 0;
        foreach (Person person in persons)
        {
            count++;
            // Z_User was renamed to Z_Role
            if (person.Z_Role == "1")
            {
                person.Z_Role = PersonRoleEnum.User.ToString();
            }
            else
            {
                person.Z_Role = PersonRoleEnum.Undefined.ToString();
            }
        }
    }

The SQL server does raise the follow exception:

"This operation conflicts with another pending operation on this transaction. The operation failed."

The exception is raised when the count = 251;

I know that you'll send the data to the sql with batches. And it seems that it is limited to 251 items.

Hope you can help me

Regards Marco Leonardi

Accepted Answer

0

By default, Session has EntityChangeRegistrySize property set to 250 items (see SessionConfiguration class). This means that as soon as the number of added/modified entities reaches this level, Session tries persisting modified entities to the database.

Let's try the following settings in your app.config file:

<domain...>
    <sessions>
      <session name="System" entityChangeRegistrySize="XXX"/>
    </sessions>
</domain>

where XXX is a number greater then 250. Note that we are configuring "System" session, because it is used in upgrade procedure.

Another option is to fetch all entites before modifying them. This can be achieved by calling ToList() on persons.

answered Dec 28 '10 at 06:21

Dmitri Maximov
2211●1●2●11

edited Dec 28 '10 at 06:35

This would mean that I have to set the 'entityChangeRegistrySize' to maximum ? What are the side effect of them? Why is the default set to 250?

Yes, the option to fetch all data is an alternative. This means that on each Upgrade I have to fetch 'always' all data.

(Dec 28 '10 at 07:54) MarcoLeonardi

Yes, the default of 250 would make sense for speed reasons

(Dec 28 '10 at 07:59) MarcoLeonardi

But, I think this is a bug of the OnUpgrade operation. Anyway the fetch (.ToList()) has solved the problem. thanks

(Dec 28 '10 at 08:10) MarcoLeonardi

Exactly. In this code snippet you are fetching all Person data anyway. The alternative proposal is to fetch all required entities firstly and modify them only after that.

The value of 250 is for speed reasons.

BTW, the source of the error is somewhere inside MS SQL Server. AFAIK, in some circumstances it might fail in case when there are DDL & DML operations mixed within 1 transaction. The issue first was found in SQL Server 2005, but then reported to be fixed. Although, people claim that the problem still exist even in SQL Server 2008 R2.

(Dec 28 '10 at 08:12) Dmitri Maximov

Subscription:

Related questions