Why does MS SQL use exclusive locks on the entire table to update one record?

Question

We have problems with deadlocks, judging by the logs, two requests simultaneously want to get exclusive access to the table. However, both queries change one record in the table. The error occurs at high loads. There was a lack of RAM. In this case, we use the READ COMMITTED SNAPSHOT isolation level. I understand that this isolation level is very dependent on temporary tables and if there is a lack of memory, then it will not lead to anything good. But I'm trying to explain why an exclusive table lock occurs, what is the reason?

Error log:

  

      
        
      
      
        
          
            
    UPDATE S SET 
          State = 4, 
          CompleteDate = @Dt
          FROM WF4_Stations S inner join WF4_Routes R on RouteID = R.ID AND S.State IN (0,1,2,5) AND S.PRUZ <> 1 
            AND DocUID = @docUID AND R.State NOT IN(3,4,6) AND R.PRUZ <> 1 AND R.Type =    
          
          
    Proc [Database Id = 8 Object Id = 748490391]   
        
        
          
            
    update WF4_Routes set
          State = case when @Dt >= stat.ControlDate then 4 else 3 end,
          CompleteDate = @Dt,
          UserUID = @OperatorUID,
          ModifyDate = @Dt
        from WF4_Routes R
        inner join WF4_Stages stages
          on  R.ID = @RouteID
          and stages.RouteID = R.ID
          and stages.StageType = 7
          and stages.PRUZ <> 1
        inner join WF4_Stations stat
          on  stat.ApprovalStageID = stages.ID
          and stat.PRUZ <>    
          
          
    Proc [Database Id = 8 Object Id = 1315340296]

Accepted Answer

The reason for blocking the entire table was that ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF were disabled on our index. Hope this helps someone.

Answer

Hi @Михаил
The way to do it in SQL Server is to set an isolation level on the transaction that contains the statements that you want to execute. See this REF page (at bottom of this answer) but the general structure would look something like:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
select * from ...
update ...
COMMIT TRANSACTION;
SERIALIZABLE is the highest isolation level.

REF: http://msdn.microsoft.com/en-us/library/ms173763.aspx

Why does MS SQL use exclusive locks on the entire table to update one record?

1 additional answer