浅谈innodb_autoinc_lock_mode的表现形式和选值参考方法

2020-03-29 16:59:26

字体：大中小

来源：转载

供稿：网友

前提条件，percona 5.6版本，事务隔离级别为RR

mysql> show create table test_autoinc_lock/G*************************** 1. row *************************** Table: test_autoinc_lockCreate Table: CREATE TABLE `test_autoinc_lock` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_a` (`a`)) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> select * from test_autoinc_lock;+----+------+| id | a |+----+------+| 1 | 1 || 12 | 2 || 2 | 3 || 3 | 5 || 4 | 7 || 5 | 7 || 6 | 9 || 7 | 10 |+----+------+8 rows in set (0.00 sec)

条件1 innodb_autoinc_lock_mode设置为0

session1 begin;delete from test_autoinc_lock where a>7;//这时未提交session2mysql> insert into test_autoinc_lock(a) values(100);//gap锁的存在，这时处于锁等待session3mysql> insert into test_autoinc_lock(a) values(2);//这时同样处于等待状态，理论上这个不是gap锁的锁定范围，那么它是在等什么呢session4mysql> select * from information_schema.innodb_trx/G*************************** 1. row *************************** trx_id: 2317 trx_state: LOCK WAIT trx_started: 2016-10-31 19:28:05 trx_requested_lock_id: 2317:20 trx_wait_started: 2016-10-31 19:28:05 trx_weight: 1 trx_mysql_thread_id: 9 trx_query: insert into test_autoinc_lock(a) values(2) trx_operation_state: setting auto-inc lock trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 1 trx_lock_memory_bytes: 360 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0trx_autocommit_non_locking: 0

这时查看session3是等待自增锁，一直处于setting auto-inc lock状态

session2

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这时session3锁等待超时退出

session3

这时再看session3可以发现insert完成。

mysql> select * from test_autoinc_lock;+----+------+| id | a |+----+------+| 1 | 1 || 12 | 2 || 13 | 2 || 2 | 3 || 3 | 5 || 4 | 7 || 5 | 7 || 6 | 9 || 7 | 10 |+----+------+9 rows in set (0.00 sec)//注意看这时的最大自增值是13，也就是之前自增最大值上+1，也就是说session2后来释放了预计生成的自增id，将13留给了session3，自增id值的申请完全是串行顺序的。

结论：innodb_autoinc_lock_mode为0时的，也就是官方说的traditional

级别，该自增锁是表锁级别，且必须等待当前SQL执行完成后或者回滚掉才会释放，这样在高并发的情况下可想而知自增锁竞争是比较大的。

上一篇：innodb_index_stats导入备份数据时报错表主键冲突的解决方法

下一篇：详谈innodb的锁(record,gap,Next-Key lock)