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 INSERT
s 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