2

I have a C#/.NET application that, in multiple threads (i.e. concurrently, 6 threads), tries to perform following update:

UPDATE cWrh SET
   [Options] = cStg.[Options] -- byte array
FROM
   [wrh].[Cars] cWrh
      INNER JOIN [stg].[Cars] cStg ON
         cWrh.[Id] = cStg.[Id]
         AND cWrh.[Manufacturer_Id] = @ManufactuerId -- each thread gets different id here
         AND cWrh.[Options] <> cStg.[Options]

This is piece of code is run in the transaction. Both tables have 3+mio records. Clustered key is on the [Id] field, there are some non-clustered indexes as well.

Funny thing is that I manually checked and in my particular example with 3+mio records cWrh.[Options] and cStg.[Options] are always the same so in the end the update would not be necessary.

I'm attaching the deadlog graph, redacted values say DB.wrh.Cars: Deadlock graph

Yes, in this particular example concurrency is not really adding any value, but this is the "Reset" query; the "Recalculate" query which does some [Options] calculation in C#, bulk inserts back into SQL and updates later in a concurrent mode speeds up the thing significantly.

I would just like to stick to this concurrent approach regardless of the task (simple reset vs CPU intensive work) if possible.

Any suggestion how to work around the deadlock is appreciated.

mpfx
  • 21
  • 1
  • No idea, but wondering if a `readpast` hint may be appropriate?? Since each thread has a different `@ManufactuerId`, they *shouldn't* trample each other (right?) It may also be worth checking to see if `rowlock` is helpful. Again, I really don't know, so take this with a grain of salt. – David Nov 16 '16 at 18:58
  • the answer to this question might help. It suggests using isolation snapshots. http://stackoverflow.com/questions/27206244/sql-server-prevent-lost-update-and-deadlocks – Fuzzy Nov 16 '16 at 20:25
  • @David you are right, ManufacturerId is always unique per thread. I tried with both readpas as well as rowlock but still get deadlocks – mpfx Nov 17 '16 at 12:22
  • @KamranFarzami: thanks for mentioning isolation snapshots. They seem to be the right way to go. However, I can't use them all the time as some of my transactions (other than the "Reset" one mentioned above) work with temp tables and creting indexes on top of these temp tables; that seems to be a known limitation as you can't do DDL in transaction with snaphot isolation level regardless if it's on local temp tables or DB tables – mpfx Nov 17 '16 at 12:26

1 Answers1

0

As suggested by @KamranFarzami, the answer by @Grantly solved my problem. The point for answering this question goes to them.

Transaction Isolation Level of SNAPSHOT prevents deadlocks from ocurring.

mpfx
  • 21
  • 1