Why SQL server update statements have lock_m_u (RID u lock) lock wait when two sessions updating on different row?
As Oracle DBA, I did not expect this lock wait, but it's happening on SQL Server. This link has answer. the solution is to create index. https://stackoverflow.com/questions/62852902/update-with-rowlock-in-mssql-server specifically explained here: Lock hints are only hints. You can't "force" SQL to take a particular kind of lock. You can see the locks being taken with the following query: select tl.request_session_id, tl.resource_type, tl.request_mode, tl.resource_description, tl.request_status from sys.dm_tran_locks tl join sys.partitions pt on pt.hobt_id = tl.resource_associated_entity_id join sys.objects ob on ob.object_id = pt.object_id where tl.resource_database_id = db_id() order by tl.request_session_id OK, let's run some code in an SSMS query window: create table t(i int , j int ); insert t values ( 1 , 1 ), ( 2 , 2 ); begin tran; update