rice.tx (7/14/2013)
Thank you both for your responses. It seems the answer will involve explicit transaction and explicit locks. I was hoping to avoid that. I know there is a proper time to use explicit transactions, but I have also seen a lot of problems introduced (deadlocks, and performance issues). Similarly with optimizer hints... if I have to resort to them, I suspect there is some other design problem....
The great examples you provided use UPDLOCK. It's not clear to me this would solve the problem because this doesn't prevent other transactions from reading the rows to calculate the next id (which could result in two transactions trying to use the same ID). You may be right, but it seems to me you would need the XLOCK with a PAGLOCK to prevent other transactions from reading the rows until you can insert the desired row. But now it seems I'm really trying too hard.
...
If you want a 100% certainty that there will not be a key violation, you must use an IDENTITY column (but presumably you know that and have some reason why you think you cannot).
While there may be edge cases where the locks don't work, I can say my suggestion to use UPDLOCK was more than theoretical. We've used it quite successfully in a very similar case and from what I can tell from the documentation (and actually trying it with two SSMS sessions) that it does exactly what it was designed to do. And the system that uses this is a relatively high volume one.
I still recommend that since it is possible to combine the SELECT MAX and the INSERT, that you do so in order to avoid latency between queries which might contribute to deadlock.
And the only reason I've seen where a TRANSACTION might cause unwarranted deadlocks is the case where you've got some inefficient query running within the TRANSACTION (in which case try to improve that query), or you're not following the best practice which is to minimize the amount of work being done within the TRANSACTION.
For a somewhat contrived example, I've seen TRANSACTIONs that perform SELECTs within their scope that have nothing to do with the data being updated. Those should be removed from the TRANSACTION to minimize the overall processing time.
Good luck!
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