Race Status Between Selection and Update

Question

I need to update a row in an Oracle database so that I do not hide changes from another client in my web application.

On my current system, I do the following:

SELECT * FROM table WHERE id=:ID AND lastmodified=:LASTMOD 

if the row still exists with the same last modified date when we started, we know that no one changed it, so we can update it using the last modified time.

However, when you do this manually using two sessions, I noticed that if two clients select at about the same time, you can skip it, that the line has been changed between the selection and update stages due to the fact that they occur in for the same second or millisecond.

The end result is that I am compressing the changes of another user and not warning about its occurrence.

I was thinking about using SELECT FROM UPDATE, but it seems like a bad idea (especially for web applications), the article recommends re-reading (this is what I am doing above), but I still think I'm in danger of a race.

Edit: I realized I was worried about how time refers.

Answer 1

I assume that your UPDATE itself checks the lastmodified value that you read in your SELECT , as ninesided suggests.

If lastmodified is a DATE , then there is a potential race condition if there are several updates of the same row in the same second, since the DATE only has a grain size to the second. If lastmodified is TIMESTAMP , on the other hand, the window in which the race condition may occur is much more limited, since TIMESTAMP will have 3 to 9 digits of second second accuracy (3 on most Windows machines, 6 on most Unix machines). This is rather unlikely, although not impossible, that you will have two updates in one millisecond or even the same microsecond. But it is not faultless.

You can use the generated sequence, not the last modified date. This can ensure that you do not lose the update, since the NOCYCLE sequence will not return the same value twice. But if you go this way, you either lose the information advantage by having the latest update date for each row, or you save a few extra bytes of data in each row of the table. Any of these trade-offs may be worth it depending on your application or may create more problems than it solves.

Answer 2

one approach you could take is that when the user updates the table, they add

 AND lastmodified = :LASTMOD 

in the WHERE the update statement, where :LASTMOD is the value returned by the user's original choice. If the update has now affected the rows ( SQL%ROWCOUNT=0 ), then you know that the second user updated this row, since the first user initially performed their selection.

Answer 3

I'm not sure about the internal SQL solution, but I assume that you are using some kind of scripting language (php? Perl?) To work with the web application / backend, so you can just use them to use file locking or semaphore / mutex to lock the record and wait (if blocked) or just ask the user to wait a minute and try again.

Alternatively, you can look at the source of MediaWiki. I know that they have protection so that two users do not overwrite each other in the database. And honestly, I really think Wikipedia would be a pretty big example of a platform where such parallel editing could happen more than once at a time.

Answer 4

Another option would be to include ROWDEPENDENCIES in the table (which requires rebuilding the table!) And use the ORA_ROWSCN pseudo- ORA_ROWSCN . SCN tracking at the row and block level is 6 bytes per line; however, it is smaller than a DATE or TIMESTAMP column and does not require the creation of additional objects — like a sequence or trigger to make sure this sequence is full.

See Tom Kyte asktom here for details.