Locking Select queries on rows

[jkcool]

Hi all,

I have a problem in implementing the following logic

I have a stored procedure which is executed by 5 different nodes.

begin
select iseqno from tinputqueue where istatus = 1
update tinputqueue set istatus = 1 where iseqno in ( select iseqno from tinputqueue where istatus = 1) and update those to 2 as status.

end


When the above SP is executed simultaneosly from 5 nodes only one node should get the seqnos which have status as 1.

if I use a simple begin tran and a commit tran will this work ?

Please help me.

[Motoma]

I think row locking is what you will need to do.