SQL Server 并发控制 第二篇:隔离级别和锁(1)

SQL Server 并发控制 第一篇:并发模式和事务

SQL Server 并发控制 第二篇:隔离级别和锁(1)

SQL Server 并发控制 第三篇:隔离级别和行版本(2)

 

隔离级别定义事务处理数据读取操作的隔离程度,在悲观并发模式下,隔离级别只会影响读操作申请的共享锁(Shared Lock),而不会影响写操作申请的互斥锁(Exclusive Lock),隔离级别控制读操作的行为:

  • 在读数据时是否使用共享锁,申请何种类型的锁;
  • 事务持有共享锁的时间;
  • 读操作引用被其他事务更新,但尚未提交的数据行时,控制读操作的行为:
    • 被阻塞,等待其他事务释放互斥锁;
    • 读没有提交的数据,获取更新之后的数据值;

在乐观并发模式下,读操作引用被其他事务更新,但尚未提交的数据行时,控制读操作的行为:

  • 获取更新之前的数据值,从tempdb中读取行版本(row version),该行版本在事务开始时已经提交;

在执行写操作时,事务总是持有互斥锁,直到事务结束才释放,互斥锁不受事务隔离级别的影响。在悲观并发模式中,互斥锁和任意锁都不兼容,在同一时刻,同一个数据行上,只能有一个事务持有互斥锁,就是说,写操作是顺序进行的,完全隔离的,不能并发执行。隔离和并发,此消彼长。

一,事务运行时的隔离级别

SQL Server 数据库级别默认的事务隔离级别是Read Committed,用户不能修改Database-Level默认的隔离级别,但是,用户能够修改Session-Level默认的事务隔离级别。

每一个事务都运行在一个特定的隔离级别内,该隔离级别是由会话(session)决定的。SQL Server定义的五个隔离级别,都是为了定义读数据的行为:

  • READ UNCOMMITTED :允许脏读、数据不可重复读和数据范围不可重复读
  • READ COMMITTED:防止脏读(读取未提交的数据更新),允许数据不可重复读和数据范围不可重复读
  • REPEATABLE READ:防止脏读和数据不可重复读,允许数据范围不可重复读
  • SERIALIZABLE:防止脏读、数据不可重复读和数据范围不可重复读
  • SNAPSHOT:快照隔离级别,主要是为了避免读写相互阻塞

事务的隔离级别共有5个,使用SET命令修改Session-Level的隔离级别,使用DBCC UserOptions 查看当前Session的隔离级别:

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

注意:隔离级别只能定义读操作的行为,无法定义写操作的行为,写操作跟写操作与读操作都是互斥的。

在任何隔离级别下,事务在执行写操作时都申请互斥锁(exclusive lock),持有互斥锁直到事务结束,互斥锁不受隔离级别的控制;而共享锁(Shared Lock)受到隔离级别的控制,隔离级别影响Shared Lock的申请和释放:

  • 在 Read Uncommitted隔离级别下,读操作不会申请Shared Lock;
  • 在 Read Committed(不使用row-versioning),Repeatable Read 和 Serializable隔离级别下,都会申请Shared Lock;
  • 在 Read Committed(不使用row-versioning) 隔离级别下,在读操作执行时,申请和持有Share Lock;一旦读操作完成,释放Shared Lock;
  • 在 Repeatable Read 和 Serializable隔离级别下,事务会持有Shared Lock,直到事务结束(提交或回滚);
  • 在Serializable隔离级别下,事务会持有范围Shared Lock(Range Lock),锁定一个范围,在事务活跃期间,其他事务不允许在该范围中进行更新(Insert 或 delete)操作;

二,悲观并发模式下的隔离级别

在悲观并发模式下,可以设置4个隔离级别,分别是READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE,其中READ UNCOMMITTED、REPEATABLE READ和SERIALIZABLE是悲观并发模式独有的。

1,脏读

当事务运行在 READ UNCOMMITTED 隔离级别下时,有可能会读取到其他事务未提交的数据更新。

  • 例如,事务A修改了数据,把数据由1修改2,但是还没有提交,
  • 之后,另一个事务B读取了该数据,结果是2,
  • 在事务B读取数据之后,事务A回滚,那么该数据的最终值是1。

出现脏读的原因是读操作不加共享锁,读操作不会被写操作阻塞,使得读操作可能会读取到写事务未提交的值。

2,数据不可重复读

当事务运行在 READ COMMITTED 隔离级别下时,在同一个事务内,两次读取同一个数据,可能读取到不同的结果,这就是数据不可重复读现象。

  • 例如,事务A读取了一个数据,值是1,事务未提交
  • 之后,事务B修改了该数据,把数据值由1修改为2,并提交事务。
  • 在事务B提交之后,事务A重新读取该书,值为2

出现数据不可重复读的原因是事务在读取数据时申请了共享锁,但是在语句执行完成之后,就立马释放了共享锁。要想避免出现数据不可重复读的现象,事务必须一直持有共享锁,直到事务提交或回滚。

3,数据范围不可重复读

当使用where子句限定了数据范围之后,事务在两次执行相同的查询,获得的数据范围不同,也就是说,在相同的数据范围内,增加了新的数据。

  • 例如,事务A查询Age在10和20之间的男生,共有7个,事务未提交
  • 事务B向数据表中插入一条新的数据,Age是15,事务提交
  • 事务A重新查询Age在10和20之间的男生,共有8个

出现数据范围不可重复读的原因是事务只申请了共享锁,没有申请范围锁,这就导致其他写事务可以向特定的数据范围内新增数据。

三,锁

在悲观并发模式下,事务使用锁来实现数据的一致性。锁是一种机制,锁施加在资源上,表示锁定该资源。锁的所有者是事务,事务的特性决定了锁的特性。

1,锁定的资源

事务可以在数据行、数据页、键范围、分区和表上加锁,

锁定的资源类型,按照粒度的级别,从低向高分别是:

  • RID:堆中的一行
  • KEY:聚集索引中的键
  • KEY Range:键范围
  • PAGE:堆表的数据页或B-Tree的索引页
  • EXTENT:分区,连续的8个Page
  • HoBT:堆或B-Tree, 用于保护堆(没有聚集索引的表)和堆中的B 树
  • IDX:索引中的数据行
  • OBJECT:表(整个表)

2,锁模式

共享锁(S锁):读操作在读取的数据上施加共享锁,用于select子句中

互斥锁(X锁):写操作在更新的数据上施加互斥锁,一个数据上,只能施加一个互斥锁,X锁和任意锁都是互斥的。

更新锁(U锁):U锁和S锁是兼容的,在写操作中,数据的更新过程分为两步,第一步是找到更新的数据,对于找到的数据施加U锁;第二步是在真正执行数据更新时,把U锁转换为X锁。

意向锁(I锁):意向锁有 IS、IX和IU 三种类型,当事务申请低粒度锁时,事务会在相同的对象上,依次申请高粒度资源的意向锁,直到表上的意向锁。例如,事务A申请在数据行R上施加X锁,同时,事务A会在包含该数据行R的数据页上申请IX锁,同时申请表级的IX锁,也就是说,在一行上申请X锁,那么该行所在的页和表都会申请IX锁。在这种情况下,其他事务就很容易探测到有事务在更新表中的数据,避免锁住整个表。

键范围锁(Key-Range):键范围锁出现在SERIALIZABLE隔离级别,如果事务需要扫描一个范围的数据,事务使用键范围锁,锁定表中的特定范围,避免其他事务向范围中插入新的数据,键范围锁与特定的索引键关联。

3,锁持续的时间

X锁持续到事务结束,S锁的持续时间受到事务隔离级别的影响。

  • READ UNCOMMITTED :不申请S锁
  • READ COMMITTED:申请S锁,S锁在读取操作完成时就立马释放,S锁持续的时间是语句执行的时间
  • REPEATABLE READ:申请S锁,当事务结束之后,立马释放S锁,S锁持续的时间是事务执行的时间
  • SERIALIZABLE:申请S锁,锁定的资源是键范围,当事务结束之后,立马释放S锁,S锁持续的时间是事务执行的时间

4,锁的兼容性

自行百度

5,锁升级

锁升级是指锁施加的资源的粒度增加,从低粒度升级到高粒度,这会使数据库系统并发度降低,增加阻塞和死锁的风险,但是带来的好处是降低锁管理的开销、保证查询执行的速度。

锁升级的第一种方式是锁占用的内存达到阈值。通常来说,一个锁结构大概需要96B的内存空间,当数据库中存在大量的锁结构时,锁结构会占用较多的内存空间。当SQL Server使用超过24%的Buffer Poll用于存储锁结构时,SQL Server 引擎会选择一些正在持有的锁的会话,把锁升级到高层次的级别,使锁锁定的资源粒度变大,降低锁的数量。

锁升级的第二种方式是锁的数量达到阈值。在一个表上,当一个会话申请的锁的数量达到一定的阈值,阈值的默认值是5000,也就是说,当一个会话持有锁的数量超过5000时,SQL Server就自动把锁升级。

锁升级是自动进行的,也可以使用LOCK HINT,手动控制锁的粒度。

 

参考文档:

posted @ 2019-08-19 11:29  悦光阴  阅读(1902)  评论(0编辑  收藏  举报