prevent simultaneous update

Question

Post reply

prevent simultaneous update

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
June 7, 2013 at 6:14 am

#278302

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 3

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.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 4

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.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 5

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/61537