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 写入数据库的值不一定是连续的. 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133735/viewspace-2061891/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133735/viewspace-2061891/