精华内容
下载资源
问答
  • 数据库通过锁以及锁协议来进行并发控制,解决并发事务带来的问题,本篇博文主要是解析数据库的锁协议和Mysql的默认存储引擎InnoDB的锁机制。 如果对事务隔离级别以及并发事务带来的问题不熟悉可以翻阅我的另外一篇...
  • 主要介绍了PHP+redis实现的悲观锁机制,简单介绍了redis锁机制与乐观锁、悲观锁等概念,并结合实例形式分析了php+redis实现悲观锁相关操作技巧,需要的朋友可以参考下
  • Java锁机制详解.pdf

    2017-11-02 17:05:48
    Java锁机制详解.pdf java线程 java多线程 Java锁机制详解.pdf java线程 java多线程
  • 由浅入深解析synchronized机制,各种的概念的介绍,膨胀过程,基于redis的分布式demo。
  • 对于任何一种数据库来说都需要有相应的锁定机制,所以MySQL自然也不能例外。下面这篇文章主要给大家介绍了关于mysql中锁机制的相关资料,文中通过示例代码介绍的非常详细,需要的朋友可以参考下
  • 今天小编就为大家分享一篇关于redis锁机制介绍与实例,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
  • 主要介绍了Java多线程锁机制相关原理实例解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
  • MYSQL锁机制

    2018-07-26 19:23:56
    本文详细描述了MYSQL锁机制,掌握锁机制,对于避免死锁,提高软件的工作效率,有很大的帮助.
  • 腾讯云数据库负责人林晓斌说过:“我们面试MySQL同事时只考察两点,索引和锁”。言简意赅,MySQL锁的重要性不言而喻。...本文通过同事“侨总”的一场面试,带你通俗易懂的掌握MySQL各种锁机制,希望可以帮到你!

    小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL江湖路 | 专栏目录

      腾讯云数据库负责人林晓斌说过:“我们面试MySQL同事时只考察两点,索引和锁”。言简意赅,MySQL锁的重要性不言而喻。
      本文通过同事“侨总”的一场面试,带你通俗易懂的掌握MySQL各种锁机制,希望可以帮到你!近期会继续整理深入性的锁机制文章,有兴趣的老铁,记得关注一下,到时叫你❤️❤️~

      今天的主人公是我们公司同事侨总,传说中手上有10个比特币的男人。自从比特币大涨以来,养成了几个小爱好:周末听戏坐包厢,骑马酒吧滑雪场。

    在这里插入图片描述

      这不,前两天侨总又双叒叕出来体验面试了,晚上请我烧烤时跟我聊了聊这次有趣的面试经历,真是意犹未尽,趁他回味之余我又吃了十几串儿腰子和羊肉~ 嗯,真香!

      对不住,跑题了。。人到中年嘛,保温杯里泡枸杞之余总会。。。

      来不及解释了,快上车!

    大家好,我是陈哈哈的同事“侨总”,领导一般不敢喊我名字,都叫我小侨~

    下面是我的一次面试经历,面试官是技术经理和HR,大家吃好喝好~

    在这里插入图片描述

    侨总:马…小马哥好!

    面试官:你好,小侨啊,看你简历写着精通MySQL锁,你认为精通应该是啥水平呢?

    侨总:马哥我是个老实人,我认为精通就是,比面试官知道的多就完了。。

    面试官:(??怎么有种似曾相识的感觉?《听我讲完redo log、binlog原理,面试官老脸一红》

    面试官:行,那你先给我说说MySQL设计这个锁是干啥用的呀?

    侨总:数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,为了保证数据的一致性,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要机制。

      简单说,数据表就像公共厕所。emmm…换个下饭的说法,数据表就好比您开的一家酒店,而每行数据就像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他用完退房后才可以再次使用,这样保证了房间的一致性,方便酒店进行管理。

      MySQL锁机制的初衷便是如此,当然,MySQL数据库由于其自身架构的特点,存在多种数据存储引擎,每种存储引擎所针对的应用场景特点都不太一样,为了满足各自特定应用场景的需求,每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计,所以各存储引擎的锁定机制也有较大区别。

    面试官:嗯,那你说一下MySQL都分为哪些锁。

    在这里插入图片描述

    侨总

    1. 按锁粒度从大到小分类:表锁页锁行锁;以及特殊场景下使用的全局锁

    2. 如果按锁级别分类则有:共享(读)锁排他(写)锁意向共享(读)锁意向排他(写)锁

    3. 以及Innodb引擎为解决幻读等并发场景下事务存在的数据问题,引入的Record Lock(行记录锁)Gap Lock(间隙锁)Next-key Lock(Record Lock + Gap Lock结合)等;

    4. 还有就是我们面向编程的两种锁思想:悲观锁、乐观锁。

    面试官:袁芳你怎么看?

    HR小姐姐:。。。

    面试官:小侨啊,那你来谈一谈你对表锁、行锁的理解吧。

    表锁

    侨总:表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单,带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以可以很好的避免困扰我们的死锁问题。

      当然,锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高,大大降低并发度。

      使用表级锁定的主要是MyISAM,MEMORY,CSV等一些非事务性存储引擎。

    行锁

    侨总:与表锁正相反,行锁最大的特点就是锁定对象的颗粒度很小,也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小,所以发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。

      虽然能够在并发处理能力上面有较大的优势,但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要做的事情也更多,带来的消耗自然也就更大了。此外,行级锁定也最容易发生死锁

      使用行级锁定的主要是InnoDB存储引擎。

    • 适用场景:从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新数据的情况,同时又有并发查询的应用场景。

    页锁

      除了表锁、行锁外,MySQL还有一种相对偏中性的页级锁,页锁是MySQL中比较独特的一种锁定级别,在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间,所以获取锁定所需要的资源开销,以及所能提供的并发处理能力也同样是介于上面二者之间。另外,页级锁定和行级锁定一样,会发生死锁。

      使用页级锁定的主要是BerkeleyDB存储引擎。

    面试官:那全局锁是什么时候用的呢?

    全局锁

    侨总:首先全局锁,是对整个数据库实例加锁。使用场景一般在全库逻辑备份时。

      MySQL提供加全局读锁的命令:Flush tables with read lock (FTWRL)

      这个命令可以使整个库处于只读状态。使用该命令之后,数据更新语句、数据定义语句和更新类事务的提交语句等修改数据库的操作都会被阻塞。

    风险:

    1. 如果在主库备份,在备份期间不能更新,业务停摆
    2. 如果在从库备份,备份期间不能执行主库同步的binlog,导致主从延迟同步

      还有一种锁全局的方式:set global readonly=true ,相当于将整个库设置成只读状态,但这种修改global配置量级较重,和全局锁不同的是:如果执行Flush tables with read lock 命令后,如果客户端发生异常断开,那么MySQL会自动释放这个全局锁,整个库回到可以正常更新的状态。但将库设置为readonly后,客户端发生异常断开,数据库依旧会保持readonly状态,会导致整个库长时间处于不可写状态,试想一下微信只能看,不能打字~~

    HR小姐姐:那微信不就完蛋了?

    侨总:是啊,抓紧找老实人背锅!

    面试官:不错,你把这几种锁的侧重点都表述清楚了。那你再说一下你对不同级别的那几种锁的使用场景和理解吧?

    侨总:MySQL基于锁级别又分为:共享(读)锁排他(写)锁意向共享(读)锁意向排他(写)锁

    共享(读)锁、排他(写)锁、意向共享(读)锁、意向排他(写)锁

    侨总:对于共享(读)锁排他(写)锁,比如咱们住酒店,入住前顾客都是有权看房的,只看不住想白嫖都是可以的,前台小姐姐会把门给你打开。当然,也允许不同的顾客一起看(共享 读),比如和这位杀马特小伙子。

    在这里插入图片描述

      看房时房间相当于公共场所,小姐姐嘱咐不能乱涂乱画,也不能偷喝免费的矿泉水。。如果你觉得不错,偷偷跑到前台要定这间房,交钱后会给你这个房间的钥匙并将房间状态改为已入住,不再允许其他人看房(排他 写)。

      对了,当办理入住时前台小姐姐也会通知看房的杀马特小伙子说这间房已经有人定了!!等看房的杀马特小伙儿骂骂咧咧出门后,看到满头大汗的你,鄙夷着咽了一口口水,咳tui!然后你锁上门哼着歌儿,开始干那些见不得人的事儿~~直到你退房前,其他人无法在看你的房

      可见,读锁是可以并发获取的(共享的),而写锁只能给一个事务处理(排他的)。当你想获取写锁时,需要等待之前的读锁都释放后方可加写锁;而当你想获取读锁时,只要数据没有被写锁锁住,你都可以获取到读锁,然后去看房。

      另外还有意向读\写锁,严格来说他们并不是一种锁,而是存放表中所有行锁的信息。就像我们在酒店,当我们预定一个房间时,就对该行(房间)添加 意向写锁,但是同时会在酒店的前台对该行(房间)做一个信息登记(旅客姓名、男女、住多长时间、家里几头牛等)。大家可以把意向锁当成这个酒店前台,它并不是真正意义上的锁(钥匙),它维护表中每行的加锁信息,是共用的。后续的旅客通过酒店前台来看哪个房间是可选的,那么,如果没有意图锁,会出现什么情况呢?假设我要住房间,那么我每次都要到每一个房间看看这个房间有没有住人,显然这样做的效率是很低下的。杀马特小伙儿表示支持!

      读写锁、意向锁的兼容性如下所示;

    锁类型读锁写锁意向读锁意向写锁
    读锁兼容冲突兼容冲突
    写锁冲突冲突冲突冲突
    意向读锁兼容冲突兼容兼容
    意向写锁冲突冲突兼容兼容

    侨总:再回到MySQL原理上讲

    1 共享(读)锁(Share Lock)

      共享锁,又叫读锁,是读取操作(SELECT)时创建的锁。其他用户可以并发读取数据,但在读锁未释放前,也就是查询事务结束前,任何事务都不能对数据进行修改(获取数据上的写锁),直到已释放所有读锁。

      如果事务A数据B(1024房)加上读锁后,则其他事务只能对数据B上加读锁,不能加写锁。获得读锁的事务只能读数据,不能修改数据。

    SQL显示加锁写法:

    SELECTLOCK IN SHARE MODE;
    

      在查询语句后面增加LOCK IN SHARE MODE,MySQL就会对查询结果中的每行都加读锁,当没有其他线程对查询结果集中的任何一行使用写锁时,可以成功申请读锁,否则会被阻塞。其他线程也可以读取使用了读锁的表,而且这些线程读取的是同一个版本的数据。

    2 排他(写)锁(Exclusive Lock)

      排他锁又称写锁、独占锁,如果事务A数据B加上写锁后,则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据,又能修改数据

    SQL显示加锁写法:

    SELECTFOR UPDATE;
    

      在查询语句后面增加FOR UPDATE,MySQL 就会对查询结果中的每行都加写锁,当没有其他线程对查询结果集中的任何一行使用写锁时,可以成功申请写锁,否则会被阻塞。另外成功申请写锁后,也要先等待该事务前的读锁释放才能操作。

    3 意向锁(Intention Lock)

      意向锁属于表级锁,其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁:

    • 意向共享锁(IS):表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁;

    • 意向排他锁(IX):类似上面,表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

      意向锁是 InnoDB 自动加的,不需要用户干预。

      再强调一下,对于INSERT、UPDATE和DELETE,InnoDB 会自动给涉及的数据加排他锁;对于一般的SELECT语句,InnoDB 不会加任何锁,事务可以通过以下语句显式加共享锁或排他锁。

    共享锁:SELECT … LOCK IN SHARE MODE;
    排他锁:SELECT … FOR UPDATE;

    面试官:(这小子有两下子)嗯,袁芳你怎么看?

    HR:通俗易懂,我听懂了~~

    面试官:好,那最后一个问题,你上面提到了乐观锁和悲观锁,谈谈你对它的看法吧。

    侨总:其实悲观锁和乐观锁,也并不是 MySQL 或者数据库中独有的概念,而是并发编程的基本概念。主要区别在于,操作共享数据时,“悲观锁”即认为数据出现冲突的可能性更大,而“乐观锁”则是认为大部分情况不会出现冲突,进而决定是否采取排他性措施。

      反映到 MySQL 数据库应用开发中,悲观锁一般就是利用类似 SELECT … FOR UPDATE 这样的语句,对数据加锁,避免其他事务意外修改数据。乐观锁则与 Java 并发包中的 AtomicFieldUpdater 类似,也是利用 CAS 机制,并不会对数据加锁,而是通过对比数据的时间戳或者版本号,来实现乐观锁需要的版本判断

      MySQL的多版本并发控制 (MVCC),其本质就可以看作是种乐观锁机制,而排他性的读写锁、两阶段锁等则是悲观锁的实现。

    面试官:好,小侨我看你对MySQL锁这块儿确实研究得比较透彻,连HR都听懂了,还是让我比较满意的。

    面试官:你平时有什么爱好么?

    侨总:我除了周末听戏坐包厢,骑马酒吧滑雪场。就是喜欢炒比特币啦!

    面试官:哦,不好意思,我们公司反对炒比特币的行为,回去等通知吧。

    侨总:???
    在这里插入图片描述

      说着面试官马经理走出了会议室,HR小姐姐表示我哪壶不开提哪壶,马老板高点买的比特币,现在泡沫炸了,亏成马,老板都差点亏没了。

    展开全文
  • MySQL高级 锁机制

    2017-12-20 17:18:12
    该文档详细介绍了MySQL的锁机制,个人认为比较容易理解,总体都是个人记录,包括截图。
  • MySQL的锁机制和加锁原理

    万次阅读 多人点赞 2019-03-09 10:35:01
    MySQL的锁机制和加锁原理 文章目录MySQL的锁机制和加锁原理1.行锁2.表锁3.页锁4.乐观锁和悲观锁4.1悲观锁4.2乐观锁5.MySQL/InnoDB中的行锁和表锁问题5.1InnoDB锁的特性6.Record Lock、Gap Lock、Next-key Lock锁6.1...

    MySQL的锁机制和加锁原理


    首先对mysql锁进行划分:

    1. 按照锁的粒度划分:行锁、表锁、页锁
    2. 按照锁的使用方式划分:共享锁、排它锁(悲观锁的一种实现)
    3. 还有两种思想上的锁:悲观锁、乐观锁。
    4. InnoDB中有几种行级锁类型:Record Lock、Gap Lock、Next-key Lock
    5. Record Lock:在索引记录上加锁
    6. Gap Lock:间隙锁
    7. Next-key Lock:Record Lock+Gap Lock

    1.行锁

    ​ 行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁。

    共享锁用法(S锁 读锁)

    ​ 若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

    select ... lock in share mode;
    

    共享锁就是允许多个线程同时获取一个锁,一个锁可以同时被多个线程拥有。

    排它锁用法(X 锁 写锁)

    ​ 若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

    select ... for update
    

    排它锁,也称作独占锁,一个锁在某一时刻只能被一个线程占有,其它线程必须等待锁被释放之后才可能获取到锁。

    2.表锁

    ​ 表级锁是mysql锁中粒度最大的一种锁,表示当前的操作对整张表加锁,资源开销比行锁少,不会出现死锁的情况,但是发生锁冲突的概率很大。被大部分的mysql引擎支持,MyISAM和InnoDB都支持表级锁,但是InnoDB默认的是行级锁。

    共享锁用法

    LOCK TABLE table_name [ AS alias_name ] READ
    

    排它锁用法

    LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE
    

    解锁用法

    unlock tables;
    

    3.页锁

    ​ 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。BDB支持页级锁

    4.乐观锁和悲观锁

    ​ 在数据库的锁机制中介绍过,数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

    ​ 乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。

    ​ 无论是悲观锁还是乐观锁,都是人们定义出来的概念,可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念,像memcache、hibernate、tair等都有类似的概念。

    ​ 针对于不同的业务场景,应该选用不同的并发控制方式。所以,不要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念,更不要把他们和数据中提供的锁机制(行锁、表锁、排他锁、共享锁)混为一谈。其实,在DBMS中,悲观锁正是利用数据库本身提供的锁机制来实现的。

    4.1悲观锁

    ​ 在关系数据库管理系统里,悲观并发控制(又名“悲观锁”,Pessimistic Concurrency Control,缩写“PCC”)是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁,那只有当这个事务把锁释放,其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境,以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

    ​ 悲观锁,正如其名,它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度(悲观),因此,在整个数据处理过程中,将数据处于锁定状态。 悲观锁的实现,往往依靠数据库提供的锁机制 (也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)

    悲观锁的具体流程:

    在对任意记录进行修改前,先尝试为该记录加上排他锁(exclusive locking)

    如果加锁失败,说明该记录正在被修改,那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。

    如果成功加锁,那么就可以对记录做修改,事务完成后就会解锁了。

    其间如果有其他对该记录做修改或加排他锁的操作,都会等待我们解锁或直接抛出异常。

    在mysql/InnoDB中使用悲观锁:

    ​ 首先我们得关闭mysql中的autocommit属性,因为mysql默认使用自动提交模式,也就是说当我们进行一个sql操作的时候,mysql会将这个操作当做一个事务并且自动提交这个操作。

    1.开始事务
    begin;/begin work;/start transaction; (三者选一就可以)
    2.查询出商品信息
    select ... for update;
    4.提交事务
    commit;/commit work;
    

    通过下面的例子来说明:

    1.当你手动加上排它锁,但是并没有关闭mysql中的autocommit。

    SESSION1:
    mysql> select * from user for update;
    +----+------+--------+
    | id | name | psword |
    +----+------+--------+
    |  1 | a    | 1      |
    |  2 | b    | 2      |
    |  3 | c    | 3      |
    +----+------+--------+
    3 rows in set
    
    这里他会一直提示Unknown
    mysql> update user set name=aa where id=1;
    1054 - Unknown column 'aa' in 'field list'
    mysql> insert into user values(4,d,4);
    1054 - Unknown column 'd' in 'field list'
    

    2.正常流程

    窗口1:
    mysql> set autocommit=0;
    Query OK, 0 rows affected
    我这里锁的是表
    mysql> select * from user for update;
    +----+-------+
    | id | price |
    +----+-------+
    |  1 |   500 |
    |  2 |   800 |
    +----+-------+
    2 rows in set
    
    窗口2:
    mysql> update user set price=price-100 where id=1;
    执行上面操作的时候,会显示等待状态,一直到窗口1执行commit提交事务才会出现下面的显示结果
    Database changed
    Rows matched: 1  Changed: 1  Warnings: 0
    
    窗口1:
    mysql> commit;
    Query OK, 0 rows affected
    mysql> select * from user;
    +----+-------+
    | id | price |
    +----+-------+
    |  1 |   400 |
    |  2 |   800 |
    +----+-------+
    2 rows in set
    
    

    ​ 上面的例子展示了排它锁的原理:一个锁在某一时刻只能被一个线程占有,其它线程必须等待锁被释放之后才可能获取到锁或者进行数据的操作。

    悲观锁的优点和不足

    ​ 悲观锁实际上是采取了“先取锁在访问”的策略,为数据的处理安全提供了保证,但是在效率方面,由于额外的加锁机制产生了额外的开销,并且增加了死锁的机会。并且降低了并发性;当一个事物所以一行数据的时候,其他事物必须等待该事务提交之后,才能操作这行数据。

    4.2乐观锁

    在关系数据库管理系统里,乐观并发控制(又名“乐观锁”,Optimistic Concurrency Control,缩写“OCC”)是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,正在提交的事务会进行回滚。

    ​ 乐观锁( Optimistic Locking ) 相对悲观锁而言,乐观锁假设认为数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测,如果发现冲突了,则让返回用户错误的信息,让用户决定如何去做。

    ​ 相对于悲观锁,在对数据库进行处理的时候,乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

    数据版本,为数据增加的一个版本标识。当读取数据时,将版本标识的值一同读出,数据每更新一次,同时对版本标识进行更新。当我们提交更新的时候,判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对,如果数据库表当前版本号与第一次取出来的版本标识值相等,则予以更新,否则认为是过期数据。

    乐观锁的优点和不足

    ​ 乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的,因此尽可能直接做下去,直到提交的时候才去锁定,所以不会产生任何锁和死锁。但如果直接简单这么做,还是有可能会遇到不可预期的结果,例如两个事务都读取了数据库的某一行,经过修改以后写回数据库,这时就遇到了问题。

    5.1InnoDB锁的特性

    1. 在不通过索引条件查询的时候,InnoDB使用的确实是表锁!
    2. 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
    3. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
    4. 即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划(explain查看),以确认是否真正使用了索引。

    有关执行计划的解释可以看着这篇文章:https://www.jianshu.com/p/b5c01bd4a306

    1.通过非索引项检索数据,加表锁!

    price属性并没有加索引,因此这时候添加的锁为表级锁!
    窗口1:
    mysql> select * from product where price=88 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  2 | 蒙牛 |    88 |   1 |
    +----+------+-------+-----+
    
    窗口2:
    mysql> update product set price=price-100 where id=6;
    这里会等待,直到窗口1 commit后显示下面结果!
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0
    

    2.使用相同索引值但是不同行引发的冲突

    这里的num属性 加上了普通索引,price属性并没有索引
    窗口1:
    mysql> set autocommit=0;
    Query OK, 0 rows affected
    
    mysql> select * from product where num=1 and price=68 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  1 | 伊利 |    68 |   1 |
    +----+------+-------+-----+
    
    窗口2:
    mysql> update product set price=price+100 where num=1 and price=88;
    这里会发生等待,直到窗口1 commit 显示下面结果
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> select * from product;
    +----+----------+-------+-----+
    | id | name     | price | num |
    +----+----------+-------+-----+
    |  1 | 伊利     |    68 |   1 |
    |  2 | 蒙牛     |   188 |   1 |
    +----+----------+-------+-----+
    

    3.当使用索引检索数据时不同事务可以操作不同行数据

    锁一行数据,DML操作其他行并没有影响
    窗口1:
    mysql> select * from user where id=1 for update;
    +----+-------+
    | id | price |
    +----+-------+
    |  1 |   400 |
    +----+-------+
    
    窗口2:
    mysql> update user set price=price+100 where id=2;
    无需等待窗口1 commit
    Database changed
    Rows matched: 1  Changed: 1  Warnings: 0
    

    6.Record Lock、Gap Lock、Next-key Lock锁

    6.1.Record Lock

    ​ 单条索引上加锁,record lock 永远锁的是索引,而非数据本身,如果innodb表中没有索引,那么会自动创建一个隐藏的聚集索引,锁住的就是这个聚集索引。所以说当一条sql没有走任何索引时,那么将会在每一条聚集索引后面加X锁,这个类似于表锁,但原理上和表锁应该是完全不同的。

    6.2.Gap Lock

    ​ 间隙锁,是在索引的间隙之间加上锁,这是为什么Repeatable Read隔离级别下能防止幻读的主要原因。有关幻读的详细解释:https://blog.csdn.net/qq_38238296/article/details/88363017

    6.2.​1 什么叫间隙锁

    ​ 直接通过例子来说明:

    mysql> select * from product_copy;
    +----+--------+-------+-----+
    | id | name   | price | num |
    +----+--------+-------+-----+
    |  1 | 伊利   |    68 |   1 |
    |  2 | 蒙牛   |    88 |   1 |
    |  6 | tom    |  2788 |   3 |
    | 10 | 优衣库 |   488 |   4 |
    +----+--------+-------+-----+
    其中id为主键 num为普通索引
    窗口A:
    mysql> select * from product_copy where num=3 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  6 | tom  |  2788 |   3 |
    +----+------+-------+-----+
    1 row in set
    
    窗口B:
    mysql> insert into product_copy values(5,'kris',1888,2);
    这里会等待  直到窗口A commit才会显示下面结果
    Query OK, 1 row affected
    
    但是下面是不需要等待的
    mysql> update product_copy set price=price+100 where num=1;
    Query OK, 2 rows affected
    Rows matched: 2  Changed: 2  Warnings: 0
    mysql> insert into product_copy values(5,'kris',1888,5);
    Query OK, 1 row affected
    

    ​ 通过上面的例子可以看出Gap 锁的作用是在1,3的间隙之间加上了锁。而且并不是锁住了表,我更新num=1,5的数据是可以的.可以看出锁住的范围是(1,3]U[3,4)。

    6.2.2 为什么说gap锁是RR隔离级别下防止幻读的主要原因。

    首先得理解什么是幻读:https://blog.csdn.net/qq_38238296/article/details/88363017

    解决幻读的方式很简单,就是需要当事务进行当前读的时候,保证其他事务不可以在满足当前读条件的范围内进行数据操作。

    ​根据索引的有序性,我们可以从上面的例子推断出满足where条件的数据,只能插入在num=(1,3]U[3,4)两个区间里面,只要我们将这两个区间锁住,那么就不会发生幻读。

    6.2.3. 主键索引/唯一索引+当前读会加上Gap锁吗?

    直接通过例子来说明

    窗口A:
    mysql> select * from product_copy where id=6 for update;
    +----+------+-------+-----+
    | id | name | price | num |
    +----+------+-------+-----+
    |  6 | tom  |  2788 |   3 |
    +----+------+-------+-----+
    
    窗口B:并不会发生等待
    mysql> insert into product_copy values(5,'kris',1888,3);
    Query OK, 1 row affected
    

    例子说明的其实就是行锁的原因,我只将id=6的行数据锁住了,用Gap锁的原理来解释的话:因为主键索引和唯一索引的值只有一个,所以满足检索条件的只有一行,故并不会出现幻读,所以并不会加上Gap锁。

    6.2.4通过范围查询是否会加上Gap锁

    ​ 前面的例子都是通过等值查询,下面测试一下范围查询。

    窗口A:
    mysql> select * from product_copy where num>3 for update;
    +----+--------+-------+-----+
    | id | name   | price | num |
    +----+--------+-------+-----+
    | 10 | 优衣库 |   488 |   4 |
    +----+--------+-------+-----+
    
    窗口B:会等待
    mysql> insert into product_copy values(11,'kris',1888,5);
    Query OK, 1 row affected
    不会等待
    mysql> insert into product_copy values(3,'kris',1888,2);
    Query OK, 1 row affected
    

    其实原因都是一样,只要满足检索条件的都会加上Gap锁

    6.2.5 检索条件并不存在的当前读会加上Gap吗?

    1.等值查询

    窗口A:
    mysql> select * from product_copy where num=5 for update;
    Empty set
    
    窗口B:64都会等待
    mysql> insert into product_copy values(11,'kris',1888,6);
    Query OK, 1 row affected
    
    mysql> insert into product_copy values(11,'kris',1888,4);
    Query OK, 1 row affected
    

    原因一样会锁住(4,5]U[5,n)的区间

    2.范围查询

    这里就会有点不一样

    窗口A:
    mysql> select * from product_copy where num>6 for update;
    Empty set
    窗口B:84 都会锁住
    mysql> insert into product_copy values(11,'kris',1888,4);
    Query OK, 1 row affected
    
    mysql> insert into product_copy values(11,'kris',1888,8);
    Query OK, 1 row affected
    

    ​ 上面的2例子看出当你查询并不存在的数据的时候,mysql会将有可能出现区间全部锁住。

    6.3.Next-Key Lock

    这个锁机制其实就是前面两个锁相结合的机制,既锁住记录本身还锁住索引之间的间隙。

    7.死锁的原理及分析

    7.1. MVCC

    ​ MySQL InnoDB存储引擎,实现的是基于多版本并发控制协议—MVCC(Multi-Version Concurrency Control) MVCC最大的好处,相信也是耳熟能详:读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不冲突是非常重要的,极大的增加了系统的并发性能,这也是为什么现阶段,几乎所有的RDBMS,都支持了MVCC。

    7.2. 2PL:Two-Phase Locking

    ​ 传统RDBMS(关系数据库管理系统)加锁的一个原则,就是2PL (二阶段锁):Two-Phase Locking。相对而言,2PL比较容易理解,说的是锁操作分为两个阶段:加锁阶段与解锁阶段,并且保证加锁阶段与解锁阶段不相交。下面,仍旧以MySQL为例,来简单看看2PL在MySQL中的实现。

    transactionmysql
    begin;加锁阶段
    insert into加insert对应的锁
    update table加update对应的锁
    delete from加delete对应的锁
    commit解锁阶段
    将insert、update、delete的锁全部解开

    ​ 上面的例子可以看出2PL就是将加锁、解锁分为两个阶段,并且互相不干扰。加锁阶段只加锁,解锁阶段只解锁。

    7.3 为什么会发生死锁

    ​ MyISAM中是不会产生死锁的,因为MyISAM总是一次性获得所需的全部锁,要么全部满足,要么全部等待。而在InnoDB中,锁是逐步获得的,就造成了死锁的可能。(不过现在一般都是InnoDB引擎,关于MyISAM不做考虑)

    ​ 在InnoDB中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。

    ​ 当两个事务同时执行,一个锁住了主键索引,在等待其他相关索引。另一个锁定了非主键索引,在等待主键索引。这样就会发生死锁。

    通过两个SQL死锁的例子来说明

    1.两个session的两条语句

    在这里插入图片描述

    ​ 这种情况很好理解,首先session1获得 id=1的锁 session2获得id=5的锁,然后session想要获取id=5的锁 等待,session2想要获取id=1的锁 ,也等待!

    2.两个session的一条语句

    在这里插入图片描述

    ​ 这种情况需要我们了解数据的索引的检索顺序原理简单说下:普通索引上面保存了主键索引,当我们使用普通索引检索数据时,如果所需的信息不够,那么会继续遍历主键索引。

    ​ 假设默认情况是RR隔离级别,针对session 1 从name索引出发,检索到的是(hdc,1)(hdc,6)不仅会加name索引上的记录X锁,而且会加聚簇索引上的记录X锁,加锁顺序为先[1,hdc,100],后[6,hdc,10] 这个顺序是因为B+树结构的有序性。而Session 2,从pubtime索引出发,[10,6],[100,1]均满足过滤条件,同样也会加聚簇索引上的记录X锁,加锁顺序为[6,hdc,10],后[1,hdc,100]。发现没有,跟Session 1的加锁顺序正好相反,如果两个Session恰好都持有了第一把锁,请求加第二把锁,死锁就发生了。

    避免死锁,这里只介绍常见的三种

    1. 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。
    2. 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
    3. 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率;

    这篇文章关于mysql锁写的很有深度:http://hedengcheng.com/?p=771

    展开全文
  • 本文实例讲述了MySQL锁机制与用法。分享给大家供大家参考,具体如下: MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁;BDB存储引擎采用的是...
  • Oracle锁机制深度分析

    千次阅读 2019-01-10 20:11:44
    Oracle锁机制深度分析一、概述二、Oracle两种锁机制三、Oracle锁类型1、 DML锁1)、TM锁2)、TX锁2、 DDL锁1)、排它的DDL锁定(Exclusive DDL Lock)2)、共享的DDL锁定(Shared DDL Lock )3)、可打破的解析锁定...

    一、概述

    Oracle数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储 不正确的数据,破坏数据库的一致性。
    加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作 前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务 释放锁之前,其他的事务不能对此数据对象进行更新操作。

    二、Oracle两种锁机制

    在Oracle数据库中锁机制分为两种:独占锁与共享锁,数据库利用这两种基本的锁机制来对数据库的事务进行并发控制。
    独占锁(Exclusive Lock):即X锁,又称排它锁,是用来防止同时共享相同资源的锁。加了独占锁的数据库对象不能被其它事务读取和修改。
    共享锁(Share Lock):即S锁,是通过对数据存取的高并行性来实现的。加了共享锁的数据库对象可以被其它事务读取,但是不能被其它事务修改。

    三、Oracle锁类型

    根据保护的对象不同,Oracle数据库锁可以分为以下几大类:DML锁、DDL锁、内部锁和闩,前两种我们经常遇到。
    DML锁(data locks,数据锁):用于保护数据的完整性,能够防止同步冲突的DML和DDL操作的破坏性交互。
    DDL锁(dictionary locks,字典锁):用于保护数据库对象的结构,如表、索引等的结构定义。
    内部锁和闩(internal locks and latches):保护数据库的内部结构,如数据文件,对用户是不可见的。

    1、 DML锁

    用来保证并行访问数据的完整性。能够防止同步冲突的DML和DDL操作的破坏性交互。在Oracle数据库中,DML锁主要包括TM锁和TX锁,其中TM锁称为表级锁,TX锁称为事务锁或行级锁。

    1)、TM锁

    当Oracle执行DML语句时,系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后,系统再自动申请TX类型的锁,并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志,而只需检查TM锁模式的相容性即可,大大提高了系统的效率。TM锁包括了SS、SX、S、SSX 等多种模式,在数据库中用0-6来表示。不同的SQL操作产生不同类型的TM锁。

    锁模式锁描述解释SQL操作
    0None
    1NULLSelect
    2SS (Row-S)行级共享锁,其他对象只能查询这些数据行;是锁的类型中限制最少的锁,也是在表的并发程度中使用最多的Select for update、Lock for update、Lock row share
    3SX (Row-X)行级排它锁,在提交前不允许做DML操作Insert、Update、 Delete、Lock row share
    4S(Share)共享锁Create index、Lock share
    5SSX (Share-Row-X)共享行级排它锁Lock share row exclusive
    6X(Exclusive)排它锁Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

    2)、TX锁

    TX锁是Transaction Exclusive Lock行级排它锁,对一条记录加上TX锁后,其他用户不能修改、删除该记录
    在数据行上只有X锁(排他锁)。在Oracle数据库中,当一个事务首次发起一个DML语句时就获得一个TX锁,该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时,第一个会话在该条记录上加锁,其他的会话处于等待状态。当第一个会话提交后,TX锁被释放,其他会话才可以加锁。
    当Oracle数据库发生TX锁等待时,如果不及时处理常常会引起Oracle数据库挂起,或导致死锁的发生,产生ORA-60的错误。这些现象都会对实际应用产生极大的危害,如长时间未响应,大量事务失败等。

    2、 DDL锁

    当 DDL命令发出时,Oracle会自动在被处理的对象上添加DDL锁定,从而防止对象被其他用户所修改。当DDL命令结束以后,则释放DDL锁定。DDL锁定不能显式的被请求,只有当对象结构被修改或者被引用时,才会在对象上添加DDL锁定。比如创建或者编译 存储过程时会对引用的对象添加DDL锁定。在创建视图时,也会对引用的表添加DDL锁定等。
    在执行DDL命令之前,Oracle会自动添加一个隐式提交命令,然后执行具体的DDL命令,在DDL命令执行结束之后,还会自动添加一个隐式提交命令。实际上,Oracle在执行DDL命令时,都会将其转换为对数据字典表的DML操作。比如我们发出创建表的DDL命令时,Oracle会将表的名称插入数据字典表tab 里 , 同 时 将 表 里 的 列 名 以 及 列 的 类 型 插 入 c o l 里,同 时将表里的列名以及列的类型插入col col表里等。因此,在DDL命令中需要添加隐式的提交命令,从而提交那些对数据字典表的DML操作。即使DDL命令失 败,它也会发出提交命令。DDL锁包括三种类型:排它的DDL锁定、共享的DDL锁定、可打破的解析锁定。

    1)、排它的DDL锁定(Exclusive DDL Lock)

    大部分的DDL操作都会在被操作的对象上添加排他的DDL锁定,从而防止在DDL命令执行期间,对象被其他用户所修改。当对象上添加了排他的DDL锁定以后,该对象上不能再添加任何其他的DDL锁定。如果是对表进行DDL命令,则其他进程也不能修改表里的数据。

    2)、共享的DDL锁定(Shared DDL Lock )

    用来保护被DDL的对象不被其他用户进程所更新,但是允许其他进程在对象上添加共享的DDL锁定。如果是对表进行DDL命令,则其他进程可以同时修改表里的数据。比如我们发出create view命令创建视图时,在视图的所引用的表(这种表也叫基表)上添加的就是共享的DDL命令。也就是说,在创建视图时,其他用户不能修改基表的结构,但是可以更新基表里的数据。

    3)、可打破的解析锁定(Breakable Parsed Lock)

    在shared pool里缓存的SQL游标或者PL/SQL程序代码都会获得引用对象上的解析锁定。如果我们发出DDL命令修改了某个对象的结构时,该对象相关的、位于 shared pool里的解析锁定就被打破,从而导致引用了该对象的SQL游标或者PL/SQL程序代码全都失效。下次再次执行相同的SQL语句时,需要重新解析,这 也就是所谓的SQL语句的reload了。可打破的解析锁定不会阻止其他的DDL锁定,如果发生与解析锁定相冲突的DDL锁定,则解析锁定也会被打破。

    3、 内部锁和闩

    内部锁保护内部数据库结构,如数据文件,对用户是不可见的。

    四、悲观锁和乐观锁

    当一个会话保持另一个会话正在请求的资源上的锁定时,就会发生阻塞。被阻塞的会话将一直挂起,直到持有锁的会话放弃锁定的资源为止。锁机制的适当应用保证了数据的完成性,应用不当会导致死锁,从而我们又将锁分为悲观锁乐观锁

    1、悲观封锁

    锁在用户修改之前就发挥作用,如:Select …for update(nowait) ,Select * from tab1 for update 用户发出这条命令之后,oracle将会对返回集中的数据建立行级封锁,以防止其他用户的修改。 如果此时其他用户对上面返回结果集的数据进行dml或ddl操作都会返回一个错误信息或发生阻塞。
    1)对返回结果集进行update或delete操作会发生阻塞。
    2)对该表进行ddl操作将会报:Ora-00054:resource busy and acquire with nowait specified.
    此时Oracle已经对返回的结果集上加了排它的行级锁,所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放,产生的外在现象就是其他的操作将发生阻塞,这个操作commit或rollback.
    同样这个查询的事务将会对该表加表级锁,不允许对该表的任何ddl操作,否则将会报出ora-00054错误::resource busy and acquire with nowait specified.

    2、乐观封锁

    乐观的认为数据在select出来到update进去并提交的这段时间数据不会被更改。这里面有一种潜在的危险就是由于被选出的结果集并没有被锁定,是存在一种可能被其他用户更改的可能。因此Oracle仍然建议是用悲观封锁,因为这样会更安全。

    五、死锁

    死锁-deadlock ,当两个用户希望持有对方的资源时就会发生死锁. 即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚。
    4个常见的dml语句会产生阻塞 INSERT ,UPDATE,DELETE,SELECT…FOR UPDATE 。
    INSERT:Insert发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。当2个的会话同时试图向表中插入相同的数据时,其中的一个会话将被阻塞,直到另外一个会话提交或会滚。一个会话提交时,另一个会话将收到主键重复的错误。回滚时,被阻塞的会话将继续执行。
    UPDATE 和DELETE:当执行Update和delete操作的数据行已经被另外的会话锁定时,将会发生阻塞,直到另一个会话提交或会滚。
    Select …for update:当一个用户发出select…for update的错作准备对返回的结果集进行修改时,如果结果集已经被另一个会话锁定,就是发生阻塞。需要等另一个会话结束之后才可继续执行。可以通过发出 select… for update nowait的语句来避免发生阻塞,如果资源已经被另一个会话锁定,则会返回以下错误:Ora-00054:resource busy and acquire with nowait specified.

    1、死锁案例分析

    1:用户1对A表进行Update,没有提交。
    2:用户2对B表进行Update,没有提交。
    此时双反不存在资源共享的问题。
    3:如果用户2此时对A表作update,则会发生阻塞,需要等到用户一的事物结束。
    4:如果此时用户1又对B表作update,则产生死锁。此时Oracle会选择其中一个用户进行会滚,使另一个用户继续执行操作。

    2、解决死锁方法

    Oracle的死锁问题实际上很少见,如果发生,基本上都是不正确的程序设计造成的,经过调整后,基本上都会避免死锁的发生。 当出现session锁时,我们要及时进行处理,处理方法如下:
    步骤一、查看被锁会话
    方法一:针对所有类型锁
    V L O C K 视 图 包 含 所 有 被 锁 对 象 , 被 锁 对 象 可 以 是 表 、 存 储 过 程 、 视 图 等 , 在 不 确 定 锁 类 型 D D L 与 D M L 类 型 时 , 可 以 通 过 查 看 V LOCK视图包含所有被锁对象,被锁对象可以是表、存储过程、视图等,在不确定锁类型DDL与DML类型时,可以通过查看V LOCKDDLDMLVLOCK视图查看,语句如下:
    代码片

    SELECT L.SID         "会话ID",
           S.SERIAL#     "会话序列号",
           P.SPID        "会话进程号",
           L.TYPE        "锁类型",
           S.USERNAME    "所属用户",
           S.MACHINE     "客户端",
           O.OBJECT_NAME "被锁对象",
           O.OBJECT_TYPE "被锁对象类型",
           L.CTIME       "被锁时间(S)"
      FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O, V$PROCESS P
     WHERE L.SID = S.SID
       AND L.ID1 = O.OBJECT_ID
       AND S.PADDR = P.ADDR
       AND S.SCHEMA# <> 0
       AND S.USERNAME = 'GANGMA2';
    

    方法二: DML类型锁
    V$LOCKED_OBJECT视图只包DML锁信息,查询语句如下:
    代码片

    SELECT L.SESSION_ID "会话ID",
           S.SERIAL# "会话序列号",
           P.SPID "会话进程号",
           S.USERNAME "所属用户",
           S.MACHINE "客户端",
           O.OBJECT_NAME "被锁对象",
           O.OBJECT_TYPE "被锁对象类型",
           CEIL((SYSDATE - S.LOGON_TIME) * 24 * 60 * 60) "被锁时间(S)"
      FROM V$LOCKED_OBJECT L, V$SESSION S, DBA_OBJECTS O, V$PROCESS P
     WHERE L.SESSION_ID = S.SID
       AND L.OBJECT_ID = O.OBJECT_ID
       AND S.PADDR = P.ADDR
       AND S.SCHEMA# <> 0
    AND S.USERNAME = 'GANGMA2';
    

    方法三: DDL类型锁
    DBA_DDL_LOCKS只包DML锁信息,查询语句如下:
    代码片

    SELECT L.SESSION_ID "会话ID",
           S.SERIAL# "会话序列号",
           P.SPID "会话进程号",
           S.USERNAME "所属用户",
           S.MACHINE "客户端",
           S.PROGRAM "客户端程序",
           O.OBJECT_NAME "被锁对象",
           O.OBJECT_TYPE "被锁对象类型",
           CEIL((SYSDATE - S.LOGON_TIME) * 24 * 60 * 60) "被锁时间(S)"
      FROM DBA_DDL_LOCKS L, V$SESSION S, DBA_OBJECTS O, V$PROCESS P
     WHERE L.SESSION_ID = S.SID
       AND S.ROW_WAIT_OBJ# = O.OBJECT_ID
       AND S.PADDR = P.ADDR
       AND S.SCHEMA# <> 0
       AND S.USERNAME = 'GANGMA2';
    

    步骤二、kill锁的进程
    方法一、数据库层面
    根据上面查询到的会话ID与会话序列号进行杀进程:
    代码片

    alter system kill session 'SID,SERIAL#';
    

    方法二、操作系统层面
    根据上面结果查询的会话进程号,在操作系统上查杀进程:
    windows平台:orakill
    代码片

    orakill gangma2 3876
    

    linux平台:kill -9 SPID
    代码片

     kill -9 9876
    

    六、附录

    1、V$LOCK视图结构

    列名类型字段说明
    ADDRRAW(4 / 8)Address of lock state object
    KADDRRAW(4/8)Address of lock
    SIDNUMBER会话的sid,可以和v$session 关联
    TYPEVARCHAR2(2)区分该锁保护对象的类型(表v$lock锁类型说明)TM – DML enqueue TX – Transaction enqueue UL – User supplied–我们主要关注TX和TM两种类型的锁–UL锁用户自己定义的,一般很少会定义,基本不用关注–其它均为系统锁,会很快自动释放,不用关注
    ID1 ID2NUMBERID1,ID2的取值含义根据type的取值而有所不同。 对于TM 锁:ID1表示被锁定表的object_id 可以和dba_objects视图关联取得具体表信息,ID2 值为0;对于TX 锁:ID1以十进制数值表示该事务所占用的回滚段号和事务槽slot number号,其组形式:0xRRRRSSSS,RRRR=RBS/UNDO NUMBER,SSSS=SLOT NUMBERID2 以十进制数值表示环绕wrap的次数,即事务槽被重用的次数
    LMODENUMBER0 – none 1 – null (NULL) 2 – row-S (SS) 3 – row-X (SX) •4 – share (S) 5 – S/Row-X (SSX) 6 – exclusive (X) 具体见表TM锁模式
    REQUESTNUMBER同LMODE–大于0时,表示当前会话被阻塞,其它会话占有改锁的模式
    CTIMENUMBER已持有或者等待锁的时间
    BLOCKNUMBER是否阻塞其他会话锁申请 1:阻塞 0:不阻塞

    2、和v$lock的其它相关视图说明

    视图名描述主要字段说明
    v$session查询会话的信息和锁的信息。sid,serial#:表示会话信息。program:表示会话的应用程序信息。row_wait_obj#:表示等待的对象,和dba_objects中的object_id相对应。lockwait :该会话等待的锁的地址,与v$lock的kaddr对应.
    v$session_wait查询等待的会话信息。sid:表示持有锁的会话信息。Seconds_in_wait:表示等待持续的时间信息 Event:表示会话等待的事件,锁等于enqueue
    dba_locks对v$lock的格式化视图。Session_id:和v$ lock中的Sid对应。Lock_type:和v$ lock中的type对应。Lock_ID1: 和v$ lock中的ID1对应。Mode_held,mode_requested:和v$lock中的lmode,request相对应。
    v$locked_object只包含DML的锁信息,包括回滚段和会话信息。Xidusn,xidslot,xidsqn表示回滚段信息。和v$ transaction相关联。Object_id:表示被锁对象标识。Session_id:表示持有锁的会话信息。Locked_mode:表示会话等待的锁模式的信息,和v$lock中的lmode一致。

    3、TM锁模式

    锁模式锁描述解释SQL操作
    0None
    1NULLSelect
    2SS (Row-S)行级共享锁,其他对象只能查询这些数据行;是锁的类型中限制最少的锁,也是在表的并发程度中使用最多的Select for update、Lock for update、Lock row share
    3SX (Row-X)行级排它锁,在提交前不允许做DML操作Insert、Update、 Delete、Lock row share
    4S(Share)共享锁Create index、Lock share
    5SSX (Share-Row-X)共享行级排它锁Lock share row exclusive
    6X(Exclusive)排它锁Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

    4、v$lock锁类型说明

    System TypeDescriptionSystem TypeDescription
    BLBuffer hash table instanceNA…NZLibrary cache pin instance (A…Z?= namespace)
    CFControl file schema global enqueuePFPassword File
    CICross-instance function invocation instancePI, PSParallel operation
    CUCursor bindPRProcess startup
    DFdatafile instanceQA…QZRow cache instance (A…Z?= cache)
    DLDirect loader parallel index createRTRedo thread global enqueue
    DMMount/startup db primary/secondary instanceSCSystem change number instance
    DRDistributed recovery processSMSMON
    DXDistributed transaction entrySNSequence number instance
    FSFile setSQSequence number enqueue
    HWSpace management operations on a specific segmentSSSort segment
    INInstance numberSTSpace transaction enqueue
    IRInstance recovery serialization global enqueueSVSequence number value
    ISInstance stateTAGeneric enqueue
    IVLibrary cache invalidation instanceTSTemporary segment enqueue (ID2=0)
    JQJob queueTSNew block allocation enqueue (ID2=1)
    KKThread kickTTTemporary table enqueue
    LA … LPLibrary cache lock instance lock(A…P = namespace)UNUser name
    MMMount definition global enqueueUSUndo segment DDL
    MRMedia recoveryWLBeing-written redo log instance
    展开全文
  • SQL的锁机制

    2015-07-09 11:26:45
    SQL的锁机制,详细介绍sql Server的锁机制原理。
  • PostgreSQL锁机制

    千次阅读 2018-12-18 18:14:19
    下面对PostgreSQL数据库锁机制的理解,大部分来自与《PostgreSQL修炼之道 从小工到专家》-唐成书中,以及网络上的博客的总结。通过实际测试发现,还是存在一些不合理的点,后面实际的案列中,会有一些说明。 1.表级...

    PostgreSQL中有两类锁:表级锁和行级锁。当要查询、插入、更新、删除表中数据时,首先要获得表级锁,然后获得行级锁。

    下面对PostgreSQL数据库锁机制的理解,大部分来自与《PostgreSQL修炼之道 从小工到专家》-唐成书中,以及网络上的博客的总结。通过实际测试发现,还是存在一些不合理的点,后面实际的案列中,会有一些说明。

    1.表级锁模式

    模式

    解释

    Access Share

    只与Access Exclusive锁模式冲突。

    查询命令(Select command)将会在它查询的表上获取Access Shared锁,一般地,任何一个对表上的只读查询操作都将获取这种类型锁。

    Row Share

    与Exclusive和Access Exclusive锁模式冲突。

    Select for update和Select for share命令将获得这种类型锁,并且所有被引用但没有for update 的表上会加上Access Shared锁。

    Row Exclusive

    与Share,Shared Row Exclusive,Exclusive,Access Exclusive模式冲突。

    Update/Delete/Insert命令会在目标表上获得这种类型的锁,并且在其它被引用的表上加上Access Share锁,一般地,更改表数据的命令都将在这张表上获得Row Exclusive锁。

    Share Update Exclusive

    Share Update Exclusive,Share,Share Row Exclusive,Exclusive,Access exclusive模式冲突,这种模式保护一张表不被并发的模式更改和Vacuum。

    Vacuum(without full),Analyze 和 Create index concur-ently命令会获得这种类型锁。

    Share

    与Row Exclusive,Shared Update Exclusive,Share Row Exclusive,Exclusive,Access exclusive锁模式冲突,这种模式保护一张表数据不被并发的更改。

    Create index命令会获得这种锁模式。

    Share Row Exclusive

    与Row Exclusive,Share Update Exclusive,Shared,Shared Row Exclusive,Exclusive,Access Exclusive锁模式冲突。

    任何PostgreSQL命令不会自动获得这种类型的锁。

    Exclusive

    与ROW Share , Row Exclusive, Share  Update  Exclusive, Share , Share  Row Exclusive, Exclusive, Access Exclusive模式冲突,这种锁模式仅能与Access Share 模式并发,换句话说,只有读操作可以和持有Exclusive锁的事务并行。

    任何PostgreSQL命令不会自动获得这种类型的锁。

    Access Exclusive

    与所有模式锁冲突(Access Share,Row Share,Row Exclusive,Share  Update Exclusive,Share , Share Row Exclusive,Exclusive,Access Exclusive),这种模式保证了当前只有一个人访问这张表;ALTER TABLE,DROP TABLE,TRUNCATE,REINDEX,CLUSTER,VACUUM FULL 命令会获得这种类型锁,在Lock table 命令中,如果没有申明其它模式,它也是默认模式。

    2.表级锁的冲突矩阵

    请求的锁模式

    已申请到的锁模式

    Access Share

    Row Share

    Row Exclusive

    Share Update Exclusive

    Share

    Share Row Exclusive

    Exclusive

    Access Exclusive

    Access Share

    Y

    Y

    Y

    Y

    Y

    Y

    Y

    N

    Row Share

    Y

    Y

    Y

    Y

    Y

    Y

    N

    N

    Row Exclusive

    Y

    Y

    Y

    Y

    N

    N

    N

    N

    Share Update Exclusive

    Y

    Y

    Y

    N

    N

    N

    N

    N

    Share

    Y

    Y

    N

    N

    N

    N

    N

    N

    Share Row Exclusive

    Y

    Y

    N

    N

    N

    N

    N

    N

    Exclusive

    Y

    N

    N

    N

    N

    N

    N

    N

    Access Exclusive

    N

    N

    N

    N

    N

    N

    N

    N

    表中“N”表示这两种表冲突,也就是不同的进程不能同时持有这两种锁。

    最普通的是Share和Exclusive这两种锁,它们分别是读、写锁的意思。加了Share锁,即读锁,表的内容就不被修改了;可以为多个事务加上此锁,只要任意一个事务不释放这个读锁,则其他事务就不能修改这个表。加上了Exclusive,相当于加了写锁,这时别的进程不能写也不能读这条数据。但后来数据库又加上了多版本的功能。修改一条语句的同时,允许了读数据,为了处理这种情况,又增加了两种锁Access Share和Access Excusive,锁中的关键字 Access 是与多版本相关的有了该功能。其实,有了该功能后,如果修改一行数据,实际并没有改原先那行数据,而是复制了一个新行,修改都在新行上,事务不提交,其他人是看不到修改的这条数据的。由于旧行数据没有变化,在修改过程中,读数据的人仍然可以读到旧的数据。

    表级锁加锁对象是表,这使得加锁范围太大,导致并发并不高,于是人们提出了行级锁的概念,但行级锁与表级锁之间会产生冲突,这时需要一种机制来描述行级锁与表级锁之间的关系,有了意向锁的概念,这时又加了两种锁,即意向共享锁(Row Share) 和意向排他锁(Row Exclusive),由于意向锁之间不会产生冲突,因为他们是“有意”做,还没真做;而且意向排它锁相互之间也不会产生冲突,于是又需要更严格一些的锁,这样就产生了Share Update Exclusive,Share Row Exclusive可以看成Share与Row Exclusive,PostgreSQL不会自动请求这个锁模式,也就是PostgreSQL内部目前没有使用这种锁。

    这里稍微补充一下多版本并发控制原理。

    多版本并发控制原理:

    大家熟知的读与写锁是不能并发的,所以有人想到一种新的能够让读写并发的方法,称这种方法为MVCC。MVCC的方法是写数据时,旧版本的数据并不删除,并发的读操作还能读到旧版本的数据。

    实现MVCC的两种方法:

    1. 写新数据时,把旧数据移到一个单独的地方,如回滚段中,其他读数据时,从回滚段中把旧版本数据读出来。
    2. 写新数据时,旧版本的数据不删除,而是把新数据插入。

    PostgreSQL数据库使用的正是第二种方法,而oracle与MySQL中的innodb引擎用的是第一种方法。

    PostgreSQL实现该功能,需要在每张表上添加四个系统字段tmin、tmax、cmin、cmax,通过这四个字段可以区分并发时,记录不同数据的版本,和事务标识,当删除时,只会标记记录,而不会从数据块中删除,空间也没有立即释放。

    PostgreSQL通过运行vaccum进程来进行回收之前的存储空间,默认PostgreSQL中的autovacuum是打开的,当一表更新达到一定数量时,autovacuum会自动回收空间。

    3.表级锁类型对应的数据库操作

    锁类型

    对应的数据库操作

    Access Share

    select

    Row Share

    select for update, select for share

    Row Exclusive

    update,delete,insert

    Share Update Exclusive

    vacuum(without full),analyze,create index concurrently

    Share

    create index

    Share Row Exclusive

    任何Postgresql命令不会自动获得这种类型的锁

    Exclusive

    任何Postgresql命令不会自动获得这种类型的锁

    Access Exclusive

    alter table,drop table,truncate,reindex,cluster,vacuum full

    4.行级锁模式

    行级锁模式只有两种,分别是共享锁和排他锁,或者说是读锁或写锁。由于多版本的实现,实际读取行数据时,并不会在行上执行任何锁。

    特定行上的行级锁是在行被更新的时候自动请求的(或者被删除时或标记为更新)。 锁一直保持到事务提交或者回滚。行级锁不影响对数据的查询;它们只阻塞对同一行的写入。 要在不修改某行的前提下请求在该行的行级锁,用 SELECT FOR UPDATE 选取该行。请注意一旦我们请求了特定的行级锁,那么该事务就可以多次对该行进行更新而不用担心冲突。

    PostgreSQL修炼之道 从小工到专家》-唐成书中强调的是,行级锁只有共享锁与排他锁,也就是读锁、写锁,但是实际测试中有发现一些tuple类型的多版本控制的写锁。

    5.页级锁

    除了表级别的和行级别的锁以外, 页面级别的共享/排他销也用于控制对共享缓冲池中表页面的读/写访问。这些锁在抓取或者更新一行后马上被释放。应用程序员通常不需要关心页级锁,在这里提到它们只是为了完整。

    6.锁命令

    1.表级锁命令

    在PsotgreSQL中,显示的在表上加锁命令为LOCK TABLE命令:

    LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

    说明如下:

    1. name:要锁定的现有表的锁名称(可选模式限定)。 如果在表名之前指定ONLY,则仅该表被锁定,如果未指定ONLY,则表及其所有后代表(如果有)被锁定。
    2. lock_mode:锁模式指定此锁与之冲突的锁。 如果未指定锁定模式,则使用最严格的访问模式ACCESS EXCLUSIVE。
    3. NOWAIT:如果没有NOWAIT关键字时,当无法获得锁时,会一直等待,而如果加了NOWAIT关键字,在无法立即获取该锁时,此命令会立即退出并发出一个错误信息

    2.行级锁命令

    在PsotgreSQL中,显示的行级锁命令是由select命令发出的:

    SELECT …… FOR  { UPDATE | SHARE } [OF table_name[,……]] [ NOWAIT]

    说明如下:

    1. 指定 OF table_name,则只有被指定的表会被锁定。
    2. 例外情况,主查询中引用了WITH查询时,WITH查询中的表不被锁定。
    3. 如果需要锁定WITH查询中的表,需在WITH查询内指定FOR UPDATA或FOR SHARE。
    4. NOWAIT关键字与显示加表级锁命令作用一样。

    7.死锁

    死锁是指两个或者两个以上的事务在执行过程中相互持有对方期待的资源,若没有其他机制,它们将无法进行下去。

    1.形成死锁的4个必要条件

    1. 请求与保持条件:获取资源的进程可以同时申请新的资源。
    2. 非剥夺条件:已经分配的资源不能从该进程中剥夺。
    3. 循环等待条件:多个进程构成环路,并且每个进程都在等待相邻进程正占用的资源。
    4. 互斥条件:资源只能被一个进程使用。

     2.减少死锁的方法

    1. 在所有事务中都以相同的次序使用资源。
    2. 使事务尽可能简单并在一个批处理中。
    3. 为死锁超时参数设置一个合理范围,如10s;超时则自动放弃本操作,避免进程挂起。可以在postgresql.conf文件中设置:log_lock_waits = on         deadlock_timeout = 10s
    4. 避免在事务内核用户进行交互,减少资源的锁定时间。
    5. 使用较低的隔离级别,相比较高的隔离级别能够有效减少持有共享锁的时间,减少锁之间的竞争。
    展开全文
  • MySQL数据库:锁机制

    千次阅读 2018-11-26 01:19:00
    MySQL锁机制的基本工作原理就是,事务在修改数据库之前,需要先获得相应的锁,获得锁的事务才可以修改数据;在该事务操作期间,这部分的数据是锁定,其他事务如果需要修改数据,需要等待当前事务提交或回滚后释放锁...
  •  的结尾我们提到了为了解决幻读的问题,必须先行介绍MySQL的锁机制,那么在这一篇博文中,我们就来聊一聊MySQL的锁机制吧。本文整理总结于 极客时间 - 《MySQL实战45讲》 ,欢迎大家订阅学习,干货满满。   ...
  • MySQL事务处理与锁机制详解

    千次阅读 2019-05-22 03:39:27
    事务的基本概念、ACID特性、事务的隔离级别、提交与回滚、MyISAM表锁、InnoDB行锁、间隙(Next-Key
  • Mysql锁机制简单了解一下

    万次阅读 多人点赞 2018-06-07 20:52:11
    Mysql为了解决并发、数据安全的问题,使用了锁机制。 可以按照锁的粒度把数据库锁分为表级锁和行级锁。 表级锁: Mysql中锁定 粒度最大 的一种锁,对当前操作的整张表加锁,实现简单 ,资源消耗也比较少,加锁快...
  • MySQL数据库锁机制

    千次阅读 2018-11-27 11:22:22
    是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须...
  • 主要介绍了Java锁机制Lock用法,结合具体实例形式分析了Java锁机制的相关上锁、释放锁、隐式锁、显式锁等概念与使用技巧,需要的朋友可以参考下
  • 悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。 一个典型的依赖数据库的悲观锁...
  • MySQL锁机制

    千次阅读 多人点赞 2019-06-26 17:53:29
    文章目录一、的分类(一)、按粒度划分的1、表级(偏向于读)2、行级3、页级(二)、按锁的级别划分1、共享(读)2、排他(写)三、MyISAM存储引擎的1、支持表锁(偏向于读)2、并发2、调度...
  • 要说锁,应该追溯到操作系统中的多线程原理,锁机制在其中发挥着必不可少的作用,先抛出锁的定义 锁是计算机协调多个进程或线程并发访问某一资源的机制 在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用...
  • 数据库锁机制

    万次阅读 多人点赞 2016-08-15 12:38:50
    看到网上大多语焉不详(尤其更新),所以这里做个简明解释,为下面描述方便,这里用T1代表一个数据库执行请求,T2代表另一个请求,也可以理解为T1为一个线程,T2 为另一个线程。T3,T4以此类推。下面以SQL Server...
  • MySQL中的锁机制详细说明

    千次阅读 多人点赞 2020-06-30 17:45:40
    一、MySQL锁机制起步 锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能,实现了锁机制,MySQL中的锁是在服务器层或者存储引擎层实现的。 二、行锁与...
  • MySQL锁机制以及锁的粒度

    千次阅读 2019-09-04 19:04:25
    文章目录MySQL锁机制概述什么是锁,为什么使用锁锁的运作锁定机制分类并发控制锁粒度两种重要的锁策略表锁(table lock)行级锁(row lock) MySQL锁机制概述 什么是锁,为什么使用锁 锁是计算机协调多个进程或纯...
  • Innodb 锁机制

    千次阅读 2018-03-31 22:46:04
    不同数据库和不同搜索引擎都可能有不同的锁机制,MyISAM 引擎的锁是表锁设计,并发读没有问题,但是并发写入可能就存在一定的问题。  Microsoft SQL Server 在2005版本之前是页锁设计,相对于 MyISAM 并...
  • MySQL的锁机制

    2016-06-04 12:36:20
    MySQL的锁、锁机制的解释。想看就看,勿过于勉励

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 452,789
精华内容 181,115
关键字:

锁机制