Currently when moving/new linking assets, the links are updated in the database, then a hipo job is fired to update lookups etc.
The hipo part of the process is wrapped up in a database transaction, but the first part isn't. This means that if the hipo failes for whatever reason, the database is left in an inconsistent state, and can cause a multitude of errors.
One of our users recently managed to move the whole of our site into the trash. The subsequent hipo stalled, which left most things working, but when the site was moved back, and the next hipo cancelled, there were still inconsistencies in the database, notice links (asset listing roots) missing etc. I've manually dealt with the problems at db level, but it's annoying to say the least.
My opinion is that the whole move/new link process should be wrapped in a transaction, probably within the hipo job itself. Any other opinions/ideas?
Transactional processing moving assets
The part of the HIPO that updates lookups is just rebuilding a cache and can die on large system. The linking part is authorative and quick.
The linking needs to be done quickly so it doesn't tie up the main linking tables for hours with long running transactions. The updating of lookups can always be re-run at any time and can be run on or off the Squiz Server or even via a command line script.
Even if you do use transactions for the update lookups part, you may find the DB is just working too hard for a large system and you actually need to disable the Squiz Server and let the DB commit every now and then.
Dragging an entire site to the trash is tragic, but we hear of regular occurences! I'm sorry to hear that it has happened to you, especially with this associated cleanup required.
I'll let one of our developers respond as to the robustness of the HIPO process, but thanks for bringing it up.