SQL Server中事务和并发详解

SQL Server中事务和并发详解

A、Transaction（事务）是什么：

事物是SQL Server中的基本工作单元。通常它由几个读取和更新数据库的SQL命令组成，但是这些 *** 作都不被看为最终的，直到发出一个COMMIT命令为止。 事务是作为单一工作单元而执行的一系列 *** 作。包括增删查改。

2、事务的种类：

 事务分为显示事务和隐式事务：

隐式事务：就是平常我们使用每一条sql 语句就是一个事务，只不过他们执行完成之后事务就跟着结束了。

显示事务：就是需要我们来手写了，这个时候就可以进行控制事务的开始和结束了。

 1 --显式事务（对事物可以进行控制） 2  3 --开始事务 4 begin transaction; 5 update [Sales.Shippers] 6 set companyname='顺丰' where shipperid=5; 7  8 select * from [Sales.Shippers]; 9 10 --结束事务：11 --第一种：事务的回滚12 rollback;13 14 --第二种：事务的提交15 commit;

3、事务很重要的四个属性： 

 1、原子性：事务必须是原子工作单位。——在事务中修改数据，要么全都执行，要么全都不执行。在事务执行完成之前（调提交指令写入到sql的事务日志之前），出现问题或重启，sql server 会回滚所有的修改事务。 但是也有例外的错误不会回滚事务————例如：主键冲突和锁超时等。  错误日志会 捕获这些错误的指令，并记录日志里面，然后执行一些 *** 作（例如：回滚事务）

2、一致性：发生在同一进程的事物里面的 修改和 查询是不会产生冲突的。保持访问的数据的一致性。

3、隔离性：控制数据访问的机制； 说明： 一个事务正在对一个表的数据正在修改， 还没有执行完成；；这时另一个事务，想要查询里面的数据，是不能查到的，必须等到 修改的事务执行完成。：sql server 采用的 “锁”的机制，将正在修改的事务 处理的表的数据 锁定。这样是为了保证数据同步，数据的一致性。

4、持久性：  当一个事务的指令 已经提交到 事务日志里面，即使磁盘上的数据还没有修改，这个时候数据库的服务停止，在服务重启的时候还会将事务日志里的指令执行(进行回复处理)。保证数据的持久性。

B、什么是并发性？
并发性可以定义为多个进程在相同时间访问或者更改共享数据的能力。既然是能力，那么一个系统的并发性就会有强弱之分。既然如此，我们该怎样判断一个系统并发行的强弱呢？
一般情况而言，一个系统在互不干扰的情况下可以激活的并发用户的进程数越多，该系统的并发性就越强。
可能影响并发性的一些原因分析：
当正在更改数据的进程阻止其他进程读取该数据时，或者当读取数据的进程阻止其他进程更改该数据时，并发行会减弱。另外，当多个进程试图同时更改相同数据时，且无法在不牺牲数据的一致性的前提下都能成功时，并发性也会受到影响。对于并发性的理解我们和容易联想到铁道部的订票网站。由于处理并发性能力不够，导致订票高峰出现奔溃现象，对网络订票造成不良影响。由此可见，一个大型网站的数据库系统提高处理并发性的能力是在必要。

处理并发性的方法：

SQLServer 2008提供两种方法：乐观和悲观两个模型。我们可以通过一下命令来指定：
SETTRANSACTION ISOLATION LEVEL（事物的隔离级别）来指定。

两者的区别：
在两种模型中，两个进程试图在相同时间修改相同数据时，可能会出现冲突。那么这两个模型之间的区别在于冲突是在出现前被避免还是在出现后采取某种方式进行处理。
悲观并发模型：
对于悲观并发SQL Server默认的行为是获取锁来阻塞对另一个进程正在使用的数据的访问。悲观并发假设系统中有足够的数据修改 *** 作，因而给定的任何一个读写 *** 作都可能受到另外一个用户的数据修改 *** 作的影响。悲观并发通过获得正在被读取数据上的锁，使其他进程无法修改该数据而避免冲突。换言之，在悲观模型下，读取者阻塞写入者，写入者也阻塞读取者。

乐观并发模型：
乐观并发假设系统中有足够少的数据修改 *** 作，因而任何单个事物都不太可能另一个事物正在修改的数据。乐观并发的默认行为是使用行版本控制来允许数据读取者看到修改之前的数据状态。数据行教老的版本被保存，所以读取数据的进程可以看到进程开始读取时的数据，不会受到对该数据正在做出任何更改的进程的影响。换言之，读取者不阻塞写入者，写入者也不阻塞读取者，但是，写入者可以而且会阻塞写入者，这也是导致冲突的原因所在。这时SQL Server在冲突出现时产生一个错误信息，但是是由应用程序来负责影响该错误。

上面将基本的事务介绍了一下，下面开始介绍并发。所以必须要介绍就是事务的“锁”。

4、事务中的锁

 事务中都含有什么锁呢？

最常用的锁：排它锁（独占锁）和共享锁，还有其他的锁，这里就不做介绍了，比如：更新锁、架构锁、意向锁等。

5、排它锁和共享锁

 排它锁：

当一个事务执行更新修改 *** 作的时候会申请排它锁，主要是在写 *** 作里面使用。需要注意的两点：1、一个事务含有排它锁，就不能含有其他任何锁。2、一条数据只能被一个排它锁锁住，就不能再被其他排他锁锁定。

共享锁：

 主要是在读 *** 作中使用，并且多个事务可以同时对一条数据使用共享锁。

排它锁和共享锁最重要的区别：排它锁是不能被控制他的处理方式和时间，但是共享锁是可以控制其隔离级别来控制其处理的时间。

1 begin transaction;2 update [Sales.Shippers] set companyname='顺丰' where shipperid=5;3 --事务还没有查询完成，为这条数据 加上一个 排它锁。这时这条数据就不能被其他进程 访问到

事务还没有执行完成，再开一个线程，执行查询 *** 作

1 select * from [Sales.Shippers] where shipperid=5

因为读 *** 作默认使用的共享锁，但是这个时候这条数据已经被其他线程的排它锁锁住，所以会造成阻塞，直到排它锁释放。

6、隔离级别 

 首先要先明白三点：

1、用于控制并发用户如何读写数据的 *** 做。

2、读 *** 作默认使用共享锁；写 *** 作需要使用排它锁。

3、读 *** 作能够控制他的处理的方式，写 *** 作不能控制它的处理方式

隔离级别分为六种：

read uncommited（读取未提交数据），read commited（读取已提交数据）读取的默认方式，repeatable read（可重复读），serializable（可序列化），snapshot（快照），read commited snapshot（已经提交读隔离）（后两个是sql server 2005 里面 引入的）。隔离的强度依次递增。

 1、read uncommitted:

1 select * from [Sales.Shippers] where shipperid=3;

 查询结果：

在本线程内执行：

1 begin transaction;2 update [Sales.Shippers] set companyname='圆通' where shipperid=3;

在另外一个线程内 使用 read uncommitted 隔离级别 查询数据：

1 --设置读 *** 作的隔离级别2 set transaction isolation level read uncommitted;3 select * from [Sales.Shippers] where shipperid=3;

 查询结果：

如果这个时候将那个事务回滚，那么这个时候  查询到的数据就是“脏数据”。

总结：

read uncommitted：最低的隔离级别：查询的时候不会请求共享锁，所以不会和排它锁产生冲突（不会等待排它锁执行完），查询效率非常高，速度飞快。但是缺点：会查到“脏数据”（排它锁的事务已经将数据修改，还没提交，这个时候查询到的数据 是已经更改过的。如果事务回滚，就是“脏数据”）

优点：查询效率非常高，速度非常快。

缺点：会产生“脏数据”

适用性：

适用于 像聊天软件的 聊天记录，会是软件的运行速度非常快。 但是不适用于 商务软件。尤其是银行

2、read committed

 读取的默认隔离级别就是read committed 和上面正好相反。如果上面情况，采用read committed 隔离级别查询的话查到的就是还没有更改之前的数据。

 所以在这里就不再演示。

3、repeatable read：

 查询的时候会加上共享锁，但是查询完成之后，共享锁就会被撤销。比如一些购票系统，如果查到票了，当买的时候就没有，这是不行的。所以要在查询到数据之后做一些延迟共享锁，进而阻塞排它锁来修改。

在查询线程里面执行sql语句：

1 set transaction isolation level repeatable read;2 begin transaction;3 select * from [Sales.Shippers] where shipperid=4;

然后在 另外一个线程内执行修改语句：

update [Sales.Shippers] set companyname='shit' where shipperid=4;

这个时候会将更改的线程阻塞掉：

4、serializable（可序列化）

 更高级的 隔离。用户解决“幻读”。就是使用上面的  加上共享锁 并不撤销，如果锁定的 一行数据，那么 其他的进程 还可以对 其他的数据进行 *** 作，也可以 进行新增和删除的 *** 作。   所以如果想要在查询的时候，不能对整张表进行任何 *** 作，那么就要 将表的结构也 锁定    （就需要使用 更强的 锁定）

在查询线程执行sql语句：

1 set transaction isolation level serializable;2 3 begin transaction;4 select * from [Sales.Shippers] where shipperid=3;

那么在另外一个线程执行下面两个语句，不论那一条语句都会阻塞住：

update [Sales.Shippers] set companyname='联邦' where shipperid=3;insert into [Sales.Shippers] (companyname,phone) values('韵达','12345678')

总结：

可序列话 隔离读 *** 作：用户 解决 幻影数据（将标的数据和表的结构都锁定）,是并发降低...隔离级别越高,并发越低,但是效率越低,所以不是要确定使用  最好不要使用

下面两种隔离级别是在 sql server 2005才出现的，隔离级别更高：

5、snapshot（快照）

 为数据产生一个临时数据库，当sql server 数据更新之前将当前数据库复制到 tempdb数据库里面，查询就是从tempdb数据库中查询

--设置数据库支持快照隔离级别：alter database ssdemo set allow_snapshot_isolation on;--这个时候会产生一个临时数据库（写 *** 作的排它锁锁定的是 现实存在的数据库，，读 *** 作的读取的是 临时数据库）

 在一个线程中执行 更新 *** 作，用排它锁锁定当前数据

begin transaction;--使用 排它锁（独占锁）X，锁定 下面的那条数据update [Sales.Shippers] set companyname='飞凤' where shipperid=3;

这个时候在在另外一个线程中查询这条数据（默认的隔离级别），就会将当前线程阻塞。

如果使用 snapshot 隔离级别查询就不会阻塞。

1 set transaction isolation level snapshot;2 --下面的就可以  从临时数据库中查询到数据3 begin transaction;4 --使用 共享锁 S5 select * from [Sales.Shippers] where shipperid=3;--查询到的 是还没有完成更新之前的数据

但是同时也会带来两个问题：

1、当 另外一个事务  已经提交，但是这边的查询到数据还是没有修改。因为 每次查询到的快照是针对于 本次回话对应的那个 transaction 的，因为在这个事务里面是没有修改的，所以查询到的数据是没有修改的。

2、（更新问题）因为 那边的数据已经是 飞凤公司了，但是这里还是   联邦,所以,在这个事务里面是不能对表进行修改,因为访问的是临时数据库,想要对 数据库修改是不可能的（sql server 就会报错，阻止修改） 

针对于上面两个问题，所以下面 更高的隔离级别出现了 read committed snapshot：

6、read committed snapshot

 首先开启数据库的 read committed snapshot 隔离级别：

1 --设置 数据库 为 读取已经提交的快照 开启2 alter database ssdemo set read_committed_snapshot on;

在一个线程中执行：

begin transaction;update [Sales.Shippers] set companyname='联邦' where shipperid=3;

在另外一个线程中：

1 --不用显示声明使用  read committed snapshot 隔离级别，因为设置完 read_committed_snapshot 隔离级别启动，默认就是 read commited snapshot 隔离级别2 begin transaction;3 select * from [Sales.Shippers] where shipperid=3;--查询到是 已经提交之后的数据4 5 update [Sales.Shippers] set companyname='xiaoxiao' where shipperid=3;

这个时候查询到的数据是还没有更改之前的，如果将 前面的那个回话提交，那么在查询 查询到的数据是 提交修改之后的数据。所以解决了上面的问题1.

如果在修改的话。也是在第一个 更新线程中的事务更新之后的数据进行执行修改的 *** 作，不会报错。