数据库隔离级别

sql server锁的机制

   sql server的所有活动都会产生锁。锁定的单元越小，就越能越能提高并发处理能力，但是管理锁的开销越大。如何找到平衡点，使并发性和性能都可接受是sql server的难点。
sql server有如下几种琐：

1、 共享锁
用于只读操作(select)，锁定共享的资源。共享锁不会阻止其他用户读，但是阻止其他的用户写和修改。

2、 更新锁
更新锁是一种意图锁，当一个事务已经请求共享琐后并试图请求一个独占锁的时候发生更新琐。例如当两个事务在几行数据行上都使用了共享锁，并同时试图获取独占锁以执行更新操作时，就发生了死锁：都在等待对方释放共享锁而实现独占锁。更新锁的目的是只让一个事务获得更新锁，防止这种情况的发生。

3、 独占锁
一次只能有一个独占锁用在一个资源上，并且阻止其他所有的锁包括共享缩。写是独占锁，可以有效的防止’脏读’。

4、 意图缩
在使用共享锁和独占锁之前，使用意图锁。从表的层次上查看意图锁，以判断事务能否获得共享锁和独占锁，提高了系统的性能，不需从页或者行上检查。

5、 计划锁
sch-m,sch-s。对数据库结构改变时用sch-m，对查询进行编译时用sch-s。这两种锁不会阻塞任何事务锁，包括独占锁。

读是共享锁，写是排他锁，先读后更新的操作是更新锁，更新锁成功并且改变了数据时更新锁升级到排他锁。锁的类型有：
db-----数据库，由于 dbid 列已包含数据库的数据库 id，所以没有提供任何信息
fil----文件
idx----索引
pg-----页，数据或索引页。页码。页由 fileid:page 组合进行标识，其中，fileid 是 sysfiles 表中的 fileid，而 page 是该文件内的逻辑页码。
key----键,用于保护可串行事务中的键范围
tab----表，包括所有数据和索引在内的整个表。由于 objid 列已包含表的对象 id，所以没有提供任何信息
ext----区域, 相邻的八个数据页或索引页构成的一组。正被锁定的扩展盘区中的第一个页码。页由 fileid:page 组合进行标识
rid----行，表内已锁定行的行标识符。行由 fileid:page:rid 组合进行标识，其中，rid 是页中的行标识符。

细分锁的模式：
0 null 没有得到资源的访问权限
1 sch-s (schema stability) 对查询进行编译时。能防止加锁的对象被删除直到解锁
2 sch-m (schema modification) 改变数据库结构时发生。能防止其他的事务访问加锁的对象
3 is (intent shares) 意图共享锁。
4 siu(share intent update) 意图在维护资源的共享锁时，把更新锁放到锁层次结构的下层资源上
5 is-s(intent share-shared) 复合键范围锁
6 ix(intent exclusive) 意图排他锁
7 six(share intent exclusive)
8 s(share) 共享锁
9 u(update) 更新锁。防止死锁
10 iin-nul(intent insert-null) 索引行层次的锁定，复合键范围锁
11 is-x(intent share-exclusive)
12 iu(intent update) 意图更新锁
13 is-u(intent share update) 串行更新扫描
14 x(exclusive) 排他锁
15 bu 块操作使用的锁

所以有如下的结论。

1、一个连接在修改数据块时别的连接不能修改这个数据块，直到解锁。
并行访问是任何数据库解决方案都最为重视的问题了，为了解决并行访问方面的问题各类数据库系统提出了各种各样的方案。sql server采用了多线程机制，它当然能够一次处理多个请求。不过，在用户修改数据的情况下并行访问问题就变得复杂起来了。显然，数据库通常只允许唯一用户一次修改特定的数据。当某一用户开始修改某块数据时， sql server能很快地锁定数据，阻止其他用户对这块数据进行更新，直到修改该数据的第一位用户完成其操作并提交交易或者回滚。但是，当某一位用户正在修改某块数据时假设另一位用户又正想查询该数据的信息时会发生什么情况呢？
2、通常情况下，一个连接在修改数据块时别的连接也不能查询这个数据块，直到解锁。反之亦然：读的时候不能写和修改。这个方案会降低系统的性能和效率，尽管现在是行级锁（7.0以前是锁页甚至是锁表），如果你一次修改多行数据，sql server则会把数据锁定范围提升到页级别乃至锁定整个数据表，从而不必针对每一记录跟踪和维护各自的数据锁，这样能加快修改的速度，消耗小的服务器资源，但是并发性就差了。。
3、一个连接写的时候，另一个连接可以写，但是不得读
4、多个连接可以同时读同一行。

所以锁发生在读、写的竞争上。
5.设置事务的级别
数据库事务有严格的定义，它必须同时满足4个特性：原子性（Atomic）、一致性（Consistency）、隔离性（Isolation）和持久性（Durabiliy），简称为ACID。其中隔离性指在并发数据操作时，不同的事务拥有各自的数据空间，其操作不会对对方产生干扰。隔离允许事务行为独立或隔离于其他并发运行的事务。通过控制隔离，每个事务在其行动时间里都像是修改数据库的惟一事务。一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱
1.隔离级别 read uncommitted
级别read uncommitted（又称读取未提交内容）允许任务读取数据库中未提交的数据更改。也称为脏读，原因在于任务会显示以后被回退的结果。
2.隔离级别 Read Committed
级别Read Committed（又称读取已提交内容）可防止脏读。该级别查询只读取已提交的数据更改。如果事务需要读取被另一未完成事务修改的数据，该事务将等待，直到第一个事务完成（提交或回退）。
3.repeatable read：锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。

4.隔离级别Serializable
级别Serializable（可串行化）可防止幻像和非重复读。在非Serializable的级别下，如果一个事务读取了满足搜索条件的一组数据记录，而第二个事务修改了数据（通过 insert、delete 或 update 语句）；如果第一个事务按相同的搜索条件重复读取，它将获得一组不同的数据记录，产生幻像读或非重复读。

注释
一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效，直到显式更改该选项为止。这是默认行为，除非在语句的 from 子句中在表级上指定优化选项。
set transaction isolation level 的设置是在执行或运行时设置，而不是在分析时设置。


术语解释：
在一个程序中，依据事务的隔离级别将会有三种情况发生。
　　●脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，产生脏读。
    ●非重复读（non-repeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生非重复读。
    ●幻像读（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻像读。


结合以上的理论知识，将isolationlevel枚举的各值解释如下：

readcommitted：
假设a事务对正在读取数据data放置了共享锁，那么data不能被其它事务改写，所以当b事务对data进行读取时总和a读取的data数据是一致的，所以避免了脏读。由于在a没有提交之前可以对data进行改写，那么b读取到的某个值可能会在其读取后被a更改从而导致了该值不能被重复取得；或者当b再次用相同的where字句时得到了和前一次不一样数据的结果集，也就是幻像数据。

readuncommitted：
假设a事务即不发布共享锁，也不接受独占锁，那么并发的b或者其它事务可以改写a事务读取的数据，那么并发的c事务读取到的数据的状态和a的或者b的数据都可能不一致，那么。脏读、不可重复读、幻象数据都可能存在。

repeatableread：
（注意msdn原文中的第一句话：在查询中使用的所有数据上放置锁，所以不存在脏读的情况）。
假设a事务对读取的所有数据data放置了锁，以阻止其它事务对data的更改，在a没有提交之前，新的并发事务读取到的数据如果存在于data中，那么该数据的状态和a事务中的数据是一致的，从而避免了不可重复的读取。但在a事务没有结束之前，b事务可以插入新记录到data所在的表中，那么其它事务再次用相同的where字句查询时，得到的结果数可能上一次的不一致，也就是幻像数据。它是MySQL的默认隔离级别

serializable：
在数据表上放置了排他锁，以防止在事务完成之前由其他用户更新行或向数据集中插入行，这是最严格的锁。它防止了脏读、不可重复读取和幻象数据。

以下是对照表：

隔离级别                        脏读   不可重复读  幻读
读未提交（read uncommitted）  可能   可能       可能 
读已提交（read committed）   不可能   可能      可能 
可重复读（repeatable read）  不可能  不可能     可能 
可串行化（serializable ）    不可能   不可能    不可能