Locks

Locks can be applied at different levels

1. Database level(db)
2. Table level(tab)
3. Page level(page)
4. Row level(row)

SQL Server 2005 support different types of locking modes

1) Shared Lock(s):
A shared lock is used when you only need to read the data - that is you wont be changing anything, meaning Select statement [Read only] - Any number of transactions can apply shared lock on the data at the same time. Shared locks prevents users from performing dirty reads.

2)Exclusive Locks(x)
Exclusive locks are not compatible with any other lock, they cannot be achieved if any other lock exists, nor will they allow a new lock of any form to be created on the resource while the exclusive lock is still active, this prevents two people from updating, deleting or whatever at the same time.
A transaction applied exclusive lock on the data can perform any DML operation until that particular transaction is completed, no other transaction can access the same data.

3)Update Lock(u)
Update locks are hybrids, something between shared and exclusive locks and update locks are compatible only with shared locks and intent locks.
Update locks indicate that you have a shared lock that's going to become exclusive lock after you have done your initial scan of the data to figure out what exactly needs to be updated. This acknowledges the fact that there are two distinct stages to an update:

• First, the stage where you are going to figure out what meets the WHERE clause criteria(whats going to be updated). This is the part of an update query that has an update lock.
• Second, the stage where, if you actually decide to perform the update, the lock is upgraded to an exclusive lock, otherwise the lock is converted to a shared lock.

4)Intent Locks(i):
A transaction applying lock at high level and other transaction applying at low level is called intent lock, intent locks can be combined with other locks also.
Intent locks come in three different flavors:

• Intent shared lock: A shared lock has or is going to be established at some lower point in the hierarchy. For example, a page is about to have a page level shared lock established on it. This type of lock applied only to tables and pages.
• Intent exclusive lock: This is the same as intent shared, but with an exclusive lock about to be placed on the lower level item.
• Shared with intent exclusive lock: A shared lock has or is about to be established lower down the object hierarchy, but the intent is to modify data, so it will become an intent exclusive at some point.

5)Schema Locks:This comes in two flavors

• Schema modification lock(Sch-M): A schema change is being made to the object. No queries or other CREATE,ALTER or DROP statements can be run against this object for the duration of the Sch-M Lock.
• Schema Stability Lock(Sch-S):This is very similar to shared lock; This locks sole purpose is to prevent a Sch-M since there are already locks for other queries(or CREATE,ALTER,DROP statments) active on the object. This is compatible with all other lock types.

6)BULK Update Locks:
A bulk update lock (BU) is really just a variant of a table lock with one little(but significant) difference. Bulk update locks will allow parallel loading of data - that is, the table is locked from any other "normal"(T-SQL Statements) activity, but multiple BULK INSERT or Bcp operations can be performed at the same time.

PS:In order to monitor the locks information we can use "sp_lock" function.