Why deadlocks occuring when I have @@lock_timeout set to -1 (infinite wait) ???
-
In my batch reporting environment, I'm not too concerned about lengthy blocking waits since performance isn't top priority.
I have lots of SQL reads and lots of writes to temp tables specific to each stored proc. I do have two tables that are shared by my procs:
1. I have a common log table where I post stored proc step-by-step logging to.
2. I have a common print "spool" table so that reports are posted to there by my report procs and then they are spooled out by a custom reader proc in serial fashion and e-mailed to distro lists via SMTP.
I mention the two tables above because if I'm getting deadlocks erros, it's likely because of one or both of these two tables. Haven't turned on SQL trace yet because my question is more of a higher level question regarding infinite wait setting for @@lock_timeout. If I have it set to -1 then I should never get deadlock timeout errors, right?
I've got my report procs kicked off by SQL Agent jobs which run during set times daily. I suspect that my spool reader stored proc, which is kicked off every minute by SQL Agent and which can run concurrently, may be deadlocking my report creation procs or vice versa.
When I look at a failed SQL Agent job log, it's alwasy because of ... "Msg 1205, Sev 13: Transaction (Process ID ###) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
I’ve confirmed that my same SQL Agent logs register the setting by seeing …
[font="Courier New"]Deadlock Timeout
----------------
-1[/font]
... as a result of the following code inserted in my procs:
[font="Courier New"]DECLARE @Timeout int
SELECT @Timeout = @@lock_timeout
SELECT @Timeout as [Deadlock Timeout][/font]
Also, not that this probably matters, In SQL Server Properties (Configure) > Connections tab, my “Allow other SQL Servers to connect remotely to this SQL Server using RPC” query time-out setting is set to 0 (unlimited) … I use linked servers once in a while.
What am I missing? If I have @@lock_timeout set to -1, why am I getting deadlock errors at all?
Many thanks in advance!
Dan
-
Why are you getting deadlock timeout errors? Because when a deadlock occurs, there's always a victim...
There really way to much to explain about deadlocks here... you really need to make a trip to Books Online and read about deadlocks, why they occur, what happens when they occur, and how to avoid them.
I'm thinking you've probably got some code to rewrite...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Blocking will eventually resolve itself. A deadlock, however, will never clear up by itself. That's why SQL Server has a deadlock monitor that, when it finds a deadlock, will roll one of the sessions back.
To see what processes are involved in a deadlock, switch on traceflag 1204 (DBCC TRACEON (1204,-1))
With that flag on, the deadlock graph will be written to the error log when a deadlock happens. The graph will show you what processes were involved in the deadlock, what atatement they were running at the time, and what resources they were deadlocked over.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
OK, based on the two posted responses thus far, some more clarification regarding my question is obviously required …
- I’m not asking about what a deadlock is (been doing this for more than ten years … just because I’m a forum newbie don’t mean I’m a SQL Server DBA newbie).
- I’m not asking how to monitor deadlocks (ditto from above parenthesized note).
- I’m not asking how to resolve deadlocks (ditto from above parenthesized note).
I am perfectly fine with deadlocked sys processes being put on infinite hold and in fact have seen that happen in some other SQL Server environments. Based on my BOL research, my assumption is that by setting @@lock_timeout to -1 and verifying that is set to -1 in the SQL Agent job log, I will see my jobs being put on infinite hold.
So then why am I getting deadlock timeout errors (i.e., aforementioned Msg 1205 errors) when they should be in fact holding infinitely???
If @@lock_timeout = -1 is not working to acheive infinite waiting, then how do I make it so that the deadlock jobs stay on infinite waitand don’t error out?
Dan
-
Let me get this straight, you'd rather see deadlocked processes sittting around indefinately until you come in and kill one or both of them? The problem with this is that the deadlocked processes could cause blocking issues for ather processes, that could cause blocking issues for other processes, etc. You could possiby come into work one morning and have hundreds or even thousands of blocked processes just because of 2 deadlocked processes.
Personally, the fact that SQL Server identifies deadlocked processes and kills one on its own is beneficial.
😎
-
Lynn,
Yup, that is correct, I want my jobs to wait infinitely, hence I set @@lock_timeout to -1 for infinite wait, just like BOL says I should do. If this causes a daisy-chain deadlock wait across hundreds or thousands of jobs, that's perfectly fine with me.
This is just the first step in my process ... I'm just trying to keep my question simple in this context. Later on I will be setting up other steps to monitor blocked SPIDs and text message my PDA phone, etc. So, like any veteran DBA who knows better, I'm tethered to my databases 24/7, 365 days a year ... just like an assimilated Borg drone. As such, I won't be waiting till the next morning to see all my blocked jobs because I'll be taking care of it from home via VPN at 1am ... yes, just when I've about fallen into deep sleep (as if that's even possible for any DBA supporting production databases), that's when I will joyfully get my text message via my PDA phone laying on my nightstand a mere few inches from my head.
(Like I said, been doing this for at least 10 years, so I've already given up all hope for a "normal" life as "normal" humans know it.)
Anyways, I want these jobs to wait infinitely and I will take appropriate action once I know they are being blocked and once I've researched the blocking issues. (BTW, this is all in my development environment, NOT my production environment. So at this point, it's just experimental proof-of-concept stuff.)
If I set @@lock_timeout to -1, why is it not waiting infinitely? Is there another setting somewhere I'm missing out on?
Thanks,
Dan
-
I guess I'd rather get notified a lot earlier about a deadlock, and let SQL Server kill one of the processes so that other work isn't hung up, and then identify and correct the cause of the deadlock. If that meant rewriting code, or changing how processing was done, whetever.
I'd also be concerned about excessive or long term blocking issues as well. If a process is blocked for an hour, that just wouldn't be acceptable if it is preventable.
I've been doing this for 12 years myself, so I know about not having a life outside of work (but I've done well to be sure to have one!). Made some people at a previous company unhappy becasue I would turn my pager off while in church, or would even have it on me while running up and down a soccer field as ref, but oh well, there were others that could be called as I wasn't the only support person.
-
Huh? Turned off your pager? Shame on you, Lynn. Blasphemy!
Didn't you know that resistance is futile? You MUST assimilate.
-
Hey, I had the blessing of my immediate supervisor. He agreed that I was entitled to a life outside of work.
As it was I had my SQL Servers setup so that if the Production server crashed the development server notified me with in a minute. The development server (oddball) was a stable as a rock, the production server lived up to its name: crapgame. Oddball would page me when it could not run a simple query on crapgame. Three minutes later, it page me again if it still couldn't. If I got a third page three minutes later, I knew crapgame was down hard and would call the computer room to check on it.
If jobs failed on crapgame, I was paged and would take care of the problem right then instead of waiting. Most of the jobs were data imports from our legacy system, but we had other production systems that were dependent on the data getting loaded in a timely fashion.
Never had any blocking or deadlock issues. Mostly duplicate data (or crapgame just deciding to reboot for no apparent reason -- turned out to be a problem with the motherboard, but the sys admins kept insisting it was SQL Server) where there shouldn't be which required editting of down load files followed by correcting data in the source system when I got in to work.
-
Daniel Storm (1/17/2008)
OK, based on the two posted responses thus far, some more clarification regarding my question is obviously required …- I’m not asking about what a deadlock is (been doing this for more than ten years … just because I’m a forum newbie don’t mean I’m a SQL Server DBA newbie).
- I’m not asking how to monitor deadlocks (ditto from above parenthesized note).
- I’m not asking how to resolve deadlocks (ditto from above parenthesized note).
I am perfectly fine with deadlocked sys processes being put on infinite hold and in fact have seen that happen in some other SQL Server environments. Based on my BOL research, my assumption is that by setting @@lock_timeout to -1 and verifying that is set to -1 in the SQL Agent job log, I will see my jobs being put on infinite hold.
So then why am I getting deadlock timeout errors (i.e., aforementioned Msg 1205 errors) when they should be in fact holding infinitely???
If @@lock_timeout = -1 is not working to acheive infinite waiting, then how do I make it so that the deadlock jobs stay on infinite waitand don’t error out?
Dan
Blocked resources are not the same as deadlocks and @@lock_timeout has nothing to do with deadlocks. You cannot make deadlocks NOT select a victim.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, thanks. Yes, this makes sense and would certainly explain why my @@lock_timeout=-1 setting has been a no-go for me.
Dan
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply