数据库锁


内容转载自:
[1] 数据库锁分类和总结
[2] 数据库锁机制
[3] SQL 常用优化手段总结 - 认识数据库锁

由于针对不同的存储引擎的数据库锁的实现是不相同的,而现代大型项目基本都选择了 innoDB 作为存储引擎,所以本章节只探讨 innoDB 数据库锁的内容。

1. 为什么要有数据库锁?

之所以会有数据库锁,就是因为当并发的时候会出现对多个线程/进程对同一数据进行增删改查操作,为了保持数据的一致性,就必须使用锁。锁主要用于多用户环境下保证数据库完整性和一致性。

就像上卫生间一样,同一时间卫生间只能供一人使用,里边的人上了锁,外边的人就只能等待锁开了之后,才能对卫生间进行你想要的操作。这就防止大家同时挤在一个卫生间,感觉有点sq。

2. 数据库锁有哪些?

那么数据库锁有哪些呢?

2.1 共享锁(Share Lock,读锁,S锁)

共享锁也叫读锁,顾名思义就是进行只读操作的锁。
共享 (S) 用于不更改或不更新数据的操作(只读操作),如 SELECT 语句。允许一个事务读取一行,阻止其他事务获得相同数据集的排他锁(道理很简单,文件被读取的时候不允许写入)。但允许其他事务获取共享锁。共享锁是非独占的,允许多个并发事务读取其锁定的资源。

性质

  1. 多个事务可封锁同一个共享页;
  2. 任何事务都不能修改该页;
  3. 通常是该页被读取完毕,S锁立即被释放。
SQL Server中,默认情况下,数据被读取后,立即释放共享锁。 
例如,执行查询语句“SELECT * FROM my_table”时,首先锁定第一页,读取之后,释放对第一页的锁定,然后锁定第二页。
这样,就允许在读操作过程中,修改未被锁定的第一页。 
例如,语句“SELECT * FROM my_table HOLDLOCK”就要求在整个查询过程中,保持对表的锁定,直到查询完成才释放锁定。

2.2 更新锁(U锁)

为解决死锁,引入更新锁。U锁,在修改操作的初始化阶段用来锁定可能要被修改的资源,这样可以避免使用共享锁造成的死锁现象。

因为当使用共享锁时,修改数据的操作分为两步:

  1. 首先获得一个共享锁,读取数据,
  2. 然后将共享锁升级为排他锁,再执行修改操作。
    这样如果有两个或多个事务同时对一个事务申请了共享锁,在修改数据时,这些事务都要将共享锁升级为排他锁。这时,这些事务都不会释放共享锁,而是一直等待对方释放,这样就造成了死锁。
    如果一个数据在修改前直接申请更新锁,在数据修改时再升级为排他锁,就可以避免死锁。

性质

  1. 用来预定要对此页施加X锁,它允许其他事务读,但不允许再施加U锁或X锁;
  2. 当被读取的页要被更新时,则升级为X锁;
  3. U锁一直到事务结束时才能被释放。

2.3 排他锁/独占锁 (Exclusive Lock,写锁,X锁)

排他锁也叫写锁,顾名思义是进行写操作的锁,当然也可以用来读操作。
排他锁:允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享与排他锁。但是可以对获取了排他锁的数据集进行单纯的查询访问。

性质

  1. 仅允许一个事务封锁此页;
  2. 其他任何事务必须等到X锁被释放才能对该页进行访问;
  3. X锁一直到事务结束才能被释放。
产生排他锁的SQL语句如下:select * from ad_plan for update;

2.4 意向锁(Intent Lock)

意向锁就是说在屋(比如代表一个表)门口设置一个标识,说明屋子里有人(比如代表某些记录)被锁住了。另一个人想知道屋子
里是否有人被锁,不用进屋子里一个一个的去查,直接看门口标识就行了。

当一个表中的某一行被加上排他锁后,该表就不能再被加表锁。数据库程序如何知道该表不能被加表锁?一种方式是逐条的判断该
表的每一条记录是否已经有排他锁,另一种方式是直接在表这一层级检查表本身是否有意向锁,不需要逐条判断。显然后者效率高。

2.5 计划锁(Schema Lock)

该锁不允许任何其它session连接该表。连都连不了这个表了,当然更不用说想对该表执行什么sql语句了。

表锁

MyIsam 实现了表锁。表锁是指对数据库表加锁,不如行锁灵活,
表锁分为:读锁与写锁。
innoDB 存储引擎实现了行锁与表锁(意向锁)。行锁可以以行为单位对数据集进行锁定。行锁也分为两种锁:分别是读锁与写锁。

页锁

作用范围为一页。

行锁

光理解锁的基本概念是远远不够的。在 InnoDB 中,行锁是通过给索引上的索引项加锁来实现的。如果没有索引,InnoDB 将会通过隐藏的聚簇索引来对记录加锁。另外,根据针对 sql 语句检索条件的不同,加锁又有以下三种情形需要我们掌握。

Record lock:对索引项加锁。相同索引的无法同时建立排他锁,一个建立会阻塞其他所有针对此索引的排他锁。
Gap lock:对索引项之间的间隙加锁。
Next-key lock:对记录记前面的间隙加锁

数据库能够确定那些行需要锁的情况下使用行锁,如果不知道会影响哪些行的时候就会使用表锁。


举个例子,一个用户表user,有主键id和用户生日birthday。 
当你使用updatewhere id=?这样的语句时,数据库明确知道会影响哪一行,它就会使用行锁; 
当你使用updatewhere birthday=?这样的的语句时,因为事先不知道会影响哪些行就可能会使用表锁。


2.6 悲观锁(Pessimistic Lock)

每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人拿这个数据就会block(阻塞),直到它拿锁。

悲观锁(Pessimistic Lock):正如其名,具有强烈的独占和排他特性。它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。
传统的关系数据库里用到了很多这种锁机制,比如行锁、表锁、读锁、写锁等,都是在操作之前先上锁。

2.7 乐观锁(Optimistic Lock)

每次去拿数据的时候都认为别人不会修改,所以,不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据,可以使用版本号等机制。

乐观锁( Optimistic Locking ): 相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。
悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。
乐观锁,大多是基于数据版本( Version )记录机制实现。
数据版本:为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。

2.8 如何提高并发效率

悲观锁

利用数据库本身的锁机制实现。通过上面对数据库锁的了解,可以根据具体业务情况综合使用事务隔离级别与合理的手工指定锁的方式比如降低锁的粒度等减少并发等待。

乐观锁

利用程序处理并发。原理都比较好理解,基本一看即懂。方式大概有以下3种

  1. 对记录加版本号.

版本号(version)
版本号(记为version):就是给数据增加一个版本标识,在数据库上就是表中增加一个version字段,每次更新把这个字段加1,读取数据的时候把version读出来,更新的时候比较version,如果还是开始读取的version就可以更新了,如果现在的version比老的version大,说明有其他事务更新了该数据,并增加了版本号,这时候得到一个无法更新的通知,用户自行根据这个通知来决定怎么处理,比如重新开始一遍。这里的关键是判断version和更新两个动作需要作为一个原子单元执行,否则在你判断可以更新以后正式更新之前有别的事务修改了version,这个时候你再去更新就可能会覆盖前一个事务做的更新,造成第二类丢失更新,所以你可以使用update … where … and version=”old version”这样的语句,根据返回结果是0还是非0来得到通知,如果是0说明更新没有成功,因为version被改了,如果返回非0说明更新成功。

  1. 对记录加时间戳.

时间戳(timestamp):和版本号基本一样,只是通过时间戳来判断而已,注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。

  1. 对将要更新的数据进行提前读取、事后对比。

待更新字段:和版本号方式相似,只是不增加额外字段,直接使用有效数据字段做版本控制信息,因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值(即开始读的值),如果是就把我修改的count的值更新到该字段,否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。

3. 什么情况用什么锁?

通常是由数据库来控制的,基本也不用咱自己搞。如果想自己搞,用hint。

4. 总结

pass

5. 参考文献

[1] 数据库锁分类和总结
[2] 数据库锁机制
[3] SQL 常用优化手段总结 - 认识数据库锁

相关推荐
<p> 欢迎参加英特尔® OpenVINO™工具套件初级课程 !本课程面向零基础学员,将从AI的基本概念开始,介绍人工智能与视觉应用的相关知识,并且帮助您快速理解英特尔® OpenVINO™工具套件的基本概念以及应用场景。整个课程包含了视频的处理,深度学习的相关知识,人工智能应用的推理加速,以及英特尔® OpenVINO™工具套件的Demo演示。通过本课程的学习,将帮助您快速上手计算机视觉的基本知识和英特尔® OpenVINO™ 工具套件的相关概念。 </p> <p> 为保证您顺利收听课程参与测试获取证书,还请您于<strong>电脑端</strong>进行课程收听学习! </p> <p> 为了便于您更好的学习本次课程,推荐您免费<strong>下载英特尔® OpenVINO™工具套件</strong>,下载地址:https://t.csdnimg.cn/yOf5 </p> <p> 收听课程并完成章节测试,可获得本课程<strong>专属定制证书</strong>,还可参与<strong>福利抽奖</strong>,活动详情:https://bss.csdn.net/m/topic/intel_openvino </p> <p> 8月1日-9月30日,学习完成【初级课程】的小伙伴,可以<span style="color:#FF0000;"><strong>免费学习【中级课程】</strong></span>,中级课程免费学习优惠券将在学完初级课程后的7个工作日内发送至您的账户,您可以在:<a href="https://i.csdn.net/#/wallet/coupon">https://i.csdn.net/#/wallet/coupon</a>查询优惠券情况,请大家报名初级课程后尽快学习哦~ </p> <p> <span style="font-size:12px;">请注意:点击报名即表示您确认您已年满18周岁,并且同意CSDN基于商务需求收集并使用您的个人信息,用于注册OpenVINO™工具套件及其课程。CSDN和英特尔会为您定制最新的科学技术和行业信息,将通过邮件或者短信的形式推送给您,您也可以随时取消订阅不再从CSDN或Intel接收此类信息。 查看更多详细信息请点击CSDN“<a href="https://passport.csdn.net/service">用户服务协议</a>”,英特尔“<a href="https://www.intel.cn/content/www/cn/zh/privacy/intel-privacy-notice.html?_ga=2.83783126.1562103805.1560759984-1414337906.1552367839&elq_cid=1761146&erpm_id=7141654/privacy/us/en/">隐私声明</a>”和“<a href="https://www.intel.cn/content/www/cn/zh/legal/terms-of-use.html?_ga=2.84823001.1188745750.1560759986-1414337906.1552367839&elq_cid=1761146&erpm_id=7141654/privacy/us/en/">使用条款</a>”。</span> </p> <p> <br /> </p>
<p> <br /> </p> <p> <br /> </p> <p> <br /> </p> <p> <b><span style="background-color:#FFE500;">【超实用课程内容】</span></b> </p> <p> <span>本课程根据实际开发中总结出来的一些学习思路,从零开始详细讲解</span><span>Flutter</span><span>的基础知识点。从简到难,从浅入深,逐步带领大家了解</span><span>Flutter</span><span>,熟悉</span><span>Flutter</span><span>的组成部分,并且带领大家学习如何使用</span><span>Flutter</span><span>实现</span><span>UI</span><span>功能编写。通过对本视频的学习,你将会掌握</span><span>Flutter</span><span>常用组件和常用布局构建复杂布局、</span><span>Flutter</span><span>路由导航实现多页面构建和交互、</span><span>Flutetr</span><span>手势处理和动画实现动态交互效果,以及如何使用音视频、图片、文字、字体等资源。除此之外,你还可以收获每一章节遇到的重难点问题的解决方案。</span> </p> <p style="font-family:Helvetica;color:#3A4151;font-size:14px;background-color:#FFFFFF;"> <b><span style="background-color:#FFE500;">【课程如何观看?】</span></b> </p> <p style="font-family:Helvetica;color:#3A4151;font-size:14px;background-color:#FFFFFF;"> PC端:<a href="https://edu.csdn.net/course/detail/26277"></a><a href="https://edu.csdn.net/course/detail/26150"></a><a href="https://edu.csdn.net/course/detail/26150"></a><a href="https://edu.csdn.net/course/detail/27286"></a><a href="https://edu.csdn.net/course/detail/26858"></a><a href="https://edu.csdn.net/course/detail/26227">https://edu.csdn.net/course/detail/26227</a> </p> <p style="font-family:Helvetica;color:#3A4151;font-size:14px;background-color:#FFFFFF;"> 移动端:CSDN 学院APP(注意不是CSDN APP哦) </p> <p style="font-family:Helvetica;color:#3A4151;font-size:14px;background-color:#FFFFFF;"> 本课程为录播课,课程永久有效观看时长,大家可以抓紧时间学习后一起讨论哦~ </p> <p class="ql-long-24357476" style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;"> <strong><span style="background-color:#FFE500;">【学员专享增值服务】</span></strong> </p> <p class="ql-long-24357476" style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;"> <b>源码开放</b> </p> <p class="ql-long-24357476" style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;"> 课件、课程案例代码完全开放给你,你可以根据所学知识,自行修改、优化 </p> <p class="ql-long-24357476" style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;"> 下载方式:电脑登录<a href="https://edu.csdn.net/course/detail/26277"></a><a href="https://edu.csdn.net/course/detail/26150"></a><a href="https://edu.csdn.net/course/detail/27286"></a><a href="https://edu.csdn.net/course/detail/26858"></a><a href="https://edu.csdn.net/course/detail/26227">https://edu.csdn.net/course/detail/26227</a>,点击右下方课程资料、代码、课件等打包下载 </p> <p> <br /> </p>
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页