sqlserver2005锁粒度的注意点

Keep in mind that if the table has a clustered index, the data rows are at the leaf level of the clustered index and they are locked with key locks instead of row locks.

键锁

两种类型的键锁：

 actual index keys

If the isolation level is Read Committed, Repeatable Read, or Snapshot, SQL Server tries to lock the actual index keys accessed while processing the query.

键锁和行锁的关系：

是否使用对应的行锁，主要根据索引使用的聚集索引还是非聚集索引。

key-range locks
which is associated with a particular key value in an index and indicates that all values between that key and the previous one in the index are locked


选择哪种，主要根据事物的隔离级别。

事务类型	键锁类型	索引类型	是否行锁
Read Committed, Repeatable Read, or Snapshot	actual index keys	聚集索引	不用
		非聚集索引	用
Serializable isolation	key-range locks

键范围锁的几种类型

这些类型都是瞬态的，过度阶段的状态。

Types of Key-Range Locks

Abbreviation	Description
RangeS-S	Shared lock on the range between keys; shared lock on the key at the end of the range
RangeS-U	Shared lock on the range between keys; update lock on the key at the end of the range
RangeIn-Null	Exclusive lock to prevent inserts on the range between keys; no lock on the keys themselves
RangeX-X	Exclusive lock on the range between keys; exclusive lock on the key at the end of the range
RangeIn-S	Conversion lock created by S and RangeIn_Null lock
RangeIn-U	Conversion lock created by U and RangeIn_Null lock
RangeIn-X	Conversion of X and RangeIn_Null lock
RangeX-S	Conversion of RangeIn_Null and RangeS_S lock
RangeX-U	Conversion of RangeIn_Null and RangeS_U lock

其他锁资源：
extent locks
In addition to locks on objects, pages, keys, and rows, a few other resources can be locked by SQL Server. Locks can be taken on extentsunits of disk space that are 64 kilobytes (KB) in size (eight pages of 8 KB each). This kind of locking occurs automatically when a table or an index needs to grow and a new extent must be allocated. You can think of an extent lock as another type of special purpose latch, but it does show up in sys.dm_tran_locks. Extents can have both shared extent and exclusive extent locks

数据库锁
永远是共享锁。
锁的目的除了避免出现脏数据，还要避免数据结构不会被多人同时修改删除等。

 HOBT and ALLOCATION_UNIT
无关无用块。
Although all table and index structures are based on HOBTs and contain one or more ALLOCATION_UNITs, when these locks occur, it means SQL Server is dealing with one of these resources that is no longer tied to a particular object

所有update语句都是由一个delete和insert组成？页中没有足够的空间时？
If your update can't happen in place because you're updating clustering keys, the update will occur as a delete followed by an insert
In some cases, you'll get a hybrid update: some of the rows will be updated in place and some won't. If you're updating index keys, SQL Server builds a list of all the rows that need to change as both a delete and an insert operation.