企业手机网站建设,网站运营工资,j永久网站,精美网站设计本文是对沈剑大佬锁机制十多篇文章的概括总结#xff0c;文末有全部链接#xff0c;还参考了 10 多位其他网友的优秀分享。 1、概要 MySQL 中的锁可以按照粒度分为锁定整个表的表级锁(table-level locking)和锁定数据行的行级锁(row-level locking)#xff1a; 表级锁具有开… 本文是对沈剑大佬锁机制十多篇文章的概括总结文末有全部链接还参考了 10 多位其他网友的优秀分享。 1、概要 MySQL 中的锁可以按照粒度分为锁定整个表的表级锁(table-level locking)和锁定数据行的行级锁(row-level locking) 表级锁具有开销小、加锁快的特性但是表级锁的锁定粒度较大发生锁冲突的概率高支持的并发度低行级锁具有开销大加锁慢的特性但是行级锁的锁定粒度较小发生锁冲突的概率低支持的并发度高。 InnoDB 存储引擎同时支持行级锁(row-level locking)和表级锁(table-level locking)默认情况下采用行级锁。 InnoDB的行锁是实现在索引上的而不是锁在物理行记录上。所以如果访问没有命中索引就无法使用行锁将要退化为表锁(共享行锁上升为共享表锁排他行锁上升为排他表锁)。(注意Oracle的行锁实现机制不同。) 2、全局锁 全局锁就是对整个数据库实例加锁获得全局锁后的数据库就无法进行数据的更新操作与表结构修改操作。 当你需要让数据库变为只读状态时可以使用 Flush tables with read lock语句进行加锁此时数据的更新操作会被阻塞。解锁命令UNLOCK TABLES; 3、行级锁 InnoDB 实现了以下两种类型的概念行锁 3.1 排它锁 Shared、共享锁 Exclusive 排他 X 锁和共享 S 锁是 Innodb 的行级概念锁。保证同一行记录修改与删除的串行性从而保证数据的强一致。 共享锁是读锁多个事务可以拿到同一行记录的共享锁所以读读可以并发。排他锁是写锁同一行记录的排他锁在同一时刻只能有一个事务获得所以写写是互斥的。实际上读写也是互斥的也就是有排他锁就不能加共享锁有共享锁就不能加排他锁。 3.1.1 行共享锁的触发场景 加了lock in share mode 的 select 语句 比如select … lock in share mode 普通 select 实施不加锁多版本快照读 3.1.2 行排他锁的触发场景 加了 for update 的 select 语句比如 select … for updateupdate, delete, insert 都是行级排它锁 3.2 行级锁有三种实现算法记录锁、间隙锁、临键锁。 在数据库的优化器优化过程中会根据where字段的用到的索引类别自动的加相应种类的行锁。 根据触发条件的不同每种锁都有排他锁和共享锁两种类型实现。比如触发条件是加了lock in share mode 的 select 语句那么可能获取到的就是共享类型的记录锁/间隙锁/临键锁。 3.3 记录锁(Record Locks)(加锁对象是索引节点) select * from t where id1 for update; 手动或者自动加了for update 才会加记录锁否则不加锁实施快照读。 它会在id1的索引记录上加锁以阻止其他事务插入更新删除id1的这一行。 只适用于在唯一索引上使用了唯一查询条件。如果唯一索引不是聚集索引还会额外锁定聚集索引的索引记录。 3.4 间隙锁(Gap Locks)(实施在索引上) 间隙锁它封锁索引记录之间的间隔或者第一条索引记录之前的范围又或者最后一条索引记录之后的范围。是一个左开右闭的区间即(x,y]的形式。 可以防止间隙内有新数据被插入以及防止已存在的记录更新成间隙内的记录。 适用于在唯一索引上使用了范围查询条件。 3.5 临键锁(实施在索引上) 临键锁是记录锁与间隙锁的组合既封锁索引记录本身又封锁索引记录之前的区间。 临键锁针对的是普通索引(注意不是唯一索引) 3.6 插入意向锁(实施在索引上) 间隙锁的一种。 作用多个事务在同一个索引同一个范围区间插入记录时如果插入的位置不冲突不会阻塞彼此。用于提高插入并发因为如果使用间隙锁的话不允许多个事务同时往同一个索引间隙插入记录但是使用插入意向锁可以。如果插入的位置冲突呢怎么办另一个回滚吗 比如两个事务都想往id(10,20)插入一条记录但是两个事务插入记录的id分别是 11 和 12插入的位置不冲突所以不会阻塞对方。 4、表级锁 表级锁有两种表数据锁(常说的表锁)元数据表锁(MDLmetadata lockMysql5.5版本之后加的)。 4.1 元数据锁 MDL锁在语句开始执行时申请在事务提交后释放。只要有事务在执行mysql 就会自动加上元数据表锁(MDL)这样在执行过程中就不能发生表结构变更。 对表进行增删改查加 MDL 读锁执行表结构变更(DDL命令)加 MDL 写锁。读读不互斥、读写和写写都互斥。 4.2 普通表锁如何触发 如果想手动加表锁可以使用 lock 和 unlock 关键词来加锁和释放锁 lock tables 表名 read/write。比如lock tables A read,B write。未解锁前不能其它表进行CRUD操作。加上读锁后任何线程都只能执行都操作写操作都会被阻塞包括加锁的线程也是这样。 4.3 意向锁(Intention Locks) 如果要获取表锁按一般法的方法首先需要看该表是否已经被其他事务加上了表级锁然后遍历该表中的每一行是否已经被其他事务加上了行级锁如果存在行级锁需要等待行级锁释放检测行级锁的算法效率很低。为此InnoDB 引入了另外一种锁意向锁(Intention Lock)用于加表锁前快速判定表中是否存在行级锁。 意向锁属于表级锁由 InnoDB 自动添加不需要用户干预。 是一种表级锁弱锁仅仅用于表明意向。分成意向共享锁 (intention shared lock, IS) 和意向排他锁 (intention exclusive lock, IX)分别表示事务有意向对表里的某些记录加共享行锁和排他行锁。 加共享行所和排他行锁之前会自动获取到对应的意向共享锁或者意向排他锁。由于对每个数据行加锁是互不干扰的所以意向排他锁跟意向共享锁相互兼容可以共存。 如果有意向共享锁可以同时加共享行锁和排他行锁但是如果有意向排他锁只能加排他行锁这里感觉有点问题如果已经有意向共享锁和共享行锁那理论上来说可以加排他行锁但是获取排他行锁需要先获取意向排他锁因为有共享行级锁所以无法加排他行锁难道一张表不能同时读写吗这样效率岂不是太低了 4.4 自增锁(一类特殊的表锁) 4.4.1 唯一索引约束被违反了会发生什么 当 sql 违反主键和唯一索引约束时如果存储引擎支持事务比如innodbSQL会自动回滚。如果不支持事务SQL的执行会中断一个update语句部分执行成功部分执行失败可能造成不符合预期的结果集 对于 insert 插入语句,可以使用 insert … on duplicate key update xxx 来指定违反约束时的动作也不会中断。猜测很多 upsert 就是这么做的 插入成功影响行数为1插入失败但是修改成功影响行数为2插入失败且修改的值等于当前值影响行数为0。 4.4.2 三种插入形式简单插入、批量插入、混合插入 4.4.2.1 简单插入(simple insert) 能够提前知道被插入的行数时的插入。普通的 insert/replace 语句不管是单条插入还是多条插入都是简单插入。这类插入很容易保证自增的连续性。(1不包含递归的子查询2不包含insert … on duplicate key update… ) 4.4.2.2 批量插入(bulk insert) 不知道被插入的行数时的插入。如insert into t1(name) select name from t2;、 由于不能够提前知道多少行插入在处理自增列时每插入一行才会赋值新的自增值。在批量插入事务并发时“可能”出现同一个事务的自增键不连续。 4.4.2.3 混合插入(mixed-mode insert) 有些行插入时指定了自增列的值无需数据库生成有些行插入时未指定自增列的值(NULL)或者指定了自增列的值为零值或者空此时需要数据库生成。具体走哪个分支实际执行时才知道。比如insert … on duplicate key update… 有些行插入实际上是修改也无需数据库生成自增键这类也是混合插入无法预知到是插入还是修改。比如 insert … on duplicate key update…这种情况其实会生成自增键但是插入操作变成修改操作那么系统生成的自增键就用不上但是却是实打实的占用了一个系统自增键。 4.4.3 自增锁实现 自增锁是一种特殊的表级别锁(table-level lock)专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况如果一个事务正在往表中插入记录所有其他事务的插入必须等待以便第一个事务插入的行是连续的主键值。控制同一sql 语句插入的所有记录的自增id是连续的。 innodb通过 innodb_autoinc_lock_mode可以查看自增锁的状态。取值为0/1/2默认为 1, 4.4.3.1 innodb_autoinc_lock_mode 0表粒度加锁实施互斥 所有的 insert 语句(insert like) 都要在语句开始的时候得到一个表级的 auto_inc 锁在语句结束的时候才释放这把锁持续时间长影响了并发插入效率。(sql 语句级别而非事务级别) 保证了简单插入和批量插入时同一条语句插入的行记录的自增ID是连续的。可以保证主和从复制的同一张表的同一个行记录的自增ID是一样的。 4.4.3.2 innodb_autoinc_lock_mode 1表粒度加锁实施互斥 同样所有的 insert 语句(insert like) 都要在语句开始的时候得到一个表级的 auto_inc 锁但是对简单插入做了优化由于简单插入插入的记录数能提前预知到在获取到所需的自增键数量后autoinc_lock 就会被释放,不必等到语句执行结束。但对于bulk insert自增锁会被一直持有直到语句执行结束才会被释放。性能有所提高。 仍就可以保证简单插入和批量插入时同一条语句插入的行记录的自增ID是连续的。也可以保证主和从复制的同一张表的同一个行记录的自增ID是一样的。 4.4.3.3 innodb_autoinc_lock_mode 2不加锁 不加自增锁效率最高但是 simple insert 语句因为自增键是一次性分配的所以仍能保证ID是连续的但是 bulk insert的ID则可能有空洞。主从复制的同一张表下的同一行id有可能不一样。 4.5 从不同角度的锁分类 处理锁的态度 悲观锁、乐观锁 锁的粒度 行锁、表锁、全局锁 锁是否互斥的特性 共享锁、排他锁 算法锁 临键锁、间隙锁、记录锁 状态锁 意向共享锁、意向排他锁 5、数据库 4 类隔离级别下各种 sql 分别会使用什么锁 5.1 未提交读 select 不加锁可能出现读脏 5.2 提交读 普通select快照读锁 select /update /delete 除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间外都只会使用记录锁可能出现不可重复读和幻读 5.3 可重复读 普通select快照读锁select /update /delete 根据查询条件情况会选择记录锁或者间隙锁/临键锁以防止读取到幻影记录 (1)普通的select 使用无锁快照读(snapshot read) (2)加锁的select(select ... lock in share mode / select ... for update), update, delete等语句它们的锁依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)或者范围查询条件(range-type search condition) 在唯一索引上使用唯一的查询条件会使用记录锁(record lock)而不会封锁记录之间的间隔即不会使用间隙锁(gap lock)与临键锁(next-key lock)其他索引查询条件会使用间隙锁与临键锁锁住索引记录之间的范围避免范围间插入记录以避免产生幻影行记录以及避免不可重复的读非唯一索引上的查询临键锁。 (3)insert语句用排它锁封锁被插入的索引记录并在在插入区间加插入意向锁(insert intention lock)但这个并不会真正封锁区间也不会阻止相同区间的不同KEY插入。 5.4 可串行化 select 隐式转化为 select ... in share mode。 6、问题 问主从复制时执行并发批量插入可能导致不同库里同一张表下的同一行id有可能不一样如何避免 把 innodb_autoinc_lock_mode 设置成 1则在批量插入时会加上自增表锁保证从库回放 sql 时得到的自增 id 跟主库一样。 问同一张表可以同时有多个事务进行写操作吗 如果事务之间操作的是不同的记录行可以同时进行如果操作的是相同的记录行则不能同时进行因为事务之间会因为获取行锁冲突而阻塞未获取到行锁的事务需要等获取到行锁再继续执行。 问如果发生全表扫描是直接顺序扫描聚集索引的叶子节点吗 暂未掌握。 问事务里的 sql 语句执行过程中加上表锁或者行锁后这些锁是 sql 执行完毕就释放还是持续到事务执行完毕才释放为什么不 sql 执行完毕就释放即上面的这些锁是语句级别的还是事务级别的 事务执行完毕才释放。在 InnoDB 事务中行锁是在需要的时候才加上的但并不是不需要了就立刻释放而是要等到事务结束时才释放。这个就是两阶段锁协议(两个阶段指的是加锁阶段和解锁阶段)。 比如使用使用临键锁来避免幻读的问题加锁后不确定事务内后续是否仍有同样的范围查询语句会执行如果每次 sql 执行完毕就释放锁多次范围查询语句之间仍旧有可能在范围之间插入数据导致幻读那临键锁就没有意义了。所以为了保证每次查到的结果都一样即保证不出现幻读只能是把临键锁持续到事务完成才释放。 问事务内sql加的锁都是在事务完成才释放这个有什么指导意义 如果事务中需要锁多个行要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放减少这些记录行的加锁时间降低锁冲突概率。 假设你用淘宝积分购买了一件商品那么这个动作需要至少有三条 sql扣减你的积分、扣减商品数量、增加订单记录假设淘宝所有数据都在一个数据库里那么这三个 sql 可以用一个事务包裹起来这时会把商品扣减sql放在最后你的积分扣减放在最前面因为商品扣减的锁冲突的概率最大最可能影响并发你的积分扣减锁冲突最小。 问既然锁的持有时间这么长除了降低并发外还可能导致什么问题 增大死锁的概率。锁的持有时间越长死锁的概率就越大。 问如何处理死锁 策略一直接进入等待直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置默认为 50s即如果不开启死锁检测则在发生死锁之后会等待 50s 后回滚事务释放锁。策略二发起死锁检测发现死锁后主动回滚死锁链条中的某一个事务让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on表示开启这个逻辑。innodb 默认开启死锁检测但是死锁检测会消耗大量的CPU资源。 问select * from t where id1 for update; 一定会加记录锁吗 不一定如果 id 列有唯一索引那么会加记录锁如果 id 列没有索引会在聚集索引上加临键锁如果有索引但不是唯一索引会在 id 索引节点和主键索引节点上加临键锁 问多个间隙锁可以共存吗 可以。 问update name2 from t where id1 lock in share mode 跟 select * from t where id1 for update; 加的锁一样吗 不一样前者加的行级共享锁后者加的是行级排他锁。 问可串行化(Serializable) 和可重复读 RR 两种隔离级别分别怎么避免幻读的 隔离级别为 RR 手动在 select 语句后面加 for update 来避免幻读问题不手动加这个不会加对应的锁来避免幻读。 隔离级别为可串行化自动给 select 加上了 for update 来避免幻读。 问手动加 for update 可能会有什么问题 表锁 问为什么当唯一索引不是聚集索引时记录锁还会额外锁定聚集索引的索引记录 因为即使锁定了唯一索引的索引记录但是其他事务可以绕过这个唯一索引通过聚集索引来修改记录。 假设记录锁只锁定查询条件中的唯一索引数树的唯一索引节点不锁定聚集索引的索引节点。 比如 id 和 name 都是 stu 表的唯一索引id 还是聚集索引。假设存在记录(1, zhangsan, 19) update set age18 where namezhangsan虽然索引了 name 列的索引节点但是仍旧可以通过 update set age18 where id1; 来修改同行记录。 问没有命中索引的查询更新语句会加什么锁跟命中索引的语句有什么不同 InnoDB的行锁是实现在索引上的而不是锁在物理行记录上。所以如果访问没有命中索引就无法使用行锁将要退化为表锁共享行锁上升为共享表锁排他行锁上升为排他表锁。加表锁之前会检查意向排他锁来判断当前是否存在排他行锁如果存在则需要阻塞等待排他行锁的释放否则可以直接获得表锁。 问mysql 极限吞吐量低可能有哪些原因 1、慢查询 2、开启了死锁检测因为死锁检测会消耗大量的CPU资源所以单个事务的时间时间会加长很多。 问全局锁有什么应用场景 全局锁的一个使用场景是做全库逻辑备份(mysqldump)加了全局锁后在备份过程中整个库处于只读状态。如果备份主库备份期间更新相关业务都无法执行如果是备份从库备份期间从库就不能执行主库同步过来的 binlog会导致主从延迟。 支持事务的引擎InnoDB可以不使用FTWRL使用mysqldump命令备份时可以使用参数 --single-transaction 来获取一致性备份它会在开始备份前启动一个事务利用了MVCC技术实现多版本可重复读其他事务的更新对它不可见所以备份过程可以正常更新数据。 而不支持事务的引擎MyISAM就无法使用 --single-transaction参数了它不支持事务隔离所以使用MyISAM引擎的数据库备份需要使用FTWRL命令。 问既然因为 DML 锁控制了只要有事务在执行就不能执行表结构变更操作那如果线上数据表每时每刻都有事务在执行那岂不是永远无法执行表结构变更 创建新表迁移数据删除旧表。 7、完整参考 前 11 篇文章均是沈剑大佬的公众号文章沈剑大佬公众号“架构师之路”满满干货忍不住分享。 挖坑InnoDB的七种锁、 InnoDB并发插入居然使用意向锁 InnoDBselect为啥会阻塞insert 我CA一个SQL语句为啥只执行了一半 MySQL不为人知的主键与唯一索引约束 插入InnoDB自增列居然是表锁 自增主键很多人以为自己懂了然而... 自增主键三类插入测验答案在这里。 别废话各种SQL到底加了什么锁 4种事务的隔离级别InnoDB如何巧妙实现 如何插入 | 非开车纯技术交流 MySQL 8.0AUTO_INCREMENT Handling in InnoDB Mysql的Gap锁(间隙锁)详解 MySQL InnoDB锁机制之Gap Lock、Next-Key Lock、Record Lock解析 通过各种简单案例让你彻底搞懂 MySQL 中的锁机制与 MVCC MySQL之MVVC简介 每天一个知识点行锁 mysql学习笔记(三)全局锁、表锁、行锁 MySQL锁全局锁、表级锁和行锁 https://www.cnblogs.com/JiangLe/p/6362770.html AUTO_INCREMENT 处理 MySQL innodb_autoinc_lock_mode设置 MySQL innodb_autoinc_lock_mode 详解 面试必备-行锁、表锁 - 乐观锁、悲观锁的区别和联系(史上最全)
文章转载自： http://www.morning.rmrcc.cn.gov.cn.rmrcc.cn http://www.morning.fznj.cn.gov.cn.fznj.cn http://www.morning.zffn.cn.gov.cn.zffn.cn http://www.morning.dmxzd.cn.gov.cn.dmxzd.cn http://www.morning.lqynj.cn.gov.cn.lqynj.cn http://www.morning.yuminfo.com.gov.cn.yuminfo.com http://www.morning.lsnnc.cn.gov.cn.lsnnc.cn http://www.morning.qwzpd.cn.gov.cn.qwzpd.cn http://www.morning.ybgt.cn.gov.cn.ybgt.cn http://www.morning.jjwt.cn.gov.cn.jjwt.cn http://www.morning.pyncx.cn.gov.cn.pyncx.cn http://www.morning.mtxrq.cn.gov.cn.mtxrq.cn http://www.morning.frtt.cn.gov.cn.frtt.cn http://www.morning.rzcbk.cn.gov.cn.rzcbk.cn http://www.morning.jmllh.cn.gov.cn.jmllh.cn http://www.morning.bhqlj.cn.gov.cn.bhqlj.cn http://www.morning.rlsd.cn.gov.cn.rlsd.cn http://www.morning.zzfjh.cn.gov.cn.zzfjh.cn http://www.morning.wptrm.cn.gov.cn.wptrm.cn http://www.morning.hyfrd.cn.gov.cn.hyfrd.cn http://www.morning.zcwtl.cn.gov.cn.zcwtl.cn http://www.morning.dpgdj.cn.gov.cn.dpgdj.cn http://www.morning.rknsp.cn.gov.cn.rknsp.cn http://www.morning.zwndt.cn.gov.cn.zwndt.cn http://www.morning.zztmk.cn.gov.cn.zztmk.cn http://www.morning.qfbzj.cn.gov.cn.qfbzj.cn http://www.morning.zwsgl.cn.gov.cn.zwsgl.cn http://www.morning.beijingzy.com.cn.gov.cn.beijingzy.com.cn http://www.morning.mrskk.cn.gov.cn.mrskk.cn http://www.morning.nhlyl.cn.gov.cn.nhlyl.cn http://www.morning.smxrx.cn.gov.cn.smxrx.cn http://www.morning.bpmdr.cn.gov.cn.bpmdr.cn http://www.morning.skpdg.cn.gov.cn.skpdg.cn http://www.morning.chfxz.cn.gov.cn.chfxz.cn http://www.morning.skmzm.cn.gov.cn.skmzm.cn http://www.morning.srjbs.cn.gov.cn.srjbs.cn http://www.morning.pbmkh.cn.gov.cn.pbmkh.cn http://www.morning.kcsx.cn.gov.cn.kcsx.cn http://www.morning.nbwyk.cn.gov.cn.nbwyk.cn http://www.morning.lkkgq.cn.gov.cn.lkkgq.cn http://www.morning.wxfgg.cn.gov.cn.wxfgg.cn http://www.morning.qineryuyin.com.gov.cn.qineryuyin.com http://www.morning.kkwgg.cn.gov.cn.kkwgg.cn http://www.morning.gtjkh.cn.gov.cn.gtjkh.cn http://www.morning.yxwnn.cn.gov.cn.yxwnn.cn http://www.morning.wbyqy.cn.gov.cn.wbyqy.cn http://www.morning.gwdmj.cn.gov.cn.gwdmj.cn http://www.morning.rongxiaoman.com.gov.cn.rongxiaoman.com http://www.morning.rxnl.cn.gov.cn.rxnl.cn http://www.morning.ppdr.cn.gov.cn.ppdr.cn http://www.morning.znqmh.cn.gov.cn.znqmh.cn http://www.morning.lqffg.cn.gov.cn.lqffg.cn http://www.morning.wjhqd.cn.gov.cn.wjhqd.cn http://www.morning.lfbsd.cn.gov.cn.lfbsd.cn http://www.morning.bmmyx.cn.gov.cn.bmmyx.cn http://www.morning.xyhql.cn.gov.cn.xyhql.cn http://www.morning.xdlwm.cn.gov.cn.xdlwm.cn http://www.morning.lkmks.cn.gov.cn.lkmks.cn http://www.morning.rnwmp.cn.gov.cn.rnwmp.cn http://www.morning.trqsm.cn.gov.cn.trqsm.cn http://www.morning.grbp.cn.gov.cn.grbp.cn http://www.morning.qbfwb.cn.gov.cn.qbfwb.cn http://www.morning.kdnrc.cn.gov.cn.kdnrc.cn http://www.morning.dspqc.cn.gov.cn.dspqc.cn http://www.morning.bsplf.cn.gov.cn.bsplf.cn http://www.morning.c7497.cn.gov.cn.c7497.cn http://www.morning.pmdlk.cn.gov.cn.pmdlk.cn http://www.morning.zztmk.cn.gov.cn.zztmk.cn http://www.morning.nhdw.cn.gov.cn.nhdw.cn http://www.morning.pcqxr.cn.gov.cn.pcqxr.cn http://www.morning.phwmj.cn.gov.cn.phwmj.cn http://www.morning.mnccq.cn.gov.cn.mnccq.cn http://www.morning.bprsd.cn.gov.cn.bprsd.cn http://www.morning.nnpfz.cn.gov.cn.nnpfz.cn http://www.morning.rgwrl.cn.gov.cn.rgwrl.cn http://www.morning.wrwcf.cn.gov.cn.wrwcf.cn http://www.morning.ghfmd.cn.gov.cn.ghfmd.cn http://www.morning.sprbs.cn.gov.cn.sprbs.cn http://www.morning.qpsft.cn.gov.cn.qpsft.cn http://www.morning.rbnj.cn.gov.cn.rbnj.cn
