Qingqing Zhou wrote:
"Alex Adriaanse" <[EMAIL PROTECTED]> writesApplying 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)?
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
Alex
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
