Sql Server 2008 Wait Types - Locks

Front 1

LCK_M_BU

Back 1

Occurs when a task is waiting to acquire a Bulk Update (BU) lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 2

LCK_M_IS

Back 2

Occurs when a task is waiting to acquire an Intent Shared (IS) lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 3

LCK_M_IU

Back 3

Occurs when a task is waiting to acquire an Intent Update (IU) lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 4

LCK_M_IX

Back 4

Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 5

LCK_M_RIn_NL

Back 5

Occurs when a task is waiting to acquire a NULL lock on the current key value and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 6

LCK_M_RIn_S

Back 6

Occurs when a task is waiting to acquire a shared lock on the current key value and an Insert Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 7

LCK_M_RIn_U

Back 7

Task is waiting to acquire an Update lock on the current key value and an Insert Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 8

LCK_M_RIn_X

Back 8

Occurs when a task is waiting to acquire an Exclusive lock on the current key value and an Insert Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 9

LCK_M_RS_S

Back 9

Occurs when a task is waiting to acquire a Shared lock on the current key value and a Shared Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 10

LCK_M_RS_U

Back 10

Occurs when a task is waiting to acquire an Update lock on the current key value and an Update Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 11

LCK_M_RX_S

Back 11

Occurs when a task is waiting to acquire a Shared lock on the current key value and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 12

LCK_M_RX_U

Back 12

Occurs when a task is waiting to acquire an Update lock on the current key value and an Exclusive range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 13

LCK_M_RX_X

Back 13

Occurs when a task is waiting to acquire an Exclusive lock on the current key value and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 14

LCK_M_S

Back 14

Occurs when a task is waiting to acquire a Shared lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 15

LCK_M_SCH_M

Back 15

Occurs when a task is waiting to acquire a Schema Modify lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 16

LCK_M_SCH_S

Back 16

Occurs when a task is waiting to acquire a Schema Share lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 17

LCK_M_SIU

Back 17

Occurs when a task is waiting to acquire a Shared With Intent Update lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 18

LCK_M_SIX

Back 18

Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock. For a lock compatibility matrix seesys.dm_tran_locks.

Front 19

LCK_M_U

Back 19

Occurs when a task is waiting to acquire an Update lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 20

LCK_M_UIX

Back 20

Occurs when a task is waiting to acquire an Update With Intent Exclusive lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 21

LCK_M_X

Back 21

Occurs when a task is waiting to acquire an Exclusive lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 22

LCK_M_BU

Back 22

Occurs when a task is waiting to acquire a Bulk Update (BU) lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 23

LCK_M_IS

Back 23

Occurs when a task is waiting to acquire an Intent Shared (IS) lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 24

LCK_M_IU

Back 24

Occurs when a task is waiting to acquire an Intent Update (IU) lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 25

LCK_M_IX

Back 25

Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 26

LCK_M_RIn_NL

Back 26

Occurs when a task is waiting to acquire a NULL lock on the current key value and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 27

LCK_M_RIn_S

Back 27

Occurs when a task is waiting to acquire a shared lock on the current key value and an Insert Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 28

LCK_M_RIn_U

Back 28

Task is waiting to acquire an Update lock on the current key value and an Insert Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 29

LCK_M_RIn_X

Back 29

Occurs when a task is waiting to acquire an Exclusive lock on the current key value and an Insert Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 30

LCK_M_RS_S

Back 30

Occurs when a task is waiting to acquire a Shared lock on the current key value and a Shared Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 31

LCK_M_RS_U

Back 31

Occurs when a task is waiting to acquire an Update lock on the current key value and an Update Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 32

LCK_M_RX_S

Back 32

Occurs when a task is waiting to acquire a Shared lock on the current key value and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 33

LCK_M_RX_U

Back 33

Occurs when a task is waiting to acquire an Update lock on the current key value and an Exclusive range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 34

LCK_M_RX_X

Back 34

Occurs when a task is waiting to acquire an Exclusive lock on the current key value and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix see sys.dm_tran_locks.

Front 35

LCK_M_S

Back 35

Occurs when a task is waiting to acquire a Shared lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 36

LCK_M_SCH_M

Back 36

Occurs when a task is waiting to acquire a Schema Modify lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 37

LCK_M_SCH_S

Back 37

Occurs when a task is waiting to acquire a Schema Share lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 38

LCK_M_SIU

Back 38

Occurs when a task is waiting to acquire a Shared With Intent Update lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 39

LCK_M_SIX

Back 39

Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock. For a lock compatibility matrix seesys.dm_tran_locks.

Front 40

LCK_M_U

Back 40

Occurs when a task is waiting to acquire an Update lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 41

LCK_M_UIX

Back 41

Occurs when a task is waiting to acquire an Update With Intent Exclusive lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 42

LCK_M_X

Back 42

Occurs when a task is waiting to acquire an Exclusive lock. For a lock compatibility matrix see sys.dm_tran_locks.

Front 43

Lock Granularity

Back 43

Lock granularity refers to the level at which locks occur:

* Row
* Table
* Page
* Database

Front 44

Locking

Back 44

Locking is a mechanism used by the Microsoft SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.

Front 45

Why is locking used?

Back 45

To protect transactions against other transactions that might modify the same data.

Front 46

Locking conflict rule:

Back 46

No transaction can be granted a lock that would conflict with the mode of a lock already granted on that data to another transaction.

Front 47

What happens when locking conflicts occur?

Back 47

If a transaction requests a lock mode that conflicts with a lock that has already been granted on the same data, the instance of the Database Engine will pause the requesting transaction until the first lock is released.

Front 48

How long are locks held for modifications?

Back 48

When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction.

Front 49

How long will a transaction hold the locks acquired to protect read operations?

Back 49

depends on the transaction isolation level setting.

Front 50

When are locks released?

Back 50

All locks held by a transaction are released when the transaction completes (either commits or rolls back).

Front 51

How are locks normally requested.

Back 51

Locks are managed internally by the query processor to the lock manager.

Front 52

Lock Granularity and Hierarchies

Back 52

Locks can be requested for different types of resources, such as rows, pages, indexes, tables, or databases. Some operations require placing locks at multiple levels of granularity, forming a hierarchy of locks.

Front 53

Lock Modes

Back 53

Locks have different modes that specify the level of access other transactions have to the locked resource.

Front 54

Lock Compatibility (Database Engine)

Back 54

Multiple transactions can acquire concurrent locks on the same resource if their lock modes are compatible. If a transaction requests a lock mode that conflicts with an existing lock, the transaction is paused until the first lock is freed.

Front 55

Key-Range Locking

Back 55

Locking a range of keys allows transactions running at the serializable isolation level to prevent phantom inserts and deletes.

Front 56

Lock Escalation (Database Engine)

Back 56

If a transaction acquires a large number of row or page locks, the Database Engine may grant a table lock and then free all the lower-level locks to minimize locking overhead.

Front 57

Dynamic Locking

Back 57

The Database Engine can dynamically choose the level of lock granularity based on the optimizer's estimate of the number of rows a Transact-SQL statement will reference.

Front 58

Displaying Locking Information (Database Engine)

Back 58

The Database Engine and its associated APIs provide several mechanisms for displaying information about the locks currently held in an instance or a database.

Front 59

Deadlocking

Back 59

Deadlocks occur when two tasks permanently block each other because each task has a lock on a resource needed by the other task.

Front 60

Resources that can be locked (10)

Back 60

RID - A row identifier used to lock a single row within a heap.

KEY - A row lock within an index used to protect key ranges in serializable transactions.

PAGE - An 8-kilobyte (KB) page in a database, such as data or index pages.

EXTENT - A contiguous group of eight pages, such as data or index pages.

HoBT - A heap or B-tree. A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.

TABLE - The entire table, including all data and indexes.

FILE - A database file.

APPLICATION - An application-specified resource.

METADATA,
ALLOCATION_UNIT,
DATABASE

Front 61

Lock Modes

Back 61

Shared (S)
Update (U)
Exclusive (X)
Intent (IS), (IX), (SIX)
Schema (Sch-M), (Sch-S)
Bulk Update (BU)
Key-Range

Front 62

Shared Lock Mode (S)

Back 62

Used for read operations that do not change or update data, such as a SELECT statement.

Front 63

Update Lock Mode (U)

Back 63

Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Front 64

Exclusive Lock Mode (X)

Back 64

Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Front 65

Intent Lock Mode (IS), (IX), (SIX)

Back 65

Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Front 66

Schema Lock Mode (Sch-M), (Sch-S)

Back 66

Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

Front 67

Bulk Update Lock Mode (BU)

Back 67

Used when bulk copying data into a table and the TABLOCK hint is specified.

Front 68

Key-range Lock Mode

Back 68

Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Front 69

Intent locks serve two purposes:

Back 69

1. To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.

2. To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity.

Front 70

sys.dm_os_wait_stats

Back 70

Returns information about the waits encountered by threads that executed. Does NOT show CURRENT waits but only the time for completed waits.

Front 71

Types of Waits (3)

Back 71

Resource Waits
Queue Waits
External Waits

Front 72

Resource Waits

Back 72

occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available.

Front 73

Queue Waits

Back 73

occur when a worker is idle, waiting for work to be assigned. These tasks will wait for work requests to be placed into a work queue.

Front 74

Typical Queue Waits

Back 74

Queue waits are most typically seen with system background tasks such as the deadlock monitor and deleted record cleanup tasks.

Queue waits may also periodically become active even if no new packets have been put on the queue.

Front 75

Typical Resource Waits

Back 75

Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects

Front 76

External Waits

Back 76

occur when a SQL Server worker is waiting for an external event, such as an extended stored procedure call or a linked server query, to finish.

Front 77

External waits and blocking issues....

Back 77

When you diagnose blocking issues, remember that external waits do not always imply that the worker is idle, because the worker may actively be running some external code.

Front 78

How to reset sys.dm_os_wait_stats

Back 78

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)
or when Server restarts