一、加锁(locking)、阻塞(blocking)、死锁(deadlock)定义
加锁:用于管理多个连接的进程。当连接需要访问一块数据时,在这些数据上放置某种类型的锁。
阻塞:指一个连接需要访问一块数据时,必须等待另一个连接的锁解除。
死锁:指两个连接形成被称为"僵局"的形式,它们互相等待对方的锁解除。
在 SQL Server 中,每个连接都可以看作是一个单独的会话。
二、数据库锁
1、锁粒度
为了改善并发性,SQL Server 实现如下资源级别上的锁粒度:
- 行(RID)
- 关键字(KEY)
- 页面(PAG)
- 区(EXT)
- 堆或 B 树(HoBT)
- 表(TAB)
- 数据库(DB)
(1)行级锁
该锁是在一个表的单独行上维护,是数据库表上最低级别的锁。当查询表中的一行数据时,查询被授予该行的 RID 锁。
因为阻塞被限制在所影响的行,所有行级锁提供了很高的并行性。
(2)键级锁
这是索引中的行级锁,被标识为一个关键字(KEY)锁。
对于具有聚集索引的表,表的数据页面和聚集索引的叶子页面相同。因为表和聚集索引的行相同,从表(或聚集索引)中访问行时,在该聚集索引行或有限范围的行中只能获得一个关键字锁。
该锁是针对有聚集索引的表,与行级锁类型,也有很高的并发性。
(3)页级锁
这种锁在表或索引的单一页面中维护,被标识为 PAG 锁。当查询请求一个页面中的多行时,请求的所有行的一致性可以通过获得单独行上的 RID/KEY 锁或整个页面上的一个 PAG 锁来维护。在查询计划中,锁管理器确定获得多个 RID/KEY 锁的资源压力,若果压力比较大,锁管理器请求一个 PAG 锁来代替。
页级锁减少了锁的开销,增进了查询的性能,但它阻塞了该页面上所有行的访问从而损害了数据库的并发性。
(4)区级锁
这种锁在区(一组连续 8 个数据或索引页面)上维护并且标识为 EXT 锁。
如,这种锁用于在一个表上执行 ALTER INDEX REBUILD 命令,并且该表从现有的区移动到新的区时。在这期间,区的完整性用 EXT 锁来保护。
(5)堆或 B- 树锁
堆或 B- 树锁用于描述这两种对象(堆 和 B- 树)被加锁的情况。这意味在一个无序的堆、没有聚集索引的表上的锁,或者一个 B- 树对象上的锁,通常指分区上的锁。因为分区被跨多个文件组存储,每个都有自己的数据分配定义。它的操作类似于表级锁,但是是在分区而不是表上进行。
(6)表级别
这是表上最高级别的锁。在一个表上的表级锁保留了对整个表及其所有索引的访问。
在执行一个查询时,锁管理器若果确定获取行级锁或是页级锁的资源压力较高,这时会直接为查询获取一个表级锁。
(7)数据库级锁
当应用程序建立一个数据库连接时,锁管理器分配一个数据库共享锁给对应的 SPID。这阻止用户意外地在其他用户连接时卸掉或者恢复数据库。
锁级别不需要由用户或数据库管理员指定,锁管理器会自动确定。在访问少量行时,它一般首先行级锁和键级锁以提高并发性。若多个行级锁的开销变得很高时,锁管理器会自动选择合适的较高级别的锁。
2、锁模式
根据所请求的类型,SQL Server 在锁定资源时使用不同的锁模式,下面是注意的三种模式:
- 共享(S)
- 更新(U)
- 排他(X)
(1)共享(S)模式
共享模式只用于只读查询。它不会阻止其他只读查询同时访问数据,因为查询不会破坏数据完整性。但是会阻止数据上的并发修改。
S 锁在数据上维持直到数据读出。默认情况下,SELECT 语句获取的 S 锁在数据读出之后会立即释放。
(2)更新(U)模式
U 锁与 S 锁不同,U 锁目的在于修改,因此为了维护数据完整性,在数据上不允许超过一个 U 锁。U 锁与 UPDATE 语句有关。
UPDATE 操作实际上有两个中间步骤:
- 读取需要修改的数据(加 U 锁);
- 修改数据(加 X 锁)。
在这两个步骤中,会使用不同的锁模式以最大化并发性。第一步骤中,获取数据上一个 U 锁,第二步,U 锁被转换为一个 X 排他锁以进行修改。若不需要修改,U 锁会被释放,也就是说它不会被保存到事务结束。
既然为了提高并发性,为什么第一步获取的是 U 锁,而不是 S 锁?
这里说说第一步中用 S 锁代替 U 锁的缺点。
若有两个连接同时执行一个事务,事务中是对同一条数据进行更新。两个事务先都使用 S 锁读取需要修改的数据,然后在请求一个 X 锁进行修改。当第一个事务试图将 S 锁转换为 X 锁时,它被第二个事务保持的 S 锁阻塞。同样如此,第二个事务试图将 S 锁转换为 X 锁时,也会被第一个事务的 S 锁阻塞。这样导致循环阻塞,也就发送了死锁。
为了避免这种情况,UPDATE 语句在第一个中间阶段使用 U 锁代替 S 锁。U 锁不允许在相同的资源上使用另一个 U 锁,这样强制第二个并发的 UPDATE 语句必须等待第一个 UPDATE 语句完成才执行。
(3)排他(X)模式
X 锁提供用于数据操作查询,如 INSERT、UPDATE 和 DELETE 在数据库资源上修改的排他能力。它阻止其他事务访问修改之下的资源。INSERT 和 DELETE 在执行开始获取 X 锁, UPDATE 语句在被修改的数据读出之后转换为 X 锁。在事务中授予的 X 锁会保持到事务结束。
X 锁有两个目的:
- 阻止其他事务访问修改之下的资源,这样他们可以看到修改之前或之后的值,但不能是正在修改的值;
- 在需要时允许事务修改资源以安全地回滚到修改之前的原始值,因为没有其他事务被允许同时修改资源。
三种之间的区别和联系:
S 锁 和 U 锁 执行期很短,默认情况下在读出数据后会立即释放;
U 锁 和 X 锁 具有独占能力;
X 锁 执行期是在整个事务阶段。
SELECT 使用 S 锁;INSERT 和 DELETE 使用 X 锁;UPDATE 使用两阶段锁,读取阶段为 U 锁,修改阶段为 X 锁。
使用 UPDATE 更新某一数据时,在扫描阶段先对数据使用 U 锁,若不满足立即退出,满足条件才转换为 X 锁进入修改阶段。
思考:为什么 UPDATE 使用两阶段锁,而 DELETE 直接使用 X 锁 ?
三、隔离级别(ISOLATION)
SQL Server 有这几种隔离级别:
- 未提交读(READ UNCOMMITTED)
- 已提交读(READ COMMITTED)
- 可重复读(REPEATABLE READ)
- 可序列化(SERIALIZABLE)
其他两种隔离级别提供行版本控制(row versioning)。这种行的额外版本允许读查询访问数据而不需要获取锁:
- 已提交读快照(已提交读隔离的一部分)
- 快照
(1)未提交读
未提交读 是隔离级别中最低级的。它允许 SELECT 语句读取数据而不需要请求 S 锁,这样它就不会被 X 阻塞,也不会阻塞 X 锁。这样就允许 SELECT 语句读取正在修改(包括新增和删除)的数据。这种读出数据的方式被称为
脏读 。
有两种方式来设置:
使用 SET 语句配置数据库连接的隔离级别:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
使用 NOLOCK 锁提示在查询上设置隔离级别:
SELECT * FROM Products WITH (NOLOCK);
注:脏读 可能造成不可预知的情况。因为读取数据时没加锁,索引可能分离,这导致查询返回的数据中多出或丢失行。
(2)已提交读
已提交读 可避免出现脏读的情况,这意味着该隔离级别指示 SELECT 语句会请求 S 锁。这也是数据库的默认隔离级别。
但是,S 锁不会被保留到事务结束,这样可能造成
不可重复读 或 幻读 的问题。
可以通过开启数据库选项 READ_COMMITTED_SNAPSHOT 来启用 已提交读 隔离级别。当看起这选项时,数据操作事务会使用行版本控制。这会给 tempdb 带来额外的负载,在事务未提交时被修改的行的前一个版本将保存在该数据库里。这使得其他事务可以读访问数据而不需要在数据上加锁,可能会改善查询的速度和效率(因为查询数据不需要加 S 锁)。
(3)可重复读
可重复读隔离级别使得一条 SELECT 语句保持它的 S 锁直到事务结束,这样阻止了其他事务在这段时间内修改该数据。
可重复读,某事务在执行期间可反复读取未被其他事务修改的数据的能力。
S 锁允许数据获取 U 锁,但是会阻止 U 锁转换为 X 锁。这样会有一种现象发生:
A 事务查询某数据,B 事务修改该数据。先执行 A 事务,在 A 事务结束之前执行 B 事务,该数据在 A 事务中加了 S 锁,在 B 事务中加了 U 锁。此时 B 事务 UPDATE 要修改数据,要转换为 X 锁,这时 A 事务还未退出,阻止 B 事务中数据由 U 锁转换为 X 锁。事务 A 稍后更数据,试图获取 U 锁,这样就进入死循环。
解决方法:在执行 SELECT 语句时使用 UPDLOCK 锁提示请求一个 U 锁,如:
SELECT * FROM Products WITH (UPDLOCK);
(4)可序列化
这是这几张隔离级别中最高的隔离级别。可序列化不仅在访问的行上获取一个锁,而且还获取在按照请求的数据级顺序的下一行上的范围锁。这阻止了在第一个事务所操作的数据中的周期另一事务增加行,避免第一个事务在其访问内的数据集查找新建的行。也就避免了
幻读(在事务内的数据集中查找新的行)。
可序列化 隔离级别不仅和 可重复读 隔离级别一样保留 S 锁直到事务结束,而且还通过保持范围锁阻止数据集(或更多)中新建行。这可能很大的损害了数据库并行性,所有应该避免该隔离级别。
(5)快照(Snapshot)
快照隔离级别试图在打算修改的数据上使用一个排他锁。若数据已加锁,快照事务将失败。它提供了事务级读一致性。
数据库隔离级别:
![](https://i-blog.csdnimg.cn/blog_migrate/11ac7a3110f120169858a65085bcec9c.png)
隔离级别与并发性能的关系:
![](https://i-blog.csdnimg.cn/blog_migrate/c7950d6bf74f8e3b6d3db4013e9db302.png)
设置隔离级别原则
优先将数据库系统的隔离级别设置为 ReadCommitted(MSSQL 默认级别),它可以避免脏读,并且有较好的并发性。
四、多个事务并发产生的问题
- 第一类丢失更新:撤销一个事务时,把其他事务已提交的更新数据覆盖
- 脏读:一个事务读到另一个事务未提交的更新数据
- 虚读:一个事务读到另一个事务已提交的新插入的数据
- 不可重复读:一个事务读到另一个事务已提交更新的数据
- 第二类丢失更新:一个事务覆盖另一个事务已提交更新的数据(不可重复读的特例)
隔离级别与并发性能的关系:
设置隔离级别原则
优先将数据库系统的隔离级别设置为 ReadCommitted(MSSQL 默认级别),它可以避免脏读,并且有较好的并发性。
四、多个事务并发产生的问题
- 第一类丢失更新:撤销一个事务时,把其他事务已提交的更新数据覆盖
- 脏读:一个事务读到另一个事务未提交的更新数据
- 虚读:一个事务读到另一个事务已提交的新插入的数据
- 不可重复读:一个事务读到另一个事务已提交更新的数据
- 第二类丢失更新:一个事务覆盖另一个事务已提交更新的数据(不可重复读的特例)
参考书籍:
书籍:《SQL Server 2008 查询性能优化》