kramaswamy (11/19/2012)
A few questions:1) What's the difference between using an UPDLOCK and using a higher-level lock? Should I also be using ROWLOCK and XLOCK, or is that overkill?
2) How long will the SELECT statement wait for the lock to be released? Is there a way I can set a predefined timeout, so that the lock doesn't get held forever?
3) What happens if the query is interrupted from the client-side application before the lock is returned? Does the server simply kill the query off, or will it continue waiting for the lock to be acquired?
4) The documentation on Books Online for UPDLOCK specifies that the lock is acquired and released only when the transaction is complete. Is the "transaction" in my case the entire BEGIN TRAN-END TRAN block, or is it referring only to the SELECT statement?
This is actually all pretty cool information to be getting, since I'm studying for the 70-433 exam, and one of my weaker areas was transactions, so I'm killing two birds with one stone 😛
Thanks for all the help btw, and for the kudos!
The return product is probably OK.
For the withdraw product, you can probably do it the way you're doing it but you're introducing unnecessary latency by all the separate SQL statements. Do it as a single SQL statement (similar to my suggestion) by counting active withdrawals against product quantity in a CTE and doing the 1 (or 0) record insert based on it. This reduces the overall time the transaction needs to be active.
As to your questions:
1. Read BOL on UPDLOCK and the others. I think it should be sufficient for your case. I've used it in similar cases and it works fine.
2. The UPDLOCK should occur on the Products table for a very short period of time if you use the approach I've suggested. There should be no need to specify a timeout and in any event I don't think you can (for the transaction) I believe there's a global setting which I wouldn't want to touch.
3. I don't know how the client app could interrupt the transaction. Once it executes the SP, the SP is off and running.
4. The entire BEGIN TRAN/END TRAN block.
Once again, I suggest you consider putting TRY/CATCH blocks around the INSERT and UPDATE statements to make the code bulletproof in case of deadlocks.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St