QOD 6/10/2003
-
Not to beat a really, really dead horse, but I think there's another interesting point here.
When I run the following two batches, I DON'T GET A DEADLOCK. I started with the QOD query, and then changed it to work in Northwind. I don't think I made any changes to the type of query being requested. The reason I tried this to begin with is that I didn't see why there *should* be any deadlock to begin with. You can't deadlock yourself, and that seems to be what this question implied to begin with. Am I missing something?
Here's my code:
CREATE PROCEDURE
UpdateProducts @ProductID int
As
Begin
DECLARE @UnitPrice money
BEGIN TRANSACTION
SELECT @UnitPrice =(SELECT UnitPrice
From Products with (HOLDLOCK)
WHERE ProductID = @ProductID)
IF (@UnitPrice) > 0 BEGIN
UPDATE Products SET UnitPrice = UnitPrice - 1
Where ProductID = @ProductID
END
COMMIT TRANSACTION
END
go
exec updateproducts 1
(1 row(s) affected)
-
Really need to grind up this horse....
Greg is correct, UPDATE is not a real option, but how should we know the intent was to say UPDLOCK. Hey, today, John started off as a "her" and two months later became a "he"
What's the business problem you're trying to solve? -
Remove the locking hint was my immediate gut reaction answer to this question. In the SQL books online MS even states;
"It is recommended that table-level locking hints be used to change the default locking behavior only when necessary. Disallowing a locking level can affect concurrency adversely."
How do you know it's absolutley necessary to use a locking hint in this situation? You're reading too much into the question. Do you think, if you had gotten this wrong on an MCSE exam it would have not counted against you if you complained loud enough? Not likely.
Admittedly, UPDLOCK would be a nice choice, BUT IT WASN'T AVAILABLE! Bunch of whiners...
-
It's interesting to me that nobody else seems to have picked up on the fact that the original query will NOT cause a deadlock. You cannot deadlock yourself. Didn't anybody else notice this?
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply