Allocate records to users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Allocate records to users

Author

Topic

martin.cooper
Starting Member

2 Posts

Posted - 2011-05-05 : 06:01:48

I'm writing a Stored Procedure for a multiuser system. The purpose of the SP is to allocate an open record to the calling user:

update Customer_Survey set Username=@UserName where Customer_Survey_PKID in
(Select top 1 Customer_Survey_PKID from Customer_Survey
WHERE Username is null and Closed=0 order by timestamp)

Select top 1 * from Customer_Survey where closed=0 and Username=@UserName

This works. BUT is it theoretically possible that 2 people execute the update simultaneously, so that instead of 2 records being updated, the same record is updated twice (once with one users username and just afterwards with the others). Thus for one of the users the select command will return no record, or worse, both users will get the same record ?

I could create a second table, containing a field to store the calling users username and one to store the PKID of the original record. This field would have a unique index on it. Rather than update the original record, I could copy it to the new table. An attempt to write the same record twice would fail, due to the unique index. This would be trappable, so the user who's write attempt failed could try again, until they get a record of their own.
However I don't want to go to all this trouble if it is not required. Suggestions ?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-05-05 : 06:54:59

[code]
;WITH CustomerName
AS
(
SELECT TOP 1 Customer_Survey_PKID, [Username]
-- only one session can acquire the UPDLOCK, any others will wait.
FROM Customer_Survey WITH (UPDLOCK)
WHERE [Username] IS NULL
AND Closed = 0
ORDER BY [timestamp]
)
UPDATE CustomerName
SET [Username] = @UserName
[/code]

martin.cooper
Starting Member

2 Posts

Posted - 2011-05-05 : 07:57:19

Ifor,
Thanks for that. updlock apears to be just what I want.
I've never seen that ;WITH trick either, so I have learned 2 things today

Unfortunately I am limited to SQL 2000, so I don't think I can use the ;WITH syntax in this case.
A quick google of updlock revealed this:(http://www.mssqltips.com/tip.asp?tip=1257)
Not as elegant but hopefully it will do ...
Mart

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources