It's easier to forget the details of SQL Transaction Isolation levels (during interview)

So I interviewed again yesterday with a City-based asset management company you've probably not heard of but a cash rich client is exactly what we need right now. Shaking off a cold hit-hard mid-week wasn't ideal circumstance but it wasn't feeling less than fabulous that did the trick but in part, in fact, not being able to remember how each of the SQL Server Transaction Isolation levels might impact queries on the same table.

This is partly why I like interviews. Potentially embarrassing situations during which one is able to demonstrate what one cannot remember. Here's what the MSDN entry has to say about the SET TRANSACTION ISOLATION LEVEL (Transact-SQL) command which controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. Yay.

So the story in a nutshell is that using this command it's possible, amongst other things, to allow dirty reads if that's the desired result. In order to try the code below in SQL Management Studio use one tab to run the transaction once all the necessary database objects have been created and another tab to view the results of the different isolation levels.

We create a table to use for holding some random values.

CREATE TABLE RandomLetters
(
     ID int IDENTITY(1,1) NOT NULL
    ,Letter varchar(32) NOT NULL
) 

In order to populate this table with some random values we rely on a stored procedure inspired by one over here.

CREATE PROCEDURE sp_GenerateRandomLetters
     @Length int
    ,@randomLetters varchar(32) OUT
AS
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharactersLength int
DECLARE @ValidCharacters varchar(255)

SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$'

SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomLetters = ''

SET NOCOUNT ON

SET @counter = 1
WHILE @counter < (@Length + 1)
BEGIN
        SET @RandomNumber = Rand()
        SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))
        SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
        SET @counter = @counter + 1
        SET @RandomLetters = @RandomLetters + @CurrentCharacter
END

And because we're going to populate this table with more than the one record:

DECLARE @randomLetters varchar(32)
DECLARE @randomLettersCount int

SELECT @randomLettersCount = 0

WHILE (@randomLettersCount < 1000)
BEGIN
        EXEC sp_GenerateRandomLetters 12, @randomLetters OUT
        INSERT INTO RandomLetters (Letter)
        SELECT    @randomLetters
        SELECT @randomLettersCount = @randomLettersCount + 1
END

None of this is even nearly shattering. It's not meant to be. Also, the following ought to error the first time it's run.

DROP TABLE ##OriginalRandomLetters

Now let's make that table which gets dropped by the statement above.

CREATE TABLE ##OriginalRandomLetters
    (
         ID int NOT NULL
        ,Letter varchar(32) NOT NULL
    )

And we sync this global temporary table with those values in the permanent table created above

INSERT INTO ##OriginalRandomLetters
    SELECT ID, Letter
    FROM RandomLetters

Confirm that we are good.

SELECT * FROM ##OriginalRandomLetters

And back on that other tab and therefore using another connection...

BEGIN TRANSACTION

    DECLARE @RandomUpdateCount int
    DECLARE @RandomID int
    DECLARE    @randomLetters varchar(32)

    SELECT @RandomUpdateCount = 0

    SET NOCOUNT ON

    WHILE @RandomUpdateCount < 20000
    BEGIN
        SELECT @RandomID = 1 + RAND() * 999
        -- SELECT @RandomID '@RandomID'

        EXEC sp_GeneratePassword 12, @randomLetters OUT
        -- SELECT @randomLetters '@randomLetters'

        UPDATE    RandomLetters
        SET        Letter = @randomLetters
        WHERE    ID = @RandomID

        SELECT @RandomUpdateCount = @RandomUpdateCount + 1
    END

ROLLBACK TRANSACTION

And finally let's try one of these transaction isolation levels

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

And the result of the following query will depend on the chosen isolation level.

SELECT * FROM RandomLetters

For example the READ UNCOMMITTED isolation level will allow you to see the changes being made to the values in the RandomLetters table whilst the transaction to update values is being run whereas most of the other isolation levels will cause SELECT * FROM RandomLetters to hang until the transaction is complete. In the case of SNAPSHOT there's an error: Snapshot isolation transaction failed accessing database 'WimiroDemo' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

I might be forgiven for not remembering all this behaviour during interview but not having a handle immediately on the correct strategy for ASP.NET paging and the 10 steps of the ASP.NET Page Life Cycle might have been forgetfulness too far. But that's what the internet is for, I say. Fingers crossed I get the gig anyway.