How can I atomically update a row with a timestamp?

Question

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:

Client "Red" calculates sysdate
Client "Green" calculates sysdate
1 hour passes
Client "Red" updates TimeOfLastUpdate with old sysdate
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?

While not exactly your question, I think the accepted answer would be my advice to you as well. Use a sequence if you absolutely must ensure that only one updates it. http://stackoverflow.com/questions/8498169/race-condition-between-select-and-update — Uncle Iroh, Apr 16 '13 at 19:32

score 0 · Answer 1 · answered Apr 17 '13 at 13:53

0

As per my understanding these kind scenarios should be handled at the code level, rather leaving it to backend. Synchronization can be handled easily during programming. Try to implement that way.

answered Apr 17 '13 at 13:53

Madhusudan Joshi

4,438
3
26
42

How would you recommend accomplishing this? I don't see how it would be handled "easily". Keep in mind this is a distributed application, which means there are multiple clients and their clocks are not necessarily synchronized. – Jay Sullivan Apr 17 '13 at 17:14
You are absolutely right that it won't be easy to handle in such a distributed application environment. Hers's what i am thinking, leaving this synchronization of updating to data base level won't be much effective than programming level. There should be some intermediate interface which subscribed to different clients and do the synchronization. – Madhusudan Joshi Apr 18 '13 at 06:35
It sounds like you're suggesting I create a web service that all clients point to. Maintaining a web server just for this introduces a lot of future maintenance. – Jay Sullivan Apr 19 '13 at 13:17

Jay Sullivan · Accepted Answer · 2013-04-30T13:07:44.120

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.)

How can I atomically update a row with a timestamp?

2 Answers2