War Stories: Bringing Down the Server With a Simple Select Statement

What follows is one of my more horrific war stories. There were many casualties, and I was almost killed in action, but my indomitable fighting spirit spurred me on. That, and I took two days off that resulted in a five-day vacation, since it fell during the week of Thanksgiving.

I was writing a SQL query that would merge users. Since users are connected to numerous tables throughout this particular database, this requires a lot of updating of different tables. Also, since the user table is the table with all the, you know, user data like UserName and UserPassword, that's the table that's queried when a user logs into the website. Easy stuff, right? Obviously, this user table is an extremely important table in the database.

Since I'm having to update all these tables with new user information and later remove old user information, I wisely decided that all of these UPDATE queries need to be wrapped in a transaction. For the uninitiated, when a group of queries is wrapped in a transaction, all of the queries function as one atomic unit. This means that if any one query in the transaction fails, all changes made up to that point in the transaction are rolled back. To the programmer and the end user, it appears as if nothing has happened. Maintaining such data integrity is absolutely imperative where I work, since we don't use test databases and instead directly query the production databases. At the end of my transaction, just before the COMMIT statement, I select from the user table to grab some pertinent information.

So I'm writing my transaction and feeling great about life. I put the transaction in a Try block and put code in the Catch block to rollback the transaction, thus guaranteeing that my transaction will behave correctly. Or so I thought.

As I was testing my shiny new transaction, it fails. No sweat, I think, my catch block just rolls everything back and we're good as gold. Then I notice that I'm receiving email notifications indicating that individuals are not able to login to the website. At the time, I don't make any connection with what I'm working on and this login problem, so I continue working.

For the next forty-five minutes, these login errors keep popping up. No one has said anything to me, nor do the errors indicate that any problems were occurring in the user table, so I continue merrily along, testing my transaction and watching it fail, but thinking nothing of the failure.

Then my boss calls me into his office. I immediately recognize the tone as the "You just seriously f__ked up tone." I walk in and he has the activity monitor pulled up, which shows a stalled transaction that's locked the user table. He asks me what I'm doing, I tell him, and he immediately lays into me, asking why on God's green earth I'm SELECTing in a transaction, and if I absolutely have to, why I'm not using WITH(NOLOCK), which keeps the table from being locked during a read. Not having known that it was possible that my transactions were not being rolled back and further not having known that my SELECT statement was locking the user table, I proceeded to put on my best deer-in-the-headlights look and stammer stupidly. He sent me back to my office and had one of my coworkers join me to explain what had happened.

He was so mad, he did not even chew me out after the initial questioning. It was kind of surreal, as up until that moment in my life, I had never felt so ashamed at not being chewed out. I had messed up that badly. I was so absolutely mortified that I wanted to go hide underneath a rock. This happened around 2 o'clock in the afternoon, and I gave serious thought to just leaving. In retrospect, I'm surprised that my boss didn't send me home. I even began to question whether or not software development was truly my thing.

So what lessons did I glean from this? Several, listed below and in no particular order:

1. If you're selecting from a heavily-used or mission-critical table, always use WITH(NOLOCK) to keep the read from locking the table.
2. Do not rely in SQL Server 2005's Try and Catch blocks to work correctly. I still have no idea why my Catch block was never executed. Instead, if using a transaction, you should precede it with SET XACT_ABORT ON and end it with SET XACT_ABORT OFF just after the COMMIT statement. SET ARITHABORT ON ensures that the current transaction, and only the current transaction, is rolled back if any of its contained queries fails.
3. Use named transactions. If you don't use named transactions and they become nested, say by having a stored procedure that contains a transaction which calls another stored procedure that contains a transaction, then it's possible that either the wrong transaction will be rolled back, or none of them at all.
4. The activity monitor is your friend! I now keep it open at all times and check it often during testing.

So that was one of my most embarrassing moments in my career. However, it did teach me a lot, and the lessons I learned about transactions will probably never be forgotten.

The Junior Developer

This blog will be about exactly what the title says - The Junior Developer. Why The Junior Developer? Because that's what I am: A Junior Developer. I've been developing software using .NET, ASP.NET, and SQL Server 2005 for a little over a year and a half now.

And boy, it can be a rough ride sometimes.

I've had peaks so high that I couldn't see the ground. We're talking those programmer highs you get when you finally make that breakthrough towards which you've been battling for a week. We're talking the highs that come from finally developing that awesome algorithm that does exactly what you need it to do, no more, no less. We're talking that high that comes from creating that one method that answers all your business objects' problems

But we're also talking about the lowest of the valleys. We're talking bringing down the entire website by accidentally and unknowingly locking a table in a transaction that never got rolled back. We're talking forgetting to catch error's on the main login page so that no one can log in. We're talking forgetting to restart IIS on the production server after copying new files over. We're talking mistakes that could cost your company hundreds of thousands of dollars.

There's a big similarity for me in both these peaks and valleys: I find myself not caring about my salary. At the summit of the peaks, I don't care about how much I make because I'm so euphoric and in the moment that my boss could reduce my salary to $1 and I wouldn't care for at least an hour or so. During the troughs of the valleys, I'm so embarrassed, ashamed, and mortified by my own mistakes that no amount of money would make me feel better.

This blog is about the ups and downs of being The Junior Developer. This blog will offer tips for other Junior Developers, tips that I've come across either by my own research or, more often, my own experience (read: mistakes). The tips will not necessarily be coding tips: They may also be emotional, mental, or just plain old common sense in nature.

I'm starting this blog not only for myself, but for all the other Junior Developers out there that have experienced the highs and lows of being the low man with no experience. I'm starting it for every Junior Developer who needs guidance, who needs that dose of encouragement, or who's just about ready to throw in the towel.

Remember, brother (or sister!): You're not alone. I'm here leading the charge, and there's thousands of other Junior Developers who are or have been exactly where you are.