locked
Cannot set transaction isolation level to REPEATABLE READ RRS feed

  • Question

  • I'm trying to write a stored procedure that contains a transaction. The thing is that I need to lock a few rows for update and I don't want other users be able to read them at the same time. Since they could end up making the same update exactly and I want to avoid that.

     

    So I use the readpast optimizer and an error raised that said I couldn't use it unless the transaction isolation level was READ COMMITTED o REPEATABLE READ; so I'm trying to set any one of those levels but I keep getting the same error.

     

    As you can see I'm kind of  newbie with locking and stuff, so any comment would be much appreciated! =)

     

    Here's the code for my SP so far:

     

    ALTER PROCEDURE [dbo].[exportarDocs] 
    AS
    DECLARE @exportables TABLE
      (id bigint,
      documento varbinary(MAX),
      plantilla varchar(250))
    BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    BEGIN TRANSACTION;
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
      -- Insert statements for procedure here
    	INSERT INTO @exportables
    	SELECT id, documento, plantilla FROM documentos WITH (READPAST, ROWLOCK, HOLDLOCK)
    	WHERE estado like 'EX' and plantilla is not null
    	ORDER BY plantilla;
    	
    	UPDATE documentos SET estado = 'YA' 
    	WHERE estado like 'EX' and plantilla is not null;
    	
    	IF @@ERROR <> 0
    	BEGIN
    		RAISERROR ('Error al exportar localmente un documento (ExportarDocs)', 16, 1);
    		ROLLBACK;
    	END
    	
    	SELECT * FROM @exportables;
    	COMMIT TRANSACTION;
    END
    

    Wednesday, March 23, 2011 2:33 PM

Answers

  • You cannot use both READPAST and HOLDLOCK as hints to the same statement.  HOLDLOCK is equivalent to setting the transaction isolation level to "SERIALIZEABLE", so it cannot be used with READPAST.

    You will have to find some other way to avoid the deadlocks.  One thing to try would be to load the @exportables table with the same statement that does the update.  Then you should not need the HOLDLOCK hint.  For example,

    ALTER PROCEDURE [dbo].[exportarDocs] 
    AS
    DECLARE @exportables TABLE
     (id bigint,
     documento varbinary(MAX),
     plantilla varchar(250))
    BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    BEGIN TRANSACTION;
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
     -- Insert statements for procedure here
    	--INSERT INTO @exportables
    	--SELECT id, documento, plantilla FROM documentos WITH (READPAST, ROWLOCK, HOLDLOCK)
    	--WHERE estado like 'EX' and plantilla is not null
    	--ORDER BY plantilla;
    	
    	UPDATE documentos SET estado = 'YA' 
    	OUTPUT inserted.id, inserted.documento, inserted.plantilla INTO @exportables(id, documento, plantilla)
    	WHERE estado like 'EX' and plantilla is not null;
    	
    	IF @@ERROR <> 0
    	BEGIN
    		RAISERROR ('Error al exportar localmente un documento (ExportarDocs)', 16, 1);
    		ROLLBACK;
    	END
    	
    	SELECT * FROM @exportables;
    	COMMIT TRANSACTION;
    END
    

    Tom

    • Proposed as answer by Naomi N Wednesday, March 23, 2011 3:30 PM
    • Marked as answer by Adrianita Wednesday, March 23, 2011 10:27 PM
    Wednesday, March 23, 2011 3:22 PM

All replies

  • What error do you get? Remove those hints and try again

    WITH (READPAST, ROWLOCK, HOLDLOCK)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 23, 2011 2:43 PM
    Answerer
  • Thank you Uri for your answer.

     

    The error I get is: 'You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels'. I get this error only when I try to execute the procedure. No when I try to alter it.

     

    And I cannot remove the hints because ROWLOCK and HOLDLOCK is what I want to do. I mean:

    If I have 2 or more connections and they both try to execute the SP "exportarDocs" at the same time I don't want user 2 to be able to do the same select user 1 has made, I want user 2 to ignore the rows that user one has selected. And I read that rowlock would do that, I understood that rowlock would make those rows innaccesible for other users to read and the holdlock would keep them lock until the end of the transaction. Is this not correct?

    I'd like to add that if I don't use READPAST one of my transactions always get deadlock. I was trying to avoid that by using READPAST and that's how I got into this touble.
    Wednesday, March 23, 2011 2:54 PM
  • You are inserting into a temporary table (variable) that lives in scope of that procedure, I mean user1 will get its @exportables and user2 will get @exportables Perhaps you do not need to use a temporary table but issue

    SELECT id, documento, plantilla FROM documentos  WHERE estado like 'EX' and plantilla is not null
    ORDER BY plantilla;

    within a transaction in SP???


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 23, 2011 3:16 PM
    Answerer
  • You are inserting into a temporary table (variable) that lives in scope of that procedure, I mean user1 will get its @exportables and user2 will get @exportables Perhaps you do not need to use a temporary table but issue

    SELECT id, documento, plantilla FROM documentos  WHERE estado like 'EX' and plantilla is not null
    ORDER BY plantilla;

    within a transaction in SP???


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 23, 2011 3:16 PM
    Answerer
  • You cannot use both READPAST and HOLDLOCK as hints to the same statement.  HOLDLOCK is equivalent to setting the transaction isolation level to "SERIALIZEABLE", so it cannot be used with READPAST.

    You will have to find some other way to avoid the deadlocks.  One thing to try would be to load the @exportables table with the same statement that does the update.  Then you should not need the HOLDLOCK hint.  For example,

    ALTER PROCEDURE [dbo].[exportarDocs] 
    AS
    DECLARE @exportables TABLE
     (id bigint,
     documento varbinary(MAX),
     plantilla varchar(250))
    BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    BEGIN TRANSACTION;
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
     -- Insert statements for procedure here
    	--INSERT INTO @exportables
    	--SELECT id, documento, plantilla FROM documentos WITH (READPAST, ROWLOCK, HOLDLOCK)
    	--WHERE estado like 'EX' and plantilla is not null
    	--ORDER BY plantilla;
    	
    	UPDATE documentos SET estado = 'YA' 
    	OUTPUT inserted.id, inserted.documento, inserted.plantilla INTO @exportables(id, documento, plantilla)
    	WHERE estado like 'EX' and plantilla is not null;
    	
    	IF @@ERROR <> 0
    	BEGIN
    		RAISERROR ('Error al exportar localmente un documento (ExportarDocs)', 16, 1);
    		ROLLBACK;
    	END
    	
    	SELECT * FROM @exportables;
    	COMMIT TRANSACTION;
    END
    

    Tom

    • Proposed as answer by Naomi N Wednesday, March 23, 2011 3:30 PM
    • Marked as answer by Adrianita Wednesday, March 23, 2011 10:27 PM
    Wednesday, March 23, 2011 3:22 PM
  • Thank you Tom!

     

    I think that might be it. But just to be sure. If I alter my procedure according to your suggestiong. Will there be a posibility that 2 or more users would update the same rows? Because if 2 or more users excecute this procedure at the same time they souldn't be able to update the same rows, I want them to get different sets of rows to update.

    Wednesday, March 23, 2011 3:46 PM
  • Why not simply perform the UPDATE first, marking the records as exported, and then do whatever constitutes exporting them? Since you are in a transaction, you simply need to ensure you ROLLBACK upon any error, and then you're certain nothing is marked as exported without actually having been exported.

    With default transaction isolation level (READ COMMITTED) for all connections this alone would be enough to eliminate any concurrency issues.

    Please note that you cannot actually prevent anyone else from reading the rows by setting the isolation level in this procedure - you can cause locks to be issued, but it's up to each connection to choose it's isolation level and another process using READ UNCOMMITTED would still be able to do just that (read uncommitted changes) regardless of what isolation level you use.

    To see this, insert a WAITFOR DELAY '00:01:00' just prior to COMMIT, run the proc, and issue "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" and "SELECT * FROM [documentos]" on another connection (each tab in Management Studio has it's own connection of course) before the minute is up.

    Wednesday, March 23, 2011 3:46 PM
  • I already checked Tom's suggestion.

     

    I currently have 14 rows in the table "documentos" that are estado like 'EX' and made some threads to call the SP. And sometimes both returned with the 14 rows. That's not a desired result for me.

     

    I need that if there are 14 either one gets the whole 14 rows or some partial sum each that will sum up 14. ie. 7 and 7 or 8 and 6 or 5 and 9, etc

    Wednesday, March 23, 2011 4:08 PM
  • I already checked Tom's suggestion.

     

    I currently have 14 rows in the table "documentos" that are estado like 'EX' and made some threads to call the SP. And sometimes both returned with the 14 rows. That's not a desired result for me.

     

    I need that if there are 14 either one gets the whole 14 rows or some partial sum each that will sum up 14. ie. 7 and 7 or 8 and 6 or 5 and 9, etc


    When you do the update, the rows that are changed are locked so that no other process can update those rows.  So. two processes cannot possibly get and update the same rows.  The first process will lock the rows, update them, and then load your table variable @exportables.  A second process that came along would have to wait until the first process either committed or rolled back the changes.  And, of course, if the changes were committed, the second process would not find and update the same rows because you have changed the value of the estado column so that the WHERE condition is no longer true.

    So something mst be going on.  It's hard for us to know exactly what is happening without seeing everything that you are doing in calling this stored proc.  But my first guess would be that you are (at least sometimes) already in transaction state when this stored proc is called.  If that happens, then you do the Begin Transaction, do the update, then you do a Commit - but this Commit does not actually commit the changes to the database.  They will only be committed when the outer transaction is committed.  If a second process now calls the stored proc, it will wait at the update because these rows are still locked.  Meanwhile, the first process now returns the 14 rows to your client.  If the first process now does a Rollback either explicitly because it executed a ROLLBACK or implicitly for some reason, like the first process closed the connection when still in transaction state or got an error that forces a rollback, the changes to those 14 rows will be rolled back.  Now the rows are free again, but they have not been changed.  So the second process wakes up and grabs the same 14 rows, does the update, and returns the same 14 rows again.

    If you want to be sure this stored proc never returns the same rows, you could add a check at the beginning of the stored proc to check the value of @@TRANCOUNT.  If it is not zero, then you are already in transaction state and you may want to just raise an error and exit.

    If, on the other hand, you want to be able to call this proc when you are already in transaction state, the process that calls this proc must be written in such a way that if it later gets an error that makes a rollback happen, the fact that some other call to this stored proc will now get the same rows back will be properly handled.

    There might something else going on, but the above is my best guess.

    Tom

    Wednesday, March 23, 2011 10:07 PM
  • Thank you Tom for your patience! You were right indeed! I was not emptying the dataTable I was filling with the result of the stored procedure between calls to the SP (I looping) and that's why I got 14 in both DTs. Because in one round the firts process won and then the other so both appeared to have 14 at the same time when the thing was that one was loaded from the previous call.

     

    Thank you very much. And thank you for your second reply to because it helped me clear some doubts as well...

    Wednesday, March 23, 2011 10:26 PM