innodb_autoinc_lock_mode - xiaoboluo768/qianjinliangfang GitHub Wiki

• 控制innodb自增长锁模式参数，全局变量，只读变量，整型值，有0，1，2三个值（分别代表traditional(“传统”)， consecutive(“连续”)和interleaved(“交叉”)），默认为1，表示自增ID是连续的。
  • 在5.1.22之后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode，该实现方式是通过轻量级互斥量的增长机制完成的。它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：
    • 0：通过表锁的方式进行，也就是所有类型的insert都用AUTO-inc locking。
    • 1：默认值，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行。直接通过分析语句，获得要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。
    • 2：对所有的insert-like 自增长值的产生使用互斥量机制完成，分配自增id时是预分配超过需要的自增范围段，性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式。innodb_autoinc_lock_mode = 2 和 innodb_autoinc_lock_mode = 1 的测试情况一样。但该模式下是来一个分配一个，而不会锁表，只会锁住分配id的过程，和1的区别在于，不会预分配多个，这种方式并发性最高。但是在replication中当binlog_format为statement-based时存在问题
  • 尽量让主键ID没有业务意义，或则使用simple inserts模式插入，下面是innodb插入类型说明：
    • 1）.INSERT-LIKE：指所有的插入语句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等
    • 2）.Simple inserts：指在插入前就能确定插入行数的语句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。
    • 3）.Bulk inserts：指在插入前不能确定得到插入行的语句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.
    • 4）.Mixed-mode inserts:指其中一部分是自增长的，有一部分是确定的。
  • 总结：通过上面2个问题的说明，自增主键会产生表锁，从而引发问题；自增主键有业务意义，不连续的主键导致主从主键不一致到出现问题。对于simple inserts 的插入类型，上面的问题都不会出现。对于Bulk inserts的插入类型，会出现上述的问题。

---

上一篇：innodb_thread_concurrency | 下一篇：innodb_data_file_path