Types of locking

Locking is the way that SQL Server manages transaction concurrency for multi-user environment. A lock as an in-memory structure is 96 bytes in size. 

How to check locking or DMV for track locking:

select distinct  request_session_id,db_name(resource_database_id),
     resource_type, request_mode,request_type,
     request_status,request_owner_type
from sys.dm_tran_locks

Level of locking(types of locking ):

Database: Database level.

Table: table level.

Page: data & index page level.

Key: Row lock within an index.

RID: row Level.

Extent: Contiguous group of eight data pages or index pages.

Types of locking modes:

1. Shared (S)
2. Exclusive (X)
3. Update (U)
4. Intent (I)
5. Schema (Sch)
6. Bulk update (BU)

Shared(S):

     When any one data reading from database means SELECT statement create shared lock. its allow to read same data and same time in other transaction or session but can’t allow to modify(update and delete)until the shared locks are released.

See there I have start T1 transection and read data from saleshead table there lotof dummy data and also I have use same table 3 time like a cross join so table will take time in select, in other Transaction T2 i’m try to update single records but this one not allow to me and its waiting for completion for T1 transection and both of transaction make some locking like below screenshot  request_session_id=54 is T1 and 60 is T2

Exclusive Lock (X) –when data modified means when INSERT, UPDATE and DELETE operation executing that time creating exclusive lock and its making completely lock and it’s not allow to modify and reads in other transection.

Ex 1.

Ex 2. Exclusive lock on Update time

Ex 3. Exclusive lock on Deletion time

Update lock (U):

 If a transaction modifies a row, then the update lock is escalated to exclusive lock, otherwise it is converted to a shared lock. Only one transaction can acquire update locks to a resource at one time.

See their T56 updating some records after that T55 also come for update with same data and T54 inserting some data in same objects so that case  T56 convert in Exclusive because it’s operation already started then T55 waiting for Exclusive lock completion so its convert in Update lock and T54 mark exclusive and waiting

Schema (Sch-): its create in DDL activity like

1. Schema modification time create (Sch-M), such as CREATE and ALTER TABLE,INDEX etc.

2. Schema stability locks (Sch-S) when store procedures are being compiled, meta data creation etc.

Bulk update locks (BU):

when performing bulk-copy inserting or process, it’s marking TABLOCK hint. TABLOCK improve performance of bulk copying data but its lock fully table you can’t able read or modify data.

Intent locks sub types:

1. Intent shared (IS),
2. Intent exclusive (IX),
3. Shared with intent exclusive (SIX).

1. IS: indicate that the transaction will read some (but not all) the resources in the table or page by placing shared locks.
2. IX: indicate that the transaction will modify some (but not all) resources in the table or page by placing exclusive locks.
3. SIX: indicate that the transaction will read all resources, and modify some(but not all) of them.

Locking hierarchy

Note: always a shared locks on the database level because it’s imposed to prevent dropping of the database or restoring a database backup over the database when its use.

Ex.1: Just like I want to inserting 1 row that case as per rules 

Exclusive(X) lock will create on row level and IX lock will on table and page level and shared lock (S) on the database level.

Ex.2: when I want to selecting data to table time shared lock (S) will on row level and IS lock will on page or table level and shared lock (S) on the database level.

Lock escalation

Lock escalation is the process of converting many fine-grained locks (such as row or page locks) into table locks.

When many locks created on the same table’s row in Same session the Sql Server itself automatically upgrades these locks into a table lock, it is count all locks like page, row and index-level locks on the a single table.

Escalation’s threshold values is 5000 locks (on single table) it’s maintained by Sql Server itself and we no need to change in configuration.  

Note: Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock.

Lock Escalation Modes:– Lock Escalation Modes is also Part of Types of locking

1. Table: Table is the default mode and specifies that lock escalation will be done at table-level granularity.
2. Auto: Auto mode allows the Database Engine to select the lock escalation granularity that is appropriate for the table schema.
3. Disable:  allows to disable lock escalation.

Check Lock Escalation Modes:–

We can check any specific table’s escalation mode using sys.tables like

select name,object_id,lock_escalation_desc  from sys.tables where name =’saleshead’

Change Lock Escalation Mode: We also can change it using ALTER TABLE statement like

How to check/ trace Escalation:

many ways to can trace it now try will profiler

1. login in profiler and select tsql_locks or also you can use blank template like :

2. Now Select Lock Escalation Events form “Events Selection” Tab there events lists come according to your selection template In that case I have selected Blank template

3. Finally check where and which query

How to Prevent Lock Escalation:

1. To keep short transactions: Break up large batch operations into several smaller operations. For example, suppose we deleting several hundred thousand old records then we found that it caused a lock escalation that blocked other users.

DELETE FROM sqllogs WHERE datastamp < ‘20190101’

Break above query like that:

SET ROWCOUNT 500

delete_more:

     DELETE FROM sqllogs WHERE datastamp< ‘20190101’

  IF @@ROWCOUNT > 0

           GOTO delete_more

SET ROWCOUNT 0

2. Reduce lock footprint of expensive queries by doing performance tuning and making them efficient.

Lock Timeout

Timeout is part of types of locking

Lock Timeout event indicates that a request for a lock on a resource, such as a page, has timed out because another transaction is holding a blocking lock on the required resource. Time-out is determined by the @@LOCK_TIMEOUT system function.

We also can be set timeout time using SET LOCK_TIMEOUT statement there duration milliseconds and 1000 milliseconds means 1 second.

For example I have set lock timeout as a1000 milliseconds  (1 seconds) and create 2 session 1 update saleshead tables (so there exclusive lock create) and saleshead have lotof data so it will take time and same time session 2 selecting data from the same table, so it will wait for 1 seconds after that will timeout.

like that :

Lock Timeout events with a duration of 0 are commonly the result of internal lock probes and are not necessarily an indication of a problem. The Timeout (timeout > 0) event can be used to ignore time-outs with duration of 0.

Lock Timeout ModeID

0=NULL – Compatible with all other lock modes (LCK_M_NL)

1=Schema Stability lock (LCK_M_SCH_S)

10=Shared with Intent Exclusive (LCK_M_SIX)

11=Update with Intent Exclusive (LCK_M_UIX)

12=Bulk Update Lock (LCK_M_BU)

13=Key range Shared/Shared (LCK_M_RS_S)

14=Key range Shared/Update (LCK_M_RS_U)

15=Key Range Insert NULL (LCK_M_RI_NL)

16=Key Range Insert Shared (LCK_M_RI_S)

17=Key Range Insert Update (LCK_M_RI_U)

18=Key Range Insert Exclusive (LCK_M_RI_X)

19=Key Range Exclusive Shared (LCK_M_RX_S)

2=Schema Modification Lock (LCK_M_SCH_M)

20=Key Range Exclusive Update (LCK_M_RX_U)

21=Key Range Exclusive Exclusive (LCK_M_RX_X)

3=Shared Lock (LCK_M_S)

4=Update Lock (LCK_M_U)

5=Exclusive Lock (LCK_M_X)

6=Intent Shared Lock (LCK_M_IS)

7=Intent Update Lock (LCK_M_IU)

8=Intent Exclusive Lock (LCK_M_IX)

9=Shared with intent to Update (LCK_M_SIU)

Lock Timeout OwnerID

1=TRANSACTION

2=CURSOR

3=SESSION

4=SHARED_TRANSACTION_WORKSPACE

5=EXCLUSIVE_TRANSACTION_WORKSPACE

Timeout – TypeID

1=NULL_RESOURCE

2=DATABASE

3=FILE

5=OBJECT

6=PAGE

7=KEY

8=EXTENT

9=RID

10=APPLICATION

11=METADATA

12=AUTONAMEDB

13=HOBT

14=ALLOCATION_UNIT

How to check/ trace Lock Timeout using profiler:

1. login in profiler and select tsql_locks or also you can use blank template.
2. Now Select Timeout Events form “Events Selection” Tab there events lists come according to your selection template In that case I have selected Blank template like

3. Select column according to your required  and click on OK/Run but like