December 1st, 2003, 12:55 PM
Select and Update transaction?
I need a way to grab a specified number of records from a table and at the same time or (as close to the same time as possible) i need to update each record returned.. I need to avoid returning the same records to two or more clients..
The table i am doing the select on has a field called "in_progress" . I would like to set this field to true once it has been selected so that no other client will have that row returned.
I hope this makes sense. What is the correct way to do this? I am programming in a disconnected environment .. i'm not totally sure how to go about this. I am worried that once the client has the records returned in the time it takes to then UPDATE those returned rows (the in_progress field) the DB may have handed out rows to someone else.
One more thing that may not be clear. The original select is not grabbing specific rows it is just returning a specified @amount .
Thanks for any help!
December 1st, 2003, 01:42 PM
The easiest would be to put a lock on the rows you initialy select. This will prevent other users from selecting those rows. I'm not 100% sure what you are trying to accomplish, but I think the following syntax should give you and idea of what you need:
SELECT * FROM myTable
WITH(UPDLOCK) WHERE id= 1234
UPDATE TABLE myTable SET somecol = 'data' WHERE id = 1234
December 1st, 2003, 06:58 PM
generate a unique code number before doing anything
then update the selected rows with 'in_progress' and the unique number in a column which you will basically disregard everywhere else except the next query
now select the rows with that unique number
no locking required
December 1st, 2003, 10:31 PM
But how do you ensure that the rows are not being selected by another user while the updates are happening for the same rows? The original select looks for a "job_id" and the in_progress as being null.. So the bad case scenario would be someone calls the select, grabs the rows, someone else calls the select and gets the same rows before the original rows (first select by another user) have their in_progress field updated. This is in a disconnected environment..
Also, as i have stated.. the original Select grabs the top rows that have in_progress as null. that is the only criteria for the select.
Messorian - does the lock release when the transaction is complete? Also (i am new to this sorry) will the update change all the rows in the table with the specified id or just those from the original select(remember i am selecting an @amount specified) in the same transaction?
Basically i have a table with 10000's of records. A client makes a request for the first (example)500 rows that meet the criteria, Each client does the same thing. Everytime a client selects the rows those rows must be marked as in_progress before another client makes a request for a set of rows.. If i update all
those with a specified id then all records are marked in_progress but aren't really. It's for a distributed app, the table is a join table. So in the table their are many more rows with a given id then any one select will every get..
Thanks to everyone for your help!
December 2nd, 2003, 05:45 AM
as i have stated, you update before you select, not after
the database will ensure that no other thread selects those rows while they're being updated, and once they've been updated, they won't be selected
December 2nd, 2003, 09:35 AM
Hey You are in Toronto ;')
Ok, as i said i am new to SQL in general. So my question is then how do i update the rows before i really know what they are? It is a join table and i am making a top @amount selection. ie: i am grabbing the first bunch of rows that match the criteria.. I don't want ALL the rows that match the criteria to be updated. Just the batch that each client selects.
December 2nd, 2003, 09:43 AM
yes, i am, and you are in Location: NULL
you'd be best to write a stored proc
i'm sorry, i don't know whether you can limit the number of rows updated using SET ROWCOUNT or not
but basically you would use the same WHERE clause in the UPDATE statement as you would in SELECTing them in the first place in your method
December 2nd, 2003, 10:54 AM
Sometimes Toronto is NULL... Just kidding. Anyway, yes i am using SPROC's.. There has to be a way to do this. I still do not understand how i can do this. Unless i can flag or mark the records that are selected by ROWCOUNT or TOP i will have to loop through the returned rows and update one at a time (since this is the only way to know which records to update).. thereby leaving chance that someone else will grab the same ones while the update is happening.
December 2nd, 2003, 12:15 PM
let's try this again
you update before you select them --
the trick is, the other conditions are what prevents you from choosing all the rows in the table
set inprogress = 'Y'
, uniqueid = somevalue
where inprogress = 'N'
and other conditions
once they've been flagged, then --
and now it's the value which you set in the update that lets you choose the flagged rows
where uniqueid = value
December 2nd, 2003, 01:16 PM
I understand. BUT there are no other conditions. That is the problem. The only way the other conditions would be revealed is through a select. Ie: Select the Top @amount of rows given a an ID and in_progress NULL . NOW i have my set of rows. NOW i can update in_progress by using the unique_id of each of the returned rows. I don't HAVE a unique ID for a what is basically a randomly returned number(number must be specified by client) of rows. Again it is a join table:
When a client wants to grab part (a job) of a campaign (ex: select top 500 rows where campaign_id = 3, AND in_progress = NULL). Now the client can do the job. AND it knows which rows in the campaign it's working on. Until the select is made i do not see how i would update the rows. I do not have "other conditions". The only conditions are a given amount of rows where campaign_id = something and in_progress is NULL . AND the given amount is just a portion of the rows. The only other thing i can see doing is creating a job table and putting the job_id in the join table. But that requires breaking up the campaign into jobs which does not allow the client to specify the number of items they would like to work on..
December 2nd, 2003, 02:19 PM
I think r937 has the right idea for the functionality you need. As far as the "other" condtion, you can do the following assuming you have a pk on your table (you do have a pk right?):
SET inprogress = 'Y',
uniqueid = somevalue
WHERE inprogress = 'N'
And primary_key IN(SELECT TOP 500 primary_key FROM yourTable)
Last edited by messorian; December 2nd, 2003 at 02:25 PM.
December 2nd, 2003, 02:57 PM
Ok cool! Does this statement return the 500 rows as well? Guys, thanks for your time! I appreciate it!