dwain.c (7/12/2013)
Steven - BTW.If you do it the way you suggested, which is to break it into multiple SQL statements, you should consider 2 things:
1. Wrap the SQL statements in a TRANSACTION.
2. Add the UPDLOCK hint on the SELECT MAX (as I did in my example)
Then in your 2 windows test case, you don't need to introduce the WAIT. You can simply execute the first few statements just prior to the INSERT, switch windows and execute the other SQL (which will hold until you go back to the prior window and execute the final statements up to COMMIT TRANSACTION.
UPDLOCK holds the locked table until the transaction completes. In my case, since it wasn't in a transaction the lock is freed once the single statement completes.
Yeah, in an actual case I'd have added a TRAN block with UPDLOCK and I did that at first. But I wanted instant gratification watching the WINDOW 2 rows get inserted then flip back to WINDOW 1 and see the first row get updated after the delay. I know that it was a contrived simulation.
If this is representing a high-volume situation I'm not sure what effect the UPDLOCK might have on concurrency. And I'm not sure my method would handle more than two simultaneous insert attempts or what would happen if a duplicate PK still somehow caused an error. I had in an early version a TRY/CATCH block that would keep trying until a valid PK sessionID could be inserted, but it was late and I was really guessing at what the OPs actual requirement was. Seems to me that some sort of additional identity column would be a much easier and more reliable fix.