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 the long-duration to fetch the logical file from the physical
disk path.
You may also refer:
0 comments:
Post a Comment