Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1728601
  • 博文数量: 310
  • 博客积分: 6853
  • 博客等级: 准将
  • 技术积分: 2833
  • 用 户 组: 普通用户
  • 注册时间: 2005-08-04 16:41
文章分类

全部博文(310)

文章存档

2013年(17)

2012年(42)

2011年(76)

2010年(71)

2009年(99)

2007年(2)

2006年(1)

2005年(2)

分类: 数据库开发技术

2009-09-02 22:05:04

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.









阅读(985) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册