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.

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...

I consider any SQL client that doesn't have implicit transactions as broken. Just too easy to make mistakes.

However even with that, I agree with your second point. I always type

    -- commit

in my SQL client and then highlight "commit" and execute only the highlighted text. That way I don't commit anything if I accidentally trigger the "execute all" action.

Yes, but mind you I once locked up the database for my colleagues by not realising the consequences of implicit transactions in a DB client.

It was a long time ago, but I think it was with SQLPlus on Oracle - and presumably staging or something non-prod since it's not a particularly vivid memory!

I literally did this just yesterdayy, in production, too. Thankfully it was a greenfield deployment and the customers weren't really using the system yet.

I went in there to adjust some data so that one of our dev users could re-publish some data which would then be synchronized to another database(update foo set published = false) and I couldn't understand that it wouldn't update in the UI.

I ended up telling him I'd have to get back to him, and the issue didn't dawn on me until the app logs started spitting out errors about not being able to retrieve a connection from the pool. YIL about pending transactions in DBeaver.

I've also often used transactions to play it safe.

But our team learned the hard way that using transactions on the replica pg database actually locks it from getting updates globally during the duration of the transaction. And the whole idea of connecting to the replica was to not wreak havoc, oh well..

Oh, locking is what Oracle does. That and hanging because the work of keeping the transaction increases faster than linearly with the amount of data.

You shouldn't get access to prod if you didn't have experience with Oracle. The database is just too fragile to survive inexperienced people changing it.

I once gave thousands of government employees in Norway a long lunch break from what should have been a non-locking migration. Or so I thought. Apparently it was fixed in 12b (or something, a few years ago), and my tests in staging went fine. But in production we didn't run 12b as in staging, but 12a...

Can't remember all the details. But something about adding a new nullable column with or without a default value of some kind. It had to lock the table, but multiple queries were already running. Those queries had some locks already, but then needed those rows the migration had already gotten hold of. Leading to this huge deadlock I had no idea how to solve.

Worst part is, it was one of the first times we tried deploying during working hours. At the time we normally only got to deploy 4 times a year, but we pushed for a more modern approach. Luckily the server guys were on our side, and a few years later that government agency is one of the best technical places I've seen after a complete revamp.

> You shouldn't get access to prod if...

Well, this was back in the dark ages. Nowadays I'm sold on the notion that we shouldn't be running ad hoc stuff against the live prod database at all.

I'm sure there are exceptions, but I'll bet there are more temptations than solid reasons to do so!

Well, somebody has to have access to live prod. That somebody should avoid using it too.

Anyway, everybody should run their queries on a prod-like environment before running on prod, but on Oracle that's really not enough. Also the places that use expensive DBMSes tend not to have a lot of non-prod environments for people to test their scripts.

Nowadays Oracle supports you engineering your data so some of it is not on the bottleneck of anything and you can give some low amount of access to inexperienced people. But that's not the default situation.

Fair point, you don't want those transactions hanging around. Still, I prefer it to the alternative.

What I usually do instead?

Start with ROLLBACK at the bottom (With a BEGIN TRANSACTION up top if the DB/env warrants it)

Only when I know I'm happy, change the ROLLBACK to a COMMIT.

My approach is to type the “limit conditions” first, then go to the start of the line and do as you say (predicate the command with the comment string). So in the case of sql it might be something like starting with:

  WHERE foo=bar LIMIT 1;

Then ctrl-a, and fill in the rest:

  —- UPDATE name=“new name” WHERE foo=bar LIMIT 1;

Then take a moment, read over what I typed, and hit ctrl-a again and remove the comment string.

Ideally I’m doing this in an editor (not the db shell) as well, and when I’m done pass the saved file in on the commandline. I try to type as little as possible in the db shell unless I’m logged in as a read only user.

I also set my db shell to display the current username and database so it’s always right in front of me. And I never, ever use command history in shells to construct new commands. I swear that bit me more frequently than I got it right when I used to. It’s like a footgun with an extra footgun attachment.

I take a similar approach. I usually want to see what I'm about to change before I change it, so I'll SELECT * FROM ... WHERE ... , then go back in the command history and delete the SELECT FROM.

Same. (Also for DELETEs.) I would guess this is what a high percentage of people do.

Predicate order in SQL is pretty dodgy all around and makes it hard to build intuition around SQL queries. In select, the actual SELECT clause (field selection) can usually be conceptualized as “happening” after all other clauses, but of course it comes first in the query.

This problem is fixed in query languages like EdgeQL.[0]

[0] https://www.edgedb.com/docs/edgeql/commands/update

> In select, the actual SELECT clause (field selection) can usually be conceptualized as “happening” after all other clauses

Not quite true, DISTINCT, ORDER BY and TOP/LIMIT happen afterwards.

Also, HAVING

> A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE.

Given the annoyance of this and its ability to really, really ruin your day, I don't know why someone hasn't updated their parser to allow 'update X where [cond] set [blah]' as an alternate phrasing.

> A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident.

Well, dangerous if you are using a simple terminal interface and not using a transaction when doing updates. The latter is generally a bad idea even if you aren't also doing the former.

> A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident.

I religiously write it out of order for this reason. The IDE complains for a bit, but it's better to deal with some squiggles for a few seconds until you've filled in the column assignments part.

The problem with this is the same reason I don’t write dangerous CLI commands with the safety parts first: you can’t know whether your safety parts are safe until you introduce the dangerous parts. It’s the same reason I don’t ignore the linter, even if it’s cognitive overhead that formatting will solve… it’s more cognitive overhead to find out whether it’s safe to ignore.

At least for me, it’s safer to just… write the thing correctly in an environment incapable of running it.

I've taken to using "echo" and then when I run the command run it with a space at the front - you may have to run setopt HIST_IGNORE_SPACE or similar to get this in zsh, for example.

That way ctrl+r won't bring back a destructive command.

Echoing the command also lets you see that shell escaping, parameter expansion etc. works as you expected. I do the same when writing a one-off loop, too.

What I usually do is start typing something like "aupdate" or "pdate" (which are wrong) and only come back at the beginning (it's just a ctrl+a) to fix the command when I'm sure the sentence is OK.

Of course if the update is not only important but critical, manually starting a transaction before is the first step ;)

Sql is not the best language. As someone put it once "The entire database industry is hauling a massive SQL-shaped parachute behind them. This complexity creates a drag on everything downstream."

Linq is closest to the a more natural querying language we have come up with. Too bad it doesn't do update.

I'm kinda bummed out with these recent SQL tropes. Plenty of people look down on SQL and try or try to make the new fancy thing only for it to fall flat on it's face and for people to realize that SQL is pretty good. It's not great for programmatic access but it's great for analytics and manual querying.

Sql was good for what it was doing.

But that doesn't mean it's ideal. It just has a large most that people wasn't able to disrupt.

Check out linq both the sql-like form and the lambda form. It feels much better for what it does.

One quick trick I use a lot in the shell is to run a known safe command with the argument first, and then run the dangerous command with the the !$ variable (the last argument of the previous command) so there's no possibility of a mistake in copying and pasting. Something like: `ls /tmp/junk; rm !$`.

I think !$ takes the last argument on the previous command in history, not previous one on the same line.. at least in bash. For example, if you do:

  $ ls /
  $ ls /tmp/junk; rm !$

It's taking "/" rather than "/tmp/junk"..

EDIT: formatting

Yes that's right it's the previous command from history. That way you run ls and see if it's the expected output, then rm to delete.

Point being that "ls /path; rm $!" deletes something entirely different than "/path".

What you want is "ls /path; rm $_".

Even then, the above is fairly pointless. At the time you look at what you are deleting, it's gone.

I think you misunderstood the previous comment. I think that firstly "ls /path" is entered and if the result is ok, then "; rm $_" is added to the copy of the command then executed.

Yes, at which point you simply do:

1. ls /path

2. rm <esc><.>

(where "escape" "dot" brings up last argument) without the need to fiddle around with dollar signs underscores, exclamation marks, etc. to prevent further mistakes (e.g. "was it $! or !$ ?", shell expansion, etc)

DataGrip will not execute it without a LIMIT.

I use vim bindings for shell, so doing dangerous things I usually open it in edit mode and have to physically do :wq to execute. Much less chance of being destructive, and you can easily drop the output into clipboard or a file from here.

Otherwise, simply having a mindset of, I'm doing dangerous things also goes a long way.

Never do things if you are in a panicked/frantic state.

Edit: I guess running galera is actually a blessing. I can't run any write/update methods on our qa/prod data...