mysql innodbautoinclockmode_innodb_autoinc_lock_mode的表现形式和选值参考

最新推荐文章于 2024-08-14 09:07:25 发布
最新推荐文章于 2024-08-14 09:07:25 发布
阅读量243 收藏
点赞数
文章标签： mysql innodbautoinclockmode
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_32694271/article/details/113907945
版权

前提条件，percona 5.6版本，事务隔离级别为RR 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 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;

+----+------+

| 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 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时必然会产生错乱。我们做个测试验证复制不是安全的。

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的复制，必然出现主键冲突。

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速度(除非其他方案也能保证主键完全自增)

杨紫她爹
关注
关于MySQL innodb_autoinc_lock_mode介绍
09-09
下面小编就为大家带来一篇关于MySQL innodb_autoinc_lock_mode介绍。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
MySQL innodb_autoinc_lock_mode 详解
weixin_33901926的博客
02-03 1146
innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为； 通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡 【0】我们先对insert做一下分类 　　首先insert大致上可以分成三类： 　　　　1、simple insert 如insert into t(name) values('test') 　　...
mysql 自增长 innodb_autoinc_lock_mode
weixin_34130389的博客
06-11 196
在MySQL 5.1.22之前，innodb使用一个表锁解决自增字段的一致性问题（内部是用一个计数器维护，每次自增时要加表锁），如果一行一行的插入数据则没有什么问题，但是如果大量的并发插入就废了，表锁会引起SQL堵塞，不但影响效率，而且可能会瞬间达到max_connections而崩溃。 在 5.1.22之后，innodb使用新的方式解决自增字段一致性问题，对于可以...
mysql AUTO_INC 是否锁_MySQL->>innodb_autoinc_lock_mode参数控制auto_increment 插入数据时相关锁的模式...
weixin_29163581的博客
02-03 104
转自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2141640/----------------------------------------------------------innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为；通过对它的设置可以达到性...
MySQL系统变量innodb_autoinc_lock_mode
最新发布
zxrhhm的博客
08-14 950
`innodb_autoinc_lock_mode` 是 MySQL InnoDB 存储引擎的一个系统变量，它控制着自增（AUTO_INCREMENT）值的锁定机制。
Percona 5.6中的innodb_autoinc_lock_mode解析与事务影响
"本文主要探讨了InnoDB存储引擎中的innodb_autoinc_lock_mode参数对自动递增锁（auto-increment lock）的影响，以及在不同模式下的表现和选择参考方法。文章通过实例展示了在percona 5.6版本，事务隔离级别为可重复...
mysql innodb 自增_MySQL 表的自增字段 预判功能innodb_autoinc_lock_mode
weixin_35063099的博客
02-01 284
在5.1.22之后，innodb对于可以预判行数的insert语句，innodb使用一个轻量级的互斥量，而不是表级锁。但有可能会造成自增字段的值不连续。注：如果使用新的自增互斥方式，对于replication应该避免使用INSERT...ONDUPLICATEKEYUPDATE语句。设置新自增互斥方式：通过配置选项：innodb_autoinc_lock_mode：调整锁策略：inno...
mysql innodbautoinclockmode_关于MySQL innodb_autoinc_lock_mode介绍
weixin_39933414的博客
02-03 67
innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为；通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡【0】我们先对insert做一下分类首先insert大致上可以分成三类：1、simple insert 如insert into t(name) values('test')2、bulk insert 如load...
innodb_autoinc_lock_mode
congwan1707的博客
09-26 473
innodb_autoinc_lock_mode 有三种值0 1 2 一般默认的为1 innodb_autoinc_lock_mode=0 这种为传统模式，所有的insert-like语句都会持有table-level A...
mysql innodbautoinclockmode,MySQL innodb_autoinc_lock_mode的分析
weixin_42447265的博客
03-17 229
2018即将结束。今天的雪来的那么突然，虽然寒冷，但是又让人充满兴奋，下午带娃出去门口看雪，剩下的时间在家整理下文档，准备今天发出2018年的最后一篇文章。2018年是成长和收获的一年，今年的工作格外的忙碌，生活也充实，同时业余时间DBARUN社区共分享了15篇文章，当然15篇不算多，但是都是在工作和娃的间隙中整理和分享出来的，希望2019能继续分享出更多的文章给大家吧，借用马总(马明哲)一句话：...
mysql lock mode_MySQL innodb_autoinc_lock_mode设置
weixin_33220713的博客
01-19 238
insert 语句分类insert 语句分三种类型：simple insert, bulk insert, mixed insertsimple insertinsert 时可以预先知道插入的行记录数量。例如insert into t values(a), (b), 这个语句插入时，mysql就可以预先知道插入的行记录数量为2.insert ... on duplicate...语句不属于simp...
MySQL innodb_autoinc_lock_mode设置
corleone_4ever的博客
06-12 4272
转https://www.jianshu.com/p/f72b7bfa7467 https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html 国内的文章看得真是蛋疼，跟官网的文档意思差别太大。 insert 语句分类 insert 语句分三种类型：simple insert, bulk insert, mixed insert simple insert insert 时可以预先知道插入的行记录数量。例如
mysql insert into select如何提升性能_MySQL自增列锁参数优化——innodb_autoinc_lock_mode
weixin_39654823的博客
12-05 568
概述今天主要讲讲innodb_autoinc_lock_mode这个参数。innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为，我们可以通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡官网：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-...
mysql auto_inc_MySQL innodb_autoinc_lock_mode 详解
weixin_42318815的博客
02-01 203
innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为；通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡【0】我们先对insert做一下分类首先insert大致上可以分成三类：1、simple insert 如insert into t(name) values('test')2、bulk insert 如lo...
mysql lock_mode_关于MySQL innodb_autoinc_lock_mode介绍
weixin_29148445的博客
01-19 111
innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为；通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡【0】我们先对insert做一下分类首先insert大致上可以分成三类：1、simple insert 如insert into t(name) values('test')2、bulk insert 如load...
mysql lock_mode_MySQL innodb_autoinc_lock_mode的分析
weixin_39843431的博客
02-01 139
之前由于业务变更，我在使用pt的OSC工具在对一个线上不是很繁忙的数据库做变更时，发现每次都是产生了锁等待，后面排查发现是和OSC的bulk insert和autoinc lock有关，后面大家也对innodb_autoinc_lock_mode这个参数的锁的行为做过一些讨论。本篇文档就为大家介绍下MySQL的innodb_autoinc_lock_mode锁行为机制，以及不同设置下性能的分析。首...
聊聊Innodb中的innodb_autoinc_lock_mode
爱琴孩的博客
06-17 1845
经过上篇文章的介绍，我们知道在 MySQL 中，主键索引就是聚簇索引，MySQL 表中的数据是根据主键值聚集在一起的，聚簇索引是一棵 B+Tree，这棵树中的数据是有序的。所以，如果我们使用 UUID 字符串作为主键，那么就会导致每次数据插入的时候，都需要在 B+Tree 中寻找到适合它自己的位置，找到之后就要挪动后面的节点（就像在数组中插入一条记录），挪动后面的节点，就有可能涉及到页分裂，插入效率就会降低。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值