#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    10
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    NY
    Posts
    18
    Rep Power
    0
    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
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    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/
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    10
    Rep Power
    0
    Originally posted by r937
    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
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    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
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    10
    Rep Power
    0
    Originally posted by r937
    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.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    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
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    10
    Rep Power
    0
    Originally posted by r937
    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
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    let's try this again

    you update before you select them --
    Code:
    update yourtable
       set inprogress = 'Y'
         , uniqueid = somevalue
     where inprogress = 'N'
       and other conditions
    the trick is, the other conditions are what prevents you from choosing all the rows in the table

    once they've been flagged, then --
    Code:
    select *
      from yourtable
     where uniqueid = value
    and now it's the value which you set in the update that lets you choose the flagged rows
  18. #10
  19. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    10
    Rep Power
    0
    Originally posted by r937
    let's try this again

    you update before you select them --
    Code:
    update yourtable
       set inprogress = 'Y'
         , uniqueid = somevalue
     where inprogress = 'N'
       and other conditions
    the trick is, the other conditions are what prevents you from choosing all the rows in the table

    once they've been flagged, then --
    Code:
    select *
      from yourtable
     where uniqueid = value
    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..
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    NY
    Posts
    18
    Rep Power
    0
    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?):

    Code:
    UPDATE yourtable
       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.
  22. #12
  23. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    10
    Rep Power
    0
    Originally posted by messorian
    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?):

    Code:
    UPDATE yourtable
       SET inprogress = 'Y', 
    uniqueid = somevalue
     WHERE inprogress = 'N'
       And primary_key IN(SELECT TOP 500 primary_key FROM yourTable)
    Ok cool! Does this statement return the 500 rows as well? Guys, thanks for your time! I appreciate it!

    rw

IMN logo majestic logo threadwatch logo seochat tools logo