[摘要]+----+------+8 rows in set (0.00 sec)条件1 innodb_autoinc_lock_mode设置为0session1begin;delete from test...
+----+------+
8 rows in set (0.00 sec)
条件1 innodb_autoinc_lock_mode设置为0
session1
begin;delete from test_autoinc_lock where a>7;//这时未提交
session2
mysql> insert into test_autoinc_lock(a) values(100);//gap锁的存在，这时处于锁等待
session3
mysql> insert into test_autoinc_lock(a) values(2);//这时同样处于等待状态，理论上这个不是gap锁的锁定范围，那么它是在等什么呢
session4
mysql> 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: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_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;
+----+------+
关键词：案例详细说明mysql中innodb_autoinc_lock_mode