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.
Whenever it is time to run SQL directly in the live database I tend to become nervous. One single error and a lot of data could be lost or made incorrect. Usually I make such an update in three steps:

  1. Find the data that should be changed.
  2. Create the update statement and validate it
  3. 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…

  • Leave a Reply

    Your name as it will be displayed on the posted comment.
    Your e-mail address will not be published. It is only used if I want to get in touch during comment moderation.
    Your name will be a link to this address.
Software Development is a Job – Coding is a Passion

I'm Anders Abel, an independent systems architect and developer in Stockholm, Sweden.

profile for Anders Abel at Stack Overflow, Q&A for professional and enthusiast programmers

Code for most posts is available on my GitHub account.

Popular Posts

Archives

Series

Powered by WordPress with the Passion for Coding theme.