Select and Update transaction?

Hi All,

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!

rw

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:

-- Messorian

[code]
BEGIN TRANSACTION
SELECT * FROM myTable
WITH(UPDLOCK) WHERE id= 1234

UPDATE TABLE myTable SET somecol = 'data' WHERE id = 1234

COMMIT TRANSACTION

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


rudy
http://r937.com/

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.

thanks

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!
rw

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

rudy

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.

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

rudy

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.

Thanks

rw

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

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

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:

Join Table
-------------
campaign_to_name_id
campaign_id
name_id
in_progress
is_done


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..

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?):

Ok cool! Does this statement return the 500 rows as well? Guys, thanks for your time! I appreciate it!

rw