SQL Server锁可以解决哪些问题 详细如何应用

今天小编跟大家讲解下有关“SQL Server锁可以解决哪些问题，具体如何应用”的内容 ，相信小伙伴们对这个话题应该有所关注吧，小编也收集到了相关资料，希望小伙伴们看了有所帮助。

锁是一种防止在某对象执行动作的一个进程与已在该对象上执行的其他进行相冲突的机制。也就是说，如果有其他人在操作某个对象，那么你旧不能在该对象上进行操作。你能否执行操作取决于其他用户正在进行的操作。

通过锁可以防止的问题

锁可以解决以下4种主要问题：

1、脏读

如果一个事务读取的记录是另一个未完成事务的一部分，那么这时就发生了脏读。如果第一个事务正常完成，那么就有什么问题。但是，如果前一个事务回滚了呢，那将从数据库从未发生的事务中获取了信息。

2、非重复性读取

很容易将非重复性读取和脏读混淆。如果一个事务中两次读取记录，而另一个事务在这期间改变了数据，就会发生非重复性读取。

例如，一个银行账户的余额是不允许小于0的。如果一个事务读取了某账户的余额为125元，此时另一事务也读取了125元，如果两个事务都扣费100元，那么这时数据库的余额就变成了-75元。

有两种方式可以防止这个问题：

创建CHECK约束并监控547错误

将隔离级别设置为REPEATABLEREAD或SERIALIZABLE

CHECK约束看上去相当直观。要知道的是，这是一种被动的而非主动的方法。然而，在很多情况下可能需要使用非重复性读取，所以这在很多情况下是首选。

3、幻读

幻读发生的概率非常小，只有在非常偶然的情况下才会发生。

比如，你想将一张工资表里所有低于100的人的工资，提高到100元。你可能会执行以下SQL语句：

UPDATE tb_Money SET Salary = 100

WHERE Salary < 100

这样的语句，通常情况下，没有问题。但是如果，你在UPDATE的过程中，有人恰好有INSERT了一条工资低于100的数据，因为这是一个全新的数据航，所以没有被锁定，而且它会被漏过Update。

要解决这个问题，唯一的方法是设定事务隔离级别为SERIALIZABLE,在这种情况下，任何对表的更新都不能放入WHERE子句中，否则他们将被锁在外面。

4、丢失更新

丢失更新发生在一个更新成功写入数据库后，而又意外地被另一个事务重写时。这是怎么发生的呢？如果有两个事务读取整个记录，然后其中一个向记录写入了更新信息，而另一个事务也向该记录写入更新信息，这是就会出现丢失更新。

有个例子写得很好，这里照敲下来吧。假如你是公司的一位信用分析员，你接到客户X打开的电话，说他已达到它的信用额度上限，想申请增加额度，所以你查看了这位客户的信息，你发现他的信用额度是5000，并且看他每次都能按时付款。

当你在查看的时候，信用部门的另一位员工也读取了客户X的记录，并输入信息改变了客户的地址。它读取的记录也显示信用额度为5000。

这时你决定把客户X的信用额度提高到10000，并且按下了Enter键，数据库现在显示客户X的信用额度为10000。

Sally现在也更新了客户X的地址，但是她仍然使用和您一样的编辑屏幕，也就是说，她更新了整个记录。还记得她屏幕上显示的信用额度吗？是5000.数据库现在又一次显示客户X的信用额度为5000。你的更新丢失了。

解决这个问题的方法取决于你读取某数据和要更新数据的这段时间内，代码以何种方式识别出另一连接已经更新了该记录。这个识别的方式取决于你所使用的访问方法。

可以锁定的资源

对于SQL Server来说，有6种可锁定的资源，而且它们形成了一个层次结构。锁的层次越高，它的粒度就越粗。按粒度由粗到细排列，这些资源包括：

数据库：锁定整个数据库。这通常发生在整个数据库模式改变的时候。

表：锁定整个表。这包含了与该表相关联的所有数据相关的对象，包括实际的数据行(每一行)以及与该表相关联的所有索引中的键。

区段：锁定整个区段。因为一个区段由8页组成，所以区段锁定是指锁定控制了区段、控制了该区段内8个数据或索引页以及这8页中的所有数据航。

页：锁定该页中的所有数据或索引键。

键：在索引中的特定键或一系间上有锁。相同索引页中的其他键不受影响。

行或行标识符：虽然从技术上将，锁是放在行标识符上的，但是本质上，它锁定了整个数据行。

锁升级和锁对性能的影响

升级是指能够认识到维持一个较细的粒度(例如，行锁而不是页锁)，只在被锁定的项数较少时有意义。而随着越来越多的项目被锁定，维护这些锁的系统开销实际上会影响性能。这会导致所持续更长的时间。

当维持锁的数量达到一定限度时，则锁升级为下一个更高的层次，并且将不需要再如此紧密地管理低层次的锁(释放资源，而且有助于提升速度)。

注意，升级是基于锁的数量，而不是用户的数量。这里的重点是，可以通过执行大量的更新来单独地锁定表-行锁可以升级为页锁，页锁可以升级为表锁。这意味着可能将所有其他用户锁在该表之外。如果查询使用了多个表，则它很可能将每个人锁在这些表之外。

锁定模式

除了需要考虑锁定的资源层次以外，还要考虑查询将要获得的锁定模式，就像需要对不同的资源进行锁定一样，也有不同的锁定模式。一些模式是互相排斥的。一些模式什么都不做，只修改其他的模式。模式是否可以一起使用取决于他们是否是兼容的。

1、共享锁

这是最基本的一种锁。共享锁用于只需要读取数据的时候，也就是说，共享锁锁定时，不会进行改变内容的操作，其他用户允许读取。

共享锁能和其他共享锁兼容。虽然共享锁不介意其他锁的存在，但是有些锁并不能和共享锁共存。

共享锁告诉其他锁，某用户已经在那边了，它们并不提供很多的功能，但是不能忽略它们。然而，共享锁能做的是防止用户执行脏读。

2、排它锁

排它锁顾名思义，排它锁不与其他任何锁兼容。如果有任何其他其他锁存在，则不能使用排他锁，而且当排他锁仍然起作用时，他们不允许在资源之上创建任何形式的新锁。这可以防止两个人同时更新、删除或执行任何操作。

3、更新锁

更新锁是共享锁和排他锁的混合。更新锁是一种特殊的占位符。为了能执行UPDATE，需要验证WHERE子句来指出想要更新的具体的数据行。这意味着只需要一个共享锁，直到真正地进行物理更新。在物理更新期间，需要一个排他锁。

第一个阶段指出了满足WHERE子句条件的内容，这是更新查询的一部分，该查询有一个更新锁。

第二个阶段是如果决定执行更新，那么锁将升级为排他锁。否则，将把锁转换为共享锁。

这样做的好处是它防止了死锁。死锁本身不是一种锁定类型，而是一种已经形成矛盾的状况，两个锁在互相等待，多个锁形成一个环在等待前面的事务清除资源。

如果没有更新锁，死锁会一直出现。两个更新查询会在共享模式下运行。Query A完成了它的查询工作并准备进行物理更新。它想升级为排他锁，但是不可以这么做，因为Query B正在完成查询。除非Query B需要进行物理更新，否则它会完成查询。为了做到这点，Query B必须升级为排他锁，但是不能这么做，因为Query A正在等待。这样就造成了僵局。

而更新锁阻止建立其他的更新锁。第二个事务只要尝试取得一个更新锁，它们就会进入等待状态，直到超时为止-将不会授予这个锁。如果第一个锁在锁超时之前清除的话，则锁定会授予给新的请求者，并且这个处理会继续下去。如果不清楚，则会发生错误。

更新锁只与共享锁以及意向共享锁相兼容。

4、意向锁

意向锁是什么意思呢？就是说，加入你锁定了某一行，那么同时也加了表的意向锁(不允许其他人通过表锁来妨碍你)。

意向锁是真正的占位符，它用来处理对象层次问题的。假设一下如下情况：已对某一行建立了锁，但是有人想在页上或区段上建立所，或者是修改表。你肯定不想让另一个事务通过达到更高的层次来妨碍你。

如果没有意向锁，那么较高层次的对象将不会知道在较低层次上有锁。意向锁可改进性能，因为SQL Server只需要在表层次上检查意向锁(而不需要检查表上的每个行锁或者页锁)，以此来决定事务是否可以安全地锁定整个表。

意向锁分为以下3种不同的类型：

意向共享锁：该意向锁指已经或者将要在层次结构的一些较低点处建立共享锁。

意向排他锁：它与意向共享锁一样，但是将会在低层项上设置排他锁。

共享意向排他锁：它指已经或将会在对象层次结构下面建立共享锁，但目的是为了修改数据，所以它会在某个时刻成为意向排它锁。

5、模式锁

模式锁分为以下两种。

模式修改锁：对对象进行模式改变。在Sch-M锁期间，不能对对象进行查询或其他CREATE、ALTER或DROP语句的操作。

模式稳定锁锁定：它和共享锁很相似;这个锁的唯一目的是方式模式修改锁，因为在该对象上已有其他查询(或CREATE、ALTER、DROP语句)的锁。它与其他所有的锁定相兼容。

6、批量更新锁

批量更新锁(BU)只是一种略有不同的表锁定变体形式。批量更新锁允许并行加载数据。也就是说，对于其他任何普通操作(T-SQL)都会将表锁定，但可以同时执行多个BULK INSERT或bcp操作。

锁的兼容性

锁的资源锁定模式的兼容性表格，现有锁以列显示，要兼容的锁以行显示。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!