Re: Tracking row updates

Qingqing Zhou wrote:

>"Alex Adriaanse" <alex(dot)adriaanse(at)gmail(dot)com> writes
>
>
>>This seems to work, except there exists a race condition. Consider the
>>following series of events (in chronological order):
>>
>> 1. Initially, in the codes table there's a row with id=1, revision=1,
>> and a row with id=2, revision=2
>> 2. Client A begins a transaction, and updates the row with id=1,
>> resulting in revision=3 for that row
>> 3. Client B begins a transaction, and updates the row with id=2,
>> resulting in revision=4 for that row
>> 4. Client B commits the transaction
>> 5. Client C (which has $lastrevision=2 in its local database)
>> synchronizes with the database by doing SELECT * FROM codes WHERE
>> revision > 2; and retrieves client B's update to the row with
>> id=2, revision=4 (it doesn't yet see the update from client A)
>> 6. Client A commits the transaction
>> 7. Some time later, Client C synchronizes with the database again.
>> $lastrevision for its database is now 4, so doing SELECT * FROM
>> codes WHERE revision > 4; does not retrieve any rows. So client C
>> never sees client A's update to the row with id=1
>>
>>Essentially, the race condition occurs when the order of clients
>>committing transactions (i.e. the updates becoming visible to other
>>clients) differs from the order of clients generating sequence values.
>>Do you guys have any suggestions on how to avoid this race condition, or
>>maybe a more elegant way to synchronize the clients with the server?
>>
>>
>
>In my understanding, you are doing something like a CVS does. Say if you
>don't "check out" a file and you make a revision on the version you now
>see(say version 1), then when you want to commit, you will probabaly receive
>a "merge required" notice. Since in this interval, the file may have already
>updated by another user (to version 2) - he is free to do so since nobody
>knows that you might commit an update. To avoid this, you have to "check
>out" the file, i.e., lock the file to prevent other changes, then you are
>free of any merge requirement. The cost is that you locked the file and
>nobody could change it. So the only options are "merge" or "lock".
>
>Regards,
>Qingqing
>
Applying this analogy to our database, wouldn't that require a
table-level lock during a CVS-like commit (which would mean locking the
table, getting the revision number, updating the row(s), and committing
the transaction)?

Alex