Software Vendor Mistakes With SQL Server: Explicit And Implicit Transactions

Holler And Cuss


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)
  • Didn’t realize how god derned awful Implicit Transactions can be

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 on 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.

It’s Not Just You: Blocked Process And Deadlock XML Is Misleading

Lucky Pencil


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:

<data name="object_id">
  <type name="int32" package="package0" />
  <value>0</value>
</data>
<data name="index_id">
  <type name="uint32" package="package0" />
  <value>0</value>

Second, the lock mode will say S, which is possible when readers block writers, but…

<data name="lock_mode">
  <type name="lock_mode" package="sqlserver" />
  <value>3</value>
  <text>S</text>

Third, the statements that will be shown as blocking each other are both selects from different tables.

Blocked:

<inputbuf>
SELECT TOP (100)
    p.*
FROM dbo.Posts AS p
WHERE p.ParentId = 0
ORDER BY p.Score DESC;   
</inputbuf>

Blocking:

<inputbuf>
SELECT TOP (10000)
    u.*
FROM dbo.Users AS u
WHERE u.Reputation = 2
ORDER BY u.Reputation DESC;   
</inputbuf>

What Can You Do?


Not a whole lot. This stuff is just plain broken outside of very simple blocking and deadlocking patterns.

Monitoring tools that rely on these things to show you issues are going to bring back really confusing details.

You might think it would just be a matter of decoding the wait resource, but that’s a doggone annoying process on its own.

Thanks for reading!

Be Careful With SET ANSI_DEFAULTS ON In SQL Server

Secret Agent Plan


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:

Review the required SET options for filtered index creation in CREATE INDEX (Transact-SQL) syntax

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 on 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.

Implicit Transactions: Why Unrelated Queries Block Each Other In SQL Server

A Bit Sensational


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.

sp_WhoIsActive Blocking
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:

<Database name="StackOverflow2013">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="Posts" schema_name="dbo">
      <Locks>
        <Lock resource_type="KEY" index_name="PK_Posts_Id" request_mode="X" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK_Posts_Id" request_mode="IX" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

We may also note something else quite curious about the output. The select from Users is sleeping, with an open transaction.

sp_WhoIsActive Blocking
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:

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 on 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.