Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    53

    Unanswered: is transaction in that case enought ?

    I have many updates table Categories during transaction:
    Code:
    ALTER PROCEDURE dbo.ChangeOrderProcedure
    	(
    	@categoryId int
    	)
    AS
    
    	DECLARE @destination int, @cat_a_width int, @cat_a_lft int, @cat_a_rgt int, @diff int;
    
    	BEGIN TRANSACTION;
    	
    		SELECT @destination = rgt FROM categories WHERE categoryid = (SELECT parentid 
    		FROM categories WHERE categoryid = @categoryId);
    		SELECT @cat_a_width = ((rgt - lft) + 1) FROM categories WHERE categoryid = @categoryId;
    			
    		UPDATE categories SET rgt = rgt + @cat_a_width WHERE rgt >= @destination;
    		UPDATE categories SET lft = lft + @cat_a_width WHERE lft >= @destination;
    			
    		SELECT @cat_a_lft = lft, @cat_a_rgt = rgt FROM categories WHERE categoryid = @categoryId;
    		SELECT @diff = @destination - @cat_a_lft;
    			
    		UPDATE categories SET rgt = rgt + @diff WHERE rgt BETWEEN @cat_a_lft AND @cat_a_rgt;
    		UPDATE categories SET lft = lft + @diff WHERE lft BETWEEN @cat_a_lft AND @cat_a_rgt;
    			
    		UPDATE categories SET rgt = rgt - @cat_a_width WHERE rgt >= @cat_a_lft;
    		UPDATE categories SET lft = lft - @cat_a_width WHERE lft >= @cat_a_lft;
    			
    	COMMIT TRANSACTION;
    
    	RETURN
    So during this transaction user shouldn't update table Categories so is default isolation level READ COMMITED enought ?

  2. #2
    Join Date
    Dec 2008
    Posts
    53
    I don't believe that my question is too difficult for such experts like you

  3. #3
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Quote Originally Posted by chomik View Post
    So during this transaction user shouldn't update table Categories so is default isolation level READ COMMITED enought ?
    Read committed will write-lock all rows that are being updated preventing any other transaction from changing or reading the row (unless said other transactions are read-uncommitted that is) which would satisfy your criteria for not allowing any other user to update the rows during your transaction.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •