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

by Kofi Sarfo 26. June 2009 00:45

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.

Tags:

SQL Server

Listening to folks talk REST whilst running 10km

by Kofi Sarfo 12. June 2009 02:24

Caught .NET Rocks show #445 with Kenn Scribner on REST. A useful discussion about REST, SOAP, the evolution of 'web services' technology and where WCF best fits in. Listen here.

Notes:

.NET Rocks talk transcript

Kiva Loans

  • Jackline

    Jackline

    Quarrying

    Requested loan: $300

    Amount raised: $0

    Mombasa, Kenya

    to buy a stone cutting machine and to boost her business.

    Loan Now »

  • Cecile

    Cecile

    Cereals

    Requested loan: $700

    Amount raised: $0

    Ouagadougou, Burkina Faso

    to buy 5 bags of fonio (millet) to save and improve the living conditions of her family.

    Loan Now »

  • Ambrose

    Ambrose

    Agriculture

    Requested loan: $825

    Amount raised: $0

    Lira, Uganda

    to buy agro-produce to sell in order to increase his income and educate his children.

    Loan Now »

To see more entrepreneurs »

Make a loan to an entrepreneur across the globe for as little as $25. Kiva is the world's first online lending platform connecting online lenders to entrepreneurs across the globe.