SQL SERVER并发调用存储过程 sql server并发控制

并发可以定义为多个进程同时访问或修改共享数据的能力。处于活动状态而互不干涉的并发用户进程的数量越多，数据库系统的并发性就越好。当一个正在修改数据的进程阻止了其他进程读取该数据，或者当一个正在读取数据的进程阻止了其他进程修改该数据，并发性就降低了。本文用术语“读取”或者“访问”描述数据上的SELECT操作，用“写入”或“修改”描述数据上的INSERT,UPDATE以及DELETE操作。

一般地，数据库系统可以采用两种方式来管理并发数据访问，乐观并发控制和悲观并发控制。

并发控制模型

对于任何一种并发控制模式，如果两个事务试图同一时刻修改数据的话都会产生冲突。这两种模式之间的区别在于，是在冲突发生前进行防止，还是发生后采取某种方法来处理冲突。

悲观并发控制

对于悲观并发控制，该模型假定系统中存在足够多的数据修改操作，以致于事务的任何数据读取/修改操作都可能受到其他事务数据修改操作的影响，即假定冲突总是会发生的。SQL Server默认通过锁（lock）来保证读者和写者之间的互斥。

乐观并发控制

对于乐观并发控制，该模型假定系统中存在非常少的相互冲突的数据修改操作，以致任何单独的事务都不太可能修改其它事务正在修改的数据。乐观并发控制默认采用行版本控制来处理并发。
例如，在读取数据时我们会得到一个数据的版本version 1，当需要修改数据时，我们先检查数据的版本是不是version 1，如果是就修改数据；如果不是，就说明在当前事务的读操作和写操作之间已经有别的事务对数据进行了修改（每次修改操作都会使得数据的版本+1），SQL Server将会产生一个错误消息，由上层应用程序响应此错误。

事务处理

无论是采用哪种并发控制模型，对于事务的理解是至关重要的。事务是SQL Server中任务的基本单位。典型地，它由几个读取和修改数据的SQL命令组成，但是直到COMMIT命令被执行以后，修改操作才被认为是终结了。

ACID属性

原子性（Atomicity）
SQL Server保证事务的原子性。原子性指的是每个事务要么全部执行，要么什么都不执行。也就是说，如果一个事务提交了，它造成的所有效果都会被保留。如果中止了，其所有效果都会被撤销。
一致性（Consistency）
一致性属性确保事务不允许系统到达一个不准确的逻辑状态——数据必须总是保持逻辑上的正确。即使在发生系统故障时，约束和规则必须得到保证。（一致性一般被原子性、隔离性以及持久性所涵盖，并且概念上会产生重复）
隔离性（Isolation）

隔离性会将并发事务与其他并发事务的更新操作分隔开。当该事务正在执行时，其他事务是无法看到进行中的任务的。SQL Server会在事务之间自动实现隔离。它采用锁定数据或者行版本使得多个并发事务能够并发操作数据，以防止导致不正确结果。

隔离性意味着事务必须在不干扰其他事务的前提下独立执行。换言之，在事务执行完毕之前，其所访问的数据不能受系统其他部分的影响。

持久性（Durability）
当事务提交之后，SQL Server的持久性属性就会确保该事务的作用持续存在（即使发生系统故障）。如果在事务进行过程中发生系统故障，事务就会被完全撤销，不会在数据上遗留部分作用。如果在事务的提交确认被发送到调用的程序之后立刻发生故障，数据库会确保该事务的存在。预写式日志以及SQL Server启动恢复阶段的事务自动回滚/自动重做机制能够确保持久性。

一致性问题

事务总是全部支持ACID属性的。事务可能还会表现出一些另外的行为，称为“一致性问题”，而我并不认为它们是“问题”。它们仅仅是可能存在的行为，而用户能够决定允许哪些和阻止哪些，用户对于隔离级别的选择决定了下列这些行为中哪些是被允许的。

丢失更新

当两个事务读取相同数据并且都处理该数据（修改了它的值），然后都尝试更新原来的数据成新的值时，这种行为就会发生了。第二个事务可能完全覆盖掉第一个所完成的更新。

时间	取款事务A	取款事务B
T1	开始事务
T2		开始事务
T3		查询账户余额为1000
T4	查询账户余额为1000
T5		取出100，存款余额为900
T6	取出300，存款余额为700
T7	提交事务
T8		提交事务

最终账户余额为900，取款事务A的更新丢失了。丢失更新是这些行为中唯一一个用户可能在所有情况下都想避免的行为。

脏读

这种行为在一个事务读取未提交数据时会发生，如果一个事务修改了数据但是尚未提交修改，而另一个正在读取数据的事务会读到这个修改从而导致一种不一致的状态发生。

时间	查询事务A	取款事务B
T1	开始事务
T2		开始事务
T3		查询账户余额为1000
T4		取出100，存款余额为900
T5	查询账户余额为900
T6		撤销事务，恢复为1000
T7	提交事务

查询事务A读取到取款事务B还未提交的余额900。
默认情况下，脏读是不允许的。谨记：更新数据的事务是无法控制别的事务在它提交之前读取其数据的，这是由读取数据的事务来决定是否想要读取未必会被提交的数据。

不可重复读

这种行为又被称为“不一致分析”。如果同一事务分别以两个读操作读取相同资源时，可能会得到不同的值，这就是不可重复读。

时间	查询事务A	取款事务B
T1	开始事务
T2		开始事务
T3	查询账户余额为1000
T4		取出100，存款余额为900
T5	查询账户余额为900
T6		提交事务
T7	提交事务

查询事务A两次读取余额获取到不同结果。

 

幻读

这种行为产生于一个数据集内的部分数据被修改时。如果事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。

时间	取款记录处理事务A	取款事务B
T1	开始事务
T2		开始事务
T3	查询到5条取款记录
T4		查询余额为1000元
T5		取出100，存款余额为900
T6	查询到6条取款记录
T7	提交事务
T8		提交事务

对于取款记录处理事务A，两次查询的结果集不同。

 

事务的行为取决于隔离级别，也就是决定上述四种行为中那些是被允许的。并发控制模型决定了隔离级别是如何实现的——或者更明确的讲，决定了SQL Sever是如何确保用户所不想要的行为不发生的。

隔离级别

SQL Server支持五种隔离级别来控制读操作的行为。其中三个只在悲观并发模型中可用，一个只在乐观并发模型中可用。剩下的一个在两个模式下都是可用的。

未提交读

 

除了丢失更新以外，上面提到的其他行为都可能发生。未提交读是通过使读操作不占用任何锁来实现的，当前事务能够读取其他事务已经修改过但是尚未提交的数据。当采用未提交读时，用户是放弃了对高一致性数据的把握而趋向于支持系统的高并发能力，使用户不会再互相锁定对方。那么，何时才应该选择未提交读呢？显然，每笔数据都须保证平衡的金融交易是不适合的。而对于某些决策支持分析来说可能会很适合（譬如，需要察看销售走势时），因为完全没有必要做到完全精确而且会带来并发性能的提升，因此是相当值得的。

 

 

已提交读

已提交读是数据库引擎的默认级别。SQL Server 2005支持两种已提交读的隔离级别，这种隔离级别既可以是乐观的也可以是悲观的，默认采用悲观并发控制。为了区分，悲观实现称“已提交读（锁定）”，乐观实现称为"已提交读（快照）”。已提交读可以防止脏读。
已提交读隔离级别保证了一个操作不会读到别的程序已经修改但是尚未提交的数据。如果别的事务正在更新数据并因此在数据行上持有排它锁，当前的事务就必须等待这些锁释放后才能使用这个数据（无论是读取还是修改）。同样地，事务必须至少在要被访问的数据上加上共享锁，其他事务可以读取数据但是不能修改数据。默认，共享锁在数据读取过后就被释放掉，而无需在事务的持续时间内保留。

 

 

已提交读（快照），也能保证一个操作不会读到未提交数据，但不是通过迫使其他进程等待的方式。对于已提交读（快照），每当一行数据被修改后，SQL Server就会生成该行数据前一次已提交值的一个版本（version），被修改的数据仍旧被锁定着，但是其他进程可以看到该数据在更新操作开始之前的版本。

 

可重复读

 

可重复读是一种悲观的隔离级别。它在已提交读的基础上增加了新的属性：确保当事务重新访问数据或查询被再一次执行时，数据将不再发生改变。换句话说，在一个事务中执行相同的查询两次是不会看到由其他事务所造成的任何数据的改变的。然而，可重复读隔离级别还是允许幻读的出现。在某些情况下，防止不可重复读是用户向往的一种安全措施。但是世上没有免费的午餐，这种额外的措施所带来的开销是事务中所有的共享锁必须保留到事务完成为止。
排它锁必须总是保留到事务结束为止，无论采用何种隔离级别或者并发模型，这样事务才能在需要时被回滚。如果锁提前释放了，就不太可能完成撤销操作，因为其他并发事务可能已经使用了同一数据，并且修改了它的值。
只要事务是打开的，没有其他用户可以修改被该事务所访问的数据。显然这会严重降低并发性和性能。因此，如果事务不保持简短或者编写应用程序时没有能够注意到这样潜在的锁竞争问题，将会导致大量的事务因为等待锁释放而挂起。

 

 

快照

快照隔离是一种乐观隔离级别，类似于已提交读（快照），如果当前版本被锁定住时，它允许其他事务读取已提交数据的早期版本。快照隔离和已提交读（快照）的区别与（早期版本该有多早、保留多少个早期版本）这个问题相关，我们在行版本控制小节中详述。尽管快照隔离所避免的行为和可串行化所避免的是相同的，但是快照隔离并不是真正意义上的可串行化隔离级别。对于快照隔离，可能会有两个个事务同时执行，并引起一个任何序列化执行都不可能产生的结果。

 

 

如果两个事务并行地运行，最终会交换titles表里两本书的价格。然而，不存在一种序列化执行的方式最终导致数值的交换。无论是先执行事务1然后执行事务2，还是先执行事务2再执行事务1，任何序列顺序最终将导致两本书拥有相同的价格。

可串行化

可串行化也是一种悲观隔离级别。可串行化隔离级别在可重复读的基础上增加了新的属性：确保在重新执行查询时，SQL Server不会在中间的过渡期增加新的行。换句话说，如果同一事务在相同的查询被执行两次的话，幻读不会出现。可串行化也因此成为最健壮的悲观隔离级别，因为防止了之前所描述的所有可能的“不一致问题“。
额外的安全措施必定会带来额外的开销。可串行化隔离级别下，事务中的所有共享锁都必须保留到事务完成为止。另外，执行可串行化隔离级别不仅需要锁定已读数据，还需要锁定那些不存在的数据，参看后面的键范围锁。

锁定

对于多用户数据库系统而言，锁定是一个至关重要的功能。锁在悲观和乐观并发控制模型中都有所应用，尽管在每种模型中其他事务处理“被锁定数据”的方式是不同的。在悲观模型中，写者总是阻塞读者和写者，而读者也会阻塞写者。对于乐观模型，唯一可能发生的阻塞是写者阻塞其他写者。

锁定的基本概念

SQL Server可以使用几种不同方式来锁定数据，举例来说，读操作获取共享锁而写操作获取排他锁。更新锁在更新操作的开头部分获取。SQL Server会自动获取并释放所有这些类型的锁。它还负责管理锁定模式之间的兼容性，解决死锁问题，并在需要的时候进行锁升级。它在表、表的分页、索引键以及单独的数据行上支配锁。

锁定类型

共享锁

当数据被读取时，SQL Server自动获取共享锁。许多事务可以在同一数据上都持有共享锁，但是没有事务可以在已经有一个共享锁存在的情况下，在该数据上再获取一个排他锁。一般的，当数据已经读取完毕后，共享受就会立即释放掉，但是可以通过使用查询提示或者采用不同的事务隔离级别来改变这种默认方式。

排它锁

当数据被插入、更新或者删除操作修改以后，SQL Server就会自动获取数据上的排他锁。一次只能有一个事务持有特定数据资源上的排它锁。排它锁会保留到事务结束为止。这就意味着被修改的数据通常在当前事务提交或者回滚之前对其他事务来说是不可用的。其他事务可以通过使用查询提示来读取被排它锁锁定的数据。

更新锁

更新锁实际上并不是一种独立的锁，他是共享锁和排他锁的一种混合。当SQL Server执行一个数据修改操作但是首先需要搜索表以寻找到被修改的资源时，更新锁就会被获取。更新锁能够预防锁升级而产生的死锁，SQL Server保证更新锁的持有者能够将其转化成排他锁，死锁就可以避免了。

 

更新锁本身不足以使用户能够修改数据——所有的数据修改都要求被修改的数据资源上存在一个排它锁。只要有一个事务对资源持有更新锁，其它事务就无法获取该资源的更新锁或者排他锁了。持有更新锁的事务能够将其转换成该资源上的排它锁，因为更新锁避免了与其他进程之间的锁的不兼容。可以将更新锁看作是“意图更新锁”，这才是它实际所扮演的角色。更新锁会保留到事务结束或者当它转换成排他锁。
不要被锁的名字误导，更新锁并不只是针对更新操作而设计的。SQL Server使用更新锁适用于任何需要进行实际修改之前搜索数据的数据修改操作。这样的操作包括受限更新及删除，也包括在带有聚集索引的表上进行的插入操作。对于后面一种情况，SQL Server必须先搜索数据（使用聚集索引）以找到正确的位置来插入新的记录。当SQL Server只进行到搜索阶段时，它会采用更新锁来保护数据，而只有当它找到正确的位置并开始插入以后才将更新锁升级为排他锁。

意向锁

意向锁实际上并不是一种独立的锁定模式。你可以拥有意向共享锁，意向排他锁甚至意向更新锁。由于SQL Server可以在不同级别的粒度上获取锁，因此需要一种机制来指出一个资源上的组件已经被锁定了。例如，如果一个事务试图锁定一张表，SQL Server需要采用一种机制来判断是否这张表上的行（或者一个分页）已经被锁住了。意向锁就是起这个作用，在了解锁的粒度时会深入研究意向锁。

键范围锁

只在可串行化隔离级别中为了锁定一定范围内的数据而被获取。共享锁和排它锁可以在表、分页、行或者键上获取，而键锁只能从键上获取。

锁的粒度

SQL Server可以锁定表、分页、行等级别的数据资源。它同样可以锁定索引键及一定范围内的索引键。谨记如果表上存在聚集索引，数据行就在聚集索引的叶级，并且是由键锁而不是行锁来锁定它们的。

 

SQL Server对每个锁都进行追踪并且包含了锁、被锁定资源（行、键或分页）、锁的模式以及特定资源的一个标识符。当一个事务申请锁时，SQL Server会将所申请的锁与已经申请的锁进行比较并寻找完全匹配资源类型以及标识符的锁。但是，如果一个事务在表中的某行上占有一个排他锁，别的事务可能会尝试在整张表上获取一个锁。由于是两种不同的资源，SQL Server不会找到一个完全的匹配，这就需要使用意向锁了。SQL Server会记录在表的一行记录上拥有排他锁的事务也在包含该行记录的分页上占有一个意向锁，以及在包含该行记录的这张表上拥有一个意向锁。当其他事务试图获取这张表上的一个排他锁时，其他事务将会被阻塞。

键锁

SQL Server支持两种类型的键锁，而它采用哪种类型则取决于当前事务的隔离级别。如果隔离级别是已经提交读、可重复读或者快照，SQL Server会在处理查询时尝试锁定实际被访问的索引键。对于聚集索引的表而言，数据行就是索引的叶级别，而用户可以看到所获取的键锁。如果表是堆结构的话，用户可能会看到非聚集索引上的键锁以及实际数据上的行锁。
如果隔离级别是可串行化，情况就有所不同了。为了防止幻读，如果一个事务中扫描了一个范围内的数据就需要充分锁定住该表以确保没人能够插入新值到已扫描的范围内。在SQL Server早期版本中是通过锁定整个分页甚至整张表来保证这一点的。在许多情况下，这可能导致了更大范围的数据被锁定住了，造成了不必要的资源竞争。SQL Server 2005采用了一种称为“键范围锁”的单独锁模式，与索引中的特定键值相关联并表明在索引中这两个键之间的所有值被锁定住了。

锁的兼容性

锁简称

简单兼容性矩阵

完整兼容性矩阵

 

行级锁定VS分页锁定

锁粒度越小，加锁操作越频繁，管理锁带来的开销就越大。但是，锁粒度越小，冲突率越小，并发性能会更好。每种类型的锁定在针对不同类型的程序和处理方法时都会显示出其独特的优势，因此选中那种类型的锁定，取决于应用程序和数据。

锁升级

SQL Server在适当的时候会自动将行、键或者分页级锁升级为粒度更粗的表级锁。这种升级保护了系统资源（防止系统使用太多的内存在追踪锁），并且提高了效率。例如，在一个查询获取许多行级锁后，锁级别可以升级为表级锁，因为这时获取并持有一个单独的表级锁比持有许多行级锁可能更有意义。

死锁

当两个事务都在等待获取资源，但是由于相互阻碍对方获取资源导致没有事务能够前进时就会发生死锁。

行级版本控制

乐观并发控制采用了一种称为行版本控制的新技术来保障事务。在使用乐观锁并发控制时会获取排他锁。乐观并发和悲观并发的区别在于乐观并发中写操作与读操作之间不会互相阻塞。换句话说就是，当被请求资源当前拥有共享锁时，申请排它锁的事务不会被阻塞，相反，当被请求资源当前拥有排他锁时，申请共享锁的进程也不会被阻塞。
一旦启用乐观并反控制，SQL Server就使用tempdb数据库来存储所有已经修改过的记录的副本，并且只要存在来自任意事务的访问需求，就会继续维持这些副本。当tempdb用来存储被修改记录的早期版本时，就其称为版本存储区。

行版本控制的实现

SQL Server引入了一种新的隔离级别：快照隔离以及一种新式的无阻塞风格的已提交读隔离——已提交读（快照）。这些基于版本控制的隔离级别允许读者获取行的一个先前已提交过的值而不会产生阻塞，这样就提高了系统的并发能力。为了使它起作用，SQL Server必须在行被修改或删除时保留旧版本的记录。如果在同一行上进行多次更新，SQL Server就可能需要维护该行的多个早起版本。鉴于此，行版本控制有时也被称为多版并发控制。
当表或索引中的一行数据被更新时，SQL Server会用执行更新的那个事务的事务序列号来标记新的行。事务序列号是一个单调递增的数字，在每个SQL Server的实例中保证唯一。在更新一行数据时，之前的版本存放在版本存储区内，而新的行包含一个指向版本存储区中旧的行数据的指针。版本存储区里旧的行数据可能包含了指向更早版本的指针。一条行记录的所有版本串接成一个链表。SQL Server可能需要沿着链表中的几个指针才能到达一个正确的版本，只要有操作需要引用它们，行的版本就必须在版本存储区内保存。

在应用程序使用默认的悲观模型造成的并发性下降而不能令人满意时，SQL Server可以改用乐观并发控制模型。在切换到基于乐观版本控制的隔离级别之前，用户必须仔细权衡使用新型并发模型的效果。处理需要额外的管理来为版本存储区监控tempdb以外，鉴于维护旧版本锁带来的额外工作量，版本控制还会降低更新操作的性能。即使当前没有人在读取数据，更新操作也得为此买单。如果有使用行版本控制的读操作，它们必须花费额外的开销来遍历链表指针，以找到需要的行数据的合适版本。
另外，由于快照隔离的乐观并发模型假定系统不会发生很多的更新冲突，如果用户预见到在同一数据上的并发更新会产生竞争，就不应该选择快照隔离级别。快照隔离级别能够使读者不被写者阻塞，但是并发的写者仍然不被允许。在默认的悲观模型中，第一个写者会阻塞所有的后续写者，但如果采用快照隔离，后续写者实际上会接受到错误消息且应用程序需要重新提交初始请求。

基于快照的隔离级别

已提交读快照隔离（RCSI）

已提交读快照隔离是一种语句级的快照隔离，也就是任何查询都能看到在语句开始那一刻最近提交过的数值。假设在启用了RCSI的数据库上有如下两个事务，且在事务开始运行之前Product 922的ListPrice值是8.89

注意当时间为2时，事务1所作出的修改尚未提交，因此Product ID=922的行上仍然持有锁。但是事务2不会被这个锁阻塞住，它能够访问该行数据上一次已提交的ListPrice值8.89。这仍然属于已提交读隔离级别（一个无阻塞的变种），所以不能防止“不可重复读”。
RCSI最大的益处是可以引入更好的并发性，因为读者与写者之间不会相互阻塞。但是写者之间还是会发生阻塞，因此标准的加锁机制适用于全部的更新、删除和插入操作。

快照隔离（SI）

SI提供了数据的一种事务一致性视图。任何读取操作都将得到在事务开始那一刻最近已提交过的数据版本（对于RCSI，会得到在语句开始那一刻最近已提交过的数据版本）。需要谨记的一个要点：事务并不是从BEGIN TRAN语句开始的，对于SI来说，事务是在第一次访问数据库内任意数据的时候才开始的。

尽管事务1已经提交了，事务2继续返回它读到的初始值8.99，直到事务2完成为止。只有在事务2完成以后，该链接才能读到ListPrice的新值。

更新冲突

两种乐观并发级别之间的重要区别在于：SI可能会造成潜在的更新冲突。

冲突发生是因为事务2在Quantity值为324的时候开始，当这个值被事务1更新后，行版本324被存储到版本存储区内。事务2会在事务的持续时间内继续读取该行数据。如果两个更新操作都被允许成功执行的话，就会产生经典的更新丢失情形。事务1增加了200个数量，然后事务2会在初始值上增加300个数量并存储。由第一个事务增加的那200个产品就会彻底丢失，SQL Server不会允许这样的情况发生。
当事务2开始尝试执行更新时，并不会立刻得到一个错误——仅仅是被阻塞。事务1在行上拥有一个排他锁，因此事务2尝试获取排他锁时会被阻塞。如果事务1回滚，那么事务2就能够完成更新。但事务1最终被提交了，SQL Server检测到一个冲突并产生错误。
冲突只可能发生在SI模式下，因为SI隔离级别是基于事务而不是基于语句的。如果上述例子在一个采用RCSI的数据库中执行，事务2执行的更新语句不会使用该数据的原来值。当试图读取当前的Quantity值时，它会被阻塞住，而接着事务1完成时，它就能读取更新过的Quantity将其作为当前值并再增加300，没有一个更新会丢失。
如果用户选择工作在SI模式下就需要注意可能发生的冲突，它们能够被减少到最低限度，但是如同死锁一样，用户不能保证不发生冲突。用户必须写程序来合理地处理冲突，并且不能想当然地认为更新已经成功了。如果冲突只是偶然发生，用户可能需要将其作为使用SI模式的部分代价考虑在内，但如果冲突太过频繁，就需要额外措施来避免冲突。