Hacker News Reader

Comment by JeffRosenberg | Hacker News Reader

I try to start any UPDATE or DELETE by writing out a transaction first:

    BEGIN TRAN;
    
    -- TODO: update statement
    
    ROLLBACK;

Usually I'll run it with the ROLLBACK first, to confirm that it impacts the number of rows I'd expect, and only then change my ROLLBACK to a COMMIT.

View on HN

I learned this technique the first time I actually worked with a real DBA many years ago and have used it ever since: one thing I like about it is that is mitigates the ‘fear factor’ of production changes, as (topically) ‘fear is the mind killer’ and makes mistakes more likely. Then there’s mitigating ‘complacency’ - “I do this all the time” - which can also lead to mistakes.

And yes, before that I had run an UPDATE without a WHERE in production..

I don’t even do this, it’s too easy to sidestep. For example using any GUI client where the current selection or statement is executed by default. (Yes this is very useful, no I am not interested in being convinced not to use these tools thank you.)

Instead I do my dry run queries with a read only user and I select the affected data, often using CTEs (or temp tables where performance is an issue) to model any intermediate state. I don’t ever run any writes of this kind without review, backups, and automation.

Performance wise, if this was done in production for all queries, do transactions make queries noticeably slower?

I do this when I test my migrations locally.

Run code first, throw an error to force rollback the transaction...

Reply on news.ycombinator.com