(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
Description:
This wait type is when a thread is waiting to acquire an Exclusive lock on a resource and there is at least one other lock in an incompatible mode granted on the resource to a different thread.
General locking information:
- For the complete lock compatibility matrix, see the Books Online page Lock Compatibility.
- For information on the lock hierarchy, see the Books Online page Lock Granularity and Hierarchies.
- For information on some of the lock modes, see the Books Online page Lock Modes.
- For other locking topics, see the Books Online page Locking in the Database Engine.
(Books Online description: “Occurs when a task is waiting to acquire an Exclusive lock.”)
Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.
Added in SQL Server version:
Pre-2005/2005
Removed in SQL Server version:
N/A
Extended Events wait_type value:
The map_key value in sys.dm_xe_map_values is 5 in all versions through 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.
Other information:
General guidance around troubleshooting lock waits:
- It is not possible to determine the lock resource from the sys.dm_os_wait_stats output. You can see the resource from sys.dm_os_waiting_tasks (using my script) or looking at the resource_description field of sys.dm_tran_locks where the request_status is ‘WAIT’.
- You can use the blocked process report to get more detailed information on queries that are waiting for locks for a specified threshold (see here).
- Look to see what is at the head of the blocking chain (i.e. the thread that’s holding the lock that’s blocking everyone) using a script (plenty of them available online – I don’t have a preferred one). What is that thread waiting for? Fixing that wait may help unravel the blocking. For example, a thread may be holding locks and committing a transaction, but there’s a synchronous mirror with a slow I/O subsystem so the mirror log write takes a long time, making the transaction commit take longer, and the locks take longer to be released, causing blocking.
- Look for lock escalation, where an UPDATE transaction has escalated to a table X lock, causing widespread blocking.
- Look for index operations causing table locks, and consider using online index operations (or if already using them, consider the WAIT_AT_LOW_PRIORITY feature in 2014+).
- Look for code that specifies a TABLOCK (causes a table Shared lock) or TABLOCKX (causes a table Exclusive lock) hint.
- Look for application code that will cause locks to be acquired and then waits for user input, or fails to commit a transaction for a long time.
- Consider creating nonclustered indexes to remove row locks from the underlying heap/clustered index.
- Consider using snapshot isolation or read committed snapshot isolation to allow readers to not take S/IS locks and reduce blocking.
- Check the correct isolation level is being used as REPEATABLE_READ and SERIALIZABLE will hold S/IS locks until the end of a transaction.
- Check for accidental use of the SERIALIZABLE isolation level, from using distributed transactions or incorrectly scoped .Net TransactionScope objects.
Specific guidance for LCK_M_X waits:
- For an Exclusive lock, the resource could be a row, page, partition, table, file, or database.
- Common blockers are a table X (Exclusive) lock from lock escalation occurring, or a SCH_M (Schema Modification) lock from an index build/rebuild.
- If the blocker is holding a table S lock, investigation why the blocking thread has that lock (e.g. use of TABLOCK hint or lock escalation in a restrictive isolation level).
- If the thread is trying to acquire a page X lock, investigate whether row locks have been disallowed for the table/index or whether the PAGLOCK hint was specified.
- If the thread is trying to acquire a table X lock, investigate whether lock escalation is occurring or whether the TABLOCKX hint was specified.
Known occurrences in SQL Server (list number matches call stack list):
- Waiting for an Exclusive lock on a page (in this case, to insert a row into a table where the clustered index has been set to disallow row locks)
- Waiting for an Exclusive lock on a table (in this case, as part of dropping the table)
- Waiting for an Exclusive lock on a file (in this case, as part of processing a checkpoint operation)
And many other similar stacks.
Abbreviated call stacks (list number matches known occurrences list):
- SOS_Task::PostWait+9e
EventInternal<SuspendQueueSLock>::Wait+1fb
LockOwner::Sleep+485
lck_lockInternal+10ed
GetLock+1d8
PageContext::AcquireLock+169
PageContext::AcquireLockWithWait+6b
IndexDataSetSession::AcquireLocksForInsert+af
IndexDataSetSession::InsertSmallRecord+d7e
IndexDataSetSession::InsertRowInternal+2de1
DatasetSession::InsertRow+163
RowsetNewSS::InsertRow+26
CValRow::SetDataX+5b
CQScanUpdateNew::GetRow+43d
CQueryScan::GetRow+81
CXStmtQuery::ErsqExecuteQuery+36d
CXStmtDML::XretDMLExecute+31c
CXStmtDML::XretExecute+ad
CMsqlExecContext::ExecuteStmts<1,1>+427
CMsqlExecContext::FExecute+a43
CSQLSource::Execute+86c - SOS_Task::PostWait+9e
EventInternal<SuspendQueueSLock>::Wait+1fb
LockOwner::Sleep+485
lck_lockInternal+10ed
MDL::LockObjectLocal+dbe
CMEDCacheEntryFactory::AcquireLock+61
CMEDCatalogObject::GetCachedObjectById+e2
CMEDCatalogObject::GetCachedObjectByName+81
CMEDProxySchema::GetObjectByName+82
IMetadataAccess::GetMultiNameObjectHelper+714
CMEDAccess::GetMultiNameObject+df
CallDropObject+669
CStmtDropObject::XretDropExecute+d3
CMsqlExecContext::ExecuteStmts<1,1>+427
CMsqlExecContext::FExecute+a43
CSQLSource::Execute+86c
process_request+a57
process_commands+4a3
SOS_Task::Param::Execute+21e
SOS_Scheduler::RunTask+ab - SOS_Task::PostWait+9e
EventInternal<SuspendQueueSLock>::Wait+1fb
LockOwner::Sleep+485
lck_lockInternal+10ed
CSessionLockList::AcquireLock+f0
FileMgr::AcquireFileOperationLock+a6
ProcessEvalRequest+9e
CheckpointHelper+1b8
CheckpointLoopWorker+da
RegisterCheckPtWorker+133
CheckpointLoop+d3
CheckpointThread+55
SOS_Task::Param::Execute+21e
SOS_Scheduler::RunTask+ab