Locking, Blocking, and Isolation Levels

The short story: when you add a computed column that references another table, like with a scalar user-defined function, you can end up causing concurrency problems even when people didn’t really want to go see that other table, and that table is locked by someone else. Here’s my query: Transact-SQL SELECT Id, u.DisplayName, u.BadgeCount FROM…

“Concurrency is hard” Construction begins on the Tower of Babel, 610 BCE (colorized) When designing for high concurrency, most people look to the hardware for answers. And while it’s true that it plays an important role, there’s a heck of a lot more to it. Start Optimistic Like, every other major database vendor is optimistic…

Deadlocks can kill an application’s performance. Users will complain about the app being slow or broken. Developers will ask the DBA to fix the problem, DBAs will push the problem back on developers. The next thing you know, the office looks like Lord of the Flies. What is a Deadlock? A deadlock occurs when two…

It’s morning standup, and someone says, “It’s no big deal, we just need to add a couple of columns. It’s already in the build, it works fine.” The next time this happens, stop and say, “Let’s take a closer look at that.” When you write schema changes for a relational database, take the time to…

Meetings are like bad blocking queries – they tie up precious resources and stop other people from getting work done. Today, let’s talk about how to optimize both of ’em by doing homework ahead of time and minimizing the number of objects in the transaction.