SQLServer-并发控制-锁机制

并发可能导致的影响：

1. 丢失更新：多个事务同时更新同一行；

2. 脏读：修改过程中的数据被读取；

3. 不一致的分析（不可重复读）：读取的事务里面，符合读取条件的行搜索条件被更改了；

4. 幻读：读取的事务里面，符合读取条件的行被删除或者插入了；

3和4 的区别在于前者是更新 ，后者是插入删除。

 

并发控制类型：

1. 悲观并发控制：用到锁来保护数据。用于锁消耗低于回滚事务的成本环境中；

2. 乐观并发控制：并发过程中不产生锁，读取数据后检查用户数据，判断是否产生错误， 回滚事务。用于数据争用少的环境。

 

锁：是数据库引擎用来同步多个用户同事对一个数据块访问的一种机制

 

锁的粒度和层次结构

数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。

锁定在较小的粒度（例如行）可以提高并发度，但开销大，因为锁定的范围越小，需要的锁就越多（锁定了许多行，就需要持有更多的锁）；锁定在较大的粒度（例如表）会降低并发度，但是消耗较低，因为锁定的范围大，需要的锁就越小（锁定了表，限制了其他事物对表的访问）。

 

数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。这种多粒度级别上的锁称为锁层次结构

可以锁定的资源：

可被锁定的资源

说明

RID

用于锁定堆中的单个行的行标识符

KEY

索引中用于保护可序列化事务中的键范围的行锁

PAGE

数据库中的8KB页，数据页或者索引页

EXTENT

一组连续的页

HOBT

堆或者B树，保护索引或没有聚集索引的表中数据页堆的锁

TABLE

包括所有数据和索引的整个表

APPLICATION

应用程序专用的资源

FILE

数据库文件

METADATA

元数据锁

ALLOCATION_UNIT

分配单元

DATABASE

整个数据库

 

 

 

 

 

 

 

锁的模式

数据库引擎使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。

 

1. 共享锁(S) ：读取产生的锁,防止其他事务对其的修改；

2. 更新锁(U) ：可防止死锁，在共享锁与排他锁之间，与共享锁兼容，与排他锁不兼容。

3. 排他锁(X) ：用于数据增删改操作。锁定时数据无法修改与读取（在nolock或未提交读隔离级别时可以脏读）

4. 意向锁；

 A、意向共享 IS

 B、意向排他 IX

 C、意向更新 IU

 D、共享意向更新 SIU

 E、更新意向排他 UIX

5. 架构所(Sch-)：

 A、架构稳定性锁 Sch-M

 B、架构稳定性锁 Sch-S

6. 大容量更新锁(BU):当将数据大容量复制到表，且指定TABLEOCK提示或者使用sp_tableoption设置了table lockon bulk表选项时，将使用大容量更新锁。大容量更新锁允许多个线程将数据并发地大容量加载到同一表，同时防止其他不进行大容量加载数据的进程访问该表。

7. 键范围锁：可防止幻读。在使用可序列化事务隔离级别时，会在整个事务执行过程中保持关键字范围的锁定，键范围锁定可防止其他事务插入其键值位于可序列化事务读取的键值范围内的新行。

 

锁的兼容性（控制多个事务能否同时获取同一资源上的锁）

 

 

锁升级

将许多较细粒度的锁转换成数量更少的较粗粒度的锁的过程，这样可以减少系统开销，但却增加了并发争用的可能性。

升级锁时，数据库引擎将尝试将表上的意向锁更改为对应的全锁，例如，将意向排他锁更改为排他锁。如果锁升级尝试成功并获取全表锁，将释放事务在堆或索引上所持有的所有堆或B树锁、页锁、键范围锁或行级锁。如果无法获取全锁，则不会发生锁升级，而数据库引擎将继续获取行、键或页锁。

 

 

锁升级阈值

1. SQL语句的升级阈值；

当SQL语句在表或索引的单个引用上获取5000个锁时，将触发锁升级。

只有触发升级时访问的表才会发生锁升级。如果单个select语句访问三个表，任何一个表的锁不足5000，升级都会不成功。当前面两个表升级不成功后，不会再尝试第三个表。

2. 数据库引擎实例的升级阈值

每当锁的数量大于锁升级的内存阈值时，每获取1250个新锁，数据库引擎就会定期为锁升级选择语句。内存阈值取决于locks配置选项的设置（可以使用sp_configure设置）

 

 

减少锁定和升级

在大多数情况下，使用数据库引擎默认的锁定和锁升级设置，性能最佳。如果过度频繁进行锁升级，可以通过以下方式减少锁定：

1. 当READ_COMMITED_SNAPSHOT ON  

、使用READ COMMITTED隔离级别

、使用SNAPSHOT隔离级别

、使用READ UNCOMMITTED隔离级别（允许脏读：或者 NOLOCK）

2. 使用PAGLOCK、TABLOCK表提示，使数据库引擎使用页、堆或索引锁而不是行锁。对于高并发系统，不应使用此选项。

 

DBCC TRACON(1211): 禁用所有锁升级。

DBCC DBREPAIR(1224)：禁用各个语句的锁升级，与locks选项为0的行为相同

 

自定义超时时间：SET LOCK_TIMEOUT 1800 --单位为毫秒：1800毫秒

查询lock_timeout设置：select @@lock_timeout

 

锁提示

 

还有两种类型适用于行锁定类型：

UPDATELOCK : 表提示指定在读取的每一行上设置一个更新锁定，直到整个语句或事物执行完毕，才释放这个锁定。允许其他事务获取同一行的共享锁定，但不允许他们更新行

XLOCK；指定才用排他锁并保持到事物完成。

HOLDLOCK：保持表锁定知道事务执行完毕。

 

 

事务隔离级别

当前会话事务隔离级别查询：DBCC USEROPTIONS

 

 

为连接设置隔离级别：SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

 

依赖于行版本控制的事务隔离级别：通过所使用的行复制到tempdb数据库中的方法来避免锁定，提高并发。

1. 在现有的READ COMMITTED事务隔离级别的基础上提供了一种新的实现方式，用于提供行版本控制的语句级快照；

2. 新增了一个SNAPSHOT ISOLATION （快照隔离）事务隔离级别，用于提供使用行版本控制的事务级快照。
————————————————
版权声明：本文为CSDN博主「Foliole」的原创文章,为了学习下载的。
原文链接：https://blog.csdn.net/xiaoye1202/article/details/80359509