0

I have below 2 procs that runs weekly @ 3am via SQL agent job. So both procs start executing at the same time.

The proc "dbo.Proc1" fails in a few seconds: "Transaction (Process ID 46) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." However proc "dbo.Proc2" runs successfully.

Can anyone please help me solve this deadlock issue? Thanks.

ALTER PROCEDURE dbo.Proc1
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    /* SELECT statements here that take ~20 minutes to return data */
    /* The base table here is dbo.Table1, which is a huge table */
END

ALTER PROCEDURE dbo.Proc2
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    /* SELECT statements here that take ~20 minutes to return data */
    /* The base table here is dbo.Table1, which is a huge table */
END

I prefer dirty read here because it's kind of summary report and don't need to be up to date. When these run @ 3am, nothing else is happening - i.e. Nobody accessing the same db...It's just internal app.

I'm confused why it causes deadlock even if it's dirty read!

DataPy
  • 209
  • 2
  • 10
  • Surely this depends entirely on what the missing statements are? I assume one of those is locking the table. I admit that I'm confused: (a) Why that islation level? Dirty/phantom reads are acceptable? (b) why you would run two massive select statements at 3am - are you sure there are no updates/inserts happening? I think this applies to you: http://stackoverflow.com/a/21158/1220971 – Bridge Sep 01 '16 at 16:24
  • The error message specifically states, `Rerun the transaction.`. Find a way to do that. `try/catch` might be part of the solution. – Dan Bracuk Sep 01 '16 at 16:28
  • @DanBracuk that wont help unless he puts a ~20 minute pause before the retry. Assuming Proc2 is causing the lock. – mxmissile Sep 01 '16 at 16:31
  • Generally, your risk of deadlocks will decrease tremendously as you improve the execution time. If your statements finish sooner, they will release their locks sooner, hence less time for concurrent processing to overlap. – Brandon Sep 01 '16 at 16:36
  • But we'd really need to see the details of the actual code, the schema, including indexes, and deadlock graphs. – Brandon Sep 01 '16 at 16:37
  • Thanks friends for such a quick and valuable replies. I modified the original thread to provide some more info, please check. Thanks – DataPy Sep 01 '16 at 16:37
  • By the way, if you really want to troubleshoot deadlocks, you need to see who the *other* process is. – Brandon Sep 01 '16 at 16:38
  • Hi Brandon, sure I optimized the procs from an hour to 20 minutes! – DataPy Sep 01 '16 at 16:39
  • 1
    Did you already try the "WITH (nolock)" hint in the SQL stmt? And did you try to execute the second procedure a Little later? e.g. one procedure at 03:00AM and the other one at 03:01AM? – Tyron78 Sep 02 '16 at 06:54
  • Hi Tyron78, I already am using 'READ UNCOMMITED' transaction isolation level. No I didn't try 3am & 3:01am. Do you think it'll make difference? Also my main concern is why deadlock even though it's dirty read?! -Thanks. – DataPy Sep 02 '16 at 13:46
  • Finally got it. Always feels good to know the cause! This link helped to find and fix it: https://support.microsoft.com/en-us/kb/2699720 – DataPy Sep 14 '16 at 14:18

0 Answers0