Wednesday, January 19, 2011

Locks of SQL Server:

In this article, I would like to discuss about kinds of SQL Server locks, and how compatible among them. For ACID implementation, SQL Server uses locks. So lock is a very important function in a multi-user database system. The lock is used in concurrency model. In SQL Server 2005 and next version, there are 2 types of concurrency model, pessimistic and optimistic. There are 12 kinds of locks. Let’s see each of them.
Shared lock (S):
This lock allows the process to read resource but not allows other processes to modify the resource.
Exclusive lock (IX):This lock allows a process to modify resource but doesn’t allow other processes either to read or to modify the locked resource.
Update lock (U:
This lock prevents another process to update or exclusive lock. This lock is used when searching data to modify.
Intent Share lock (IS):
This lock indicates a component of the resource has shared lock. This lock exists on page or table level.
Intent Update (IU):
This lock indicates a component of the resource has update lock. This lock exists on page or table level.
Intent Exclusive (IX):
This lock indicates a component of the resource has an exclusive lock. This lock exists on page or table level.
Shared with Intent Exclusive (SIX):
This lock indicates a resource that holds shared lock contains component (row or page level) holding an exclusive lock. This lock exists at row or page level.
Shared with Intent Update (SIU):
This lock indicates a resource that holds shared lock contains component (row or page level) holding update lock. This lock exists at row or page level.
Update with Intent Exclusive (UIX):
This lock indicates a resource that holds update lock contains component (row or page level) holding an exclusive lock. This lock exists at row or page level.
Schema Stability (Sch-S):
This lock indicates a query using this table is being compiled.
Schema Modification (Sch-M):
This lock indicates a table being changed for its structure.
Bulk Update (BU):
This lock exists when there is Bulk Insert operation into a table and TABLOCK hint is also applied.Understanding behavior of these locks will help you out troubleshooting locking issue.

Links:

No comments:

Post a Comment