这篇文章主要介绍“怎么理解mysql innodb_autoinc_lock_mode的与数据库行为”，在日常操作中，相信很多人在怎么理解mysql innodb_autoinc_lock_mode的与数据库行为问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么理解mysql innodb_autoinc_lock_mode的与数据库行为”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
mysql 的auto_incremet 都知道了
对应的还有一个这个行为的一个锁的类型的参数,对数据库的行为会有影响.
innodb_autoinc_lock_mode 有3个取值 0 , 1 ,2 对应于
“traditional”,“consecutive”, or “interleaved” lock mode
针对有3中行为:
1. insert_like 语句 包括: insert , insert … select, replace ,replace … select 已经 load data 语句.
2.simple insert 语句: insert , replace 这样.
3.bulk insert 语句: insert …select , replace…select , load data.
4.mixed insert 语句: 例如 INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
还有 INSERT … ON DUPLICATE KEY UPDATE 这种语句.
innodb_autoinc_lock_mode 取值为 0 :
这个参数是从5.1 引入的, 对应于传统的模式. 对于insert like 语句这个auto_incremet 取得的是一个表级锁.
持有锁的时间是到当前语句结束. 确保语句的写入是可以预见以及可以预测,可以重现的,也就是可以保证语句入库的数据时顺序的.
对于使用statement format 的 binlog 模式的复制,slave 跟主库同一行记录取得的值是一致.
例如 :
CREATE TABLE t1 ( c1 INT(11) NOT NULL AUTO_INCREMENT, c2 VARCHAR(10) DEFAULT NULL, PRIMARY KEY (c1)) ENGINE=InnoDB;
执行语句:
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table …
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
在statement 的复制中, 日志重演这个sql执行,是可以确保主从取得的值是一致的.
tx1 执行的时候, 是可以保证 这1000笔记录是连续的取得 autoinc 字段的值, tx2 要等到tx1 执行完后才能拿到autoinc的值.
这样限制了并发.
innodb_autoinc_lock_mode 取值 1 :
取值为1 为默认值.
对于 bulk insert 语句. 每个语句只有这个锁到语句结束,但是每个语句持有这个锁只能执行一次.
对于simple inset 语句是可预计的插入的值的数量,那么语句是可以不需要持有这个表级锁到语句结束. 而是更高级别的mutex 锁.
对于不可以预见插入数量的语句, 起行为跟bulk insert 是一样的,持有锁到语句结束,每次只能执行一次.
对于mixed inset 数据库会分派多于插入插入数量的 autoinc 值 , 语句结束后,多余的值将丢弃, 这个时候, autoinc 字段的值可能不是连续的.
innodb_autoinc_lock_mode 取值 2 :
insert like 语句 可以持有这个表级锁,并执行相同的时间.
这样对于statement based 复制来说, 在恢复的时候,结果就变得不确定了. 也就是可能主从库的autoinc 字段的值是不一致的.
这样会对业务逻辑代理困扰.
这种模式下,每个语句都可以几乎在同时产生autoinc值,这样每个语句的所取得的autoinc字段的值是不确定的.也不可预测.
对于simple insert 如果在执行前 可以预测到要插入的值的数量,这个sql的数据获取的autoinc值是连续的. 否则就不一定是连续的.
对于mixed insert 语句 取得autoinc字段的值是不可预测的.
对于bulk insert 语句 取得autoinc 的值可能存在空缺 (gap) 即取得的值可能不是连续的.
innodb_autoinc_lock_mode 的取值与复制的关系.
取值 0 跟 1 对复制来说都是安全的, 即可以保证主从数据库的值是一致的.
如果却只为2 在 基于 statement based 复制来说,从库的数据时不确定的. 即不安全.
对应 row based 或者 mixed based 的复制 从库的数据时确定跟主库一致的,是安全.
可以看出在 取值2 的时候,可以获得更大的性能.
另外如果指定了 autoinc字段的值 insert 可能会触发 key 冲突的 错误.
如果insert 指定的值大于autoinc 字段的最大值, 其结果是不确定的.
innodb_autoinc_lock_mode 取值为2的时候对于bulk insert 会产生空洞(gap) 同一个sql 写入数据库的值不一定是连续的.
到此，关于“怎么理解mysql innodb_autoinc_lock_mode的与数据库行为”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
