Experienced DBA: Rookie Mistake

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.

7 Replies to “Experienced DBA: Rookie Mistake”

  1. I’ve made the same mistake though fortunately without going to lunch!.

    An alternative approach to avoid this issue is to run the whole script including any checking statements in a single transaction with xact_abort on and with a rollback at the end.

    When I know all is well I manually alter the rollback command to commit and run the whole script a 2nd time. That way I also avoid causing blocking during my thinking time too, and am therefore not working under time pressure.

    Obviously this approach does lead to transaction churn and it can leave gaps in auto-identity sequences too so it needs to be used with care.

    I will add SELECT @@TRANCOUNT as an additional safety check. Thanks.

  2. From a number of a posts I see the only reason of all issues is – the absence of a solid background in IT, sorry..

  3. Art I also respectfully disagree. We are all human after all, mistakes will happen at some point. It’s just life. John definitely knew the right thing to do, it’s not like a lack of understanding on his point, just an honest mistake. John thanks for the post! Definitely a great reminder and an interesting resolution, I appreciate it.

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.