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:

http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

http://support.microsoft.com/kb/271509

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server