July 25, 2008 at 1:27 pm
"Transaction (Process ID 66) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
DECLARE @res INT
BEGIN TRANSACTION
EXEC @res = sp_getapplock
@Resource = 'This a Lock ID',
@LockMode = 'Exclusive',
@LockOwner = 'Transaction',
@LockTimeout = 200000,
@DbPrincipal = 'redacted'
...
EXEC @res = sp_releaseapplock
@Resource = 'This a Lock ID',
@DbPrincipal = 'redacted',
@LockOwner = 'Transaction'
COMMIT TRANSACTION
I have placed this code around the contents of a stored procedure, and I'm still getting the above error message.
I am running this query through sqlcmd in new windows using start /min to attempt to test concurrency.
I do have a complex query inside, and was trying to use locks, but was getting deadlock errors. So I decided to start from a known case - everyone is locked out via an application lock, perhaps modifying it later when more is known.
1) Does this application lock 'application' look correct?
2) If so, can you think of *anything* I can do inside (that works when only 1 instance of this proc executes) that would fail when 2 of these puppies run at the same time? My presumption was that this lock around all of the rest of the code (I don't think begin transaction and set nocount.. count.. do they?) would basically serialize any multiple requests.
I was shocked to find that SqlServer's lock escalation could cause deadlock where otherwise there wouldn't be. Is there something like that going on here?
Ger.
July 26, 2008 at 10:13 am
To answer any of that I'd need to know what it's deadlocking with. To find that you, you can run profiler and capture the deadlock graph event, or you can enable one of the traceflags 1204 or 1222 (DBCC TRACEON(1222,-1))
The traceflag will write the deadlock graph into the error log
With the deadlock graph, we can see what resource it's deadlocking on and with what its deadlocking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2008 at 11:39 am
You can also use Profiler to get a Deadlock Graph.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 26, 2008 at 1:19 pm
Gerard ONeill (7/25/2008)
2) If so, can you think of *anything* I can do inside (that works when only 1 instance of this proc executes) that would fail when 2 of these puppies run at the same time?
You see to be assuming that this procedure is deadlocking on another copy of itself. that is not necessarily the case, in fact it is not normally the case. As Gail said, first step is to find out what the other proc(s) is(are) involved in the deadlock.
My presumption was that this lock around all of the rest of the code (I don't think begin transaction and set nocount.. count.. do they?) would basically serialize any multiple requests.
Yes, it will serialize any requests that come through this procedure, but there are normally a lot of other requests on an active server. If your deadlocking is solely from this proc deadlocking on another instance of itself, then this techniuqe is a very poorly performing way to try to address it. Really, it should only be used as an absolute last resort.
I was shocked to find that SqlServer's lock escalation could cause deadlock where otherwise there wouldn't be.
Why would you assume that it is lock escalation?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 26, 2008 at 1:42 pm
Oh yeah, you don't have HT (hyperthreading) turned on do you?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 28, 2008 at 6:52 am
If you're trying to control locking through the procedure, it certainly could be the proc locking itself. Rather than try to put more and more locks on, I'd suggest putting less and less. None in fact. By and large SQL Server manages locking very well all its own.
Definitely follow Gail's or Jack's advice and capture a complete set of deadlock information.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 28, 2008 at 8:53 am
Grant Fritchey (7/28/2008)
If you're trying to control locking through the procedure, it certainly could be the proc locking itself.
Although it is certainly possible for a procedure to block another instance of itself, it is uncommon for a deadlocking procedure to be deadlocking soley on other instances of itself, probably even less so if it is using explicit locking.
A deadlock can only occur when at least two different processes try to acquire at least two different locks in both processes, but in a different order. Because procedures usually (but not always) do things in the same order every time, they usually (but not always) will not deadlock on themselves. Implicit locking means that the order is not always certain from the code, however explicit locking is determined soley by the code.
I definitely agree with everything else that you are saying though, Grant.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 28, 2008 at 10:38 am
It'll take me a while to dig it out of the archives, but we found a situation a few years ago that showed it wasn't all that hard for a single procedure to lock on itself...
Ah, got it.
The situation was actually quite simple. We had a query, written by an inexperienced developer, that did a series of reads against a few tables and then, after doing the reads, did updates based on the same criteria as the reads. This was causing the procedure to deadlock on itself, by itself. The fix was easy. We eliminated the initial read and just did the update, as it should have been done. It mainly occurred early in the use of the app when the table's selectivity and distribution was resulting in more pages being locked than were really necessary.
It is uncommon. It's just not that hard to do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 28, 2008 at 11:45 am
Hmmm, you're right, revisiting and upgrading a lock in a series of already locked resources will open a deadlocking window. Technically, relocking resources that were already locked before the current resource counts as "accessing resources in different orders" though that would not be evident from my previous post. Thanks, Grant.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply