locked
Row Level Locking in Sql Server RRS feed

  • Question

  • Hi,

    We have one table.We have updated the status for one column to 1 and we did not committed the transaction. Can we do an another update on another row.

    If you can see below example i am updating GEO_D and transaction is not committed. Now my requirement is we have to update other records (not Geo_D). If  try to update GEo_D it should wait. Can you please give me solution for this. 

    IF OBJECT_ID('TEMPDB..##TEMP_STSTUS') IS NOT NULL
    DROP TABLE ##TEMP_STSTUS

    CREATE TABLE ##TEMP_STSTUS
    (
      ID INT IDENTITY(1,1)
      ,NAME VARCHAR(10)
      ,STSTUS VARCHAR(10)

    )
    INSERT INTO ##TEMP_STSTUS SELECT 'GEO_D','0'
    INSERT INTO ##TEMP_STSTUS SELECT 'PROD_D','0'
    INSERT INTO ##TEMP_STSTUS SELECT 'CUST_D','0'
    INSERT INTO ##TEMP_STSTUS SELECT 'DT_DAY_D','0'



    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    GO
    BEGIN TRANSACTION
    UPDATE A
    SET STSTUS=1
    FROM ##TEMP_STSTUS A 
    WHERE NAME='GEO_D'


    Thanks and Regards Rajesh

    Wednesday, April 22, 2015 1:27 PM

Answers

  • Thanks All,

    Now I am able to do it with out index also. Using READPAST.

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    GO
    BEGIN TRANSACTION
    UPDATE A
    SET STSTUS=1
    FROM ##TEMP_STSTUS A  (READPAST)
    WHERE NAME='GEO_D'

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    GO
    BEGIN TRANSACTION
    UPDATE A
    SET STSTUS=1
    FROM ##TEMP_STSTUS A  (READPAST)
    WHERE NAME='CUST_D'


    Thanks and Regards Rajesh

    • Marked as answer by Eric__Zhang Monday, May 4, 2015 9:33 AM
    Friday, April 24, 2015 7:03 AM

All replies

  • Hi,

    http://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server

    • Proposed as answer by Ibrahim Mehdi Wednesday, April 22, 2015 1:42 PM
    Wednesday, April 22, 2015 1:32 PM
  • try Update with (ROWLOCK) hint

    as is being discussed and example given at RowLevelLock

    Details on all available table hints at

    https://msdn.microsoft.com/en-us/library/ms187373.aspx

    Details on how to use update statement with TableHint at 

    https://msdn.microsoft.com/en-us/library/ms177523.aspx


    Thanks and Regards, Ibrahim Mehdi. MCSE-DataPlatform

    • Proposed as answer by Ibrahim Mehdi Wednesday, April 22, 2015 1:41 PM
    • Unproposed as answer by Jingyang Li Monday, January 8, 2018 3:02 PM
    Wednesday, April 22, 2015 1:40 PM
  • With a proper table design this happens automatically, and without it no amount of lock hints will help.

    use tempdb
    go
    
     CREATE TABLE TEMP_STATUS
     (
        NAME VARCHAR(10) PRIMARY KEY
       ,STATUS VARCHAR(10)
     )
     
     INSERT INTO TEMP_STATUS SELECT 'GEO_D','0'
     INSERT INTO TEMP_STATUS SELECT 'PROD_D','0'
     INSERT INTO TEMP_STATUS SELECT 'CUST_D','0'
     INSERT INTO TEMP_STATUS SELECT 'DT_DAY_D','0'
    
    
     BEGIN TRANSACTION
     UPDATE A
     SET STATUS=1
     FROM TEMP_STATUS A 
     WHERE NAME='GEO_D'

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, April 22, 2015 2:29 PM
  • You are getting row level locking.  The problem is that due to your indexing of this table (you don't have any indexes) a row level lock can effectively lock the whole table. To see why, consider the following.  If the first query window you run

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    GO
    BEGIN TRANSACTION
    UPDATE A
    SET STSTUS=1
    FROM ##TEMP_STSTUS A 
    WHERE NAME='GEO_D'

    That locks the row where NAME='GEO_D'.  Now in another query window, you do

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    GO
    BEGIN TRANSACTION
    UPDATE A
    SET STSTUS=1
    FROM ##TEMP_STSTUS A 
    WHERE NAME='CUST_D'

    So now SQL needs to find the row or rows where NAME='CUST_D'.  You do not have an index that will help with that query.  So SQL must read the entire table to find the row(s) where NAME='CUST_D'.  That works fine until it gets to the row that you updated in the other window (the one with NAME='GEO_D').  Of course, this new query does not need to update this row in this case, but SQL cannot find this out without reading that row (because you have no index which will help with this query).  But this new update needs to read the row to find out whether or not it should update this row, but the row is locked.  So this second window cannot complete until the first window either does a commit or a rollback.

    The solution to this is to have an index which allows SQL to determine the rows that need to be updated without actually reading every single row.  In this case with these update, you would want an index on the NAME column.

    Tom

    • Proposed as answer by JamesKJ Wednesday, April 22, 2015 7:57 PM
    Wednesday, April 22, 2015 7:49 PM
  • Thanks All,

    Now I am able to do it with out index also. Using READPAST.

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    GO
    BEGIN TRANSACTION
    UPDATE A
    SET STSTUS=1
    FROM ##TEMP_STSTUS A  (READPAST)
    WHERE NAME='GEO_D'

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    GO
    BEGIN TRANSACTION
    UPDATE A
    SET STSTUS=1
    FROM ##TEMP_STSTUS A  (READPAST)
    WHERE NAME='CUST_D'


    Thanks and Regards Rajesh

    • Marked as answer by Eric__Zhang Monday, May 4, 2015 9:33 AM
    Friday, April 24, 2015 7:03 AM
  • That will work.  You must add ROWLOCK too to make sure that it works, and of course it's more expensive without and index.  And you can just replace the useless IDENTITY column you had on a table with a useful PK on NAME.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, April 24, 2015 12:03 PM