TSQL in Production - Preventing Ulcers

  2014-07-28


Disclaimer: DBA’s should not have to read this.

We all have had to modify something in production databases. And by “we all” I mean all of us developers that live and work in the real world.

Your boss comes to you and tells you the data caused by yesterday’s bug in the Camplogifier is causing the Selmoticron sub system to take itself offline every 5 minutes. And as a result the support people are getting lots of calls from very angry customers.

You think a moment, and say, “I can quickly write a script to fix the data”. Your boss looks over your shoulder, and you start typing. He keeps asking what you are doing, distracting you, because you have to keep explaining..


    UPDATE dbo.Camplogit
    SET ProblemColumn = 'CorrectValue'

OK. That looks good. You run the script…


    (547,873 row(s) affected)

WHAT THE………!!!???

You know it should have only updated about a thousand records. And then you realize that through the distractions and pressure you forgot the WHERE clause. (You can feel the ulcer coming on)

I do not think I have met many developers that have never stuffed up production data in a similar way by mistake. I have done it more than once.

It should have been like this:


    UPDATE dbo.Camplogit
    SET ProblemColumn = 'CorrectValue'
    WHERE ProblemColumn = 'ValueCausedByYesterdaysBug'
    

Introducing the PREVENT ULCER keywords


    UPDATE dbo.Camplogit
    SET ProblemColumn = 'CorrectValue'
    WHERE ProblemColumn = 'ValueCausedByYesterdaysBug'
    
    PREVENT ULCER
    

SQL server also has an alias for PREVENT ULCER. It’s called ROLLBACK TRAN. And it is friends with BEGIN TRAN. Use them like below to prevent ulcers.


BEGIN TRAN
    
    UPDATE dbo.Camplogit
    SET ProblemColumn = 'CorrectValue'
    WHERE ProblemColumn = 'ValueCausedByYesterdaysBug'
    
ROLLBACK TRAN
    

If you don’t know what a tran or transaction is, you’re in the wrong place. However, even though most developers know what a transaction is, and use them from the applications they write, it is surprising to me how few of them use it when writing ad hoc DML or DDL scripts.

I never ever run any ad hoc scripts against production data any more without wrapping it in a transaction that I rollback. I also write select statements after the DML statement inside the transaction to verify that what I intended did actually happen and nothing else. Once I am completely satisfied that my script is doing exactly what I want it to do, I just replace the ROLLBACK with COMMIT.

Example, if the first script above had been in a rolled back transaction, when we saw the below, it would have been no big deal because no changes would actually have been applied to the DB.


    (547,873 row(s) affected)

So you could have just tweaked your statement and ran it again, instead of getting ready to pull out the last backups from an hour earlier (because you have a good backup plan in place), and spending the next couple of ours restoring the data you nuked.

Conclusion

Simple practices like this one can save you many pain filled hours. It might even one day let you keep your job..

comments powered by Disqus
comments powered by Disqus