locked
Issue regarding SET ISOLATION LEVEL REPEATABLE READ RRS feed

  • Question

  • Hello All,

    I have one stored procedure and I want that stored procedure should be access by single request at a time only.

    So I have done following code in Stored procedure

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    BEGIN TRANSACTION
    DECLARE @LockResult int

    EXECUTE @LockResult = sp_getapplock
     @Resource    = 'RepeatableRead_TRANSACTION',
     @LockMode    = 'Exclusive',
     @LockTimeout = 0
      
       
    IF @LockResult <> 0
     BEGIN
       ROLLBACK TRANSACTION
       RETURN
     END
    ELSE
     BEGIN
        -- My Code

        SELECT TOP 5 * FROM Table1 WHERE STATUS <> 'READY'
        EXECUTE sp_releaseapplock @Resource = 'RepeatableRead_TRANSACTION'
        COMMIT TRANSACTION
     END

     

    Now when I check with UPDATE operation I found that when one request accessing Stored procedure at that other request also could not be able to UPDATE INTO Table1.

    So is there any solution such that I am allow to do INSERT & UPDATE while Stored procedure is lock for any request?

    I want that lock should be only in Read Operation but  allow me to UPDATE & INSERT?

    Thanks & Regards,

    Dharmesh Solanki

     

    Wednesday, December 14, 2011 7:29 AM

Answers

  • Again, have you considered Service Broker? Service Broker gives a queue handling which is built into SQL Server.

    Many of the problems you see, would not be issues if you use Service Broker.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Monday, December 19, 2011 6:41 AM
    Wednesday, December 14, 2011 11:03 PM

All replies

  • With Repeatable read you can insert data in the table but you cannot read it or modify it until the lock on that resource is held.Why do you want read operation to be blocked??

     


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Edited by Rishabh K Wednesday, December 14, 2011 8:33 AM
    Wednesday, December 14, 2011 8:33 AM
  • It's difficult to make out heads or tails of your post, because you post a piece of code that only includes a SELECT statement, but when you start talking about INSERT and UPDATE statements. Maybe you are only thinking aloud, but if you want help, it could help if you could clarify where the INSERT and UPDATE comes into play.

    By the way, it is not clear why you set the isolation level to REPEATABLE READ. With the code you have posted it is not likely to matter. REPEATABLE READ guarantees that if you read the same row twice in the same transaction, you will get the same value.

    Since you seem to be implementing a queue, the obvious question is: have you considered Service Broker?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 14, 2011 9:17 AM
  • You are setting an application lock with a call to sp_getapplock().  If your other procedures also make a call to the same application lock, they too will be locked out until this lock is released.  I really don't understand why you are using an application lock here in this procedure.

    Also, you should make a call to sp_lock in order to see the locks that have been put on your table.  Sometime, you get more than what you have asked for.

    Wednesday, December 14, 2011 9:25 AM
  • Hello All,

    Thanks all for reply..............

    I have one Window service which I want to executed from Multiple Server and window service also uses multiple thread to fetch the data from table.

    The task of thread is to get one item from Table and processed it. Now if I don't put any lock or something like that then there is situation when one of thread of one service will READ same item from Table and after some time there may situation that same task is processed by multiple thread.

    So what I want to allow any request read some data and update its status after immediately reading so when other request comes into Database won't pick up same item as its status are changed by previous request.

    And why I allow INSERT & UPDATE operation is that after reading data, Thread will process item and after processing item UPDATE its Status to database.

    If I don't allow UPDATE & INSERT then there is chance of slow down of all over operation as only one request is served by database at a time whether it is SELECT,UPDATE,INSERT.

    So my ultimate requirement is at a time only one task from database table should be processed any where in any server and by any Thread.

    Let me correct if I am wrong.

     

    Regards,

    Dharmesh Solanki

    Wednesday, December 14, 2011 10:19 AM
  • Again, have you considered Service Broker? Service Broker gives a queue handling which is built into SQL Server.

    Many of the problems you see, would not be issues if you use Service Broker.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Monday, December 19, 2011 6:41 AM
    Wednesday, December 14, 2011 11:03 PM
  • Consider Optimistic Concurrency Control:

    http://www.sqlusa.com/articles2005/rowversion/

    Pessimistic Concurrency Control has its limits.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Monday, December 19, 2011 6:43 AM