With our left-right implementation of trees (preorder tree traversal) it’s important to have a look at different updates at the same time. We have found some problems and to resolve this we need to make some transactions Serializable. Have googled some time for this and read the Concurrency chapter of Martin Fowlers “Patterns of enterprise application architecture” book. There’s a part explaining “Reducing Transaction Isolation for Liveness” that explains this well.

Check this link for isolation levels in MSSQL2005.

There are four levels

  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable

Use serializable isolation if you want to be sure of correctness. It does mess up your liveness so be aware of that. Look at each transaction to decide which isolation level is best suited.

Example in MSSQL2005:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

/****** Object:  StoredProcedure [dbo].[sp_Edumatic3_MoveTreeStructure]   
Script Date: 08/14/2008 20:00:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Edumatic3_MoveTreeStructure]
    @TreeStructureId INT,
    @ParentTreeStructureId INT,
    @INDEX INT
AS
BEGIN
    SET NOCOUNT ON;
 
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
    DECLARE @TranName VARCHAR(20);
    SELECT @TranName = 'MoveTreeStructure';
 
    BEGIN TRANSACTION @TranName
 
        BEGIN TRY	
            ...
 
            COMMIT TRANSACTION @TranName
 
        END TRY
 
    BEGIN CATCH
 
        /* If an error occured we Roll Back the transaction and raise an error that VS can catch */
 
        ROLLBACK TRANSACTION @TranName
        RAISERROR 50001 'Error while trying to move. Rollback has been executed.'
 
    END CATCH
END
pixel Serializing Transactions in MSSQL2005