Try And Retry
I’ll sometimes see people implement retry logic to catch deadlocks, which isn’t a terrible idea by itself. The problem that may arise is when the deadlock monitor takes a full 5 seconds to catch a query, which …
Category: Locking
Isolation Level <> Locking
Different Engines
I seem to have gotten quite a few questions about this lately, so I’m going to write down some thoughts here.
It’s probably crappy form for a blog post, but what the heck.
Queries Decide The Isolation Level
…How Unused Indexes Hurt: Lock Escalation
Facts
Yesterday we looked at how unused indexes can muck up the buffer pool, because writes to them will bring pages in.
Today we’re going to look at how unused indexes add to locking problems.
Hold My Liquor
…When Read Queries Block Write queries
Gritty
You may occasionally see in your favorite monitoring tool, or Most Fantastic And Glorious Script For Determining Whom Activity Belongs To™ that a read query, perhaps even a long running one, is blocking modification queries.
Under most circumstances, …
Index Key Column Order And Locking
Fall Guy
Let’s say we have a super important query. It’s not really important.
None of this is important.
SELECT u.DisplayName, u.Reputation, u.CreationDate FROM dbo.Users AS u WHERE u.CreationDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND u.Reputation < 6 ORDER…
Should Optimistic Be The Default Isolation Level?
Two Words
Alright, I’ve lied to you already. There are way more than two words involved, here.
I recently had breakfast (JUST BREAKFAST, GOD) with Microsoft’s Most Handsome Man™, and the topic came up.
Partially because there are such fundamental …
The Real Value Of Optimistic Locking
Erroneous
While a lot of people speak highly of optimistic locking because it cuts down on blocking issues, I think it’s of equal or higher value that dirty reads are prevented.
“Imagine” a situation where someone ran a query they …
Separating Long Running Queries From Modifications
Let’s Say We Have A Bad Query
I know you don’t have bad queries. You’re smart people. But some people do!
Let’s also say that bad query is taking part in a modification.
UPDATE u2 SET u2.Reputation *= 2 FROM…
Bad Estimates And Locking
Stay Sober
I recently came across a bad locking problem. It was one of those things that happened “suddenly” and “for no reason”.
When I looked at the stored procedure, the code was something like this:
CREATE PROCEDURE dbo.bad_news(@PostTypeId INT)…
In BEGIN TRAN, No ON Can Hear You Scream
But I Need To Roll That Back
It all sounds like a great idea.
You want your stored procedure to be a unit of work that you can roll back if something goes awry.
At first, it’s a few quick …