【MySQL】InnoDB锁机制之二

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 一 前言    之前的文章《InnoDB锁机制之一》介绍了InnoDB锁中的三种锁:record lock, gap lock,next-key lock ,本文继续介绍另外两种锁 Insert Intention Locks和AUTO-INC Locks二 常见的锁类型2.
一 前言
   之前的文章《 InnoDB锁机制之一》介绍了InnoDB锁中的三种锁:record lock, gap lock,next-key lock ,本文继续介绍另外两种锁 Insert Intention Locks和AUTO-INC Locks
二 常见的锁类型
2.1 根据锁持有的时间粒度,分为
1. 内存级别:类似mutex,很快释放
2. 语句级别:statement结束,释放
3. 事务级别:transaction提交或者回滚才释放
4. 会话级别:session级别,连接断开才释放

2.2  AUTO-INC lock
AUTO-INC lock是一个特殊的表级锁,当一个事务向含有自增字段的表插入数据时 ,该事务会获取一个AUTO-INC lock,其他事务必须等待直到已经获取锁的insert 语句结束。因此,多个并发事务不能同时获取同一个表上面的AUTO-INC lock,如果持有AUTO-INC锁太长时间可能会影响到数据库性能(比如INSERT INTO t1... SELECT ... FROM t2这类语句)或者死锁.
鉴于AUTO-INC 锁的特性,MySQL 5.1.22 通过新增参数 innodb_autoinc_lock_mode 来控制自增序列的算法。该参数可以设置为0,1,2.
在学习innodb_autoinc_lock_mode之前,我们先了解insert语句的类型
1 Simple inserts
  能够事先确定具体行数的insert语句,比如 insert into tab values()...(); replace 等等。 INSERT ... ON DUPLICATE KEY UPDATE和还有子查询的insert 语句除外。
2 Bulk inserts
  和Simple inserts对立,事先不能确定插入行数的 insert/replace语句 ,insert ... select ;replace ...select; load data into table .. 这种情况下Innodb在执行具体的行的时候 会为每一行单独分配一个auto_increment 值。
3  Mixed-mode inserts
  该情形是 Simple inserts 模式中,有些insert指定了 自增字段的具体值,有些没有指定。比如:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
INSERT ... ON DUPLICATE KEY UPDATE

接下来我们再看MySQL对auto_increment 的优化模式。
innodb_autoinc_lock_mode=0,是传统的方式。InnoDB会在分配前给表加上AUTO_INC锁,并在SQL结束时释放掉。该模式保证了在STATEMENT复制模式下,备库执行类似INSERT … SELECT这样的语句时的一致性,因为这样的语句在执行时无法确定到底有多少条记录,只有在执行过程中不允许别的会话分配自增值,才能确保主备一致。
很显然这种锁模式非常影响并发插入的性能,但却保证了一条SQL内自增值分配的连续性。
innodb_autoinc_lock_mode=1 ,这个是InnoDB的默认值。该模式下对于Simple inserts,InnoDB会先加一个 autoinc_mutex锁,然后去判断表上是否有别的线程加了LOCK_AUTO_INC锁,如果有的话,释放autoinc_mutex,并使用传统的加锁模式。否则,在预留本次插入需要的自增值之后,就快速的将autoinc_mutex释放掉。很显然,对于普通的并发INSERT操作,都是无需加LOCK_AUTO_INC锁的。因此该模式提高了系统并发性;
innodb_autoinc_lock_mode=2,这种模式下只在分配时加个mutex即可,很快就释放,不会像值为1那样在某些场景下会退化到传统模式。因此设为2不能保证批量插入的复制安全性。

2.3  Insert Intention Locks
     插入意向锁是gap 锁的一种,只是针对insert。当并发事务多条insert 插入同一个GAP,如果他们不是插入同一行记录,会话之间并不会相互等待。例如索引记录删 有 12 ,17 两个记录,两个会话同时插入记录13,15,他们会分别为(12,17)加上GAP锁,但相互之间并不冲突(因为插入的记录不冲突)。
     当向某个数据页中插入一条记录时,总是会调用函数lock_rec_insert_check_and_lock进行锁检查(构建索引时的数据插入除外),会去检查当前插入位置的下一条记录上是否存在锁对象,这里的下一条记录不是指的物理连续,而是按照逻辑顺序的下一条记录。
如果下一条记录上不存在锁对象:若记录是二级索引上的,先更新二级索引页上的最大事务ID为当前事务的ID;直接返回成功。
如果下一条记录上存在锁对象,就需要判断该锁对象是否锁住了GAP。如果GAP被锁住了,并判定和插入意向GAP锁冲突,当前操作就需要等待,加的锁类型为LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION,并进入等待状态。但是插入意向锁之间并不互斥。这意味着在同一个GAP里可能有多个申请插入意向锁的会话。

三 参考文章
[1] 方文档  innodb-auto-increment-handling 
[2] MySQL · 引擎特性 · InnoDB 事务锁系统简介  --强烈推荐
[3] MySQL auto_increment实现 
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4天前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
9天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.3-InnoDB中的锁
【MySQL技术内幕】6.3-InnoDB中的锁
145 57
|
9天前
|
存储 算法 关系型数据库
【MySQL技术内幕】5.7- InnoDB存储引擎中的哈希算法
【MySQL技术内幕】5.7- InnoDB存储引擎中的哈希算法
14 1
|
9天前
|
存储 关系型数据库 MySQL
【MySQL技术内幕】5.1-InnoDB存储引擎索引概述
【MySQL技术内幕】5.1-InnoDB存储引擎索引概述
22 0
|
2天前
|
存储 关系型数据库 MySQL
关系型数据库mysql日志和临时文件
【6月更文挑战第15天】
18 4
|
2天前
|
存储 关系型数据库 MySQL
关系型数据库mysql数据文件存储
【6月更文挑战第15天】
9 4
|
2天前
|
存储 关系型数据库 MySQL
关系型数据库mysql文件系统支持
【6月更文挑战第15天】
19 4
|
3天前
|
存储 自然语言处理 关系型数据库
|
18小时前
|
存储 关系型数据库 MySQL
|
18小时前
|
SQL 关系型数据库 MySQL