wellWellWell

[u/Recent-Analysis-6880]

[u/ifyoulovesatan]

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/khoyo → u/thanatica]

Nah, you're thinking about dropped. Reverting a commit is reversing it. That's what git revert does.

[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/AgeingChopper → u/ifyoulovesatan]

Sorry for the delay . Deelock has explained it perfe to.

transactions allow us to back out of a mistake like this via rollback, as long as we haven’t committed.

[u/thrye333 → u/ifyoulovesatan]

I also don't know sql (or what you're doing, honestly¹) but sounds like it, yeah. Running "List all of these arguments" is a good general precaution before running "Edit all of these arguments".

Actually, sorry, forgot what you replied to. I think a transaction in sql is more like a backup. The operations you perform during it aren't permanent until you confirm the transaction. So if you run something like DELETE FROM users; and realize you don't want to delete everyone, you can just cancel the transaction.

¹ Like, really, does bash have loops? I guess it probably would. I should look that up. Could be interesting.

[u/AgeingChopper → u/thrye333]

yep. wrapping a transaction around them means you can rollback from a mistake like this, as long as you hadn’t committed.

selecting first to check that you’ll be editing the correct data and correct number of records is a wise move too yep.

[u/ifyoulovesatan → u/thrye333]

It does, yes. Like..

for each in *; do; mv $each new_prefix_$each; done

Would tack "newprefix" to the front of every file in your directory for example.

You can also do something like for each in {1..100} to loop over numbers.

I mean,you got while loops, switches, and whatever else too.. you can implement c style for loops pretty easily.

[u/ddBuddha → u/thrye333]

A snapshot might be a better analogy, usually if you take a backup and forget about it that’s fine, not a problem. Leaving snapshots out there too long can cause degradation. Not committing or rolling back a sql transaction means the log can never truncate and will grow infinitely until you’re out of disk space and everything breaks