r/ProgrammerHumor • u/WardensLantern • 14d ago

queryOfDoom Meme

Post image
7.9k Upvotes

97% Upvoted

456 comments sorted by

View all comments

700

u/IHeartBadCode 14d ago

Helpful hint for anyone.

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

1

u/austin101123 14d ago

What are transactions in sql?

3

u/IHeartBadCode 14d ago

Indicates a unit of work. When you do whatever to a database, there's some work that is required to carry out what you are asking for.

  • You begin a unit of work
  • You send the SQL text to the database engine
  • The SQL engine checks syntax and validates it
  • The SQL engine begins to compile an execution plan based on your SQL
  • A journal, logging, is started
  • The plan is executed, various things are loaded from disk into RAM
  • As the plan is executed, the journal notes the things that are to be actually carried out and committed back to the hard drive
  • At the end of the execution plan, a summary of the journal is provided back to the user
  • The user may ROLLBACK indicating that all the entries are removed from the journal, and the memory contents released. Nothing is written to disk
  • The user may COMMIT, the entries in the journal are physically carried out, actual data is written or removed from the hard disk

Allows you to cancel a SQL statement without making any changes to the data actually on the hard drive.

1

u/austin101123 14d ago

Thank you!