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