首先数据库不同隔离级别会产生一下不同结果:
脏读:读到别人没提交的数据
不可重复读:两次读到的数据不一致,同一个事物第一次读,第二个事物提交,第二次读到第二个事物额度数据
幻读: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