mysql间隙锁产生原因

最新推荐文章于 2024-06-27 19:38:12 发布

洒洒丁

最新推荐文章于 2024-06-27 19:38:12 发布

阅读量4.1k

点赞数 1

首先数据库不同隔离级别会产生一下不同结果：
脏读：读到别人没提交的数据
不可重复读：两次读到的数据不一致，同一个事物第一次读，第二个事物提交，第二次读到第二个事物额度数据
幻读：read repeatable也无法去除，read repeatable保证同一个事物多次读一致（通过version），但是提交的时候有可能被占用。

version就是mysql的隐藏列，相当于版本号，每个事物一个，这次事物多次查询过滤版本号比自己大的，详细可以事物版本号查询相关资料。

常见的幻读出现场景：事物1 insert一条数据前先检查一遍，不存在和自己相同就那个的主键1，于是提交事物。但是在检查之后提交之前这个时间间隙，有别的事物提交了这个主键1成功，于是在执行事务1的时候就会报错。

于是出现间隙锁，间隙锁为了解决幻读：原理是通过这次事物锁住一个范围的数据，比如我想插入id 4,5。实际可能锁了id2-10，因为mysql不知道该事物要插几个。

于是常见的高并发下间隙锁竞争激烈。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定值范围内的任何数据，在某些场景下这可能会针对性造成很大的危害。

同样高并发插入下会产生另一个问题：auto_increment锁机制

innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为；

通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡，

【0】我们先对insert做一下分类

　　首先insert大致上可以分成三类：
　　　　1、simple insert 如insert into t(name) values('test')
　　　　2、bulk insert 如load data | insert into ... select .... from ....
　　　　3、mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');

【1】innodb_autoinc_lock_mode 的说明

　　innodb_auto_lockmode有三个取值：
　　　　1、0 这个表示tradition 传统
　　　　2、1 这个表示consecutive 连续
　　　　3、2 这个表示interleaved 交错

【1.1】tradition(innodb_autoinc_lock_mode=0) 模式:

　　1、它提供了一个向后兼容的能力
　　2、在这一模式下，所有的insert语句("insert like") 都要在语句开始的时候得到一个
　　　表级的auto_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的，
　　一个事务可能包涵有一个或多个语句。
　　3、它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave
的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。
4、由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。

【1.2】consecutive(innodb_autoinc_lock_mode=1) 模式:

　　1、这一模式下去simple insert 做了优化，由于simple insert一次性插入值的个数可以立马得到
确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的
(它保证了基于语句复制的安全)
　　2、这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要
语句得到了相应的值后就可以提前释放锁

【1.3】interleaved(innodb_autoinc_lock_mode=2) 模式
　　1、由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是
对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

【2】如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。

　　由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最

　　好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。

最后以一个关于auto_increment 的例子来结束

例子：不要没事去更新一个auto_increment 列的值

第一步：重现一下场景

复制代码

create table t(x int auto_increment not null primary key);
insert into t(x) values(0),(null),(3);




select * from t;
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+

复制代码

第二步：重现一下引发问题的SQL

复制代码

update t set x=4 where x=1;
select * from t;
+---+
| x |
+---+
| 2 |
| 3 |
| 4 |
+---+

复制代码

第三步：重现一下总是的表现形式

insert into t(x) values(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

第四步：对问题的总结

　　执行完第一步的时候mysql知道下一个auto_increment值是4。

　　执行完第二步的时候mysql并不知道4已经被人为的占用了，所以执行第三步的时候就出错了。

最近在工作中遇到很多使用MySQL自带的autoincrement函数作为发号器，在实际使用中当并发比较小的时候还没有问题，一旦并发增加就会出现很多问题，特此进行如下总结。

一、自增配置

通过如下建表语句就可以完成自增的配置

CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、修改自增大小

通过如下sql可以自动生成数字：

insert into test_inc values();

当增加3行后表中数据如下：

+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+

CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8；

使用 alter table test_inc auto_increment=10;将自增修改成10后再次插入的数据为10.

+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 10 |
| 11 |
+----+

三、自增幅度

自增幅度通过auto_increment_offset和auto_increment_increment这2个参数进行控制

set global auto_increment_increment=2;
set global auto_increment_offset=2;
set session auto_increment_increment=2;
set session auto_increment_offset=2;

生成偶数的自增

set global auto_increment_increment=2;
set global auto_increment_offset=1;
set session auto_increment_increment=2;
set session auto_increment_offset=1;

生成奇数的自增

auto_increment_offset表示起始数字

auto_increment_increment表示调动幅度（即每次增加n个数字，2就代表每次+2）

四、获得最后一个数字

通过使用last_insert_id()函数可以获得最后一个插入的数字

select last_insert_id();

五、自增锁

如果存在自增字段，MySQL会维护一个自增锁，和自增锁相关的一个参数为（5.1.22版本之后加入）

innodb_autoinc_lock_mode：可以设定3个值，0，1，2

0：traditonal （每次都会产生表锁）

1：consecutive （会产生一个轻量锁，simple insert会获得批量的锁，保证连续插入）

2：interleaved （不会锁表，来一个处理一个，并发最高）

ps：这个参数值控制InnoDB引擎的设置，所有Myisam均为traditonal，每次均会进行表锁。但是Innodb会视参数不通二产生不通的锁。目前MySQL默认的配置为1。

六、自增的过程

第一种，插入空值的时候

当innodb_autoinc_lock_mode=0时

复制代码

1、申请AUTO_INC锁

2、得到当前的AUTO_INCREMENT值n，并加1

3、执行插入操作，并将n写入新增的对应字段中。

4、释放AUTO_INC锁。

复制代码

第二种，插入已经有值的自增

复制代码

1、插入第一条数据

2、如果失败流程结束

3、如果成功，申请AUTO_INC锁

4、调用set_max函数，修改AUTO_INCREMENT

5、语句结束，释放AUTO_INC锁

复制代码

七、存在的问题

1、复制的问题

在innodb_autoinc_lock_mode=2的时候，由于是来一个分配一个，故当replication模式为SBR的时候，如果发生Bulk inserts会在分配的时候向其他insert分配，就会出现主从不一致的情况，但是如果改为RBR就不会出现这种情况。

也就是说在RBR模式下，innodb_autoinc_lock_mode=2是安全的，其他情况还是建议设置为1.

2、load data的问题

当使用load data语句的时候，就算innodb_autoinc_lock_mode=1也会退化回0，这是因为为了保证数据的一致性。首先要说一下load data的执行过程，在主库上load data为原始SQL语句，而在从库上会先将文件传输过去在tmp下生成临时问题，然后在执行load data语句。为了保证主库和从库的自增ID的一致性，binlog中会有set insert_ID命令，标明这个load语句的第一行的自增ID值，这样在表锁的情况下，就可以保证一致性了。

八、insert的补充说明

复制代码

1.“INSERT-like”：

INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()

2.“Simple inserts”

就是通过分析insert语句可以确定插入数量的insert语句, INSERT, INSERT … VALUES(),VALUES()

3.“Bulk inserts”

就是通过分析insert语句不能确定插入数量的insert语句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA

4.“Mixed-mode inserts”

INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');

INSERT … ON DUPLICATE KEY UPDATE

复制代码

九、官方链接

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode

洒洒丁

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql间隙锁产生原因

首先数据库不同隔离级别会产生一下不同结果：脏读：读到别人没提交的数据不可重复读：两次读到的数据不一致，同一个事物第一次读，第二个事物提交，第二次读到第二个事物额度数据幻读：read repeatable也无法去除，read repeatable保证同一个事物多次读一致（通过version），但是提交的时候有可能被占用。version就是mysql的隐藏列，相当于版本号，每个事物一个，这次...
复制链接

扫一扫