Re: Tracking row updates

Qingqing Zhou wrote:

>"Alex Adriaanse" <alex.adriaanse@gmail.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