SQL Server lock

Lock mode

Lock mode	Description
Share (S)	Used for read operations that do not change or do not update data, such as a SELECT statement.
Update (U)	Used in resources that can be updated. Prevents common forms of deadlocks that occur when multiple sessions are read, locked, and subsequent resource updates are possible.
Exclusive (X)	Used for data modification operations, such as INSERT, UPDATE, or DELETE. Make sure that you do not make multiple updates to the same resource at the same time.
Intention	The hierarchy used to establish the lock. An intent lock consists of three types: intent sharing (IS), intent Exclusive (IX), and intent exclusive sharing (SIX).
Architecture	Used when performing operations that depend on the table schema. Schema locks consist of two types: schema modification (SCH-M) and schema Stability (sch-s).
Bulk Update (BU)	Used when bulk data replication is made to a table and TABLOCK hints are specified.
Key Range	Protects the range of rows read by a query when using the SERIALIZABLE transaction isolation level. Make sure that other transactions cannot insert rows that conform to a serializable transaction query when you run the query again.

The size of the lock

tr>

Resource	Description
RID	The row identifier used to lock a single row in the heap.
Key	The row lock in the index that is used to protect the key ranges in a serializable transaction.
Page	8 KB pages in the database, such as data pages or index pages.
EXTENT	A contiguous set of eight pages, such as data pages or index pages.
HoBT	Heap or B-tree. A lock that is used to protect a B-tree (index) or heap data page in a table that does not have a clustered index.
Table	Includes the entire table for all data and indexes.
File	Database file.
Application	Application-specific resources.
METADATA	Metadata lock.
Allocation_unit	Allocation unit.
Database	Entire database.

implementing row locks with Rowlock

 --  connect a   tran  select  *  from  B with  (Rowlock,holdlock ) --  plus row lock  where  id=  1  waitfor  delay  " 00:01:00    --  Wait 60 seconds to commit the transaction  commit  tran  

--Connection TwoUpdateBSetName='A1'whereId=1--will clogUpdateBSetName='A1'whereId=2--does not clog

To implement a row lock on a table, you must have a primary key, because the row lock can only be applied to the primary key, and there is only a table lock if there is no primary key.

SQL Server lock