The following are the locking modes in SQL server 2008 R1

Shared (S): Used for read operations that do not change or update data, such as a SELECT statement.

Update (U): Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Exclusive (X): Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Intent: Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Intent shared (IS)
Intent exclusive (IX)
            Shared with intent exclusive (SIX)
            Intent update (IU)
            Shared intent update (SIU)
            Update intent exclusive (UIX)

Schema: Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

Bulk Update (BU): Used when bulk copying data into a table and the TABLOCK hint is specified.

Key-range: Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Cursor Locking: In SQL Server, the SELECT statement in a cursor definition is subject to the same transaction locking rules that apply to any other SELECT statement. In cursors, however, an additional set of scroll locks can be acquired based on the specification of a cursor concurrency level.

We have faced many situations where the queries are waiting for the data to be available. This was manily due to the open transaction in the application. We have to always check the lock compatibilty between the locks. If two lock modes are not mutually compatible there is a good prone to have a blocking and locking leads to dead lock situation.

Eg:

--Session 1(Execute the below statement)
--create Table T1 (Col1 int , Col2 int)

BEGIN TRAN T1

Update T1 Set Col1=2


--Session 2 (Execute the below and see the waiting)
Select * From T1

--Session 2 (Stop the above query execution and look at the active transactions)
SELECT SP.SPID,nt_username,[TEXT] as SQLcode FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST WHERE OPEN_TRAN=1


--Session 1(After confirming the delay due to the open transaction, please fire rollback and see the result in second session)
Rollback Tran T1


--Session 2 (Execute the below and see the results)
Select * From T1

**

SQL Locks

**

Shared (S)

Used for read operations that do not change or update data, such as a SELECT statement.

Update (U)

Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions

are reading, locking, and potentially updating resources later.

Exclusive (X)

Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates

cannot be made to the same resource at the same time.

Intent

Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX),

and shared with intent exclusive (SIX).

Schema

Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema

modification (Sch-M) and schema stability (Sch-S).

Bulk Update (BU)

Used when bulk copying data into a table and the TABLOCK hint is specified.

Key-range

Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures

that other transactions cannot insert rows that would qualify for the queries of the serializable transaction

if the queries were run again.

Few detail study about SQL Lock from wiki

SQL Server allows multiple clients to use the same database concurrently.

As such, it needs to control concurrent access to shared data, to ensure data integrity - when multiple clients update the same data, or clients attempt to read data that is in the process of being changed by another client. SQL Server provides two modes of concurrency control: pessimistic concurrency and optimistic concurrency.

When pessimistic concurrency control is being used, SQL Server controls concurrent access by using locks. Locks can be either shared or exclusive.

Exclusive lock grants the user exclusive access to the data - no other user can access the data as long as the lock is held.

Shared locks are used when some data is being read - multiple users can read from data locked with a shared lock, but not acquire an exclusive lock.

The latter would have to wait for all shared locks to be released.

Locks can be applied on different levels of granularity - on entire tables, pages, or even on a per-row basis on tables.

For indexes, it can either be on the entire index or on index leaves.

The level of granularity to be used is defined on a per-database basis by the database administrator. While a fine grained locking system allows more users to use the table or index simultaneously, it requires more resources. So it does not automatically turn into higher performing solution.

SQL Server also includes two more lightweight mutual exclusion solutions - latches and spinlocks - which are less robust than locks but are less resource intensive.

SQL Server uses them for DMVs and other resources that are usually not busy. SQL Server also monitors all worker threads that acquire locks to ensure that they do not end up in deadlocks - in case they do, SQL Server takes remedial measures, which in many cases is to kill one of the threads entangled in a deadlock and rollback the transaction it started.

To implement locking, SQL Server contains the Lock Manager.

Example SQL Lock Refrence Click 1

Study SQL Lock Refrence Click 2

Thanks,

Yogesh K

Below are the different locks inside the SQL server 2008 R2:

Shared (S): Used for read operations that do not change or update data, such as a SELECT statement.

Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Exclusive (X): Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Intent: Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Schema: Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

Bulk Update (BU): Used when bulk copying data into a table and the TABLOCK hint is specified.

Key-range: Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Intent shared (IS) : Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.

Intent Exclusive (IX): Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX is a superset of IS, and it also protects requesting shared locks on lower level resources.

Shared with Intent exclusive (IX) : Protects requested or acquired shared locks on all resources lower in the hierarchy and exclusive locks on some (but not all) of the lower level resources. intent concurrent IS locks at the top-level resource are allowed. For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.

Intent Update (IU): Protects requested or acquired update locks on all resources lower in the hierarchy. IU locks are used only on page resources. IU locks are converted to IX locks if an update operation takes place.

Shared intent update (SIU): A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.

Update intent exclusive (UIX): A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

Schema Locks: The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.

Bulk Update Locks: The Database Engine uses bulk update (BU) locks when bulk copying data into a table, and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table.

Key-Range Locks: Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQ L statement while using the serialize transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

*Different types of locks inside SQL Server 2008 R2 are as below:*

Shared (S) - Used for read operations that do not change or update data, such as a SELECT statement.

Update (U) - Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Exclusive (X) - Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Intent - Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Schema - Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

Bulk Update (BU) - Used when bulk copying data into a table and the TABLOCK hint is specified.

Key-range - Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions into a set of records accessed by a transaction.

Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). They are as below:

Intent shared (IS) - Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.

Intent exclusive (IX) - Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX is a superset of IS, and it also protects requesting shared locks on lower level resources.

Shared with intent exclusive (SIX) - Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. Concurrent IS locks at the top-level resource are allowed. For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.

Intent update (IU) - Protects requested or acquired update locks on all resources lower in the hierachy. IU locks are used only on page resources. IU locks are converted to IX locks if an update operation takes place.

Shared intent update (SIU) - A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.

Update intent exclusive (UIX) - A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

Best example of Locking you can observe is:

1. Start transaction in one query editor
2. Update one column or record
3. Open another query editor
4. Fire select query on the same table or record to retrieve the data
5. Your query keeps on waiting for the data

If you Rollback or Commit the Transaction then query return the result without any waiting.

When we are trying to retrieve or select any data, that query or user requests a shared lock on the table/data. However if another query is running prior to this one such as update query, that would have created an exclusive lock on the table/data which prevents the select query from running. The default isolation level is READ COMMITTED which is in effect and the query keeps on waiting on the data and eventually times out (if update takes too long).

There are different types of locks that can be acquired to avoid dirty reads and get consistent results.

Shared Locks: used with select statements

Update Locks: are used when SQL Server intends to modify the page, and later promotes the update page lock to an exclusive lock before actually making the changes

Exclusive Locks: are used for DML only.

Let me describe with an example.

Process 1: fires an update query such as

begin tran update

update table table_name set col=value

commit tran update

Process 2: fires a select query as select * from table_name. Let us assume that process 1 starts before process 2. The update statement would acquire the exclusive lock on the table. Since shared lock is not compatible with exclusive lock, the process 2 ie select statement would wait until process 1 finishes. As soon as process 1 is completed, process 2 executes successfully.

Other locks are: Intent, Schema,Bulk Update and Key-range (more on: Lock Modes)

Locking Optimizer Hints

NOLOCK: no locks as name says so can read an uncommitted transaction, high concurrency but less consistency.

HOLDLOCK:directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used.HOLDLOCK is equivalent to SERIALIZABLE.

UPDLOCK: while reading table, this lock would acquire Update lock instead of Shared lock and holds it until end of transaction

TABLOCK: shared lock on the table until end of command.

PAGLOCK: Page lock is used by default by sql server.

Different types of locks inside SQL Server 2008 R2 are as below

Shared locks

Shared (S) locks are used for operations that read data, such as a SELECT statement. During Shared (S) locks use, concurrent transactions can read (SELECT) a resource, but cannot modify the data while Shared (S) locks exist on the resource. If you do not use the HOLDLOCK locking hint and your transaction isolation level is not set to REPEATABLE READ or SERIALIZABLE, the Shared (S) locks on a resource are released as soon as the data has been read. If you use the HOLDLOCK locking hint or your transaction isolation level is set to REPEATABLE READ or SERIALIZABLE, the Shared (S) locks on a resource will be held until the end of the transaction.

Update locks

Update (U) locks are used when SQL Server intends to modify a row or page, and later promotes the update page lock to an exclusive lock before actually making the changes. The Update (U) locks are used to prevent a deadlock. For example, if two transactions intend to update the same row, each of these transactions will set the shared lock on this resource and then try to set the exclusive lock. Without Update (U) locks, each transaction will wait for the other transaction to release its shared-mode lock, and a deadlock will occur.

To prevent a potential deadlock, the first transaction that tries to update the row will set the Update (U) lock on this row. Because only one transaction can obtain an Update (U) lock to a resource at a time, the second transaction will wait until the first transaction converts the update lock to an exclusive lock and releases the locked resource.

Exclusive locks

Exclusive (X) locks are used for data modification operations, such as UPDATE, INSERT, or DELETE.

Other transactions cannot read or modify data locked with an Exclusive (X) lock. If a Shared (S) exists, other transactions cannot acquire an Exclusive (X) lock.

Intent locks

Intent locks are used when SQL Server wants to acquire a shared lock or exclusive lock on some of the resources lower down in the hierarchy.

Intent locks include:
intent shared (IS)
intent exclusive (IX)
shared with intent exclusive (SIX)
intent update (IU)
update intent exclusive (UIX)
shared intent update (SIU)

Schema locks

Schema locks are used when an operation dependent on the schema of a table is executing.

Schema locks include: schema modification (Sch-M) schema stability (Sch-S) Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation is being performed.

Schema stability (Sch-S) locks are used when compiling queries. This lock does not block any transactional locks, but when the Schema stability (Sch-S) lock is used, the DDL operations cannot be performed on the table.

Bulk Update locks

Bulk Update (BU) locks are used during bulk copying of data into a table when one of the following conditions exist:

• TABLOCK hint is specified
• table lock on bulk load table option is set using sp_tableoption

The bulk update table-level lock allows processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.

Key-Range locks

Key-Range locks are used by SQL Server to prevent phantom insertions or deletions into a set of records accessed by a transaction. Key-Range locks are used on behalf of transactions operating at the serializable isolation level.

Shared Key-Range and Shared Resource (RangeS_S) locks are used to indicate a serializable range scan.

Shared Key-Range and Update Resource (RangeS_U) locks are used to indicate a serializable update scan.

Insert Key-Range and Null Resource (RangeI_N) locks are used to test ranges before inserting a new key into an index.

Exclusive Key-Range and Exclusive Resource (RangeX_X) locks are used when updating a key in a range.

There are also Key-Range conversion locks. Key-Range conversion locks include:

RangeIS RangeIU RangeIX RangeXS RangeX_U Key-Range conversion locks are created when a Key-Range lock overlaps another lock.

RangeIS locks are used when RangeIN lock overlap Shared (S) lock. RangeIU locks are used when RangeIN lock overlap Update (U) lock. RangeIX locks are used when RangeIN lock overlap Exclusive (X) lock. RangeXS locks are used when RangeIN lock overlap RangeSS lock. RangeXU locks are used when RangeIN lock overlap RangeSU lock.

Key-Range conversion locks are rarely used and can be observed for a short period of time under complex circumstances.

http://www.databasejournal.com/features/mssql/article.php/3289661/Understanding-SQL-Server-2000-Locking.htm

http://msdn.microsoft.com/en-us/library/ms175519.aspx

To Get full information about transaction locks
sys.dmtranlocks
http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx

Example
There are four processes, which attempt to lock the same page of the same table. These processes start one after another, so Process1 is the first process, Process2 is the second process and so on.

Process1 : SELECT
Process2 : SELECT
Process3 : UPDATE
Process4 : SELECT

Process1 sets the Shared lock on the page, because there are no another locks on this page.
Process2 sets the Shared lock on the page, because Shared locks are compatible with other Shared locks.
Process3 wants to modify data and wants to set Exclusive lock, but it cannot make it before Process1 and Process2 will be finished, because Exclusive lock is not compatible with other lock types. So, Process3 sets Update lock.

Process4 cannot set Shared lock on the page before Process3 will be finished. So, there is no Lock starvation.

Lock starvation occurs when read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely. So, Process4 waits before Process3 will be finished.

After Process1 and Process2 were finished, Process3 transfer Update lock into Exclusive lock to modify data.

After Process3 was finished, Process4 sets the Shared lock on the page to select data.

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

SQL Server can lock these resources:

Resource Description RID Row identifier. Used to lock a single row within a table. Key Row lock within an index. Used to protect key ranges in serializable transactions. Page 8 kilobyte –(KB) data page or index page. Extent Contiguous group of eight data pages or index pages. Table Entire table, including all data and indexes. DB Database.

The following are the locking modes in SQL server 2008 R1:

Shared (S) - Used for operations that do not change or update data (read-only operations), such as a SELECT statement.

Update (U) - Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Exclusive (X) - Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Intent - Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Schema - Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

Bulk Update (BU) - Used when bulk-copying data into a table and the TABLOCK hint is specified.

Steps to observe Locking is:

Step 1: Start transaction in one query editor Step 2: Update one column or record of a table Step 3: Open another query editor Step 4: Fire select query on the same table to retrieve the data, now you can see the waiting of query to retrive the data. Step 5: If you Rollback or Commit the transaction then query return the result without any waiting.

Shared,Update,Exclusive,Intent,Bulk Update and Key Range lock modes.

SQL Server 2008 R2 - Lock Modes

The Microsoft SQL Server Database Engine locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.

The following table shows the resource lock modes that the Database Engine uses.

Shared (S)

Used for read operations that do not change or update data, such as a SELECT statement.

Update (U)

Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Exclusive (X)

Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Intent

Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Schema

Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

Bulk Update (BU)

Used when bulk copying data into a table and the TABLOCK hint is specified.

Key-range

Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Locking

SQL Server has multigranular locking that allows different types of resources to be locked by a transaction to determine how the resources can be accessed by concurrent transactions. To minimize the cost of locking, SQL Server locks resources automatically at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency, but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions, but has a lower overhead because fewer locks are being maintained.

The following are the resource lock modes available in SQL Server R2

Shared Locks

Shared locks allow concurrent transactions to read a resource. No other transactions can modify the data while shared locks exist on the resource. Shared locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared locks for the duration of the transaction.

Update Locks

Used on resources that can be updated. Update locks prevent a common form of deadlock . Only one update lock can be held on the data at one time. update lock don’t allow to modify the underlying data. It has to be converted to an exclusive lock before the modification takes place. We can also force an update lock with the UPDLOCK hint

Exclusive Locks

Exclusive locks prevent access to a resource by concurrent transactions. No other transactions can read or modify data locked with an exclusive lock. Read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. Because DML statements first need to read the data they want to modify we will always find Exclusive locks accompanied by shared locks on that same data.

Intent Locks

Intent locks are a means in which a transaction notifies other transaction that it is intending to lock the data. An intent lock indicates that SQL Server wants to acquire a shared lock or exclusive lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive lock on the table containing that page. Intent locks improve performance because SQL Server examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Intent shared (IS): Indicates the intention of a transaction to read some (but not all) resources lower in the hierarchy by placing Shared locks on those individual resources.

Intent exclusive (IX): Indicates the intention of a transaction to modify some (but not all) resources lower in the hierarchy by placing exclusive locks on those individual resources. IX is a superset of IS.

Shared with intent exclusive (SIX) : Indicates the intention of the transaction to read all of the resources lower in the hierarchy and modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources.

Schema Locks

Schema modification locks are used when a table data definition language (DDL) operation is being performed. Schema stability locks are used when compiling queries. Schema stability locks do not block any transactional locks, including exclusive locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive locks on a table. However, DDL operations cannot be performed on the table.

Bulk Update Locks

Bulk update locks are used when bulk copying data into a table and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.

Key-Range Locks

Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

Reference:

http://msdn.microsoft.com/en-us/library/ms175519.aspx

http://blogs.technet.com/b/josebda/archive/2009/03/19/sql-server-2008-locking.aspx

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