June 7, 2013 at 6:14 am
Hi
I have a table where multiple Agents can assign tasks to themselves. So I basically pull the minimum TaskId which is not assigned using SELECT statement and then RUN an update to assign that task to Agent.
Below is the statement
UPDATE TOP(1) TaskTable
SET TaskStatus = 2
, TaskOwner = @user-id
WHERE
TaskID = (SELECT MIN(TaskID) FROM TaskTable WHERE Assigned=0)
Now there is possibility that 2 agents clicked simultaneously to assign tasks, both pulled same taskID from SELECT but as updated can't be simultaneous, so update of one agent will be overwritten by other.
How to prevent it?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 7, 2013 at 6:33 am
whether is was near simultaneous or five minutes apart, what prevents two people form overwriting the task at all?
does the business /app portion present a list of available task slots or something that have not yet been assigned?
if that's true, then i think adding a rowversion column might be the solution. then the update could test if the same rowversion still exists for the given taskid, and return an error if zero rows got updated.
Lowell
June 7, 2013 at 6:52 am
You might consider something like this too:
DECLARE @TaskID INT
UPDATE TaskTable SET
TaskStatus = 2,
TaskOwner = @user-id,
Assigned = 1, -- don't forget
@TaskID = TaskID -- collect TaskID for client
WHERE TaskID = (SELECT MIN(TaskID) FROM TaskTable WHERE Assigned = 0)
WITH (TABLOCKX) -- exclusive table lock held until end of statement (transaction)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 7, 2013 at 6:55 am
Hi
There is nothing currently at App side which can prevent 2 users fro simultaneous update.
It has to be handled from database side. The SELECT subquery selects the unassigned task and obviously there can be 2 simultaneous selects on same row. I need to put some mechanism that if someone selected one Task, then it is not picked by other agent simultaneously.
May be by locking the row that is being selected??
Lowell (6/7/2013)
whether is was near simultaneous or five minutes apart, what prevents two people form overwriting the task at all?does the business /app portion present a list of available task slots or something that have not yet been assigned?
if that's true, then i think adding a rowversion column might be the solution. then the update could test if the same rowversion still exists for the given taskid, and return an error if zero rows got updated.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 7, 2013 at 6:57 am
But it will put lock on entire table, right? whereas I want to lock only the selected record.
So that noone else is able to select it.
ChrisM@Work (6/7/2013)
You might consider something like this too:
DECLARE @TaskID INT
UPDATE TaskTable SET
TaskStatus = 2,
TaskOwner = @user-id,
Assigned = 1, -- don't forget
@TaskID = TaskID -- collect TaskID for client
WHERE TaskID = (SELECT MIN(TaskID) FROM TaskTable WHERE Assigned = 0)
WITH (TABLOCKX) -- exclusive table lock held until end of statement (transaction)
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 7, 2013 at 7:03 am
I suspect you could use sp_getapplock / sp_releaseapplock here
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy