SQL & PL/SQL

Hi,

1)- I have a table that looks like:

MY_TABLE:
id (number)
owner (number)
other columns ....

2)- I have threads concurrently running in more than one server tying to get and process the first row (smalest id) where the owner field is still zero. The logic for each thread looks like:

2.1) - Get a sequence number.

2.2) - Find the first row (smalest id) where the owner column is still zero.

2.3)- Update the "owner" column with the sequence number to prevent this row from being found by any other thread.

2.4)- Process this row (get the other columns and process them).


3)- So, what I need is to "atomically" find the first row (smallest id) and set its owner to a unique number. Will the following 2 statements be "thread safe"? I mean, If I have 5 servers trying to get this first row and they issue the UPDATE statement concurrently, will the SELECT statement return successfully to only one of my treads?

UPDATE MY_TABLE SET owner= :mySequenceValue
WHERE owner=0 and id in (SELECT MIN(id) FROM MY_TABLE WHERE owner= 0)

SELECT (other columns) FROM MY_TABLE
WHERE onwer = mySequenceValue


Thanks in advance for your help.

Edited by: user9345269 on Mar 15, 2010 6:14 PM

Edited by: user9345269 on Mar 15, 2010 6:25 PM