前提条件，percona 5.6版本，事务隔离级别为RR
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
mysql> show create table test_autoinc_lock\G
*************************** 1. row ***************************
Table: test_autoinc_lock
Create 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=utf8
1 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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
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完成。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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执行完成后或者回滚掉才会释放，这样在高并发的情况下可想而知自增锁竞争是比较大的。
条件2 innodb_autoinc_lock_mode设置为1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test_autoinc_lock where a>7;
Query OK, 2 rows affected (0.00 sec)
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
session2
mysql> insert into test_autoinc_lock(a) values(100);//同样gap锁的存在，这时处于锁等待
session3
mysql> insert into test_autoinc_lock(a) values(5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 13 | 2 |
| 2 | 3 |
| 3 | 5 |
| 15 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
10 rows in set (0.00 sec)//session3直接完成了，并且注意观察插入的自增id值是15，也就是跳过了预计分配给session2的14，可以看到自增id值立马就分配给了session3，而不必等session2执行完成
|
结论：innodb_autoinc_lock_mode为1时的，也就是官方说的consecutive
级别，这时如果是单一的insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其他事务中已经有session获取了自增锁）。另外当SQL是一些批量insert sql时，比如insert into ...select ...,load data,replace ..select..时，这时还是表级锁，可以理解成退化为必须等待当前SQL执行完才释放。
可以认为，该值为1时是相对比较轻量的锁，也不会对复制产生影响，唯一的缺陷是产生的自增值不一定是完全连续的（不过个人认为这个往往不是很重要，也没必要根据自增id值来统计行数之类）
条件3 innodb_autoinc_lock_mode设置为2
先说结论：当innodb_autoinc_lock_mode设置为2时，所有insert种类的SQL都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当binlog_format为statement时，这时的复制没法保证安全，因为批量的insert，比如insert ..select..语句在这个情况下，也可以立马获取到一大批的自增id值，不必锁整个表，slave在回放这个sql时必然会产生错乱。我们做个测试验证复制不是安全的。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
master session1
mysql> show variables like '%binlog_for%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> insert into test_autoinc_lock(a) select * from test_auto;
Query OK, 8388608 rows affected, 1 warning (29.85 sec)
Records: 8388608 Duplicates: 0 Warnings: 1
master session2（注意session2在session1执行完成之前执行）
mysql> insert into test_autoinc_lock(a) values(2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_autoinc_lock where a=2;
+---------+------+
| id | a |
+---------+------+
| 1376236 | 2 |
+---------+------+
1 row in set (0.00 sec)
slave session1（这时可看到1376236主键冲突）
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.9.73.139
Master_User: ucloudbackup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 75823243
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 541
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '1376236' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test_autoinc_lock(a) select * from test_auto'
Skip_Counter: 0
Exec_Master_Log_Pos: 75822971
|
我们这时解析下主库的binlog不难发现问题原因，第一条批量insert还没执行完时，第二条简单insert执行时获得了自增id值为1376236的锁，这时在主库写入是没有问题的，但是反应到从库时，因为是基于statement的复制，必然出现主键冲突。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SET INSERT_ID=1376236/*!*/;
#161031 21:44:31 server id 168380811 end_log_pos 75822940 CRC32 0x65797f1c Query thread_id=20 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1477921471/*!*/;
insert into test_autoinc_lock(a) values(2)
/*!*/;
# at 75822940
#161031 21:44:31 server id 168380811 end_log_pos 75822971 CRC32 0xbb91449d Xid = 274
COMMIT/*!*/;
# at 75822971
#161031 21:44:26 server id 168380811 end_log_pos 75823050 CRC32 0xa297b57b Query thread_id=57 exec_time=30 error_code=0
SET TIMESTAMP=1477921466/*!*/;
BEGIN
/*!*/;
# at 75823050
# at 75823082
#161031 21:44:26 server id 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1 Intvar
SET INSERT_ID=1/*!*/;
#161031 21:44:26 server id 168380811 end_log_pos 75823212 CRC32 0x470282ba Query thread_id=57 exec_time=30 error_code=0
SET TIMESTAMP=1477921466/*!*/;
insert into test_autoinc_lock(a) select * from test_auto
|
总结：
1 innodb row复制时，可将innodb_autoinc_lock_mode设置为2，这时可在所有insert情况下表获得最大并发度
2 innodb statement复制时，可将innodb_autoinc_lock_mode设置为1，保证复制安全的同时，获得简单insert语句的最大并发度
3 myisam引擎情况下，无论什么样自增id锁都是表级锁，设置innodb_autoinc_lock_mode参数无效（测试略）
4 实际上提问者说到的在innodb引擎下自增id值作为主键的情况下，相比uuid或者自定义的主键，是可以提到插入速度的，因为innodb是主键聚集索引，实际的主键值必须按照主键顺序存取，那么自增id本身就是升序的，那么在插入数据时，底层就不必再做额外的排序操作，也减少了索引页分裂的次数，从而大大增加insert速度（除非其他方案也能保证主键完全自增）
以上这篇浅谈innodb_autoinc_lock_mode的表现形式和选值参考方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持服务器之家。