04 - Lock Types

Hi,

Here we’ll cover below:

-          Concepts of transactions and locking.

-          Types of Locks & Lock compatibility

-          Locking issues - Blocking (Long-term locking), and Deadlocking

Lock Types-

Depending on the transaction level six types of lock can be acquired on data.

INTENT (I)	-          The intent lock shows the future intention of SQL Server's lock manager to acquire locks on a specific unit of data for a particular transaction. -          SQL Server uses intent locks to queue exclusive locks, thereby ensuring that these locks will be placed on the data elements in the order the transactions were initiated. -          Intent locks come in three flavors: o         Intent shared (IS) o         Intent exclusive (IX) o         Shared with Intent Exclusive (SIX) -          IS locks indicate that the transaction will read some (but not all) resources in the table or page by placing shared locks. -          IX locks indicate that the transaction will modify some (but not all) resources in the table or page by placing exclusive locks. -          SIX locks indicate that the transaction will read all resources, and modify some (but not all) of them. This will be accomplished by placing the shared locks on the resources read and exclusive locks on the rows modified. Only one SIX lock is allowed per resource at one time; therefore, SIX locks prevent other connections from modifying any data in the resource (page or table), although they do allow reading the data in the same resource.
SHARED (S)	-          Read-Only-  Allowed to multiple Transaction at the same time. -          No Write - No transactions are allowed to modify data until the shared locks are released.
EXCLUSIVE (X)	-          Exclusive locks (X) completely lock the resource from any type of access including reads. -          They are issued when data is being modified through INSERT, UPDATE and DELETE statements.
UPDATE (U)	-          Update locks (U) are acquired just prior to modifying the data. -          If a transaction modifies a row, then the update lock is escalated to an exclusive lock; otherwise, it is converted to a shared lock. -          Only one transaction can acquire update locks to a resource at one time. -          Using update locks prevents multiple connections from having a shared lock that want to eventually modify a resource using an exclusive lock. -          Shared locks are compatible with other shared locks, but are not compatible with Update locks.
SCHEMA (Sch-M)	-          Schema modification locks (Sch-M) are acquired when data definition language statements, such as CREATE TABLE, CREATE INDEX, ALTER TABLE, and so on are being -          executed. Schema stability locks (Sch-S) are acquired when store procedures are being compiled.
BULK UPDATE (BU)	-          Bulk update locks (BU) are used when performing a bulk-copy of data into a table with TABLOCK hint. These locks improve performance while bulk copying data into a table; however, they reduce concurrency by effectively disabling any other connections to read or modify data in the table

Lock Escalation 

-          Locks are allocated and escalated automatically by SQL Server; escalation means that Finer Grain Locks (Row And Page Locks) are converted into Coarse-Grain Table Locks.

-          Locks take up system memory, so converting many locks into one larger lock can free up memory resources.

-          Row level locking (versus page or table locking) does increase database concurrency, but at a cost of memory overhead.

-          Although you cannot control lock escalation, you do have control over locking isolation levels and locking hints in your queries.

Resources to which SQL Server can apply Locks –

In the above description, the term ‘resource’ are pThe following are resources to which SQL Server can apply locks:

           

RID	Row identifier, designating a single table row
Key	Index row lock, helping prevent phantom reads. Also called key-range lock, this lock type uses both a range and a row component. The range represents the range of index keys between two consecutive index keys. The row component represents the lock type on the index entry. Range types are as follows: RangeS_S mode – Shared range, shared resource lock. RangeS_U mode – Shared range, update resource lock. RangeI_N mode – Insert range, null resource lock (row doesn't exist yet). RangeX_X mode – Exclusive range, exclusive resource lock.
Page	Referring to a 8KB data or index page
Extent	Allocation unit of eight 8KB data or index pages
Table	Entire table, data, and indexes locked
DB	Entire database lock

Locks Compatibility

-          Not all lock types are compatible with each other; for lock types to be compatible, SQL Server must be able to place a lock on a table that has a lock already in place.

-          Lock types must be compatible with any existing lock currently placed on a resource.

-          The following table lists the lock types, and their associated compatibility types.

S	IS, S & U
U	IS, S
X	Any
IS	All except X
SIX	IS
IX	IS, IX
Sch-S	All except Sch-M
Sch-M	Incompatible with all locks.
BU	Sch-S, BU
RangeS_S mode	S, RangeS_S, RangeS_U
RangeS_U mode	S, RangeS_S
RangeI_N mode	S, U , X, RangeI_N
RangeX_X mode	Any

Locks Hints

Thanks & Regards,

Arun Manglick || Senior Tech Lead