3 ms·

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

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!

marcosdumay 5y ago
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.
- dcminter 5y ago
  > 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!
  - marcosdumay 5y ago
    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.
- matsemann 5y ago
  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.
matsemann 5y ago
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..
IceDane 5y ago
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.
magicalhippo 5y ago
Fair point, you don't want those transactions hanging around. Still, I prefer it to the alternative.
- to11mtm 5y ago
  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.

marcosdumay 5y ago

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.

dcminter 5y ago
> 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!
- marcosdumay 5y ago
  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.
matsemann 5y ago
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.

matsemann 5y ago

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

IceDane 5y ago

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.

magicalhippo 5y ago

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

to11mtm 5y ago
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.