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:

1.  I’m human and I make mistakes at times.
2.  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.

Resolution

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.

Enjoy!

© 2017, John Morehouse. All rights reserved.