澳门在线威尼斯官方 > 电脑数据库 > 事务与锁

原标题:事务与锁

浏览次数:121 时间:2019-09-25

图片 1

锁的概念

1.关于其中的S、U、X锁:

图片 2

一、锁的粒度:

 

图片 3

图片 4

锁兼容性图:

自动提交事务:是一种能够自动执行并能自动回滚事务,这种方式是T-SQL的默认事务方式。例如在删除一个表记录的时候,如果这条记录有主外键关系的时候,删除就会受主外键约束的影响,那么这个删除就会取消。
显式事务:T-sql标明,由Begin Transaction开启事务开始,由Commit Transaction 提交事务、Rollback Transaction 回滚事务结束。
隐式事务:使用Set IMPLICIT_TRANSACTIONS ON 将将隐式事务模式打开,不用Begin Transaction开启事务,当一个事务结束,这个模式会自动启用下一个事务,只用Commit Transaction 提交事务、Rollback Transaction 回滚事务即可。

比较需要注意的是RID/KEY、HoBT/PAGE这两对儿的区别,RID和HoBT是针对堆表的,即没有聚集索引的表。

  • 意向锁:数据库引擎使用意向锁来保护共享锁(S 锁)或排他锁(X 锁)放置在锁层次结构的底层资源上。在较低级别锁前可获取它们,因此会通知意向将锁放置在较低级别上。

共享锁

共享锁(S 锁)允许并发事务在封闭式并发控制下读取 (SELECT) 资源。 资源上存在共享锁(S 锁)时,任何其他事务都不能修改数据。 读取操作一完成,就立即释放资源上的共享锁(S 锁),除非将事务隔离级别设置为可重复读或更高级别,或者在事务持续时间内用锁定提示保留共享锁(S 锁)。

 

图片 5

事务的概念

更新锁

更新锁(U 锁)可以防止常见的死锁。 在可重复读或可序列化事务中,此事务读取数据 [获取资源(页或行)的共享锁(S 锁)],然后修改数据 [此操作要求锁转换为排他锁(X 锁)]。 如果两个事务获得了资源上的共享模式锁,然后试图同时更新数据,则一个事务尝试将锁转换为排他锁(X 锁)。 共享模式到排他锁的转换必须等待一段时间,因为一个事务的排他锁与其他事务的共享模式锁不兼容;发生锁等待。 第二个事务试图获取排他锁(X 锁)以进行更新。 由于两个事务都要转换为排他锁(X 锁),并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁。

若要避免这种潜在的死锁问题,请使用更新锁(U 锁)。 一次只有一个事务可以获得资源的更新锁(U 锁)。 如果事务修改资源,则更新锁(U 锁)转换为排他锁(X 锁)。

一般情况下更新都是直接获取独占锁的,但是如果被更新的行上已经存在独占锁,那么事务就会尝试先获取S锁,如果有多个会话在请求S锁的队列中,那么当独占锁被释放后就有多个会话获取了S锁并尝试转换为X锁,此时发生死锁,U锁的出现即为了解决此问题。  --这段不是官网的描述,而是我根据MySQL的重复性检测加锁机制推测的。如果官网有关于此类先加S锁再转化X的文章请@我。

事务的并发控制

二、锁的模式:

Begin Transaction:标记事务开始。
Commit Transaction:事务已经成功执行,数据已经处理妥当。
Rollback Transaction:数据处理过程中出错,回滚到没有处理之前的数据状态,或回滚到事务内部的保存点。
Save Transaction:事务内部设置的保存点,就是事务可以不全部回滚,只回滚到这里,保证事务内部不出错的前提下

排他锁

排他锁(X 锁)可以防止并发事务对资源进行访问。 使用排他锁(X 锁)时,任何其他事务都无法修改数据;仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。

数据修改语句(如 INSERT、UPDATE 和 DELETE)合并了修改和读取操作。 语句在执行所需的修改操作之前首先执行读取操作以获取数据。 因此,数据修改语句通常请求共享锁和排他锁。 例如,UPDATE 语句可能根据与一个表的联接修改另一个表中的行。 在此情况下,除了请求更新行上的排他锁之外,UPDATE 语句还将请求在联接表中读取的行上的共享锁。

Ps:在这里官网的解释可能会有歧义,DML操作虽然是读取和修改的合并,但是只有update才会先加IU、U锁来读取数据,然后修改时转化为IX、X。而删除和插入我们可以认为从一开始就是加的IX、X锁。

2.关于其中的意向锁:

意向锁有两种用途:

  • 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。
  • 提高数据库引擎在较高的粒度级别检测锁冲突的效率。

图片 6

3.关于其中的架构锁:

数据库引擎在表数据定义语言 (DDL) 操作(例如添加列或删除表)的过程中使用架构修改 (Sch-M) 锁。 保持该锁期间,Sch-M 锁将阻止对表进行并发访问。 这意味着 Sch-M 锁在释放前将阻止所有外围操作。

某些数据操作语言 (DML) 操作(例如表截断)使用 Sch-M 锁阻止并发操作访问受影响的表。

数据库引擎在编译和执行查询时使用架构稳定性 (Sch-S) 锁。 Sch-S 锁不会阻止某些事务锁,其中包括排他 (X) 锁。 因此,在编译查询的过程中,其他事务(包括那些针对表使用 X 锁的事务)将继续运行。 但是,无法针对表执行获取 Sch-M 锁的并发 DDL 操作和并发 DML 操作。

4.关于其中的大容量更新锁:

大容量更新锁(BU 锁)允许多个线程将数据并发地大容量加载到同一表,同时防止其他不进行大容量加载数据的进程访问该表。 在满足以下两个条件时,数据库引擎使用大容量更新 (BU) 锁。

  • 使用 Transact-SQL BULK INSERT 语句或 OPENROWSET(BULK) 函数,或者您使用某个大容量插入 API 命令(如 .NET SqlBulkCopy)、OLEDB 快速加载 API 或 ODBC 大容量复制 API 来将数据大容量复制到表。
  • TABLOCK指定提示或表大容量加载上的锁表选项设置使用sp_tableoption。

5.关于其中的键范围锁:

在使用可序列化事务隔离级别时,对于 Transact-SQL 语句读取的记录集,键范围锁可以隐式保护该记录集中包含的行范围。 键范围锁可防止虚拟读取。 通过保护行之间键的范围,它还防止对事务访问的记录集进行虚拟插入或删除。

关于键范围锁可以参考官网,或者另一篇博客SQL Server事务隔离级别中对于可序列化读隔离级别的加锁说明。

 

三、锁升级

SQL Server数据库会发生锁升级,官网说明的锁升级触发条件为,如果没有使用 ALTER TABLE SET LOCK_ESCALATION 选项来禁用表的锁升级并且满足以下任一条件时,触发锁升级:

  • 单个 Transact-SQL 语句在单个无分区表或索引上获得至少 5,000 个锁。
  • 单个 Transact-SQL 语句在已分区表的单个分区上获得至少 5,000 个锁,并且 ALTER TABLE SET LOCK_ESCALATION 选项设为 AUTO。
  • 数据库引擎实例中的锁的数量超出了内存或配置阈值。
  • 如果由于锁冲突导致无法升级锁,则数据库引擎每当获取 1,250 个新锁时便会触发锁升级。

对于锁升级的优化官网提供如下建议:

  1. 使用READ_COMMITTED_SNAPSHOT事务隔离级别。
  2. 使用SNAPSHOT事务隔离级别。
  3. 使用READ UNCOMMITTED事务隔离级别。

一般情况下我们只需要把READ_COMMITTED_SNAPSHOT选项打开即可,可以避免select加锁,从而避免阻塞和锁升级。

此外还可以打开1211和1224来避免锁升级,但是极度不推荐,锁升级本身就是为加快锁获取的效率而设计的,根本解决办法还是优化SQL。

 

参考文档

SQL Server 事务锁定和行版本控制指南:

关于锁升级,参考官方页面:

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

分析:由于插入table1时发生错误,根据事务的原子性,要么全做,要全不错,所以一条数据都没有插入

事务常用的语句

SQL Server中3类常见的事务

锁模式兼容性

设置事务隔离级别

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:事务与锁

关键词:

上一篇:Server查看视图定义计算,Server元数据损坏

下一篇:没有了