There’s a time and a place for everything, except scalar functions. In a lot of the locking and deadlocking issues I help clients with, developers either:
Didn’t understand the scope of their transaction
Didn’t need an explicit transaction to begin with (ha ha ha)
In this post, I’m gonna give you access to some more of my training videos about locking and blocking for free. Holiday spirit, or something.
There’s a bunch of stuff in there that’ll help you generally with these issues, and one that covers the topic of this post specifically. Enjoy!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
There you sit, an earnest database glob. Users are complaining that the database is slow again, but this time you’re ready for it.
You’ve got a monitoring tool collecting all the right things from all the right places, and it’s telling you that there’s blocking going on.
All those poor queries suffering under the tyrannical yolk of Read Committed The Garbage Isolation Level, yearning for the freedom of Read Committed Snapshot Isolation.
But when you crack open the blocked process report, it makes absolutely no sense at all.
Not one bit.
All Wrong
To show you an example of how bad these things are at collecting the actual problem, I’m going to use the code examples in this blog post about Implicit Transactions, which are horrible and you shouldn’t use them or the JDBC driver.
I mean you should always use them and call me when you need help fixing your horrible blocking problems. My rates are reasonable.
Anyway, when you look at the Blocked Process Report for that code, here’s all the stuff that’ll look screwy:
First, the object and index IDs will both have 0 for a value:
My dislike for implicit transactions is well documented. Recently, while working with a client, I noticed that they had a bunch of them causing blocking.
Digging in a little bit further, I noticed they were all coming from an Agent Job. Of course, by default, Agent runs with a bunch of wacked-out ANSI options.
To get the job to perform better — which it did — it had to make use of a filtered index on an archival task. If you scroll way down in that doc, you’ll see a note:
In order to create, or have queries use your filtered index, they need to have very specific options set correctly.
Baggage
Rather than just setting the required options, which was apparently a lot of typing, someone had just set all the ANSI defaults on.
SET ANSI_DEFAULTS ON;
But this comes with some additional baggage, in the form of implicit transactions. If you run DBCC USEROPTIONS; with that turned on:
Set Option Value
----------------------- --------------
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
statistics XML SET
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
cursor_close_on_commit SET
implicit_transactions SET <---- UwU what's this
isolation level read committed
It sets all the things you actually need, plus a couple other options for implicit transactions and cursor close on commit.
Baggage
Of course, had someone just done a bit more typing, all would have been well and good.
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
Using SET ANSI_DEFAULTS OFF;is equally disappointing, sort of.
Set Option Value
----------------------- --------------
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
lock_timeout -1
arithabort SET
concat_null_yields_null SET
isolation level read committed
It really does just flip everything off. Not that I’m saying it shouldn’t — but maybe we need a command in between?
SET ANSI_DEFAULTS BACK_TO_NORMAL; or something.
Whatever “normal” means.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
I don’t really mean that unrelated queries block each other, but it sure does look like they do.
Implicit Transactions are really horrible surprises, but are unfortunately common to see in applications that use JDBC drivers to connect to SQL Server, and especially with applications that are capable of using other database platforms like Oracle as a back-end.
The good news is that in the latter case, support for using Read Committed Snapshot Isolation (RCSI) is there to alleviate a lot of your problems.
Problem, Child
Let’s start with what the problem looks like.
awkward
A couple unrelated select queries are blocking each other. One grabbing rows from the Users table, one grabbing rows from the Posts table.
This shouldn’t be!
Even if you run sp_WhoIsActive in a way to try to capture more of the command text than is normally shown, the problem won’t be obvious.
sp_WhoIsActive
@get_locks = 1;
GO
sp_WhoIsActive
@get_locks = 1,
@get_full_inner_text = 1,
@get_outer_command = 1;
GO
What Are Locks?
If we look at the details of the locks column from the output above, we’ll see the select query has locks on Posts:
We may also note something else quite curious about the output. The select from Users is sleeping, with an open transaction.
melatonin
How It Happens
The easiest way to show you is with plain SQL commands, but often this is a side effect of your application connection string.
In one window, step through this:
--Run this and stop
SET IMPLICIT_TRANSACTIONS ON;
--Run this and stop
UPDATE p
SET p.ClosedDate = SYSDATETIME()
FROM dbo.Posts AS p
WHERE p.Id = 11227809;
--Run this and stop
SELECT TOP (10000)
u.*
FROM dbo.Users AS u
WHERE u.Reputation = 2
ORDER BY u.Reputation DESC;
--Don't run these last two until you look at sp_WhoIsActive
IF @@TRANCOUNT > 0 ROLLBACK;
SET IMPLICIT_TRANSACTIONS OFF;
In another window, run this:
--Run this and stop
SET IMPLICIT_TRANSACTIONS ON;
--Run this and stop
SELECT TOP (100)
p.*
FROM dbo.Posts AS p
WHERE p.ParentId = 0
ORDER BY p.Score DESC;
--Don't run these last two until you look at sp_WhoIsActive
IF @@TRANCOUNT > 0 ROLLBACK;
SET IMPLICIT_TRANSACTIONS OFF;
How To Fix It
Optimistically:
If you’re using implicit transactions, and queries execute together, you won’t always see the full batch text. At best, the application will be written so that queries using implicit transactions will close out immediately. At worst, there will be some bug, or some weird connection pooling going on so that sessions never actually commit and release their locks.
Fortunately, using an optimistic isolation level alleviates the issue, since readers and writers don’t block each other. RCSI is the easiest for this situation usually, because Snapshot Isolation (SI) requires queries to request it specifically.
Of course, if you’re issuing other locking hints at the query level already that enforce more strict isolation levels, like READCOMMITEDLOCK, HOLDLOCK/SERIALIZABLE, or REPEATABLE READ, RCSI won’t help. It will be overruled, unfortunately.
Programmatically:
You could very well be using this in your connection string by accident. If you have control over this sort of thing, change the gosh darn code to stop using it. You probably don’t need to be doing this, anyway. If for some reason you do require it, you probably need to dig a bit deeper in a few ways:
Is there a bug in your code that’s causing queries not to commit?
Going a little deeper, there could also be some issues with indexes, or the queries that are modifying data that are contributing to excess locking. Again, RCSI is a quick fix, and changing the connection string is a good idea if you can do it, but don’t ignore these long-term.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
