March 8, 2011 at 1:39 am
I've implemented updlock as you suggest in your comment and I encountered duplicates that occur very rarely, and not every time when I run tests with many concurrent clients. I can not explain duplicates and believed it was a sure way to protect the concurrent reads of i_nextUID. But I gave up and tested sp_getapplock, which seems to work flawlessly. There will also be better concurrency if you have a WHERE clause.
SELECT @i_uid = i_nextUID FROM t_table
WHERE rowID=@rowID
Updlock escalates to table lock in my tests and only one client may update t_table at a time and everything is going relatively slowly. Instead I put @rowID as suffixes in the @Resource variable of sp_getapplock and simulate rowlock that does not escalate.
March 8, 2011 at 12:52 pm
@seregak76
Hiya,
I would be very interested to have a little more information such as, SQL server version and SP, number of concurrent connections (also how many uids would be requested simultaneously) and how clients connect and execute the code (e.g., Clasic asp running a Stored proc via ADODB.command object, .net running via adhoc record set, etc).
If you still have it, I would also be interested to see the code that failed (PM if you don't want/can not publish it)
Please don't misunderstand my intentions; I'm not saying you've implemented it badly, nor am I contradicting your statement, but I am concerned for the applications that we have using this code as we've been using two versions of this code for many years (one version very similar to the example that generates the next unique number for the entire DB and the other that generates the next unique number for a user). I would like to know how it can fail so that we can adapt.
It may just be a matter of scale, or perhaps in my testing I never managed to get an escalation to table lock. I will have to go back and rebuild the testing code and see if I can locate the point of failure.
SgtWilko
March 8, 2011 at 4:44 pm
Assuming SQL 2005+
It is theoretically possible that you could into trouble if you use the READ UNCOMMITTED transaction isolation level. You could potentially get dirty reads in the SELECT. That isn't a fault in the implementation of the locks, but rather that you have explicitly set options that do not honour locks properly.
If you are interested in having a unique numeric ID on a record, I would suggest that defining the column with IDENTITY and then using either an OUTPUT on your INSERT statement or SCOPE_IDENTITY to see the values.
As my original article outlined (at least I hope it did!) this technique is more about ensuring that task A and task B are aware of each other and don't interfere with each other. I'm sure there are examples of it in most businesses.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply