email article printer friendly

Increase Transaction Isolation Level, Even With Sub-Selects
by Jimmy Nilsson

June 2003 Issue

Technology Toolbox: VB.NET, C#, SQL Server 2000, ASP.NET, VB6

Level: Intermediate

It's common to hear that you don't need to increase the Transaction Isolation Level to Repeatable Read or Serializable if you do the reading before writing in one single expression, with the help of a sub-select. Unfortunately, this isn't true.

Here's the ordinary code for a certain task:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

SET @theSum = 
(SELECT SUM(x) 
FROM OneTable)
   

Here's the code "optimized" with a sub-select:

INSERT INTO AnotherTable
(y)
SELECT SUM(x) 
FROM OneTable

It's true that you don't need an explicit transaction, but you do need to increase the transaction isolation level. To prove this, I ran this code 1 million times from two different SQL Query Analyzer windows:

INSERT INTO YetAnotherTable
(Id, Description)
SELECT COALESCE(MAX(Id),0)+1, 'Testing' 
FROM YetAnotherTable

I got a duplicate key around 20 times.

By changing to Serializable, I had a couple of deadlocks. This is to be expected in this case, because you're escalating the lock level. Using WITH (UPDLOCK) helps. This code works without errors:

INSERT INTO YetAnotherTable
(Id, Description)
SELECT COALESCE(MAX(Id),0)+1, 'Testing' 
FROM YetAnotherTable WITH (UPDLOCK)

The moral of the story is that if correctness is important for you, the same rules regarding Transaction Isolation Level apply to sub-selects too—not only to "read before" selects.

About the Author
Jimmy Nilsson is the owner of the Swedish consulting company JNSK AB. He has been working with system development for more than 14 years. He has written numerous technical articles and is the author of .NET Enterprise Design with Visual Basic .NET and SQL Server 2000. Reach Jimmy at .