擦亮自己的眼睛去看SQL Server之谈谈锁机制(1)

在谈谈SQLServer的锁机制之前，来思考以下这个场景：当你在酷暑的时候骑着自己的小车往目的地行走时，路上连续遇到几个时间很长的红灯，是不是很郁闷 有时候你可能实在受不了闯了个红灯，其实在大部分情况下问题不大，假如通行的汽车很多那就不好说了。因为不遵守规则的人太多，都为了达到目的去走捷径，不愿意等待。这样才有了交警。交警的作用就是维护这些红绿灯的规则。这些红绿灯就像锁一样，锁住或延长你去目的地的时间。但是假如没有交警大家又不自由遵守红绿灯规则会导致什么呢 大家想想都知道。

这个系列的一篇文章中提供的事务管理器中有个锁管理器就是这里的交警。它维护着SQLServer中的锁。前段提到的大部分情况指的就是在系统事务量不大的时候，这时候的锁永远不会是什么大问题。除非你知道你的系统永远就给几个人用，否则考虑到系统以后的并发量上升不出现数据与效率问题，那你得深入了解锁机制。在研究锁之前，假定你已经了解事务的ACID概念，它是整个SQLServer的精髓所在。假如没有事务那就不用谈锁了，除了事务需要锁以外其他任何东西都需要这个让SQL不自由的机制。说到底锁是一个平衡并发与数据安全的机制，假如没有锁，任何SQL都能覆盖其他SQL执行的数据，那么数据会出现不一致的情况。假如锁得太狠，那将影响数据库系统的并发性以及效率(包括锁本身带来的额外开销)。这时候就需要去权衡，SQLServer锁管理器就充当权衡这两者关系的角色，如下图所示：

SQLServer中锁的知识点实在太多，比如锁从模式上分为：共享锁(S)、更新锁(U)、排他锁(X)、架构锁(Sch-S、Sch-M)、意向锁(IS、IU、IX)、转换锁(SIX、SIU、UIX)、大容量更新锁(BU);锁从粒度上分为：数据库锁、文件锁、表锁、堆锁、索引锁、页锁、键锁、区锁、行锁、应用程序锁、元数据锁;锁之间存在兼容性问题;锁会根据情况进行升级;锁控制不好会出现死锁;悲观锁的隔离性：未提交读、已提交读、可重复读、可序列化;乐观锁的隔离性：读提交快照隔离、快照隔离;闩(shuan)锁。。。随便列下就一大堆问题要说清楚需要花很大篇幅。还是抱着与前几篇文章的风格，仔细分析一个具体的问题——锁升级。

1、准备

有一个动态管理视图可以查看所有锁：sys.dm_tran_locks，还有一个动态管理视图可以查看哪些请求正在阻塞其他的请求：sys.dm_os_waiting_tasks

2、什么是锁升级

锁升级是指锁的粒度由细向粗转换。如：由行锁转成表锁。

3、需要锁升级吗

一般来说，锁的粒度越小，并发性越好但是假如去锁定的东西多就需要的锁越多，这样会消耗SQLServer的cpu与内存。一个锁占用内存约为96字节，你算算假如用行锁去锁定百万千万的表需要多少内存。而且管理锁(创建锁、维护锁、销毁锁等)也是有代价的，会消耗cpu。 假如用一个大点的锁就将这些百万千万的锁合并成一个锁了，管理起来也方便消耗资源也小。

4、什么时候出现锁升级

SQLServer意识到锁定的页面或行数过大的时候发生。怎么意识到过大呢 由两种方法识别：请求用于的锁的数目超过锁数目临界值;锁管理器为单独一个查询消耗过多的内存超过内存临界值。有其他一个超过临界值，SQLServer就会试图升级。注意这里说的锁数据以及内存是值由同一个查询发生的，而不是总共的。这里说的临界值并不是固定的，SQLServer采用启发式算法去动态调整。

5、控制锁升级

SQLServer提供一些可以让我们控制锁升级的入口。在SQLServer2008中可以通过：

alter table test set (lock_escalation = auto|table|disable) 

我们还可以通过在代码中显示指定pagelock、tablock提示，会强制SQLServer使用更粗的锁。不过这个设置不合理的话会导致并发降低。建议一般情况下不用，除非你很清楚这样带来的影响。

6、举例说明

6.1建库建表：

create database Test create table test ( ID identity(1,1) primary key, [Name] varchar(50) not null default '', CreatedTime datetime not null default getdate(); ) 

查看当前锁情况：

默认某个连接对整个数据库有个共享锁。

6.2循环插入几十万条记录： 

while 1 = 1 insert into test(Name) values ('kk') 

插入时的锁快照 :

从上图中看出这个快照中有：三个数据库共享锁、一个页级意向排他锁、一个表级意向排他锁、两个行级排他锁。

三个数据库共享锁：前面已经提过，默认某个连接对整个数据库有个共享锁;

一个页级意向排他锁、一个表级意向排他锁：在页以及表级表示资源的一部分实际已经有锁进行保护，这样的好处允许其他请求锁在表页级别上进行检查，减少不必要的更细的锁请求，提高性能。比如在这种情况下，假如允许alter操作那么这个操作就会等待因为这里有表级排他锁，它提示alter操作该表有活动。

6.3 跟踪Lock:Escalation事件

在profiler中设置只跟踪Lock:Escalation事件，锁升级事件。

6.4更新表中记录：

update test set name = 'name' where name = 'kk' 

在profiler中看到了Lock:Escalation事件被触发：

更新时的快照为(按顺序)：

如上图：此时update操作以排他锁定它更新的行。

如上图：此时update操作以排他锁锁定了整个表，以架构稳定锁(Sch-S)锁定它相关的元数据表。

如上图：此时释放了对元数据表的架构稳定锁(Sch-S)锁，剩下对整个表的排他锁。

从上面的分析中，发现SQLServer锁机制是有点复杂的，不过也是很有意思的。研究后，你会发现它真的很智能。今天分析就到此结束，文中如有描述不当的地方，欢迎指出。共同进步才是硬道理。

原文链接：http://www.cnblogs.com/yueyue_jwfm/archive/2011/08/14/2138388.html

在谈谈SQLServer的锁机制之前，来思考以下这个场景：当你在酷暑的时候骑着自己的小车往目的地行走时，路上连续遇到几个时间很长的红灯，是不是很郁闷 有时候你可能实在受不了闯了个红灯，其实在大部分情况下问题不大，假如通行的汽车很多那就不好说了。因为不遵守规则的人太多，都为了达到目的去走捷径，不愿意等待。这样才有了交警。交警的作用就是维护这些红绿灯的规则。这些红绿灯就像锁一样，锁住或延长你去目的地的时间。但是假如没有交警大家又不自由遵守红绿灯规则会导致什么呢 大家想想都知道。

这个系列的一篇文章中提供的事务管理器中有个锁管理器就是这里的交警。它维护着SQLServer中的锁。前段提到的大部分情况指的就是在系统事务量不大的时候，这时候的锁永远不会是什么大问题。除非你知道你的系统永远就给几个人用，否则考虑到系统以后的并发量上升不出现数据与效率问题，那你得深入了解锁机制。在研究锁之前，假定你已经了解事务的ACID概念，它是整个SQLServer的精髓所在。假如没有事务那就不用谈锁了，除了事务需要锁以外其他任何东西都需要这个让SQL不自由的机制。说到底锁是一个平衡并发与数据安全的机制，假如没有锁，任何SQL都能覆盖其他SQL执行的数据，那么数据会出现不一致的情况。假如锁得太狠，那将影响数据库系统的并发性以及效率(包括锁本身带来的额外开销)。这时候就需要去权衡，SQLServer锁管理器就充当权衡这两者关系的角色，如下图所示：

SQLServer中锁的知识点实在太多，比如锁从模式上分为：共享锁(S)、更新锁(U)、排他锁(X)、架构锁(Sch-S、Sch-M)、意向锁(IS、IU、IX)、转换锁(SIX、SIU、UIX)、大容量更新锁(BU);锁从粒度上分为：数据库锁、文件锁、表锁、堆锁、索引锁、页锁、键锁、区锁、行锁、应用程序锁、元数据锁;锁之间存在兼容性问题;锁会根据情况进行升级;锁控制不好会出现死锁;悲观锁的隔离性：未提交读、已提交读、可重复读、可序列化;乐观锁的隔离性：读提交快照隔离、快照隔离;闩(shuan)锁。。。随便列下就一大堆问题要说清楚需要花很大篇幅。还是抱着与前几篇文章的风格，仔细分析一个具体的问题——锁升级。

1、准备

有一个动态管理视图可以查看所有锁：sys.dm_tran_locks，还有一个动态管理视图可以查看哪些请求正在阻塞其他的请求：sys.dm_os_waiting_tasks

2、什么是锁升级

锁升级是指锁的粒度由细向粗转换。如：由行锁转成表锁。

3、需要锁升级吗

一般来说，锁的粒度越小，并发性越好但是假如去锁定的东西多就需要的锁越多，这样会消耗SQLServer的cpu与内存。一个锁占用内存约为96字节，你算算假如用行锁去锁定百万千万的表需要多少内存。而且管理锁(创建锁、维护锁、销毁锁等)也是有代价的，会消耗cpu。 假如用一个大点的锁就将这些百万千万的锁合并成一个锁了，管理起来也方便消耗资源也小。

4、什么时候出现锁升级

SQLServer意识到锁定的页面或行数过大的时候发生。怎么意识到过大呢 由两种方法识别：请求用于的锁的数目超过锁数目临界值;锁管理器为单独一个查询消耗过多的内存超过内存临界值。有其他一个超过临界值，SQLServer就会试图升级。注意这里说的锁数据以及内存是值由同一个查询发生的，而不是总共的。这里说的临界值并不是固定的，SQLServer采用启发式算法去动态调整。

5、控制锁升级

SQLServer提供一些可以让我们控制锁升级的入口。在SQLServer2008中可以通过：

alter table test set (lock_escalation = auto|table|disable) 

我们还可以通过在代码中显示指定pagelock、tablock提示，会强制SQLServer使用更粗的锁。不过这个设置不合理的话会导致并发降低。建议一般情况下不用，除非你很清楚这样带来的影响。

6、举例说明

6.1建库建表：

create database Test create table test ( ID identity(1,1) primary key, [Name] varchar(50) not null default '', CreatedTime datetime not null default getdate(); ) 

查看当前锁情况：

默认某个连接对整个数据库有个共享锁。

6.2循环插入几十万条记录： 

while 1 = 1 insert into test(Name) values ('kk') 

插入时的锁快照 :

从上图中看出这个快照中有：三个数据库共享锁、一个页级意向排他锁、一个表级意向排他锁、两个行级排他锁。

三个数据库共享锁：前面已经提过，默认某个连接对整个数据库有个共享锁;

一个页级意向排他锁、一个表级意向排他锁：在页以及表级表示资源的一部分实际已经有锁进行保护，这样的好处允许其他请求锁在表页级别上进行检查，减少不必要的更细的锁请求，提高性能。比如在这种情况下，假如允许alter操作那么这个操作就会等待因为这里有表级排他锁，它提示alter操作该表有活动。

6.3 跟踪Lock:Escalation事件

在profiler中设置只跟踪Lock:Escalation事件，锁升级事件。

6.4更新表中记录：

update test set name = 'name' where name = 'kk' 

在profiler中看到了Lock:Escalation事件被触发：

更新时的快照为(按顺序)：

如上图：此时update操作以排他锁定它更新的行。

如上图：此时update操作以排他锁锁定了整个表，以架构稳定锁(Sch-S)锁定它相关的元数据表。

如上图：此时释放了对元数据表的架构稳定锁(Sch-S)锁，剩下对整个表的排他锁。

从上面的分析中，发现SQLServer锁机制是有点复杂的，不过也是很有意思的。研究后，你会发现它真的很智能。今天分析就到此结束，文中如有描述不当的地方，欢迎指出。共同进步才是硬道理。

原文链接：http://www.cnblogs.com/yueyue_jwfm/archive/2011/08/14/2138388.html