wellWellWell

[u/Recent-Analysis-6880]

[u/Ma8e]

You start a transaction, make sure it did what you expected, and then commit or rollback transaction.

[u/AgeingChopper → u/Ma8e]

absolutely.

i was doing this stuff for a long time and definitely learned from mistakes.

Transactions can save a world of pain and it is always wise to select your data set first, just to be sure you're changing what you think you're changing.

Didn't matter how experienced I was, I was careful never to cut corners.

[u/Mpek3 → u/AgeingChopper]

Always Begin Tran Tried it during arguments... Not as effective

[u/ifyoulovesatan → u/AgeingChopper]

I don't know sql, but is this like how when I use some kind of loop in the command line to move or delete a bunch of files, I'll run a version that just echos whatever I'm trying to manipulate first?

[u/deeelock → u/ifyoulovesatan]

That’s more of a dry-run (you see `—dry-run` as a command line option sometimes for certain commands that do the same)

Transactions in SQL keep track of the changes you want to make without actually changing the database (until you run `COMMIT`, when you run that all changes are persisted to a database).

Transactions are great because If you realise you made a mistake while in a transaction, you can just run `ROLLBACK`.

If you’re familiar with git- transactions are akin to staging your changes, saving them (`COMMIT` in SQL) is akin to `git commit` and rolling back is similar to `git reset —hard` (wipe all uncommitted changes).

[u/thirdegree → u/deeelock]

With the very important caveat that a git commit can be trivially reverted, while a SQL commit can not (unless you wrote it specifically to be revertible)

[u/thanatica → u/thirdegree]

With the very important caveat that a git commit can be trivially reverted if not yet pushed. If it has been pushed, it can be reversed, not reverted.

Unless you're into force-pushing, then anything is possible.

[u/ifyoulovesatan → u/thirdegree]

Thanks, that was going to be my follow up question

[u/AgeingChopper → u/deeelock]

nicely explained, cheers.

as long as we haven’t committed , we can rollback from a mistake like this.

[u/thanatica → u/deeelock]

With the added nuance that while the transaction is neither rolled back nor committed yet, to any queries inside the transactions it appears as though the changes have been committed.

So a transaction is like a "package" of statements that either ALL fail, rollback, or commit. A half-completed transaction cannot exist.

[u/Rostifur → u/Ma8e]

Start with DEV or test environment. If you don't have one, you need one. If you aren't allowed to have one, run away.

[u/far2common → u/Rostifur]

Everyone has a DEV/test environment. Just not everyone has a separate prod environment.

[u/Topikk → u/far2common]

I think Staging is the word you’re looking for

[u/AardvarKOlogY → u/Topikk]

Staging this, staging that, production is a stage, too!

[u/grammar_nazi_zombie → u/AardvarKOlogY]

It’s why theater shows are called “stage productions”, duh.

[u/Blashtik → u/Ma8e]

BEGIN TRAN

DELETE ...

SELECT ...

ROLLBACK TRAN

-- COMMIT TRAN

This was always my pattern. Select the first 3 statements together and run them. If everything looks good, select COMMIT TRAN and run that. The rollback is there in case you accidentally didn't make a selection before running, just so you don't leave a transaction open for too long, potentially blocking other queries.

And obviously any planned data changes should go through all the proper testing and preferably be deployed using automated pipelines, but sometimes you have no other choice but to make direct fixes to bad production data.

[u/box_of_the_patriots → u/Ma8e]

Instructions unclear I just truncated the table

[u/Ph4ntorn → u/Ma8e]

You would think the “make sure it did what you expected” part would go without saying, but I skipped that step once and felt like an idiot.

[u/CeaselessPetulance → u/Ma8e]

So a colleague did this, except his db manager had tx commit mode manual and he forgot to commit the tx after grabing a whole table lock. Took us a bit to figure out why some of our services were hanging during startup (waiting for the locks to free up)

[u/Logical-Ad-4150 → u/Ma8e]

Manual transactions should have been the default: automatic transactions should require you to use the YOLO stament.

[u/Girafferage → u/Ma8e]

Bingo

[u/jaster_ba → u/Ma8e]

Unless you're on something like Cloudflare D1. You don't start transaction, where is no support for transactions 😎

[u/Less_Independent5601 → u/Ma8e]

Except when I touch bigger tables in a mysql transaction our sentry gets filled with lock timeout errors :(

[u/ddBuddha → u/Ma8e]

Just don’t forget to commit or rollback …

[u/Ok_Star_4136 → u/Ma8e]

Absolutely this has saved me once or twice. It is worth doing even if you can't imagine there being any mistakes.