What's a good scheme for multi-user database synchronization?
- by Mason Wheeler
I'm working on a system to allow multiple users to collaborate on an online project. Everything is fairly straightforward, except for keeping the users in sync. Each user has their own local copy of the project database, which allows them to make changes and test things out, and then send the updates to the central server. But this runs into the classic synchronization question: how do you keep two users from editing the same thing and stomping each other's work?
I've got an idea that should work, but I wonder if there's a simpler way to do it. Here's the basic concept:
All project data is stored in a relational database.
Each row in the database has an owner. If the current user is not the owner, he can read but not write that row. (This is enforced client-side.)
The user can send a request to the server to take ownership of a row, which will be granted if the server's copy says that the current owner is NULL, or to release ownership when they're done with it.
It is not possible to release ownership without committing changes to the server.
It is not possible to commit changes to the server without having first downloaded all outstanding changes to the server.
When any changes are made to rows you own, a trigger marks that row as Dirty. When you commit changes, the database is scanned for all Dirty rows in all tables, and the data is serialized into an update file, which is posted to the server, and all rows are marked Clean. The server applies the updates on its end, and keeps the file around. When other users download changes, the server sends them the update files that they haven't already received.
So, essentially this is a reinvention of version control on a relational database. (Sort of.) As long as taking ownership and applying updates to the server are guaranteed atomic changes, and the server verifies that some smart-aleck user didn't edit their local database so they could send an update for a row they don't have ownership of, it should be guaranteed to be correct, and with no need to worry about merges and merge conflicts. (I think.)
Can anyone think of any problems with this scheme, or ways to do it better? (And no, "build [insert VCS here] into your project" is not what I'm looking for. I've thought of that already. VCSs work well with text, and not so well with other file formats, such as relational databases.)
