SQL Server事务和并发性

类型：数据库类大小：1.7M语言：英语评分：6.6标签:立即下载一、什么是Transaction:

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

2.交易类型：

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

隐式事务：通常，我们使用的每个sql语句都是一个事务，但是事务在它们被执行后就结束了。

显示交易：我们需要手写，此时我们可以控制交易的开始和结束。

1-显式事务(事物可以被控制)2 3-开始事务4开始事务；5更新【销售。发货人] 6设置公司名称=' SF '，其中发货人ID=5；7 8从[销售]中选择*。托运人]；9 10-结束事务：11-第一：事务的回滚12回滚；13 14-类型2:提交事务15提交；

3.交易的四个重要属性：

1.原子性：事务必须是原子工作单元。——修改事务中的数据，要么全部执行，要么不执行。在事务执行完成之前(提交指令写入sql的事务日志之前)，如果出现问题或重启，sql server将回滚所有修改过的事务。但是，有一些异常错误不会回滚事务——3354，例如主键冲突和锁定超时。错误日志将捕获这些错误的指令并将它们记录在日志中，然后执行一些操作(例如回滚事务)

2.一致性：同一流程的修改和查询不会冲突。保持访问数据的一致性。

3.隔离：控制数据访问的机制；注意：事务正在修改表的数据，执行尚未完成；此时，另一个想要查询内部数据的事务找不到，必须等到修改后的事务执行。sql server采用“锁”机制来锁定被修改事务的表的数据。这是为了确保数据同步和数据一致性。

4.持久性：当事务的指令已经提交到事务日志中时，即使磁盘上的数据没有被修改，此时数据库的服务也会停止，重新启动服务时会执行事务日志中的指令(做出回复)。确保数据的持久性。

b、什么是并发？并发性可以定义为多个进程同时访问或更改共享数据的能力。既然是能力，一个系统的并发性会强也会弱。在这种情况下，如何判断一个系统的强弱及其分布？一般来说，一个系统在没有相互干扰的情况下能够激活的并发用户进程越多，系统的并发性就越强。分析一些可能影响并发的原因：当正在更改数据的进程阻止其他进程读取数据时，或者当读取数据的进程阻止其他进程更改数据时，并发性会减弱。此外，当多个进程试图同时更改相同的数据，并且在不牺牲数据一致性的情况下无法成功时，并发性也会受到影响。对于并发的理解，我们很容易想到铁道部的订票网站。由于并发处理能力不足，预约高峰出现崩溃，对网上预约产生负面影响。因此，大型网站的数据库系统有必要提高其并发处理能力。

处理并发性的方法：

SQLServer 2008提供了两种方法：乐观模型和悲观模型。我们可以通过以下命令来指定：SETTRANSACTION ISOLATION LEVEL。

它们的区别：在两个模型中，当两个进程试图同时修改相同的数据时，可能会有冲突。那么这两种模式的区别就在于冲突是在出现之前避免，还是在出现之后以某种方式处理。悲观并发模型：悲观并发SQL Server的默认行为是获取锁来阻止对另一个进程正在使用的数据的访问。悲观并发假设系统中有足够多的数据修改操作，因此任何给定的读写操作都可能受到另一个用户的数据修改操作的影响。悲观并发通过获取正在读取的数据的锁来避免冲突，这样其他进程就不能修改数据。换句话说，在悲观模式下，读者阻塞了作者，作者阻塞了读者。乐观并发模型：乐观并发假设系统中有足够多的数据修改操作，这样任何一个单一的事物都不太可能是被另一个事物修改的数据。乐观并发的默认行为是使用行版本控制来允许数据读取器在修改之前看到数据状态。数据行的旧版本被保存，所以读取数据的进程可以在进程开始读取时看到数据，并且不会受到进程对数据进行任何更改的影响。换句话说，读者不屏蔽作者，作者也不屏蔽读者，但作者可以也将屏蔽作者，这也是产生冲突的原因。此时，当冲突发生时，SQL Server会生成一条错误消息，但应用程序负责影响该错误。

上面介绍了基本事务，下面介绍并发。因此，必须引入的是交易的“锁”。

4.事务中的锁

事务包含哪些锁？

这里不介绍最常用的锁：排他锁、共享锁和其他锁，如更新锁、模式锁和意图锁。

5.排他锁和共享锁

排他锁：

当事务执行更新和修改操作时，它将申请排他锁，这主要用于写操作。有两点需要注意：1。如果事务包含排他锁，则它不能包含任何其他锁。2.一条数据只能被一个排他锁锁定，不能再被其他排他锁锁定。

共享锁：

它主要用于读操作，多个事务可以同时对一个数据使用共享锁。

独占锁和共享锁最重要的区别在于独占锁不能控制其处理模式和时间，而共享锁可以控制其隔离级别来控制其处理时间。

1开始交易；2更新【销售。发货人]设置公司名称=' SF '，其中发货人id=5；3-事务尚未被查询，因此向该数据添加一个排他锁。此时，其他进程无法访问这些数据

在事务完成之前，打开另一个线程来执行查询操作

1从[销售]中选择*。托运人]其中托运人=5

因为读操作默认使用共享锁，但是此时这个数据已经被其他线程的独占锁锁定，所以在独占锁被释放之前会导致阻塞。

6.隔离级别

首先，我们必须明白三点：

1.它用于控制并发用户读写数据的方式。

2.默认情况下，读取操作使用共享锁；写操作需要使用排他锁。

3.读操作可以控制其处理模式，而写操作不能控制其处理模式

有六个隔离级别：

读取未提交、读取已提交、默认读取模式、可重复读取、可序列化、快照、读取已提交快照(后两者在sql server 2005中介绍)。隔离的强度依次增加。

1、阅读未提交的：

1从[销售]中选择*。托运人]其中ship erid=3；

查询结果：

在此线程中执行：

1开始交易；2更新【销售。发货人]设置公司名称='童渊'，其中发货人id=3；

使用未提交读取隔离级别来查询另一个线程中的数据：

1-设置读取操作的隔离级别2-设置未提交读取的事务隔离级别；3从[销售]中选择*。托运人]其中ship erid=3；

查询结果：

如果此时回滚事务，此时查询的数据就是“脏数据”。

总结：

未提交读取：最低隔离级别：查询时不会请求共享锁，所以不会与排他锁冲突(不会等待排他锁执行)，查询效率很高，速度很快。但缺点是：会发现“脏数据”(排他锁的事务已经修改了数据，还没有提交，此时查询的数据已经更改。如果事务回滚，它就是“脏数据”)

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

缺点：会产生“脏数据”

适用性：

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

2、已提交阅读

读取的默认隔离级别是读取提交，这与上面的情况相反。在上述情况下，如果使用读提交隔离级别查询，则发现之前的数据没有被更改。

所以我就不在这里演示了。

3、可重复读取：

共享锁将在查询过程中添加，但在查询完成后共享锁将被撤销。比如在一些票务系统中，如果找到了票，买的时候就没有了，这是不可能的。因此，我们应该在查询数据后做一些延迟共享锁，然后阻塞排他锁来修改它们。

在查询线程中执行sql语句：

1设置事务隔离级别可重复读取；2开始交易；3从[销售]中选择*。托运人]其中ship erid=4；

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

更新[销售。Shippers]设置companyname='shit '，其中shipper id=4；

此时，更改的线程将被阻止：

4.可序列化(可序列化)

更高级的隔离。用户解决“魔法阅读”。也就是说，使用上述共享锁不会被撤销。如果一行数据被锁定，其他进程也可以对其他数据进行操作，也可以对其进行增删。因此，如果您想要查询，您不能对整个表做任何事情，那么您应该锁定表的结构(您需要使用更强的锁)

在查询线程上执行sql语句：

1设置事务隔离级别可序列化；2 3开始交易；4从[销售]中选择*。托运人]其中ship erid=3；

然后在另一个线程中执行以下两个语句，无论哪个语句将被阻塞：

更新[销售。发货人]设置公司名称=“联盟”，其中发货人id=3；插入[销售。发货人](公司名称、电话)值('大云'，' 12345678 ')

总结：

顺序隔离读取操作：当用户解决幻像数据(锁定目标数据和表的结构)时，并发性降低.隔离级别越高，并发越低，但是效率越低，所以不需要确定最好不要使用。

以下两个隔离级别仅出现在sql server 2005中，并且隔离级别更高：

5.快照

为数据生成临时数据库，并在更新sql server数据之前将当前数据库复制到tempdb数据库。查询是从tempdb数据库中查询。

-设置数据库支持的快照隔离级别：alter database ss演示将allow _ snapshot _ isolation设置为on；-此时，将生成一个临时数据库(写操作的排他锁锁定真实数据库，读操作读取临时数据库)

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

开始交易；-使用排他锁(exclusive lock) x锁定以下数据更新[sales。发货人]设置公司名称='封飞'，其中发货人id=3；

此时，在另一个线程(默认隔离级别)中查询该数据将阻塞当前线程。

如果使用快照隔离级别，查询将不会阻塞。

1设置事务隔离级别快照；2-可以从临时数据库中查询以下数据3开始交易；4-使用共享锁S5从[销售]中选择*。托运人]其中托运人id=3；-查询是更新完成前的数据

但这也带来了两个问题：

1.提交另一笔交易后，此处找到的数据未被修改。因为每次查询的快照都是针对该回复对应的事务，并且该事务中没有修改，所以查询的数据不会被修改。

2.(更新问题)因为那里的数据已经是封飞公司的，但是还是联邦的，所以不可能修改这个事务中的表，因为是临时数据库，不可能修改数据库(sql server会报错，阻止修改)

鉴于上述两个问题，读提交快照出现在以下更高的隔离级别：

6、读取提交的快照

首先打开数据库的读取提交快照隔离级别：

1-将数据库设置为打开2 alter database ss演示将read _ committed _ snapshot设置为on以读取提交的快照；

在一个线程中执行：

开始交易；更新[销售。发货人]设置公司名称=“联盟”，其中发货人id=3；

在另一个线程中：

1-使用读提交快照隔离级别而不显示声明，因为将read_committed_snapshot隔离级别设置为start后，默认为读提交快照隔离级别2 begin transaction3从[销售]中选择*。托运人]其中ship erid=3；-查询的数据是提交的数据4 5更新[销售。发货人]设置公司名称='小小'，其中发货人id=3；

此时，查询到的数据尚未更改。如果提交了之前的回复，查询到的数据就是修改后的数据。因此，上述问题得以解决。

如果要修改的话。也是第一个更新线程中事务更新后的数据，不会报错。