The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MS SQL Development
|
Select and Update transaction?
Discuss Select and Update transaction? in the MS SQL Development forum on Dev Shed. Select and Update transaction? MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.
|
|
 |
|
|
|
|

Dev Shed Forums Sponsor:
|
|
|

December 1st, 2003, 12:55 PM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 10
Time spent in forums: < 1 sec
Reputation 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
|

December 1st, 2003, 01:42 PM
|
|
Registered User
|
|
Join Date: Jul 2003
Location: NY
Posts: 18
Time spent in forums: 2 h 34 m 18 sec
Reputation 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
|

December 1st, 2003, 06:58 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
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/
|

December 1st, 2003, 10:31 PM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 10
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
Quote: 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
|

December 2nd, 2003, 05:45 AM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
|
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
|

December 2nd, 2003, 09:35 AM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 10
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
Quote: 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.
|

December 2nd, 2003, 09:43 AM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
|
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
|

December 2nd, 2003, 10:54 AM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 10
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
Quote: 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
|

December 2nd, 2003, 12:15 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
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
|

December 2nd, 2003, 01:16 PM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 10
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
Quote: 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..
|

December 2nd, 2003, 02:19 PM
|
|
Registered User
|
|
Join Date: Jul 2003
Location: NY
Posts: 18
Time spent in forums: 2 h 34 m 18 sec
Reputation 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.
|

December 2nd, 2003, 02:57 PM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 10
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
Quote: 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
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|