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.
Subscribe