Archive for February, 2011

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

DBCC USEROPTIONS

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

SELECT TABLE [dbo].[IsoSelTopOnePlusOne]

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:

Other references:
Advertisements