SQL Server various Lock Mode

Question

Lock mode considers various lock types that can be applied to a resource that has to be locked:

Exclusive (X)
Shared (S)
Update (U)
Intent (I)
Schema (Sch)
Bulk update (BU)

when i execute this query then i saw IX on request_mode column for Emp1 table. what IX means ?

IX means intent lock or different ?

BEGIN TRANSACTION  
UPDATE Emp1 WITH (ROWLOCK,UPDLOCK)  
SET   name = 'James'  
WHERE ID=1  
  
SELECT resource_type, request_mode, resource_associated_entity_id,  
       object_name(try_convert(int, resource_associated_entity_id)),   
       COUNT(*) AS [Count]  
FROM   sys.dm_tran_locks  
WHERE  request_session_id = @@spid  
GROUP  BY resource_type, request_mode, resource_associated_entity_id,  
          object_name(try_convert(int, resource_associated_entity_id))   
go  
select * from Emp1  
ROLLBACK TRANSACTION  

screen shot attached

a) UPDATE LOCK place lock on Intent or Rows ?
b) what is difference between row lock and intent lock ?

i used WITH (ROWLOCK,UPDLOCK) row lock and update lock hint but still lock is not placed on row....why?

Answer 1

In this particular example you have updated a row in heap, a table without a clustered index, but there is a non-clustered index. The locks are taken on row level, since you requested this. The row level locks appears as KEY (in an index) or RID (in a heap).

The intent locks are taken on higher level to say "I am here below", so that a process that wants to take a lock on the entire table will be blocked. Same goes for the IX lock on page.

Answer 2

Hi @Sudip Bhatt ，

This means there is an IX lock on the column.IX refers to intent exclusive lock.

1.Use of ROWLOCK
The ROWLOCK row-level lock ensures that when the user obtains the updated row, it will not be modified by other users during this time. Therefore, row-level locks can ensure data consistency and improve the concurrency of data operations.
ROWLOCK tells SQL Server to only use row-level locks, ROWLOCK syntax can be used in SELECT, UPDATE and DELETE statements.
2.In order to obtain a lock on a specific resource type, the transaction must first obtain the same mode of intent lock at a higher granularity level. For example, in order to obtain an exclusive lock on a row, the transaction must first obtain an intent exclusive lock on the page containing that row, and also obtain an intent exclusive lock on the data object containing that page. The purpose of the intention lock is to effectively detect incompatible lock requests at a higher granularity level and prevent the granting of incompatible locks.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I suggest you read this:

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#lock_modes

Answer 4

Row locks and intention locks do not belong to the same type. Row lock is to lock the row.

Refer to an example to illustrate the intention lock. When trying to modify data, the transaction will request an exclusive lock for the data resource to be modified. This lock mode is called exclusive lock because for the same data resource, if other transactions have already acquired any type of lock on the resource, it can no longer acquire the exclusive lock on the resource; if any transaction has already acquired it With the exclusive lock on the resource, you can no longer obtain any type of lock on the resource. That is, when the data resource obtains an exclusive lock, requesting another lock will be rejected by the system, which means that these locks are not compatible. The concept of intent lock has been mentioned before, and its function is to detect incompatible lock requests at a higher level. For example, if a transaction holds a row lock, and other transactions want to request an incompatible lock on the entire page or table containing that row, SQL Server can easily identify it and reject the request.

The content of the lock is very complicated. After understanding the concepts of the two locks, you will understand the difference between them.