数据库基本知识点梳理系列 - 锁
数据库基本知识点梳理系列 - 锁
数据库的锁是用于保证数据库事务在并发的情况下依旧能够保证数据的一致性的. 所以深入理解锁的原理, 能够帮助我们更好地理解事务隔离级别的原理, 以及在实际的业务场景中, 有把握的使用隔离级别保障系统的效率和稳定.
锁的层级
| 级别 | 加锁速度以及开销 | 是否会出现死锁的情况 | 粒度 | 并发事务的性能影响 |
|---|---|---|---|---|
| 表级锁 | 快, 开销小 | 不会出现死锁 | 锁定粒度很大, 因此发生加锁的冲突概率最高 | 并发度最低 |
| 行级锁 | 开销大, 加锁慢 | 会出现死锁 | 锁定粒度最小, 发生加锁冲突概率最低 | 并发度最高 |
| 页面锁 | 开销和加速速度介于上面两者间 | 会出现死锁 | 粒度介于上面两者之间 | 并发度介于两者之间 |
行级锁
| 锁名称 | 代号 | 描述 |
|---|---|---|
| 共享锁 | （S） | 读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁的时候，所有的事务都不能对这个资源进行修改，直到数据被所有资源读取完成，共享锁释放。 |
| 排它锁 | （X） | 独占锁、写锁。就是如果你对数据资源进行增删改(DML)操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。当资源上已经有共享锁或者排他锁, 则无法对这个资源添加额外的排他锁. 即排他锁与共享锁不兼容. 排他锁与自己也不兼容. |
| 更新锁 | （U） | 当事务发现资源上既没有更新锁也没有排他锁时, 可以对资源添加更新锁, 也就是说, 更新锁与自己不兼容, 与排他锁也不兼容, 但是与共享锁兼容. 如果资源上已经有了共享锁, 那么在这种情况下, 更新锁会对该资源申请另外一个共享锁. 当事务准备修改资源时, 如果此时资源上没有其他的共享锁, 可以申请排他锁对数据进行修改. 所以对于更新锁我们可以这样理解. 一个资源只能有一个更新锁. 当更新锁发现资源上有其他共享锁时, 更新锁相当于共享锁. 如果事务准备修改数据, 但是资源上有其他共享锁, 则无法修改, 等其他共享锁释放后, 此时更新锁会变成排他锁, 用于修改资源. |
注: 两个事务对一个数据资源进行先读取在修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。
表级锁
下面介绍表级锁, 我们先看看都有哪些可以在表级别加的锁, 再对新出现的锁进行介绍:
| 锁名称 | 代号 | 描述 |
|---|---|---|
| 共享锁 | (S) | 同行级锁 |
| 排他锁 | (X) | 同行级锁 |
| 意向锁 | (IS)(IX)(SIX) | 在意向锁出现之前, 一般是通过共享锁和排他锁对表和行上锁. 所以当我们为一个表加锁时, 一方面需要检查申请的锁与该表原有的表锁是否兼容, 另一方面, 还要检查该锁是否与表内的每一行的行锁兼容. 比如事务A要在一个表上加S锁, 如果表中某一行已经被事务B加了X锁, 那么事务A对表加S锁的申请就会被阻塞. 如果表中的数据量过多, 逐行检查行锁的开销将会很大, 系统的性能也会受到影响.为了解决这个问题, 数据库开发者想出, 是否可以在表级别加入新的锁类型,来表示其表内行的加锁情况? 这就诞生了意向锁.比如事务A准备对表中某一行加锁时, 首先要对数据所在的表加一个意向锁. 在此之后, 事务B准备对表的某一行加锁, 但是发现该表已经有了意向锁, 此时事务B需要根据现有的意向锁, 决定是否去逐行检查行锁, 进而节省系统的性能. 所以意向锁一般是基本锁(写锁, 读锁)的数据的上级数据的锁: 比如准备为数据A加X锁之前, 需要对数据A的上级表A加IX锁[IX:意向排他锁]). IS锁同理. 基本锁: S, X, 与意向锁: IS, IX 自由组合诞生进阶锁: S+IS，S+IX，X+IS，X+IX, 但稍加分析不难看出，实际上只有 S+IX 有新的意义，其它三种组合都没有使锁的强度得到提高, SIX的名字叫做 共享意向排它锁 |
共享意向排他锁 Shared with intent exclusive (SIX) : 当这个锁在某个数据库级别(一般是表级别)被申请时, 这表明了事务准备读取当前级别以下的全部资源, 所以需要对表申请一个共享锁(S), 除此以外, 这个事务还准备更新一部分数据, 并不是全部的数据, 所以需要额外申请一个意向排他锁在那些需要被更改的数据上. 这就意味着这个事务需要申请一个共享意向排他锁在这个表级别. 并在表级别的下级页面级别申请意向排他锁和行级别的排他锁.
除了上述锁之外, 还有一些其他的锁, 不是太常用, 比如模式锁(schema lock) 和 大容量更新锁(bulk update lock)
锁之间的兼容性总结
意向锁提升了数据库的并发性. 了解一下不同的锁之间的兼容性, 有助于更好地理解为什么你的项目里对并发事务的阻塞问题的原因:
| 锁类型 | IS | S | U | IX | SIX | X |
|---|---|---|---|---|---|---|
| IS | Y | Y | Y | Y | Y | N |
| S | Y | Y | Y | N | N | N |
| U | Y | Y | N | N | N | N |
| IX | Y | N | N | Y | N | N |
| SIX | Y | N | N | N | N | N |
| X | N | N | N | N | N | N |
做个总结就是： 锁的模式和兼容性是SQL Server预先定义好的，没有任何参数或配置能够去修改它们。但是可以通过隔离级别来控制申请锁和释放锁的时机，如果应用申请的锁粒度都比较小，产生阻塞的几率就会比较小。如果一个连接会经常申请页面级、表级，甚至是数据库一级的锁资源，程序产生阻塞的可能性就会很大。
死锁
死锁不是一种锁, 是并发事务由于争夺锁时发生的一种现象. 死锁是系统性能的大杀器. 为了保证系统的性能和并发性, 我们不得不面临死锁.
首先举一个例子说明死锁如何产生:
- 第一个事务（称为A）：先更新lives表 --->>停顿5秒---->>更新earth表
- 第二个事务（称为B）：先更新earth表--->>停顿5秒---->>更新lives表
- 先执行事务A----5秒之内---执行事务B，此时出现死锁现象。
过程是这样子的：
- A更新lives表，请求lives的排他锁，成功。
- B更新earth表，请求earth的排他锁，成功。
- 5秒过后
- A更新earth，请求earth的排它锁，由于B占用着earth的排它锁，等待。
- B更新lives，请求lives的排它锁，由于A占用着lives的排它锁，等待。
然而数据库并没有出现无限等待的情况，是因为数据库搜索引擎会定期检测这种状况，一旦发现有情况，立马选择一个事务作为牺牲品。牺牲的事务，将会回滚数据。
避免死锁的一般经验总结
- 按照同一顺序访问数据库资源，上述例子就不会发生死锁
- 保持是事务的简短，尽量不要让一个事务处理过于复杂的读写操作。事务过于复杂，占用资源会增多，处理时间增长，容易与其它事务冲突，提升死锁概率。
- 尽量不要在事务中要求用户响应，比如修改新增数据之后在完成整个事务的提交，这样延长事务占用资源的时间，也会提升死锁概率。
- 尽量减少数据库的并发量。
- 尽可能使用分区表，分区视图，把数据放置在不同的磁盘和文件组中，分散访问保存在不同分区的数据，减少因为表中放置锁而造成的其它事务长时间等待。
- 避免占用时间很长并且关系表复杂的数据操作。
- 使用较低的隔离级别，使用较低的隔离级别比使用较高的隔离级别持有共享锁的时间更短。这样就减少了锁争用。
锁升级
系统中过度使用锁也会占用很多内存资源的. 因为锁是一种基于内存的结构, 包含一些关于资源的属性类似于: owners, types, 以及hash值.所以在内存中, 锁结构的大小通常为 96 bytes.
让我们举个例子, 假如我们现在有30,000行数据, 并且每行数据的大小大约在500 bytes左右, 然后我们准备执行一个删除操作.
在没有锁升级的情况下, 首先一个共享锁将会被添加到database级别, 一个意向排他锁添加到table级别, 然后有 1875个意向排他锁将会添加到page级别(一个page是8kb, 包含16行的500bytes的数据, 所以30,000行数据大概需要1875个pages), 然后需要30,000个排他锁在每一行上, 每个排他锁要96bytes. 共计31,877个锁, 所以一次删除操作, 大概要占用3MB的内存.
OK, 一个单一的删除操作会造成这样的内存占用. 如果大量并行的数据库操作同时发生, 内存的占用将会相当高的.
为了避免这种情况, SQL server 引入了锁升级的理念. 当一个操作需要在某个数据级别申请超过5,000个锁时, SQL server 会升级这些行级锁到一个表级别的锁. 默认情况下, Sql Server 会直接升级到表级别, 而不会升级到 page级别. 因为如果升级到page级别, 可能依旧得申请多个行级锁和page级锁. 所以不如直接升级到表级锁.
但是一旦有锁升级的现象发生, 则意味着这个表将会被阻塞. 在锁升级的过程中, 其他事务无法对该表进行读写. 因此锁升级虽然降低了系统开销, 但是降低了系统的并发性能.
合理运用锁升级
为了合理的使用锁升级, 从 SQL server 2008 R2开始, ALTER TABLE语句中可以使用 LOCK_EXCALATION去控制锁升级.
语法:
USE AdventureWorks2014 GO
ALTER TABLE Table_name SET (LOCK_ESCALATION = < TABLE | AUTO | DISABLE > –One of those options)
GO
set选项 | 描述
Table | 对于所有新创建的table, 这个是默认选项, 表示sql server总是会进行锁升级.
Auto | 笔者按: 不建议使用, 非常容易造成死锁的一个设置
Disable | 就是干脆别用锁升级. 小心你的系统内存就是了. 最简单的方式就是不要一次操作5000条数据, 必须超过这个量的可以选择分批操作.
怎么看都有哪些锁目前正在活跃?
SQL Server提供了动态管理视图（DMV）sys.dm_tran_locks 可以让咱们看看数据库目前活跃的锁都有哪些.
比较重要的一些关于锁的列是 resource_type, request_mode, and resource_description.
下面是一个用例:
SELECT resource_type, request_mode, resource_description FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'
