Q6.3: What are the different types of locks?

First of, just to get it out of the way, there is no method to perform row level locking. If you think you need row level locking, you probably aren't thinking set based -- see Q6.1 for set processing.

The SQL Server uses locking in order to ensure that sanity of your queries. Without locking there is no way to ensure the integrity of your operation. Imagine a transaction that debited one account and credited another. If the transaction didn't lock out readers/writers then someone can potentially see erroneous data.

Essentially, the SQL Server attempts to use the least intrusive lock possible, page lock, to satisfy a request. If it reaches around 200 page locks, then it escalates the lock to a table lock and releases all page locks thus performing the task more efficiently.

There are three types of locks:

Page Locks

There are three types of page locks:

shared

These locks are requested and used by readers of information. More than one connection can hold a shared lock on a data page.

This allows for multiple readers.

exclusive

The SQL Server uses exclusive locks when data is to be modified. Only one connection may have an exclusive lock on a given data page. If a table is large enough and the data is spread sufficiently, more than one connection may update different data pages of a given table simultaneously.

update

A update lock is placed during a delete or an update while the SQL Server is hunting for the pages to be altered. While an update lock is in place, there can be shared locks thus allowing for higher throughput.

The update lock(s) are promoted to exclusive locks once the SQL Server is ready to perform the delete/update.

Table Locks

There are three types of table locks:

intent

Intent locks indicate the intention to acquire a shared or exclusive lock on a data page. Intent locks are used to prevent other transactions from acquiring shared or exclusive locks on the given page.

shared

This is similar to a page level shared lock but it affects the entire table. This lock is typically applied during the creation of a non-clustered index.

exclusive

This is similar to a page level exclusive lock but it affects the entire table. If an update or delete affects the entire table, an exclusive table lock is generated. Also, during the creation of a clustered index an exclusive lock is generated.

Demand Locks

A demand lock prevents further shared locks from being set. The SQL Server sets a demand lock to indicate that a transaction is next to lock a table or a page.

This avoids indefinite postponement if there was a flurry of readers when a writer wished to make a change.