Saved by the System Admin Regarding Database Locking

Just the other day, I tried to update the LIMS I was working on by synching it with some database changes. It’s as easy as pressing a button in this particular LIMS to do this but when I pressed the button, nothing happened. I got an hourglass waiting situation where nothing happened as time passed.

The System Administrator walked into the room and I asked him if there was something going on that I couldn’t get LIMS and the database to sync-up. He immediately asked me if I had the database open. I grunted at him. (Translation: “Of course I do. What kind of silly question is that?!”) He walked over to my desk, pressed a single button in the database window, and the updates quickly took place to sync LIMS and the database. My response was to scowl, cross my arms, slump back in my chair, and grunt. (Translation: “Wow, what a dumb thing I just did. I’m so embarrassed! Thanks for putting up with me!!!”)

What was it that I had done, you wonder? Some of you already guessed that I inadvertently created a “lock conflict” situation. Here’s how I did it: I started some database changes that took a little while – maybe as long as 30 seconds to a minute. Meanwhile, someone asked me a question and I walked away from my screen. When I finally returned, my updates were done but I had not committed them (because we’re not set to “auto-commit”). Now, the database was in a locked state. Thus, when I went to sync the LIMS with the database, the LIMS, which probably needs to write to the database as it is synching with it, could not overcome the locked state that I had created. Forgetting to commit (or rollback) the changes is a critical step that made a big difference.

Having worked on past projects where I used to have to program in the lock checks, myself, including understanding what terms such as “lock conflict” and “deadlock” mean, I’ve found it handy to understand database locking. For those of you that want to understand this all, better, here’s a web page I think explains this all pretty well. While you probably won’t have to learn to program these locks, yourself, I still think it’s good to understand the concepts:  http://docs.hp.com/en/36217-90408/ch05s09.html

Also, it helps to have someone around like a good system admin/system architect/guru who will instinctively know when you’ve done something ridiculous like forgetting to commit or rollback. It’s a plus when they don’t openly laugh at you, too!  ;-)

Gloria Metrick
GeoMetrick Enterprises
http://www.GeoMetrick.com/