Allow passing of options/hints with commands

[RoelofSpijker]

Description

It would be desirable to be able to pass options/query hints through various EFE commands to have more control over the queries that are being sent to the backing DB. Specifically, we are running into an issue where we have are making changes to parent and child tables within a transaction with multiple transactions running concurrently. This is causing heavy locking that could be avoided by hinting at MSSQL to use a different type of join in the generated MERGE INTO. I don't see any option to do so unfortunately.

Transaction: Insert into ParentTable Insert many rows into ChildTable (through BulkInsert)

When these transactions run concurrently, the second statement gets locked on attempting to obtain an S lock on a KEY representing an entry into ParentTable originating in another transaction. Presumable it attempts to obtain this S lock to verify the foreign key contraint between the parent and child. In effect this means only 1 transaction can operate at a time. If we take the SQL statements that are generated by EFE (an INSERT on ParentTable, creating a temp table for ChildTable, inserting into that and then a MERGE INTO ChildTable) and give the MERGE INTO a hint to use a loop join, the locking issues disappear.

I've tried working around this with an interceptor, but it appears that this does not intercept anything generated by BulkInsert or the like.

Further technical details

• EF version: 6.4.4
• EF Extensions version: 5.1.15
• Database Provider: MSSQL

[JonathanMagnan]

Hello @RoelofSpijker ,

Could you try again with the following option: option => option.UseTableLock = true. As you understand, it will lock the table so might cause some other errors for some concurrency scenarios if you keep the transaction too long.

Unfortunately, at this moment there is no way to customize those commands than options that we are currently providing.

Best Regards,

Jon

[RoelofSpijker]

Hey @JonathanMagnan,

I'm sure locking the entire table would work. But I'm trying to get fewer locks not more/broader ones. Locking the full table in one of these transaction would basically disable concurrency altogether, while I'm looking to increase concurrency.

I suppose the goal of this issue is more of a feature request to allow the passing of hints. Right now the possibilities here are fairly limited to non-existing and I can imagine quite some scenarios where users may want more control over the generated SQL.

Is there any other workaround you are familiar with that allows me to impact the SQL commands before they are sent to the database? Specifically for the BulkInsert case?

[JonathanMagnan]

Hello @RoelofSpijker,

Thank you for reaching out to ZZZ Projects, due to summer vacation we will have limited email access which may cause some delays from May 26 to June 1. We are sorry for any inconvenience.

Best regards,

Sara

[JonathanMagnan]

Hello @RoelofSpijker ,

I don't think we have the option that will do what exactly you want.

If we add two options that will let you to specify either the query hint/table hint as shown on this page: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16, will it be enough?

Which hints would you exactly use for your BulkInsert case?

[JonathanMagnan]

Hello again,

Since our last conversation, we haven't heard from you.

Can you confirm which hints would you exactly use for your BulkInsert case?

Best regards, Jon

[RoelofSpijker]

Hey @JonathanMagnan, apologies for the delayed response.

I believe that would be a great general addition and it would solve this specific situation as well. In this case we would use the "LOOP JOIN" query hint to resolve the issue described with BulkInsert.

[JonathanMagnan]

Hello @RoelofSpijker ,

The v6.14.1 has been released.

In this version, we added the TableHintSql which allows you to pass the full hint text such as TableHintSql = "WITH (NOLOCK))"

We are currently in discussion to add the option QueryHintSql which will allow you to do your LOOP JOIN

[Groman123]

Are there any plans to implement the QueryHintSql option? We are currently having a pretty similar issue resulting in deadlocks when inserting via parallel BulkInserts each encapsulated in their own transaction.

[JonathanMagnan]

Hello @Groman123 ,

The QueryHint is already supported for SQL Server since the v7.2.11

options.QueryHint = "OPTION (LOOP JOIN)";

Let me know if that works for you.

Best Regards,

Jon

[Groman123]

We had to migrate our application to EF 7 but it worked pretty much, Thank you 👍