Versioning a dataset in an RDBMS using initials and deltas (xpost from SO) I'm working on a system that mirrors remote datasets using initials and deltas. When an initial comes in, it mass deletes anything preexisting and mass inserts the fresh data. When a delta comes in, the system does a bunch of work to translate it into updates, inserts, and deletes. Initials and deltas are processed inside long transactions to maintain data integrity.

Unfortunately the current solution isn't scaling very well. The transactions are so large and long running that our RDBMS bogs down with various contention problems. Also, there isn't a good audit trail for how the deltas are applied, making it difficult to troubleshoot issues causing the local and remote versions of the dataset to get out of sync.

One idea is to not run the initials and deltas in transactions at all, and instead to attach a version number to each record indicating which delta or initial it came from. Once an initial or delta is successfully loaded, the application can be alerted that a new version of the dataset is available.

This just leaves the issue of how exactly to compose a view of a dataset up to a given version from the initial and deltas. (Apple's TimeMachine does something similar, using hard links on the file system to create "view" of a certain point in time.)

Does anyone have experience solving this kind of problem or implementing this particular solution?

Thanks!
If the "bunch of work to translate it" is occurring during the transaction, then that is adding to the transaction time.  If you can do bulk processing on the data to translate it before you start dong inserts/updates/deletes that would at least decrease the transaction time providing fewer opportunities for contention.

An alternative would be to maintain a copy of the table and make your changes on the copy and then during a brief outage window swap the table names.  The downside is the added storage and the outage window, but the advantage is zero contention.

Update:

I see a couple difficulties with your solution.  Inserts would still cause contention if there were unique indexes on the table.  Updates would become inserts, so those wouldn't be any more difficult.  Deletes would have to be handled somehow.  You could insert a blank record with the delta number to indicate that the record has been deleted.  I'm concerned that the view necessary to pull this data together for querying would be too slow.

Perhaps someone with MySQL knowledge could suggest something.