Re: Tracking row updates

"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