SQL锁机制
今天公司进行了一个有关锁机制的培训，讲的内容其实大学都学过，但发现很多东西结合了实际项目的时候才理解更深了，发此博文做个记录，作为以后参考。
大纲
基本概念
死鎖
避免死鎖
处理死锁
锁的概念 锁主要用于多用户环境下保证数据库完整性和一致性。
多个用户能够同时操纵同一个数据库中的数据，会发生数据不一致现象，如果没有锁定且多个用户同时访问一 个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。这些问题包括：丢失更新、脏读、不可重复读等。
锁的粒度
|
资源 |
级别 |
描述 |
|
RID |
行锁 |
表中的单个行 |
|
Key |
行级锁 |
索引中的行 |
|
Page |
页级锁 |
一个数据页或者索引页 |
|
Extent |
页级锁 |
一组数据页或者索引页 |
|
Table |
表级锁 |
整个表 |
|
Database |
数据库级锁 |
整个数据库 |
锁的模式
|
锁模式 |
描述 |
|
共享（S） |
用于不更改或不更新数据（只读操作），如SELECT语句 |
|
更新（U） |
用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。 |
|
排它（X） |
用于数据修改操作，例如INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新 |
|
意向 |
SQL Server有在资源的低层获得共享锁或排它锁的意向意向锁的类型为：意向共享（IS）、意向排它（IX）以及意向排它共享（SIX） |
|
架构 |
在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改（Sch-M）和架构稳定（Sch-S） |
|
大容量更新（BU） |
向表中大容量复制数据并指定了TABLOCK提示时使用 |
死锁 死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。
死锁的四个必要条件
互斥条件(Mutual exclusion)：资源不能被共享，只能由一个进程使用。
请求与保持条件(Hold and wait)：已经得到资源的进程可以再次申请新的资源。
非剥夺条件(No pre-emption)：已经分配的资源不能从相应的进程中被强制地剥夺。
循环等待条件(Circular wait)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源
死锁的排查
使用SQL Server的系统存储过程sp_who和sp_lock，可以查看当前数据库中的锁情况；进而根据objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以查看哪个资源被锁，用dbcc ld(@blk)，可以查看最后一条发生给SQL Server的Sql语句。
避免死锁
死锁的四个必要条件，我们只要想办法破其中的任意一个或多个条件，就可以避免死锁发生。
按同一顺序访问对象，避免出现循环。
避免事务中的用户交互，减少持有资源的时间，较少锁竞争。
保持事务简短并处于一个批处理中，减少持有资源的时间。
使用较低的隔离级别，使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可串行化）持有共享锁的时间更短，减少锁竞争。
使用基于行版本控制的隔离级别，使用基于行版本控制的隔离级别：2005中支持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制，可以将读与写操作之间发生的死锁几率降至最低：
SET ALLOW_SNAPSHOT_ISOLATION ON --事务可以指定SNAPSHOT事务隔离级别;
SET READ_COMMITTED_SNAPSHOT ON --指定READ_COMMITTED隔离级别的事务将使用行版本控制而不是锁定。
死锁的处理方法
查看那个spid处于wait状态，然后用kill spid来干掉(即破坏死锁的第四个必要条件:循环等待)。
使用SET LOCK_TIMEOUT timeout_period(单位为毫秒)来设定锁请求超时。默认情况下，数据库没有超时期限(timeout_period值为-1，可以用SELECT @@LOCK_TIMEOUT来查看该值，即无限期等待)。
SQL Server内部有一个锁监视器线程执行死锁检查，锁监视器对特定线程启动死锁搜索，检测到死锁后，数据库引擎选择运行回滚开销最小的事务的会话作为死锁牺牲品回滚死锁牺牲品的事务并释放该事务持有的所有锁，使其他线程的事务可以请求资源并继续运行。
