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?



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.


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.

  • Thanks for your reply, Leigh. Your first idea definitely applies, but I don't think it will provide the kind of improvement I'm hoping for. The size and length of the transactions seems to be the bottleneck, and not the extra time spent in transaction processing data. The second idea would still cause contention over the copy of the table when trying to load multiple deltas concurrently. What do you think of the solution from my original question? – dacc May 3 '11 at 1:31
  • Updated answer. – Leigh Riffel May 3 '11 at 14:13
  • Yeah, I was thinking of a "NULL" record to indicate deletion. Contention over the index might definitely still exist, but given everything will be outside of a transaction it seems like the locking behavior would improve. – dacc May 3 '11 at 17:02
  • Oh, and regarding the view, it wouldn't have to be a "view" in the strict MySQL sense. It could be a background process actively curating a sort of materialized view in an event driven manner, or simply queries that look at the revision number on each record. – dacc May 3 '11 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.