Concurrent Behaviour at Transaction Isolation Levels

Task: to determine the behaviour of particular transaction isolation levels with respect to concurrent SQL query …SELECT TOP(1) […] + 1…
Solution: see Figure 2

USE [Test_IsolationLevels]
GO

Source code 1: Use database

CREATE TABLE [dbo].[IsoSelTopOnePlusOne](
 [Identity] [int] IDENTITY(1,1) NOT NULL,
 [TopOnePlusOne] [int] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

Source code 2: Create table

-- 0: (NOTE: default isolation level)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
---- 1:
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
---- 2:
--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
---- 3:
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
---- 4:
--ALTER DATABASE [Test_IsolationLevels] SET ALLOW_SNAPSHOT_ISOLATION ON
--SET TRANSACTION ISOLATION LEVEL SNAPSHOT
---- 5: (NOTE: set up failed)
--ALTER DATABASE [Test_IsolationLevels] SET READ_COMMITTED_SNAPSHOT ON
GO

DBCC USEROPTIONS
GO

Source code 3: Set up and select user options

Figure 1: SSMS result of DBCC USEROPTIONS

CREATE PROCEDURE [dbo].[IsoSelTopOnePlusOne_Ins]
AS
BEGIN
 -- 0: (NOTE: default isolation level)
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 ---- 1:
 --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 ---- 2:
 --SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 ---- 3:
 --SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 ---- 4:
 --SET TRANSACTION ISOLATION LEVEL SNAPSHOT

 BEGIN TRANSACTION IsoSelTop1PlusOne 

  DECLARE @Top1PlusOne INT
  SET @Top1PlusOne =
  (
   SELECT TOP(1) [TopOnePlusOne] + 1
   FROM [dbo].[IsoSelTopOnePlusOne]
   ORDER BY [TopOnePlusOne] DESC
  )
  IF
  (
   @Top1PlusOne
  ) IS NULL
  BEGIN
   SET @Top1PlusOne = 0
  END

  INSERT INTO [dbo].[IsoSelTopOnePlusOne]
  (
   [TopOnePlusOne],
   [ModifiedDate]
  )
  VALUES
  (
   @Top1PlusOne,
   GETDATE()
  )

  WAITFOR DELAY '00:00:10'

 COMMIT TRANSACTION IsoSelTop1PlusOne
END
GO

Source code 4: Create procedure

EXEC [dbo].[IsoSelTopOnePlusOne_Ins]
GO

Source code 5: Execute procedure

Figure 2: SSMS result of concurrent behaviour at the transaction isolation levels

Measuring results:

[Identity]	Isolation Level	[TopOnePlusOne]	match/mismatch
1-2	ISOLATION LEVEL READ COMMITTED	0, 1	mismatch
3-4	ISOLATION LEVEL READ UNCOMMITTED	2, 3	mismatch
5-6	ISOLATION LEVEL REPEATABLE READ	4, 5	mismatch
7-8	ISOLATION LEVEL SERIALIZABLE	6, 7	mismatch
9-10	ISOLATION LEVEL SNAPSHOT	8, 8	match

The measurement above included 2 concurrent SQL queries (two SSMS windows). The second query started up approximately in half of the first SQL query.

Additional references:

• DBCC USEROPTIONS
  by MSDN
• Isolation (database systems)
  by Wikipedia
• Isolation Levels in the Database Engine
  by MSDN
• SET TRANSACTION ISOLATION LEVEL
  by MSDN
• SQL SERVER – Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction
  by Pinal Dave
• SQL SERVER – Concurrency Basics – Guest Post by Vinod Kumar
  by Vinod Kumar
• SQL SERVER – Concurrancy Problems and their Relationship with Isolation Level
  by Pinal Dave
• SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions
  by Pinal Dave

Other references:

• SQL Server: Locking basics
  by Vinod Kumar
• SQL SERVER – Locking and Blocking – Important Aspect of Database and Effect on Performance – Quiz – Puzzle – 5 of 31
  by Pinal Dave
• SQLServer Quiz 2012 – ANSI ISOLATIONS levels are very important part of any database
  by Beyond Relational