Isolation Level Conflict

Question

Hi i was asked to look at a third party app recently.

There were a few queries doing giant reads and that was easy enough to fix. However, while i was working on the queries i kept seeing different transaction isolation levels being set. I've not really seen that before.

I also was looking at the wait stats and LCK_M_IX is at the top. Did some reading and that can be caused by incompatible mode on another thread.

My question is: is this happening because of the queries setting different ISOLATION LEVELS?

I'm seeing repeatable read quite a lot. And Serialisable. And read uncommitted. And the database isolation level is read committed snapshot.

And the developer has proposed adding more specific isolation levels in their c# code to fix it. I thought that might well make it worse! Which is why I'm asking the question.

Answer 1

If LCK_M_IX is at the top of your wait stats then you have a blocking problem, in particular, your writes are being blocked.

Different isolation levels introduce different locking behaviours. The default in SQL Server is READ COMMITTED. Read committed will drop locks it holds on reads once the query has completed, even if the transaction remains open, whereas REPEATABLE READ will hold the locks for the duration of the transaction

I can prove this using the StackOverflow Demo database.

In Session 1:

USE StackOverflow

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN
    SELECT TOP 10000 * FROM Posts ORDER BY ID

Session 2:

UPDATE Posts SET Body = 'Hello World' WHERE ID = 4

Session 2 is blocked.

The sp_whoisactive @get_locks = 1 output shows:

Session 1:

<Database name="StackOverflow">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="Posts" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Session 2:

<Database name="StackOverflow">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="Posts" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

So we can see my read has locked the entire table and the UPDATE is blocked.

If I run the same test in the default READ COMMITTED isolation level, Session 2 does not get blocked, even if I leave the transaction open.

Both REPEATABLE READ and SERIALIZABLE isolation levels will lock all the data that is being read by a transaction, for the duration of that transaction, blocking it from being updated - they are both more restrictive on reads than the default read committed isolation level. Serializable also blocks INSERTs who's criteria would fall into a running SELECT query so that is more restrictive still.

Is this happening because of the queries setting different ISOLATION LEVELS?

I would say it is likely the reason - you say your queries are doing "giant reads" in Repeatable Read and Serializable. If the reads are "giant" enough, they will lock the entire table for the duration of the transaction

I would ask the developer what the problem they are trying to solve is by introducing stricter isolation levels, are they really needed? Isolation levels are a trade off between consistency and concurrency.

I would also try and catch some of the blocking to understand what is happening, either by using sp_whoisactive or turning on the blocked process report

This will give you a clearer idea of what is actually happening - what the queries are, what objects the contention is on, which allows you to investigate further