Monday, 19 February 2018

LCK_M_XXX Wait Type in SQL Server

Locking is a structure used in the SQL Server Database Engine to retrieve and access a piece of data by multiple users at the same time. Using this mechanism can help the morality of the data being retrieved from the database.

Let’s look at some of the LCK_M_XXX types present in SQL Server. These are some of the basic lock operations encountered by me and these types of locks are probably handled by either DBAs or Database Developers. 

1. LCK_M_BU

Encountered when a request is waiting to acquire a Bulk Update(BU) lock. Basically, occurs when there is a need to protect database objects during bulk insertions.

2. LCK_M_IS

Encountered when a request is waiting to acquire an Intent Shared(IS) lock. Basically, occurs when there is a need to modify a database object the user, who will have the access to read but denied on updates.

3. LCK_M_IU

Encountered when a request is waiting to acquire an Intent Update(IU) lock. Basically, occurs when there is a need to write on a database object, which is already either in reading or modifying operation.

4. LCK_M_IX

Encountered when a request is waiting to acquire an Intent Exclusive(IX) lock. Basically, this is something like Intent Update, where there is a need to write on a database object, which is already either in reading or modifying operation.

5. LCK_M_S

Encountered when a request is waiting to acquire a Shared(S) lock. Basically, occurs when there is a need to read a request on a database object when already a write operation is on. 

6. LCK_M_U

Encountered when a request is waiting to acquire an Update(U) lock. Basically, occurs when there is a need to write on a database object, which already is either in reading or modify operation, something like LCK_M_IU. 

7. LCK_M_X

Encountered when a request is waiting to acquire an Exclusive(X) lock. Basically, occurs when there is a need to read/write on a database object, wherein a write operation is already on.

How to Resolve:

1. Keep all your transactions short and simple.
2. In case taking a backup/restore, try not to take a backup of the same database at the same time.
3. Use EXEC sp_who2 Active to check for blocking in SQL Server.
4. Try not to change the isolation to “Read Uncommitted”, because the data getting generated would be irrelevant. Setting Read Uncommitted can cause other statements to read rows that are modified but still not committed. 
5. Set up a job to monitor the queries that are running on your server for quite a long time. This can help in recognizing and killing the query in case the query is found junk.
6. Carry out an option to generate a timeout mail for the queries exceeded certain allowable limits.
7. Check for network connectivity for the server, as there are chances are transport-level error thereby causing the session to automatically go to rollback state.
8. Monitor the I/O alerts on your server, which could be probably due to long-duration to fetch the logical file from physical disk path. 

0 comments: