小知识之 InnoDB自增锁

一叶而不知秋

向管中窥豹寻知外,坐井观天又出来。

在设计数据库表时,InnoDB存储引擎建议将主键设置为自增键,当表中存在自增键时,插入数据操作的同时InnoDB会加自增锁,自增锁是表锁。当一条数据插入语句持有自增锁时,其他插入语句将会被阻塞。但InnoDB为提高数据插入语句的并发性能,针对不同的插入语句采取了不同的策略。

首先,数据插入语句可分为以下几种:

Simple inserts :在插入前,插入数据的数量是可知的,普通的插入语句(没有子查询)属于此类:

Bulk inserts :在插入前,插入数据的数量是不可知的

Mixed-mode insert

其次,InnoDB通过innodb_autoinc_lock_mode这个参数控制着不同插入语句对自增键行为的影响,其有三个取值

1.Tradition,传统模式(innodb_autoinc_lock_mode=0)

在这种模式下,以上三类数据插入语句都需要申请并持有自增锁,即会锁表,其他的插入语句申请自增表锁会阻塞,但这个锁的作用范围是语句级,而非事务级。

该模式提供了向后的兼容性,能保证单挑数据插入语句中值分配的可预见性,与连续性,可重复性,这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值,即保证了基于语句复制的安全性。

2.consecutive,连续模式(innodb_autoinc_lock_mode=1)

该模式是InnoDB的默认模式(MySQL5.7版本),这一模式下,InnoDB对Simple inserts类语句做了优化,由于simple insert一次性插入值的个数是可以确定的,所以MySQL可以一次生成几个连续的值,用于这个insert语句,不需要等待这个insert语句执行结束。对于其他类型的数据插入语句,仍需要申请并持有自增锁,且当一个非Simple inserts类的插入语句持有自增锁时,Simple inserts语句也需要等待。

3.interleaved,交错模式,(innodb_autoinc_lock_mode=2)

该模式下,所有数据插入语句均不使用自增锁,所以这个模式下的并发性能最好,但他的问题就是对于同一个语句

最后举一个Mixed-mode insert语句在不同模式下执行的结果,MySQL 5.7版本:

表结构:

插入语句:

tradition lock mode下结果:

下一个自增值为103,因为其是按行一次分一个,只有两个NULL的插入触发了分配。

consecutive lock mode下结果:

下一个自增值为105,因为其是一次性分配了4个。

interleaved lock mode下结果:

x/y具体值取决于其他并发插入操作。

你如果想学技术 | 屯干货 | 聊职场

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180914G1HIHY00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券