Transaction Isolation / Dirty Reads / Deadlocks Demo

merrillaldrich — Wed, 29 Jul 2009 22:44:00 GMT

I have just given a talk with my group at work on the basics of transaction isolation, dirty reads (nolock) and deadlocks, just as a way to get eveyone on the same page about the tradeoffs between concurrency on a busy transactional system and accuracy of reports. I essentially repackaged a very good example posted by Alexander Kuznetsov into a simpler script that I could demo to people who are less T-SQL saavy. I hope that he will forgive my re-use of his scenario - I give him full credit for inventing the example :-). Since I'd put together the scripts, I thought I'd throw them up here in case anyone else wants a quick way to demo this. Others have posted similar demos with different emphasis. This particular demo has the advantage (?) that it is very deadlock prone, by the nature of the schema. Both deadlocks and inaccurate dirty reads are readily reproducible.

So, in a test database, I created two tables, one Entities (People) and one Accounts. In the Entities table, I inserted 1000 "random" people. In Accounts, two rows for each person: account 1 and account 2, with a column for the balance. Everyone gets $50 in each account. I then created a script that would perform repeated transactions, moving $10 for one random person from one of his/her two accounts to the other account. The gist of the demo is to run several simultaneous connections with that script, to simulate load on the system, then run aggregate queries on the accounts and show scenarios for totals errors and for deadlocks.

Here's the basic setup script:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE dbo.Entities(
   EntityID INT IDENTITY(1000,1) NOT NULL,
   LastName NVARCHAR(100) NOT NULL,
   FirstName NVARCHAR(100) NOT NULL,
 CONSTRAINT PK_Entities PRIMARY KEY CLUSTERED (
   EntityID ASC
  )
)
GO

CREATE TABLE dbo.Accounts(
   EntityID INT NOT NULL,
   AccountID INT NOT NULL,
   Amount decimal(18, 2) NOT NULL,
 CONSTRAINT PK_Accounts PRIMARY KEY CLUSTERED (
   EntityID ASC,
   AccountID ASC
  )
)
GO

ALTER TABLE dbo.Accounts  WITH CHECK ADD  CONSTRAINT FK_Accounts_Entities FOREIGN KEY(EntityID)
REFERENCES dbo.Entities (EntityID)
GO

ALTER TABLE dbo.Accounts CHECK CONSTRAINT FK_Accounts_Entities
GO


-- Make 1000 "People"

DECLARE @i INT
SET @i = 1
WHILE @i <= 1000
BEGIN
   INSERT INTO dbo.Entities ( FirstName, LastName ) VALUES ( '', '' )
   -- Note: you can instead fill in 1000 random names to add a little realism,
   -- but it's not required
   SET @i = @i + 1
END
-- Fund the accounts with $50 each

INSERT INTO dbo.Accounts ( EntityID, AccountID, Amount )
SELECT Entities.EntityID, 1 AS AccountID, 50.00 FROM dbo.Entities

INSERT INTO dbo.Accounts ( EntityID, AccountID, Amount )
SELECT Entities.EntityID, 2 AS AccountID, 50.00 FROM dbo.Entities

To add a bit of realism, you can associate random names to the Entities table, using this technique.

Then, we have a script that mimics an app connection performing a lot of transactions. The script just moves money from one account to another, so the total balance in the system should remain the same, as well as the balance for every person, making it easy to repro inaccuracies from dirty reads. In a demo, I typically paste this into four separate query windows and get them all running at the same time. They do deadlock periodically, which is a good discussion point. There's logic to continue running after a deadlock:


-- Repeatedly move money for random people

DECLARE @randPerson INT
DECLARE @randAccount INT
DECLARE @i INT; SET @i = 0
WHILE ( @i < 100000 )
BEGIN
   SET @randPerson = ( CAST( RAND( checksum( NEWID() ) ) * 1000 AS INT ) % 1000 ) + 1000;
   SET @randAccount = ( CAST ( RAND( checksum( NEWID() ) ) * 1000  AS INT ) % 2 ) + 1;
   PRINT @randPerson;
   PRINT @randAccount;
   
   BEGIN TRY
       BEGIN TRAN;
           UPDATE dbo.Accounts SET Amount = Amount - 10 
               WHERE EntityID = @randPerson AND AccountID = @randAccount;
           WAITFOR delay '00:00:00.01';
           UPDATE dbo.Accounts SET Amount = Amount + 10 
               WHERE EntityID = @randPerson AND AccountID != @randAccount;
       COMMIT;
   END TRY
   
   BEGIN CATCH
       PRINT ERROR_MESSAGE();
       ROLLBACK;
       RAISERROR('Error!', 11, 127);
   END CATCH;
   
   SET @i = @i + 1;
   WAITFOR delay '00:00:00.01';
END;

With that script running (x4), in a fifth separate query you can run the following aggregates one at a time to see or demonstrate what is accurate, what deadlocks, and so on. It helps to fire them several times and see the variation in results:


/* code to demo aggregate errors */

SELECT SUM( Amount ) FROM dbo.Accounts (nolock)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SUM( Amount ) FROM dbo.Accounts 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT SUM( Amount ) FROM dbo.Accounts 

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT SUM( Amount ) FROM dbo.Accounts

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT SUM( Amount ) FROM dbo.Accounts (tablock)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT SUM( Amount ) FROM dbo.Accounts 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT SUM( Amount ) FROM dbo.Accounts (tablock)

-- alter database TransIsolationDemo set ALLOW_SNAPSHOT_ISOLATION on

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT SUM( Amount ) FROM dbo.Accounts 


SELECT * FROM dbo.Entities e 
WHERE (SELECT SUM(Amount) FROM dbo.Accounts (nolock) WHERE Accounts.EntityID = e.EntityID) != 100.00

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM dbo.Entities e 
WHERE (SELECT SUM(Amount) FROM dbo.Accounts WHERE Accounts.EntityID = e.EntityID) != 100.00

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM dbo.Entities e 
WHERE (SELECT SUM(Amount) FROM dbo.Accounts WHERE Accounts.EntityID = e.EntityID) != 100.00

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT * FROM dbo.Entities e 
WHERE (SELECT SUM(Amount) FROM dbo.Accounts WHERE Accounts.EntityID = e.EntityID) != 100.00

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT * FROM dbo.Entities e 
WHERE (SELECT SUM(Amount) FROM dbo.Accounts (tablock) WHERE Accounts.EntityID = e.EntityID) != 100.00

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT * FROM dbo.Entities e 
WHERE (SELECT SUM(Amount) FROM dbo.Accounts WHERE Accounts.EntityID = e.EntityID) != 100.00

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT * FROM dbo.Entities e 
WHERE (SELECT SUM(Amount)) FROM dbo.Accounts (tablock) WHERE Accounts.EntityID = e.EntityID) != 100.00

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM dbo.Entities e 
WHERE (SELECT SUM(Amount) FROM dbo.Accounts WHERE Accounts.EntityID = e.EntityID) != 100.00

As many others have explained, the only ones that are 100% accurate are Repeatable Read and higher isolation levels. Read Committed and lower will give variable sums both for the table and for each simulated "person." An exception is if you explicitly use (tablock), which is interesting but probably unsuitable for production :-). The demo also shows the opposite problem, which is the increasing likelihood of blocking and deadlocks as you tighten the isolation level. Advantages of Snapshot are also apparent.

Lastly, it's a decent example to explain deadlocks, and also how to avoid them. In this particular case (this isn't generalizable to other cases) most deadlocks are prevented by locking the two account rows for a person at the beginning of the transaction, instead of having one lock, then a pause, then the other:

-- Repeatedly move money for random people avoiding (most) deadlocks

DECLARE @randPerson INT
DECLARE @randAccount INT
DECLARE @i INT; SET @i = 0
WHILE ( @i < 100000 )
BEGIN
   SET @randPerson = ( CAST( RAND( checksum( NEWID() ) ) * 1000 AS INT ) % 1000 ) + 1000;
   SET @randAccount = ( CAST ( RAND( checksum( NEWID() ) ) * 1000  AS INT ) % 2 ) + 1;
   PRINT @randPerson;
   PRINT @randAccount;
   
   BEGIN TRY
       BEGIN TRAN;
       
           -- Acquire locks on both account rows at the same time:
           SELECT @temp = Amount FROM dbo.Accounts WITH (rowlock, xlock, HOLDLOCK)
           WHERE entityid = @randperson;

           UPDATE dbo.Accounts SET Amount = Amount - 10 
               WHERE EntityID = @randperson AND AccountID = @randAccount;
           WAITFOR delay '00:00:00.01';
           UPDATE dbo.Accounts SET Amount = Amount + 10 
               WHERE EntityID = @randperson AND AccountID != @randAccount;
       COMMIT;
   END TRY
   
   BEGIN CATCH
       PRINT ERROR_MESSAGE();
       ROLLBACK;
       RAISERROR('Error!', 11, 127);
   END CATCH;
   
   SET @i = @i + 1;
   WAITFOR delay '00:00:00.01';
END;

That's it. Other good examples and discussions are out there:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/04/10/selects-under-read-committed-and-repeatable-read-may-return-incorrect-results.aspx

http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

http://blogs.neudesic.com/blogs/phil_scott/archive/2005/12/05/11.aspx

Merrill Aldrich : NoLock

Transaction Isolation / Dirty Reads / Deadlocks Demo