Transaction Safety for Manual SQL Updates
When maintaining a system that is in production there are often issues that need to be solved by running custom SQL scripts directly in the production database. There can be different reasons for this:
- Updating a lookup table to add a new option to dropdown boxes, where there is no user interface for handling the table.
- Adjusting data in a way not permitted by the normal work flow.
- Reverting an incorrect operation performed by a user.
- Find the data that should be changed.
- Create the update statement and validate it
- Run the complete statement.
Find the data
To find the data I run a simple SQL query. In this example I have to update the production country of all Volvo’s in my cars table that have a registration number greater than VLV102.
SELECT * FROM Cars WHERE RegistrationNumber > 'VLV102' |
Create the Update Statement
Now I now I how to find the correct cars. Time to extend it with an update statement that can be tested without actually changing the database. I do this by wrapping the statements in a transaction which is rolled back.
BEGIN TRANSACTION UPDATE Cars SET MadeIn = 'Belgium' WHERE RegistrationNumber >'VLV102' SELECT * FROM Cars WHERE RegistrationNumber > 'VLV102' ROLLBACK |
Running this will make the update within a transaction. Then the results are selected for display. Finally the transaction is rolled back to undo the changes in the database. This step can be run any number of times when construction a complex update statement without anything being saved in the database.
Run the Complete Statement
Once I’m sure that the script is correct I replace ROLLBACK
with COMMIT
to have the changes written to the database.
BEGIN TRANSACTION UPDATE Cars SET MadeIn = 'Belgium' WHERE RegistrationNumber >'VLV102' SELECT * FROM Cars WHERE RegistrationNumber > 'VLV102' -- ROLLBACK COMMIT |
Conclusion
Using transactions when doing manual updates to important databases adds an option to try the script before running it live. I recently showed a colleague of mine this method and it saved him from doing a disastrous update. In his first attempt for the update, he had made a mistake in the where clause, making the update to far too many lines in the table. When the updated line count showed 3500 instead of the expected 12 we where saved by the transaction being rolled back. The failing where clause was fixed and tested once again before we changed the script to committing the transaction.
robert on 2018-04-29
You should consider than a rollback on a live database, with too many lines , can just put your server on his knees , and lock every ressources…