locked
How to lock few rows when udating with begin tran RRS feed

  • Question

  • User264732274 posted

    see my code

    BEGIN TRAN
    
        UPDATE EMP2 WITH (HOLDLOCK,ROWLOCK)
        SET SALARY=100 WHERE ID IN (1,2)
    
        WAITFOR DELAY '00:02:00';
    
        PRINT 'Job complete'
    
    COMMIT TRAN

    i use wait for to simulate some dealy.

    when i execute the above code and from other query window when i issue select like

    select * from Emp2

    then i saw data was not coming. it means update statement place a lock on table not few rows.

    but i want those rows should be coming which are excluded in update statement. update statement updating only employee whose id are 1 and 2 so i want other employee data should come when begin tran is running.

    please tell me how could i achieve it. thanks

    Friday, October 28, 2016 10:12 AM

Answers

  • User475983607 posted

    Use the nolock hint.

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

    select * from Emp2 with (nolock)

    But this comes with a risk as nolock does a READ UNCOMMITED, meaning it will read the uncommitted salary field.  if you have a highly transnational application this might not be a good approach as you end up with dirty data.

    You can try ROWLOCK from the openly published MSDN docs

    ROWLOCK
    Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

    You might want to investigate READPAST too.  This hint will skip a locked record but it comes with a price as well.  You simply don't know if the result set is complete.

    I suggest that you take some time to understand the technology and the problem you are trying to solve.  The MSDN link above explains each hint.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 28, 2016 11:06 AM

All replies

  • User475983607 posted

    Use the nolock hint.

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

    select * from Emp2 with (nolock)

    But this comes with a risk as nolock does a READ UNCOMMITED, meaning it will read the uncommitted salary field.  if you have a highly transnational application this might not be a good approach as you end up with dirty data.

    You can try ROWLOCK from the openly published MSDN docs

    ROWLOCK
    Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

    You might want to investigate READPAST too.  This hint will skip a locked record but it comes with a price as well.  You simply don't know if the result set is complete.

    I suggest that you take some time to understand the technology and the problem you are trying to solve.  The MSDN link above explains each hint.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 28, 2016 11:06 AM
  • User264732274 posted

    where to use READPAST and how to use it with select statement ?

    i want if 2 row is locked for the emp id 1 and 2 then when i issue select statement then 3 emp id data should come. just post the sample code how to do it. thanks

    Friday, October 28, 2016 11:25 AM
  • User753101303 posted

    Hi,

    sudip_inn

    it means update statement place a lock on table not few rows.

    IMO not yet. You selected ALL rows including those that are locked (maybe at the row level). My first move would be to see what happens if I select all rows EXCEPT those are locked.

    Edit: Ah sorry. I missed regardless of the ealier conclusion it seems you want anyway to select automatically all rows expect those that are locked.

    Friday, October 28, 2016 4:54 PM