M.A.S.H 4077. Mobile Army Surgical Hospital. One of my all time favorite TV shows that I still watch to this day. There is one episode that has always stuck with me is one where Hawkeye has to deal with death of a friend during surgery. Afterwards, Colonel Blake imparts advice to Hawkeye:
While not life threating, I was recently reminded that:
- I’m human and I make mistakes at times.
- Even the most experienced DBA cannot change rule #1.
The other day I had to update some records, in Production. I’m a firm believer of using explicit transactions and double checking things before committing a transaction. This helps ensure things go as expected. This also allows me a way to rollback the changes if they don’t. It happens.
However, this means that I have to COMMIT said explicit transaction. And not go to lunch without doing so.
Can you see my mistake? I bet you can.
I changed a single record in a table, *thought* that I had committed it and went about my day. I even left the office for an appointment over lunch. While I was at my appointment, I got a call from a teammate asking if I had being doing anything with this particular database around 11:17 that morning.
Why, yes, yes I was as a matter of fact.
As soon as I saw his number on my phone, I realized my mistake. An open transaction was left sitting there. I quickly estimated in my head how much blocking that was generating. Given this particular database, it wasn’t going to be pretty.
I told my teammate to kill my transaction which would roll back any changes. I can fix the data again once I got back to the office. The transaction was killed off and the blocking disappeared. Problem solved.
I made a mistake. A rookie mistake at that.
I’m a fan of Red Gate tools. I use SQL Prompt on a daily basis so I created a new snippet to help me remember to handle any transaction and/or check for open transactions.
Now I can use the snippet to automatically insert the syntax for the explicit transaction as well as a check (or reminder) to see what the transaction count is.
Reminder, always, always, check for open transactions before leaving for lunch!!
Learn from my rookie mistake.
© 2017, John Morehouse. All rights reserved.