UpdateAll deadlocks

[Kraviecc]

Hi,

sometimes there is a problem when using UpdateAll method by multiple separate applications/threads (updating different records). Due to the fact that standard behavior is updating rows using PAGELOCK, deadlocks may occur.

Maybe it's worth to add an additional option to specify lock type for the query (ROWLOCK/TABLOCK/PAGLOCK)?

[RudeySH]

Under the hood, UpdateAll uses SqlBulkCopy which uses row locks by default. At least, that's what the documentation says. Perhaps SQL Server is escalating the row locks to page locks in your database?

My fork of EFUtilities allows you to specify SqlBulkCopyOptions. There's an option to use table locks instead of row locks. See https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopyoptions

Does that help resolving your deadlocks?

[Kraviecc]

If I understand correctly how it works, it uses SqlBulkCopy only to create temp table.

The problem is when UPDATE statement is executed (I mean this line: https://github.com/RudeySH/EntityFramework.Utilities/blob/master/EntityFramework.Utilities/EntityFramework.Utilities/SqlQueryProvider.cs#L134).

When updating table we can write query which contains suggestion for the query optimizer to use different type of locks, like: UPDATE table with (ROWLOCK)...

Anyway if I'm wrong, I can use table lock but it will impact performance in my case.

[RudeySH]

You're right, my bad. The SqlBulkCopyOptions will only be used for inserting data into the temporary table. Perhaps you can implement a IDbCommandInterceptor that rewrites the UPDATE statement before it's executed? That would give you control over what query hints (ROWLOCK/TABLOCK/PAGLOCK) will be used.

You can read more about interception here:

• https://www.entityframeworktutorial.net/entityframework6/database-command-interception.aspx
• https://docs.microsoft.com/en-us/ef/ef6/fundamentals/logging-and-interception#interception-building-blocks

Although, I am not 100% certain that EF will be able to intercept the commands sent by EFUtilities.

[Kraviecc]

Thanks for the suggestion. I'll try to implement interceptor and will share my thoughts afterwards.

[RudeySH]

Did you have any luck implementing an interceptor?

[Kraviecc]

I tried it last week but without a success. These operations don't go through "normal" EF's flow so they cannot be intercepted.

[RudeySH]

Support for table hints will be added in v2. I have just published 2.0.0-alpha.1 on NuGet.

This is all subject to change, but right now it works like this:

var options = SqlUpdateAllOptions
{
    TableHints = new[] { "ROWLOCK" },
};

EFBatchOperation
    .For(db, db.Comments)
    .UpdateAll(comments, x => x.ColumnsToUpdate(c => c.Reads), options);