源栈培训:数据库:14 - 并发冲突和锁

更多
2019年04月15日 11点38分 作者:叶飞 修改

并发冲突

为了提高数据库性能,就不能严格遵守事务隔离性要求,只能允许在一个事务中进行其他事务的操作(并发)。但这样就带来了以下这些问题:

  • 丢失的更新:多个事务对同一数据进行更新,后更新会覆盖之前的更新
  • 脏读:读取了事务提交中的数据,但随后事务回滚,数据被删,读到的是“脏”(未提交的)数据。
  • 不可重复读:在同一次事务中,两次查询结果不一致(事务中发生了修改)
  • 幻影读:类似于“不可重复读”,但两次查询不一样的是范围(事务中发生了删除/插入)

注意:一个session(query)里的操作不是并发

为了避免上述问题,SQL Server引入了:锁 和 快照。


锁(LOCK)

执行步骤/原理:

  1. 任何事务,都必须加锁后才能执行
  2. 只有当所针对的数据上:
    • 已经有锁,而且和要加的锁相冲突,不能加锁和执行,需要等待之前的锁被释放
    • 没有锁,或者现有锁和要加的锁兼容,可以加锁并执行

粒度和升级:( / 键 / 页 / 区 ) -> -> 数据库

查看工具

  • EXEC sp_lock
  • sys.dm_tran_locks

类型

  • S:共享锁:用于只读的SELECT。加锁之后其他用户不能修改,可避免幻影读和不可重复读。为了演示,使用WITH(HOLDLOCK)让锁一直保持到事务结束
  • X:独占锁(排他):用于INSERT/UPDATE/DELET。加锁之后其他用户不能读。
  • U:更新锁:在UPDATE/DELET之前需要查找,在这个查找的过程中使用的就是U-LOCK;一旦查找到需要的数据,U-LOCK就转变成X-LOCK
  • I:意向锁:在需要加锁节点的上一级加上“意向锁”,可以提高锁查找的性能。比如在需要在某一行上加X锁,就可以在KEY的上一级PAGE(甚至是TABLE级)加IX意向锁,这样当另一个用户想要在这张表上加锁的时候,就不需要全表扫描直到加X锁的行……

性:在已经加锁的数据上再加锁时,兼容性就可以加,否则就只能等待(详见:MSDN

锁的运行由SQL SERVER自行管理。一般不推荐直接人为干预,以避免导致一系列复杂的问题。


作业

重复演示各种并发问题,解释/显示锁能否应对上述问题。


隔离级别

查看:DBCC USEROPTIONS

设置:

  • SET TRANSACTION ISOLATION LEVEL isolation_level,作用于:connection
  • 隔离提示:WITH(isolation_level),作用于:当前语句

未提交读(READ UNCOMMITTED):最低控制级别。事务还没有提交,就可以读。不申请共享锁

提交读(READ COMMITTED):默认级别。已经提交了,才可以读。可避免脏读。共享锁得到结果即释放

可重复读(REPEATABLE READ):除了无法避免幻读,其他并发问题(包括“不可重复读”)都可以避免。共享锁一直到事务结束才释放

串行化(SERIALIZABLE):最高级别。避免一切并发问题。共享锁(还锁定潜在修改:索引和表)直到事务结束才释放

—— 以上通过锁来控制,变化的只有共享锁,排他锁始终是在事务结束后释放。


作业

利用图和SQL语句演示 READ COMMITTED 和 SERIALIZABLE 存在的和可避免的问题。参考图:


思考题:游戏买装备

比如只有100个游戏币,装备30个游戏币一套。

恶意用户利用软件,瞬间向服务器发送无数条购买装备的请求。

结果就真的到手了n多装备,这是怎么一回事呢?


死锁

出现条件:

  • 双方均需要对方释放锁为前提,才能继续进行;
  • 而要释放锁,又必须要事务继续进行。

死锁不是等待。而是彻底的锁死在那里了!是无论怎么等待,都没办法活过来的那种……^_^

只能依靠SQL SERVER来自行裁判其中一个事务作为victim,进行回滚。


理解:为什么需要U锁来避免死锁?(参考:Why do we need UPDATE Locks in SQL Server?)

  • 如果使用S锁代替U锁,很容易形成死锁:
  • 使用U锁可以避免上述死锁
  • 但只用X锁也可以避免死锁啊?使用U锁是为了兼顾性能:



示范:一个最简单的死锁




快照 vs 基于快照的提交读

SQL Server还提供了基于tempdb的快照(SNAPSHOT)隔离:

  • 需要在数据库上开启
  • 开启之后,数据库上的任何行更改(增删改)都会在tempdb生成修改前的快照(注意:并不要求必须是在快照事务中)
    查看快照:sys.dm_tran_version_store
  • 读之间不加锁:读的是存储在tempdb中的数据快照
  • 同理,读写之间也互不阻塞
  • 但写和写之间是加锁并需要等待的
  • 快照由SQL SERVER的“版本控制器”定期清理(oldest and less uesed)

具体又分为两种:

a)快照隔离:

  1. 必须在DATABASE上开启快照隔离:ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON,一旦设立,哪怕连接上并不使用SNAPSHOT,也会生成快照
  2. 在连接上设置使用快照隔离:SET TRANSACTION ISOLATION LEVEL SNAPSHOT。如何数据库上没有ALLOW_SNAPSHOT_ISOLATION,所有SQL语句无法执行。

事务开始后的一切数据来源都是启动事务时产生的行版本,能避免不可重复读。

有冲突检查:在快照隔离的事务中不能更改已被其他事务更改的数据。

b)基于快照的提交读:

  1. 在DATABASE上开启:ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON  -- 其他连接必须关闭
  2. 在连接上设置:SET TRANSACTION ISOLATION LEVEL READ COMMITTED

获取语句开始而不是事务开始的最新一致性版本(快照),

没有冲突检查,而是等待上一个事务完成。

理解:乐观 / 悲观 并发


源栈培训 数据库 并发 隔离级别
赞: 8 踩: 0

打赏
已收到打赏的 帮帮币

你的 打赏 非常重要!
为了保证文章的质量,每一篇文章的发布,都已经消耗了作者 1 枚 帮帮币
没有“帮帮币”,作者无法发布新的文章。

全系列阅读
评论 / 0
叶飞的系列文章

源栈培训:ASP.NET全栈开发

飞哥的源栈培训:线上全程直播,免费收看;线下拎包入住,按周计费。本系列收录所有讲义(含视频录播地址)

编程那些事:菜鸟入门

大飞哥倾力之作,面向有意入行IT/开发/编程的初学者,欢迎任何形式的留言建议……

从包工头到程序猿

真实故事,讲述我在家装公司关门之后,如何转行成为一个程序猿的故事。(《折腾》第三卷)

《折腾》(卷一)青涩

时间段:从大学毕业到开始创业。离开青葱校园,涉世之初的那些往事……

《折腾》(卷二)风雨 之(1)工地

我一个完全的门外汉(无论装修还是管理),开始给黎叔装修房子。从踌躅满志,到四处碰壁;从一往直前,到左右为难……

《折腾》(卷二)风雨 之(2)胸怀

作为一个律师,接工程没签合同,被狠狠的坑了一把!年轻人暴烈的想要复仇,黎叔教他一个企业家的胸怀……

《折腾》(卷二)风雨 之(3)渠道

成立了公司,招聘了员工,开始大力的拓展业务,一个接一个的坑,摔倒了又爬起来……

《折腾》(卷二)风雨 之(4)视野

经历残酷现实的磨砺,终于明白:干啥事,都不能闭门造车,人要走出去,开阔视野……

未分类

系统自动生成的未分类系列

一锅大杂烩

从律师到包工头,从码农到写手,读书交友生活创业,各种零零碎碎,乱七八糟……

人人都是程序猿

计算机编程普及课程,视频:https://space.bilibili.com/55410301/#/channel/detail?cid=49491

全部
关键字



帮助

反馈