Workaround: Using the format DELETE FROMįROM #t t with(forceseek) - you can confirm that this use seek and you can use forcescan and compare the EP ![]() The FORCESEEK hint is not allowed for target tables of INSERT, UPDATE, or DELETE statements. Select top 100 ROW_NUMBER() over(order by (select null)) Here is a demo of using DELETE with FORCESEEK create table #t(id int not null, f int)Ĭreate unique clustered index ixuc_id on #t(id) It can be used when we use the format DELEETE FROM Note that, FORCESEEK cannot be used for a table that is the target of an INSERT, UPDATE, or DELETE statement only when it is used with index parameters. This can be used for updates only, deletes and (bulk)inserts does not accept this hint.Īctually, this is not correct and we can use FORCESEEK with DELETE. Therefore, this is a general comment of optional reason (which fits your description). With that said, I did not reproduce the scenario since it based on C# app. This directly leads in many cases to the deadlocks of the key as you have here. Using FORCESCAN means that the server must scan all the index and cannot use index seek. While we try to persuade our client to enable snapshot isolation for us, is there a fix or a good workaround we can use to mitigate this issue that works with updates, deletes and (bulk)inserts as well at the same time? (We obviously want to keep running concurrent transactions, limiting conurrency is not an option) We believe that using read committed snapshot isolation level this blocking shall never happen (same way as it does not happen with snapshot isolation). These scans somehow end up blocking and/or getting blocked by the exclusive locks the modifications place on the rows.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |