Easy urgent 500 pts question select+update

Hi to all...

I would like to select a row from a table and apdate a LockID field in the same row....

set @PartnerID=0
select @PartnerID=PartnerID from tblPartners where PartnerState=1
if @PartnerID<>0
begin
    update tblPartners set LockID=1 where PartnerID=@PartnerID
end

my main problem is, that another sp may change the PartnerState of the row AFTER the select and BEFORE the update...

is there any way to make an update and the select TOGETHER???


cosieAsked:
Who is Participating?
 
Jan FranekConnect With a Mentor Commented:
You probably need to lock table tblPartners to ensure, that there's no update from another transaction between your select and update. You can do it by seeting proper transaction isolation level (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
set @PartnerID=0
select @PartnerID=PartnerID from tblPartners where PartnerState=1
if @PartnerID<>0
begin
    update tblPartners set LockID=1 where PartnerID=@PartnerID
end
COMMIT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

This way nobody can change PartnerState of your PartnerId until your transaction commits.
0
 
softplusCommented:
UPDATE tblPartners SET LockId=1 WHERE PartnerState=1 AND PartnerId <> 0
should do the trick :) (possibly add "AND LockId <> 1" to the end, if you want to make sure it's not locked yet)

0
 
nmcdermaidCommented:
BOL says you can use a cursor with 'FOR UPDATE'. This may be what you're after. (I've never done it before though)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
nouloukCommented:
Something like this:
update tblPartners set LockID=1 where PartnerID IN (select PartnerID from tblPartners where PartnerState=1 AND PartnerID<>0)
0
 
cosieAuthor Commented:
softplus: :) I need some data into local variables from the updated row, eg. I need @PartnerID to filled ot to know lated in the sp if the update was succesfull or not...
0
 
cosieAuthor Commented:
hi noulouk,

this seems to wit my needs, the question is:
Does the sql server allow other sp-s to modify the row between the select and the update???
I'll test it soon...

thx
0
 
cosieAuthor Commented:
Hi Franek,
However this locks the whole table, not only the row beeing updated , my table is very short so this solution fits my needs, excellent!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.