How to use transactions to reduce risk in major data modifications

This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job. You're in the databases category.

Last Updated: 2022-12-01

I needed to modify half the records in the advisors table in production and wanted a way to do it that reduced risk.

Yes, I had backups, but having to use them would be painful. Is there something else?

Yes there is: DB Transactions. To use one, I just needed to do the following:

BEGIN TRANSACTION;
# Run the SQL that would
# ..
# But DO NOT commit the transaction yet

Next, I was able to check the results of my modifications from within this transaction (e.g. by doing some SELECT queries here).

I was happy with everything so I typed COMMIT to make the changes stick around.

If I wanted to undo and go back to my previous state, I could simply have run ROLLBACK TRANSACTION instead of committing.