Search code examples
transactionsoracle11gsql-updaterowlocking

How can I atomically update a row with a timestamp?

I have a distributed application that uses the database to synchronize clients. Clients will attempt to update a record, but will only do so if no other client has done so in the past 1 hour.

Here's the minified code and the dilemma:

Say one client tries to update a field to "Red" (checking that no one else has updated it within the past hour):

UPDATE mytesttable
SET Status = 'Red',
    TimeOfLastUpdate = sysdate 
WHERE TimeOfLastUpdate < sysdate-1/24

And, at the same time, another client tries to update it to "Green" (checking that no one else has updated it within the past hour):

UPDATE mytesttable
SET Status = 'Green',
    TimeOfLastUpdate = sysdate 
WHERE TimeOfLastUpdate < sysdate-1/24

Can I assume that only one client will succeed in updating the row?

Here's why I think the answer is "no":

Since Oracle must resolve sysdate before it acquires the row-level update lock (since it must use it to find the row in the first place), it appears there is a race condition:

  1. Client "Red" calculates sysdate
  2. Client "Green" calculates sysdate
  3. 1 hour passes
  4. Client "Red" updates TimeOfLastUpdate with old sysdate
  5. Client "Green" updates TimeOfLastUpdate with old sysdate (thus updating twice)

Am I right in identifying this as a race condition? If not, what am I missing?

If so, is there an efficient, more reliable solution to this problem?

Solution

  • The solution that worked for me: double update. For example:

    UPDATE mytesttable
SET TimeOfLastUpdate = TimeOfLastUpdate 
WHERE TimeOfLastUpdate < sysdate-1/24

UPDATE mytesttable
SET Status = 'Red',
    TimeOfLastUpdate = sysdate 
WHERE TimeOfLastUpdate < sysdate-1/24

COMMIT;

    (similar code for 'Green')

    The first update doesn't change anything, but it grabs a lock on the row, which won't be released until commit is called.

    The second update updates the row with a sysdate that is guaranteed to be greater than or equal to the time that the lock was acquired, thus preventing race conditions. (Unless sysdate were to go backwards in time.)