sql server 并发执行 sqlserver并发怎么处理

转载

智能创新者 2023-09-18 11:54:21

文章标签 sql server 并发执行 SQL Server2008 并发问题解决方案锁共享锁 文章分类 SQL Server 数据库

最近在使用数据库，综合各方面原因，选择了SQL Server2008.为了对使用的工具有更加深入的了解，所以仔细地阅读了经典翻译书籍《SQL Server 2008高级程序设计》，在此将读书过程中的感想和理解记录下来，以备以后查询温习，也希望对其他使用SQL Sercer的小伙伴们有启发帮助。
对数据库系统而言，并发是最主要的问题。它用于表示两个或多个用户同时对同一个对象进行交互的情况。用户越多——更特定的说，事物越多，即在同一时刻可以合理成功完成的事情越多，并发性也就越高。

在联机事务处理（Online Transaction Processing, OLTP）环境下，并发通常是在数据中首先要处理的事情。对于系统的性能来说，对并发问题的处理是至关重要的。在数据库中，对并发进行处理的基本方法，是一个称作锁定（locking）的过程。

锁是一种机制，用于防止一个过程在对象上进行操作时，同某些已经在该对象上完成的事情发生冲突。也就是说，如果某人已经先在对象上进行操作，你就不能再对该对象进行操作。在对象能操作还是不能操作，依赖于其他的用户正在做什么。

通过这种操作机制，SQL Server能够帮助我们避免那些因为并发问题而生成的多种不同错误。我们会对并发错误发生的可能性进行检查，并了解为了防止这些错误，应该如何设置事务隔离等级（isolation level）。

总的来说，防止并发问题主要有两种途径，分别是使用锁和设置隔离级别。使用锁来处理并发问题更加精细，需要对每一个操作对象进行锁的设置，这期间难免会带来争用的问题以及其他性能问题；设置隔离级别的方法是对用户的一次连接进行的整体设置，粒度较粗但是可以避免锁有时会带来的争用问题。设计者可以根据设计需要自行选择这两种方式。

一、使用锁解决并发问题

1、使用锁来解决什么问题

归纳来说，锁主要解决4类主要问题：脏读（dirty read）、不可重复读（No-Repeatable Read）、幻影（phantom）、丢失更新。

（1）脏读

何为脏读呢？当事务读取一条记录，而该记录是另一事务尚未完成的一部分时，就会发生脏读。如果每一个事物都是正常完成了，那么这看上去像是不会存在问题。但是，如果该事务被回滚了呢？！看下面这个例子：

事务1命令	事务2命令	逻辑数据库值	未提交数据库值	事务2显示内容
BEGIN TRAN		3
UPDATE col=5	BEGIN TRAN		5
SELECT anything	SELECT @var=col	3	5	5
ROLLBACK	UPDATE anything	3		5
	SET whatever=@var

这里就出问题了！！

事务2现在正在使用一个已经无效了的值！如果想要试图返回去审核，想看看该数字是从哪里来的，会因为发现根本无法追踪，这是非常令人头疼的事。

针对这种脏读的情况，如果使用SQL Server默认的事务隔离级别（称作READ COMMITTED）,就不会发生这种事了。

（2）不可重复读

类似于脏读，当在一个事务中两次读取记录，并且在两次读取之间，另一个单独的事务修改了该数据，这是会导致不可重复读。看下面这个例子：

事务1	事务2	@Var	事务1认为表中的值	表中的值
BEGIN TRAN		NULL		125
SELECT @Var = Value FROM table	BEGIN TRAN	125	125	125
	UPDATE value, SET Value = value-50			75
IF @Var>=100	END TRAN	125	125	75
UPDATE value, SET value =value-100
(完成，等待锁清除，然后继续）		125	25	-25(如果没有check约束强制大于0）或错误547（如果有CHECK约束）

在此例子中，约束要求value值必须是大于0的值。

可以看到，在实际的运行过程中，最终结果是-25，显然又出错了!

事务1已经预先扫描（在某些情况下这是很好的行为）以确认值是有效的，而且事务可以继续下去。问题在于，由于进行了UPDATE操作，事务2让事务1做出另外的决定性举动。如果表上没有任何CHECK约束可以避免负数值，那么实际上这个值会被设置为-25——尽管逻辑上看来这里已经通过IF语句防止此事的发生。

只有两种方法可以防止出现这个问题：

--创建CHECK约束并监视547号错误；

--设置隔离级别（isolation level）为REPEATABLE READ或SERIALIZABLE。

REPEATABLE READ或SERIALIZABLE带来的麻烦会很多。所以使用要慎重。比较而言，还是设置CHECK约束作用更明显。

（3）幻影

什么是幻影呢？我们通过一个例子来说明。

假设你正在开一个快餐厅，有很多雇员拿着政府规定的“最低工资”。政府刚刚决定将最低工资标准从每小时6.55美元提高到每小时7.25美元，你希望通过更新名为Employees的表，将那些收入少于每小时7.25美元的工资水平提高到最低工资水平。你会执行一个非常简单的语句：

UPDATE Employees

SET HourlyRate =7.25;

ALTER TABLE Employees

ADD CONSTRAINT ckWage CHECK (HourlyRate > =7.25)

这是小事一桩，对吗？错了！为了说明这个问题，这里你肯定会得到一条错误消息：

Msg 547, Level 16, State 1, Line 1

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ckWage'.The

conflict occurred in database 'FastFood', table 'Employees',column 'HourlyRate'.

当你快速运行一条SELECT语句，检查少于7.25美元的值，肯定会找到一条记录。问题是“你怎么会在这里呢！我刚刚才更新了应该修改的内容呀！”你的确运行了这条语句，而其它运行的很好——不过这里只是遇到了幻影。

这种幻影情况是比较罕见的，只有在特定的环境下才会发生。简言之，正在运行UPDATE更新的时候，如果另外有人恰好在此时执行了INSERT语句，就会出现这种情况。因为它是一个完整的新行，它上面没有锁，所以INSERT语句也执行得很好。

解决这个问题的唯一的办法就是将事务的隔离级别设置为SERIALIZABLE,此时对表的所有更新都不能在WHERE子句内部，否则它们就会被锁定。

（4）丢失更新

当成功将一个更新写入数据库中，但是又被另一个事务意外的覆盖了，就会发生丢失更新的现象。什么意思呢？看下面的这个例子：

假设你是公司的信用分析师。你接到一个电话，说你的客户X达到了他的信用额度，并希望提高它，因此你要提取这个客户的信息以查看详情。你看到客户的信用额度是5000美元，而且似乎总能按时还款。

在你浏览的时候，你所在的信用部门的另一个人Sally,正在提取客户X的记录，并更新了地址。在他提取用户记录的时候，记录中的信用额度是5000美元。

此时，你决定将客户X的信用额度提升到7500美元，并按下了回车键。数据库现在会显示对于客户X，其信用额度已经是7500美元了。

现在Sally完成了自己对地址信息的更改，但是他使用的是跟你相同的编辑屏幕——也就是说他更新的是整条记录。还记得在他的屏幕上信用额度显示的是多少吗？5000美元。数据库再次把客户X的信用额度设置为5000美元。你的更新丢失了！

要解决这个问题的方案取决于自己的代码，在读取数据和对数据进行更新的这两个时间间隔之内，如何识别另一个链接对记录的更新操作。使用不同的访问方式，识别机制也会发生很多变化。

在上面这个问题中，实际上就是两个人都提取了一条完整的记录，你改了前部分，我改了后部分。然后发生了互相覆盖。怎么解决这个问题呢？其实很简单，每个人在更新数据时，只改变自己改动的字段，没改动的就不要更新了就可以了。那么，这里就要涉及到一个可以锁定的资源的问题了。

2、可锁的资源

在SQL Server中，有6种资源是可锁的，它们形成分级层次。锁的级别越高，其粒度就越小（也就是说，如果在某些层叠的操作中，选择的锁级别越高，被锁定的对象数量也就越多，因为包含它们的对象已经被锁定）。这里按照粒度的升序列出了它们：

数据库：整个数据库都被锁定。这通常只在更改数据库模式的时候才会发生。
表：整个表被锁定。它包含了同该表相关联的所有数据对象，包括实际的数据行（其中的每项内容）以及同表相关联的所有索引上的所有键。
区段：锁定整个区段。记住一个区段由8页组成，所以区段锁定意味着锁控制了整个区段，在该区段上的8个数据或索引页，以及在这8页上的所有数据行。
页：锁定该页上的所有数据或索引键。
键：这是在索引上的个别键或一系列键上的锁。在同一个索引页上的其他键不受影响。
行或行标识（RID）：尽管锁从技术上说是放置在行的标识符（一个内部的SQL Server构件）上，但是实际上它可以锁定整个行。

基于更改数据内容的准确性考虑，当然是锁定的内容越精细越好。比如说操作时锁定一个字段肯定比锁定一个表要好得多，因为锁定一个字段并不会影响其他用户在同一时刻对其他字段的操作；但是如果锁定一个表，那么在这个用户对表内容进行操作的时候，其他用户就无法对表的任何内容进行操作了。但是，锁设置的过于精细也会带来性能上的负面影响。这就要考虑锁升级的问题了！

3、锁升级以及锁对性能的影响

升级是指认识到，当锁定的项目数量较少时，维持一个较精细的粒度级别（比如用行锁代替页锁）更加合理。

不过，越来越多的项目被锁定，维护这些锁所带来的开销会对性能产生影响。它可能会导致锁在一个地方存留的时间过长，并因此带来争用的问题。锁在某个位置上停留的时间越长，有人希望得到这个特定记录的可能性越大。

当要维护的锁的数量达到某个阈值的时候，锁被升级到下一个更高的级别，因为不用再对较低级别的锁进行紧密的管理（释放资源并提高速度，但不考虑争用）。

注意，升级是基于锁的数量，而不是用户的数量。重要性在于可以通过执行大规模更新来单独锁定一个表。行锁定可以升级为页锁定，然后再升级为表锁定。这意味着可以将其他所有的用户锁定在表之外。如果查询使用了多个表，就可以将每个用户都锁定在这些表之外。

锁的应用在解决了一些并行问题的同时，也带来了用户对于对象的争用问题。这也是不得不考虑的问题，因为没有用户想要在查询时还得不定时的等待。要想解决这个问题，就需要设计者合理的选择使用的锁的模式。什么是锁模式呢？一句话概括，就是有些模式的锁是相互排斥的，我工作时你就只能等着，我释放了你才能工作（这就会出现争用问题了）；而有些模式是可以同时工作的，也就是兼容的，那么大家就可以同时工作而互不影响了。为何达到理想的系统运行效果，设计者需要根据查询要求合理的设计选择每个任务的锁模式。

4、锁模式

（1）共享锁

这是最基本的锁类型。只有读取数据的时候才会用到共享锁——也就是说不需要修改任何东西。共享锁与其他的共享锁之间是兼容的。这并不意味着它不会给你带来麻烦——虽然共享锁不介意其他类型的锁，但还是有一些其他的锁不喜欢共享锁。

共享锁会告诉其他的锁你现在在这里。它们并没有什么目的性，不过却不容忽略。只是，拥有共享锁的事物可以防止用户在其上发生脏读之类的操作。

（2）排他锁

排他锁名副其实，它与其他类型的锁都不兼容。如果已经存在其他类型的锁，就无法使用排他锁。当排他锁活动的时候，在资源上也不能创建任何类型的新锁。这样可以防止两个人在同一时刻进行更新、删除或进行其他相关的操作。

（3）更新锁

更新锁是共享锁和排他锁之间的某种混合体。更新锁是一种特殊类型的占位符。请想象一下——为了进行UPDATE，需要验证WHERE子句（假设有一个WHERE子句），以推算需要更新的行。这意味着在真正需要物理更新之前只需要一个共享锁。在进行物理更新的时候，才需要排他锁。

更新锁意味着在完成对数据的初始扫描，从而确认需要更新的准确内容之后，共享锁变成排他锁。这表明在更新过程中要经历两个不同的阶段：

首先，第一个阶段是确认满足WHERE子句中的条件的内容（了解要更新的内容）。这是拥有更新锁的更新查询的一部分。
其次，在这个阶段，如果确实决定执行更新，就将锁换成排他锁。否则，转换为共享锁。

设计更新锁的目的和好处是形成一道屏障，以防止一种死锁变体的发生。死锁本身并不是一种锁类型，而是一种已经形成的矛盾的状态。如果因为另一个锁持有了相应的资源，一个锁就不能完成自己要做的事情从而释放，就会形成死锁。问题是，相对的资源在等待第一个事务的锁释放。

以如果没有更新锁为例，那么就会经常出现死锁。在共享方式下运行两个更新查询。查询A完成了自己的查询，并等待进行物理更新。他希望升级为排他锁，但是不可以，因为查询B正在完成自己的查询。查询B完成了自己的查询后，需要进行物理更新。为此，查询B希望将自己升级为排他锁，但是不可以，因为查询A还在等待。这就陷入了僵局。

与此相反，更新锁从创建伊始就禁止生成其他的更新锁。第二个事务试图获得更新锁的时候，这个新的事务会被置为等待状态，等待锁超时；在这段时间里，锁是不会被允许的。如果在该超时期限到期之前第一个锁释放了，那么该锁就会被授予新的请求者，随后可以继续这个过程。如果没有，就会产生一个错误。

更新锁只和共享锁和意向共享锁兼容。

（4）意向锁

意向锁是一个真正的占位符，用户处理对象层次问题。想象一下这样一种情况，假设在行上建立了一个锁，但是另一些人要在页上、或者在区上建立锁，或者对一个表进行修改。你肯定不希望有另外的事务以更高的级别在自己的事务的周围运行，是吧？

如果没有意向锁，这个更高级别的对象甚至不知道你已经在一个较低的级别上持有锁。意向锁可以提高性能，因为SQL Server不需要检测表中每行或页上的锁，只需要在表级别上检测意向锁就可以确认一个事务是否可以安全的锁住整张表。意向锁有三种不同的形式：

意向共享锁：已经或者将要在一个较低级别的位置上建立共享锁。例如在页上可以创建页级共享锁。这种类型的锁只应用在表和页上。
意向排他锁：它与意向共享锁类似，只是在较低级别的项目上要放置的排他锁。
意向排他锁共享：共享锁已经或将要位于对象层次结构的较底层，但是意向是要修改数据，所以它在某个时刻会变成意向排他锁。

（5）模式锁

它们有两种类型：

模式修改锁（Sch-M）:对模式的改变被应用在对象上。在Sch-M锁定期间，不能针对对象运行任何查询或其他CREATE、ALTER或DROP语句。
模式稳定锁（Sch-S）:它非常类似于共享锁。这种锁的唯一目的是为了防止在对象上为其他查询（或CREATE、ALTER和DROP语句）而生成的锁在活动时，出现Sch-M锁。它与其他所有类型的锁都兼容。

（6）批量更新锁

批量更新锁（BU）只是表锁的一种变种，其中只有一点（但是很重要）不同。批量更新锁允许并行载入数据——也就是说，表被锁定以防止其他任何“正常”（T-SQL语句）的活动，不过可以同时执行多个BULK INSERT或bcp操作。

到这里为止，我们已经详细的介绍了锁的作用以及各种锁的特点，大家如果已经将各种锁的功能牢记于心了，那么剩下的问题就是具体该怎么使用它们呢？接下来，我们就介绍怎么指定特定的锁类型！

5、指定特定的锁类型——优化器提示

有时候可能希望在查询中或在整个事务中更好地控制锁行为。这可以通过使用所谓的优化器提示来实现。

优化器提示是一种明确的告诉SQL Server将锁升级到指定级别的方法。它们位于要操作的表的名称的后面（在SQL语句中），并按照下边的方式被指定：

我们先来看一下具体使用锁的语法方式：使用它们的语法相当简单——只需要在表名后面加上它，或是在所使用的别名后加上它：

....

FROM <table name>[AS <alias>][[WITH](hint)]

下面几个例子中都用到了它们，所有的用法都是合法的，都会在SalesOrderHeader表上强行应用表锁（而不是应用键或行锁）：

SELECT * FROM SalesOrderHeader AS ord WITH (TABLOCKX )

SELECT * FROM SalesOrderHeader AS ord (TABLOCKX )

SELECT * FROM SalesOrderHeader WITH (TABLOCKX )

SELECT * FROM SalesOrderHeader (TABLOCKX )

上面黑体标出的部分就是指定的锁的类型，这里TABLOCKX表示排他锁表锁。还有很多其他类型的锁，将在下面这个表中一一列出，用法跟上面例子相同。

提示	描述
SERIALIZABLE/HOLDLOCK	一旦通过语句在事务中建立了锁，在事务结束（通过COMMIT或ROLLBACK）之后才会释放这个锁。在执行插入操作时，如果要插入的记录同建立该锁的查询中的WHERE子句中的条件相匹配（不是幻影），那么插入操作也被阻止。这是最高的隔离级别，并且绝对保证数据的一致性。
READUNCOMMITTED/NOLOCK	不获取锁（甚至不使用共享锁）也不支持其他的锁。这是一个非常快速的选项，可能产生脏读，也可能会产生其他问题
READCOMMITTED	这是默认选项。支持所有的锁，但是处理获得的锁的方式取决于数据库的 READ_COMMITTED_SNAPSHOT选项。如果打开这个选项，那么READCOMMITTED 不会获取锁，作为替代方案，他会通过使用一个行版本模式来确认是否发生了冲突。在实际情况下，这样做的效果不错，只有当需要提供向后兼容性，而且需要较好的性能时，才应该使用READCOMMITTED方法。
READCOMMITTEDLOCK	与上一条基本类似
REPEATABLEREAD	一旦在事务中通过语句建立了锁，直到事务结束才会释放这个锁（通过ROLLBACK或COMMIT）. 不过，可以插入新的数据
READPAST	不是等待锁被释放，而是跳过所有被锁定的行。这种跳过的行为仅限于行锁（仍然会等待页、区段和表上的锁），并且只能用在SELECT语句上
NOWAIT	立刻导致查询失败，而不是等待（如果检测到任何锁）
ROWLOCK	即使优化器选择其他较小的粒度锁定策略，它也会强行将锁的初始级别设置为行级。如果锁的数量达到系统的锁阈值，它不会阻止锁升级到更小的粒度级别
PAGLOCK	使用页级锁，不理会优化器所做出的其他选择。在两种方式中他比较有用。有时候你知道对于资源保存来说，页级锁比行锁更加合适。另一些时候，优化器会选择表锁，而你希望将争用最小化
TABLOCK	强制使用完整的表锁，不理会锁管理器使用了什么。事实上这样可以加快对已知表的扫描，但是如果其他用户希望修改表中的数据，也会导致比较大的争用问题。
TABLOCKX	同TABLOCK相似，不过要创建排他锁——根据TRANSACTION ISOLATION LEVEL的设置方式，在语句或事务期间，将其他所有的用户锁定在表外
UPDLOCK	使用更新锁来替代共享锁。在同死锁的斗争中，并没有充分发挥这个工具的功能。因为它仍然允许其他用户使用共享锁，不过会确保不会进行数据修改（其他更新锁），直至语句或事务终止（大概在继续运行并更新行之后）
XLOCK	虽然植根于TABLOCKX,这是它第一次出现在SQL Server2000中。这样做的好处是你不用理会自己已经选中（或没选中）的锁粒度，可以指定一个排他锁