Sql server has snapshot database for something similar, and you don't have to play with transactions (that may or not breaks everything in case of errors)
If there's an error, the transaction just rolls back. I've been using this strategy for years and I have never once had a transaction "break everything."
They are many ways for a database to behave in a not obvious way...
Typically error handling, xabort (in sql server, especially if you don't want to get blocking orphan connections etc).
Comments
Sql server has snapshot database for something similar, and you don't have to play with transactions (that may or not breaks everything in case of errors)
If there's an error, the transaction just rolls back. I've been using this strategy for years and I have never once had a transaction "break everything."
They are many ways for a database to behave in a not obvious way... Typically error handling, xabort (in sql server, especially if you don't want to get blocking orphan connections etc).