Hey, it’s T-SQL Tuesday again! I almost forgot about it what with Summit and all. Wayne Sheffield (blog|twitter) is our host and wants to know What were you thinking?!? If you’ve ever had the joy of sitting down with a handful of people in your profession (and I don’t think it matters what profession) you’ll start hearing horror stories. I mean we all like to tell them right? And coming just off of Summit I got to hear a bunch of them. So here are a few of my favorites that I tell.
You think your trigger is bad?
On one of our systems, we have a trigger (I could almost stop there but I won’t). This trigger calls a stored procedure (yep), and that stored procedure calls another stored procedure .. on a different server using a linked server. That stored procedure hits a table with you guessed it, another trigger. That trigger calls a stored procedure (you can kind of see where this is going can’t you?) that uses a linked server to get some data back from the first server. The first time I was asked to find out why it took so long to add data to this table and took a look I laughed, and cried, and laughed.
Now, in the interest of What were they thinking? I’m pretty sure this is one of those things that built up over time by several developers who weren’t looking at what the previous people had done. FYI it still exists because the performance isn’t too bad and they don’t want to spend time fixing it.
You think you have a NOLOCK problem?
I had a conversation over the Summit where someone mentioned that if you don’t like NOLOCK how about SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED? Note: NOLOCK is just another term for READ UNCOMMITTED, the main difference here is that changing the TRANSACTION ISOLATION LEVEL affects the session until you change it back, or everything within a SP after the point you changed it. NOLOCK is used as a query hint and only affects that particular use of a table.
Anyway, my response? We have a system where they use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning of almost every SP and NOLOCK on every table reference. (To be fair, only the selects. Even these people don’t use NOLOCK on an update, insert or delete. And yes, I’ve seen it done.) If you’re wondering why that matters, that’s kind of the point, it doesn’t. One or the other people .. well .. by preference neither.
I don’t really have an excuse here. They have always done it this way, NOLOCK is a go-fast button and we haven’t been able to convince them otherwise.