3 ms·
I try to start any UPDATE or DELETE by writing out a transaction first: BEGIN TRAN; -- TODO: update statement ROLLBACK; Usually I'll run
by JeffRosenberg 5y ago
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.
- stuartd 5y agoI 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..
- eyelidlessness 5y agoI 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.
- tehlike 5y agoI do this when I test my migrations locally. Run code first, throw an error to force rollback the transaction...
- XCSme 5y agoPerformance wise, if this was done in production for all queries, do transactions make queries noticeably slower?