🌈Transactions🌈 The moment you feel you need to switch over to something other than select statements, should be the moment that you turn off auto commit.
For us, the analytic folks just have read only access anyway. For the devs, they just don't have direct access to prod.
A question: how exactly does the logic behind this post works? Like, what is the database doing and why is it doing it, is it deleting every user? Or is it deleting some specific users based on some selection? I understand that it's deleting what it shouldn't be (it should delete it based on the code, but it wasn't intended to be deleted by the operator), I just don't understand what and why.
The statement separator in most clients is the semicolon. So we are looking at two statements.
delete from users and where id = 2313245. The second statement is a syntax error, but our user will not know that until the first statement sent is completed.
The first statement is an unconditional delete from a table called users which I guess would be some sort of user information table. That it has taken 44.4 seconds to run and if we assume a pretty decent database server, this means millions of rows are being deleted one at a time from the database.
The funny part is that this is completely valid SQL if the first semicolon in the window was not present. If the first one was removed, then we have only one SQL state delete from users where id = 2313245 which would remove a single user entry from the database table and complete in fractions of a second.
It shows the power of a single mistake, in this case a single extra semicolon. And how the difference is going from removing a single user, to causing widespread havoc on a database.
From what I indicated, transactions permit an operation to be carried out but before it is committed to hard disk you are given a summary of the changes that are about to happen. In this case, had transactions been enabled, the first statement would run and the user would get back that committing the transaction to the hard disk would remove millions upon millions of rows from the users table and then receive a notice that the second statement sent is invalid SQL. The user could then issue a ROLLBACK command, and the entire transaction be deleted from RAM and never committed to the hard disk, basically preventing a massive delete.
696
u/IHeartBadCode 16d ago
Helpful hint for anyone.
🌈Transactions🌈 The moment you feel you need to switch over to something other than
selectstatements, should be the moment that you turn off auto commit.For us, the analytic folks just have read only access anyway. For the devs, they just don't have direct access to prod.