《Mysql入门浅谈innodb_autoinc_lock_mode的表现形式和选值参考方法》要点:
本文介绍了Mysql入门浅谈innodb_autoinc_lock_mode的表现形式和选值参考方法,希望对您有用。如果有疑问,可以联系我们。
前提条件,percona 5.6版本,事务隔离级别为RRMYSQL教程
- 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设置为0MYSQL教程
- 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状态MYSQL教程
session2
MYSQL教程
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionMYSQL教程
这时session3锁等待超时退出MYSQL教程
session3MYSQL教程
这时再看session3可以发现insert完成.MYSQL教程
- 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时的,也就是官方说的traditionalMYSQL教程
级别,该自增锁是表锁级别,且必须等待当前SQL执行完成后或者回滚掉才会释放,这样在高并发的情况下可想而知自增锁竞争是比较大的.MYSQL教程
条件2 innodb_autoinc_lock_mode设置为1MYSQL教程
- 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时的,也就是官方说的consecutiveMYSQL教程
级别,这时如果是单一的insert SQL,可以立即获得该锁,并立即释放,而不必等待当前SQL执行完成(除非在其他事务中已经有session获取了自增锁).另外当SQL是一些批量insert sql时,比如insert into ...select ...,load data,replace ..select..时,这时还是表级锁,可以理解成退化为必须等待当前SQL执行完才释放.MYSQL教程
可以认为,该值为1时是相对比较轻量的锁,也不会对复制产生影响,唯一的缺陷是产生的自增值不一定是完全连续的(不过个人认为这个往往不是很重要,也没必要根据自增id值来统计行数之类)MYSQL教程
条件3 innodb_autoinc_lock_mode设置为2MYSQL教程
先说结论:当innodb_autoinc_lock_mode设置为2时,所有insert种类的SQL都可以立马获得锁并释放,这时的效率最高.但是会引入一个新的问题:当binlog_format为statement时,这时的复制没法保证平安,因为批量的insert,比如insert ..select..语句在这个情况下,也可以立马获取到一大批的自增id值,不必锁整个表,slave在回放这个sql时必然会产生错乱.我们做个测试验证复制不是平安的.MYSQL教程
- 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的复制,必然出现主键冲突.MYSQL教程
- 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
总结:MYSQL教程
1 innodb row复制时,可将innodb_autoinc_lock_mode设置为2,这时可在所有insert情况下表获得最大并发度MYSQL教程
2 innodb statement复制时,可将innodb_autoinc_lock_mode设置为1,保证复制平安的同时,获得简单insert语句的最大并发度MYSQL教程
3 myisam引擎情况下,无论什么样自增id锁都是表级锁,设置innodb_autoinc_lock_mode参数无效(测试略)MYSQL教程
4 实际上提问者说到的在innodb引擎下自增id值作为主键的情况下,相比uuid或者自定义的主键,是可以提到插入速度的,因为innodb是主键聚集索引,实际的主键值必须依照主键顺序存取,那么自增id本身就是升序的,那么在插入数据时,底层就不必再做额外的排序操作,也减少了索引页分裂的次数,从而大大增加insert速度(除非其他方案也能保证主键完全自增)MYSQL教程
以上这篇浅谈innodb_autoinc_lock_mode的表现形式和选值参考办法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持维易PHP.MYSQL教程
欢迎参与《Mysql入门浅谈innodb_autoinc_lock_mode的表现形式和选值参考方法》讨论,分享您的想法,维易PHP学院为您提供专业教程。