0

Studying isolation levels and blocking, more specific READ COMMITTED vs. READ COMMITTED SNAPSHOT.

In SQL Server 2014, the default isolation level is READ COMMITTED, and if I run

BEGIN TRANSACTION
SELECT top 1 * FROM Person.Person WITH (TABLOCKX, HOLDLOCK);
WAITFOR DELAY '03:00:00'
ROLLBACK TRANSACTION

and then in a new connection

SELECT * FROM Person.Person

the second query will block and hang.

In Azure SQL however, the isolation level is READ COMMITTED SNAPSHOT which seems to allow reads from Person.Person with a TABLOCKX + HOLDLOCK'ed tx in wait.

Question: How do I reproduce the blocking scenario, possibly with hints, forcing the first SELECT to block the table completely, while having READ COMMITTED SNAPSHOT on ? - as it is in Azure SQL and cannot be altered.

I have tried:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT top 1 * FROM Person.Person WITH (TABLOCKX, HOLDLOCK);
BEGIN TRANSACTION
...

and

BEGIN TRANSACTION
SELECT top 1 * FROM Person.Person WITH (READCOMMITTED);
...

Thank you.

user1997614
  • 97
  • 1
  • 9

1 Answers1

0

I cannot reproduce the situation you describe. When I turn on the isolation level to snapshot in a regular(2012 non-Azure SQL Server DB) with this: set allow_snapshot_isolation on and create a table dbo.x5 and populate it. In one session I run:

begin transaction

SELECT top 1 * FROM dbo.x5 WITH (TABLOCKX, HOLDLOCK);

WAITFOR DELAY '03:00:00'

ROLLBACK TRANSACTION

and in the other I run

set transaction isolation level snapshot

begin transaction

select * from dbo.x5 with (readcommitted)

The second session hangs.

If I don't add the 'readcommitted' hint it reads (nothing blocks snapshot isolation reads.)

According to the literature the main difference between Azure and non-Azure is the default locking settings but once it is set it acts the same way.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22