Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Double update when bulkupdate #64

Closed
LastImmort opened this issue Aug 1, 2018 · 3 comments
Closed

Double update when bulkupdate #64

LastImmort opened this issue Aug 1, 2018 · 3 comments
Labels

Comments

@LastImmort
Copy link

Hello, having trouble when use bulk update inside transaction:

What happening:

  1. Inserting new entities
   await dbContext.BulkInsertAsync(newClasses, new BulkConfig()
            {
                SetOutputIdentity = true,
                WithHoldlock = false,
                SqlBulkCopyOptions = SqlBulkCopyOptions.UseInternalTransaction
            });
  1. Each entry in state Unmodified. Modified each inserted entity
foreach (var newClass in newClasses){

                newClass.TableName = "newString"
            }

  1. Each entry in state Modified. Making bulk update
 await dbContext.BulkUpdateAsync(newClasses, new BulkConfig
            {
                SetOutputIdentity = true,
                WithHoldlock = false
            });
  1. Update passed, db updated, but each entry of dbContext styll in Modified state
  2. Making SaveChange cause ef tracker to update all entities

And one extra question why bulk update can block whole dbset inside of ReadCommited Transaction?

@borisdj
Copy link
Owner

borisdj commented Aug 1, 2018

It's not clear from your example how do you want to use Transaction, and what are you trying to achieve.
You have more info in README:

When used directly each of BulkOps are separate transactions and are automatically committed.
If we need multiple operations in single procedure then explicit transaction should be used, example:

using (var transaction = context.Database.BeginTransaction())
{
    context.BulkInsert(entitiesList);
    context.BulkInsert(subEntitiesList);
    transaction.Commit();
}

Your example does not have explicit transaction, and as such I have tested it, it works successfully.
I have also made test inside explicit transaction, in which case UseInternalTransaction has to be removed (Must not specify SqlBulkCopyOption.UseInternalTransaction and pass an external Transaction at same time.)
This also runs successfully:

[Fact]
private async Task RunCustomTestAsync()
{
    var entities = new List<Item>();
    for (int i = 1; i < EntitiesNumber; i++)
    {
        entities.Add(new Item
        {
            Name = "name " + i,
            Description = "info " + Guid.NewGuid().ToString().Substring(0, 3),
            Quantity = i % 10,
            Price = i / (i % 5 + 1),
            TimeUpdated = DateTime.Now,
        });
    }
    using (var context = new TestContext(ContextUtil.GetOptions()))
    {
        using (var transaction = context.Database.BeginTransaction())
        {
            await context.BulkInsertAsync(entities, new BulkConfig()
            {
                SetOutputIdentity = true,
                WithHoldlock = false
            });

            int counter = 1;
            foreach (var entity in entities)
            {
                if (counter % 2 == 0)
                {
                    entity.Name = "name Update " + counter;
                    entity.TimeUpdated = DateTime.Now;
                }
                counter++;
            }

            await context.BulkUpdateAsync(entities, new BulkConfig
            {
                SetOutputIdentity = true,
                WithHoldlock = false
            });

            transaction.Commit();
        }
    }
}

@LastImmort
Copy link
Author

Yeah, next time i need to write more clear example, so i'll try to explain on your test, if it's still make no sense tomorrow i'll try to make demo app with steps to reproduce. Currently i found that remove entities after bulk update from ef change tracker with setting their state to dettached, works for me.

And for my second question about transaction, more correct case is after open transaction with isolation level read commited, make any bulk insert operation and watch in profiler, sql operation successfully completed and i can access data from other client application(in other transactions) while insert in progress, but if i place debug point after bulk insert in c# code, i can't refresh tables in mssql managment studio from db, cause lock timeout, could it be telated to temp tables or with sql bulk copy?

[Fact]
private async Task RunCustomTestAsync() {
    var entities = new List<Item>();
    for (int i = 1; i < EntitiesNumber; i++) {
        entities.Add(new Item {
            Name = "name " + i,
            Description = "info " + Guid.NewGuid().ToString().Substring(0, 3),
            Quantity = i % 10,
            Price = i / (i % 5 + 1),
            TimeUpdated = DateTime.Now,
        });
    }
    using (var context = new TestContext(ContextUtil.GetOptions())) {
        //transaction izolation level in my case was set to ReadCommited
        //var transaction = context.Database.BeginTransaction(IsolationLevel.ReadCommited)
        using (var transaction = context.Database.BeginTransaction()) {
            await context.BulkInsertAsync(entities, new BulkConfig() {
                SetOutputIdentity = true,
                WithHoldlock = false
            });

            int counter = 1;
            foreach (var entity in entities) {
                if (counter % 2 == 0) {
                    entity.Name = "name Update " + counter;
                    entity.TimeUpdated = DateTime.Now;
                }
                counter++;
            }

            await context.BulkUpdateAsync(entities, new BulkConfig {
                SetOutputIdentity = true,
                WithHoldlock = false
            });

            //DO some extra operations inside transaction without bulk, like:
            //var otherEntity =  context.entity.FirstOrDefault(x=>x.Id == 1);
            //otherEntity.prop = 1;

            //then making save changes to apply our operations to transaction
            //here if we watch sql profiler, entries which was in bulk update updated second time by ef change tracker
            //context.SaveChanges();

            transaction.Commit();
        }
    }
}

@borisdj
Copy link
Owner

borisdj commented Aug 2, 2018

BulkOps are done with SqlBulkCopy that is bypassing EF and its ChangeTracker so it's not useful trying to combine those 2 things. Try to avoid that situation if you can.
For this reason it's best first to close transaction that is doing BulkOps and then in second Transaction do the other regular operations.

Regarding IsolationLevel I haven't use it much so not sure what is the best configuration there.
You can test all combination and see which one works the best.

As for the lock problem read this issue for more info:
Getting Deadlock Issues while doing Bulk insert question
www.github.com/borisdj/EFCore.BulkExtensions/issues/46

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants