丰富的线上&线下活动，深入探索云世界
做任务，得社区积分和周边
最真实的开发者用云体验
让每位学生受益于普惠算力
让创作激发创新
资深技术专家手把手带教
遇见技术追梦人
技术交流，直击现场
海量开发者使用工具、手册，免费下载
极速、全面、稳定、安全的开源镜像
开发手册、白皮书、案例集等实战精华
为开发者定制的Chrome浏览器插件
这次我们只来讨论和实验AUTO-INC锁。
AUTO-INC锁是表级锁，如果一张表中有自增的列（例如：idintNOTNULLAUTO_INCREMENT,）那么当向这张表插入数据时，InnoDB会先获取这张表的AUTO-INC锁。
如果一个事务正在插入数据到有自增列的表时，其他事务会阻塞等待正在持有AUTO-INC锁的事务释放AUTO-INC锁。
INSERT-LIKE：指所有的插入语句，包括：INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOADDATA等。
指在插入前就能确定插入行数的语句，包括：INSERT、REPLACE，不包含INSERT…ONDUPLICATEKEYUPDATE这类语句。
指在插入钱不能确定行数的语句，包括：INSERT...SELECT/REPLACE...SELECT/LOADDATA。
混合模式分为两种：
例如：MySQL官网给的例子，表t1有两个列（c1和c2）,其中c1列时自增列，那么构造如下SQL语句就是混合模式：
INSERTINTOt1(c1,c2)VALUES(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');这种语句会使用锁来为AUTO_INCREMENT列分配自增值，但是在更新阶段可能不会用这些分配的自增值。
AUTO-INC锁可以使用innodb_autoinc_lock_mode变量来配置自增锁的算法。innodb_autoinc_lock_mode变量可以选择三种值如下表：
在传统锁模式下，所有的"INSERT-LIKE"语句为AUTO_INCREMENT列使用表级AUTO_INC锁。一个事务的"INSERT-LIKE"语句在语句执行结束后释放AUTO_INC表级锁，而不是在事务结束后释放。这样做的目的是为了保证这个"INSERT-LIKE"语句的自增列的值是连续的。
连续锁模式对于“Simpleinserts”不会使用表级锁，而是使用一个轻量级锁来生成自增值，因为InnoDB可以提前直到插入多少行数据。自增值生成阶段使用轻量级互斥锁来生成所有的值，而不是一直加锁直到插入完成。但是如果其他事务持有AUTO_INC锁，那么“SimpleInserts”类语句也需要等待其他事务完成才能使用轻量级锁来生成所有的自增值。
连续锁模式对于“bulkinserts”类语句使用AUTO_INC表级锁直到语句完成。使用表级AUTO_INC锁的语句：INSERT...SELECT、REPLACE...SELECT、LOADDATA。
当innodb_autoinc_lock_mode=1时，在语句复制格式下(BINLOG_FORMAT=STATEMENT)，BINLOG中没有记录主库执行过程中获取到的所有自增值及其对应行的信息，要保证"Bulkinsert"操作主从复制数据一致就必须保证语句在主库和从库执行时获取到相同自增值，而因此只能通过控制“获取连续自增值”的方式来实现，同时为避免受其他事务插入操作影响，就必须在表级别加锁且保证持有锁至语句结束。
在行复制格式下(BINLOG_FORMAT=ROW)，主库BINLOG中保存有记录的所有列信息包括自增列值，因此无需通过AUTO-INC锁来保证主从数据一致。在MySQL8.0版本前，参数BINLOG_FORMAT的默认值为STATEMENT，参数innodb_autoinc_lock_mode的默认值为1。在MySQL8.0版本后，参数BINLOG_FORMAT的默认值被调整为ROW格式，参数innodb_autoinc_lock_mode的默认值为2。
所有的“INSERT-LIKE”语句都不使用表级锁，而是使用轻量级互斥锁。
交错锁模式速度快、可扩展性高，但是对于基于语句复制会有问题，只能使用基于ROW复制。
之所以称为交错模式是因为并发插入场景下自增值的分配大概率是交替这来的，时刻1事务1获得自增值，时刻2事务2获得自增值，以此类推。
如果一个插入数据的事务回滚，那么为这个事务生成的自增值就会丢失，因为自增值不会被回滚。
回滚造成的自增值丢失InnoDB是不会被重用，所以不能依赖InnoDB自增列是连续值。
这里实验几个简单场景。
建表语句：
CREATETABLE`sys_user`(`id`intNOTNULLAUTO_INCREMENT,`name`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ciNOTNULLCOMMENT'姓名',`name_pinyin`varchar(255)CHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ciNOTNULLCOMMENT'姓名拼音',`id_card`varchar(255)CHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ciNOTNULLCOMMENT'身份证号',`phone`varchar(20)CHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ciDEFAULTNULLCOMMENT'手机号',`age`int(11)NOTNULLDEFAULT1COMMENT'年龄',PRIMARYKEY(`id`),UNIQUEKEY`uni_idx_id_card`(`id_card`)USINGBTREECOMMENT'唯一索引-身份证号',KEY`idx_phone_name`(`phone`,`name`)USINGBTREECOMMENT'普通索引-手机号')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ciCOMMENT='用户'；插入记录
insertintosys_user(id,name,name_pinyin,id_card,phone,age)values(1,'小六','xiaoliu',300000000,13000008000,10);insertintosys_user(id,name,name_pinyin,id_card,phone,age)values(2,'小六','xiaoliu',300000001,13000008000,11);insertintosys_user(id,name,name_pinyin,id_card,phone,age)values(3,'小六','xiaoliu',300000002,13000008000,13);insertintosys_user(id,name,name_pinyin,id_card,phone,age)values(4,'小六','xiaoliu',300000003,13000008000,20);自增锁模式查询showvariableslike'%innodb_autoinc_lock_mode%';MySQL8.0.x版本输出：
MySQL[employees]>showvariableslike'%innodb_autoinc_lock_mode%';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|innodb_autoinc_lock_mode|2|+--------------------------+-------+1rowinset(0.95sec)自增锁丢失与间隙数据库有一张sys_user表，表中有四条记录，id为自增列，id最大值为4：
MySQL[employees]>select*fromsys_user;+----+--------+-------------+-----------+-------------+-----+|id|name|name_pinyin|id_card|phone|age|+----+--------+-------------+-----------+-------------+-----+|1|小六|xiaoliu|300000000|13000008000|10||2|小六|xiaoliu|300000001|13000008000|11||3|小六|xiaoliu|300000002|13000008000|13||4|小六|xiaoliu|300000003|13000008000|20|+----+--------+-------------+-----------+-------------+-----+4rowsinset(0.01sec)启动事务A，执行插入语句后回滚事务A：
begin;insertintosys_user(name,name_pinyin,id_card,phone,age)values('小六','xiaoliu',3000000010,13000008000,10);rollback;事务A执行结果：
MySQL[employees]>begin;QueryOK,0rowsaffected(0.00sec)MySQL[employees]>insertintosys_user(name,name_pinyin,id_card,phone,age)values('小六','xiaoliu',3000000010,13000008000,10);QueryOK,1rowaffected(0.00sec)MySQL[employees]>rollback;QueryOK,0rowsaffected(0.97sec)启动事务B,执行插入语句后提交事务B：
begin;insertintosys_user(name,name_pinyin,id_card,phone,age)values('小六','xiaoliu',3000000010,13000008000,10);commit;select*fromsys_user;事务B执行完成后，查询输出如下（事务A使用的id=5值丢失出现自增间隙）：