How can I use a transactional database to create a critical section?

Say I have 2 servers that talk to the same transactional database.

I want to set it up such that only one of these two servers will perform a given timed action (essentially using the database to enforce synchronization). From what I've heard, something along the lines of this might work:

Say my table TABLE has 2 columns, ID and STATUS. If I set up the code as such:

update TABLE set STATUS = 'processing' where ID = 1234 and STATUS != 'processing'

if (weHaveModifiedAtLeastOneRow)
{
    // do critical section stuff here
    // This is code that we only want one server to run, not both.
开发者_运维技巧
    update TABLE set STATUS = 'free' where ID = 1234
}
else
{
    // We failed to get the lock, so do nothing
}

Will this work, or am I missing some concepts here?

If you want a critical section, use dbms_lock.request. You could get a meaningful lock handle trough allocate unique like this:

DBMS_LOCK.ALLOCATE_UNIQUE ( lockname =>  'MYAPP_' || ID, lockhandle => handle);
success := DBMS_LOCK.REQUEST(lockhandle => handle, timeout => 1);
if (success = 0) then
-- Do stuff
DBMS_LOCK.release(handle);
else
-- we waited a second and didn't got the lock.
end if;

if you prefer it, you could apply a hash and the modulo operator over id, to project the id value space over a known number of locks, accepting a (low)risk of delaying unrelated transactions.