先说概念性的东西
事务的四种隔离级别:
01:Read uncommitted(读未提交):最低级别,任何情况都会发生。
02:Read Committed(读已提交):可避免脏读的发生。
03:Repeatable read(可重复读):可避免脏读、不可重复读的发生。一般数据库默认级别
04:Serializable(串行化):避免脏读、不可重复读,幻读的发生。
脏读:事务A读取到了事物B更新的数据,然后B回滚
不可重复读:事务A多次读取同一数据,事务B在A读取过程中修改了并提交,导致A多次读取同一数据结果不一样
幻读:A操作数据库的过程中B插入了一条记录,当A执行完发现有一条记录没有处理,就像发生了幻觉。
不可重复读侧重于修改,幻读侧重于新增和删除。
实验准备数据:
CREATE TABLE `yunfei` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` int(11) DEFAULT NULL,
`value` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=utf8;
INSERT INTO `yunfei` (`id`, `type`, `value`) VALUES ('1', '1', 'aa');
INSERT INTO `yunfei` (`id`, `type`, `value`) VALUES ('2', '2', 'bb');
INSERT INTO `yunfei` (`id`, `type`, `value`) VALUES ('3', '3', 'cc');
INSERT INTO `yunfei` (`id`, `type`, `value`) VALUES ('4', '4', 'dd');
INSERT INTO `yunfei` (`id`, `type`, `value`) VALUES ('5', '7', 'ee');
INSERT INTO `yunfei` (`id`, `type`, `value`) VALUES ('6', '10', 'ff');
实验A:
sessionA sessionB
begin; begin;
select * from yunfei;
id type value
1 1 aa
2 2 bb
3 3 cc
4 4 dd
5 7 ee
6 10 ff
insert into yunfei(type,value) values ('9','ddddddd');
commit;
select * from yunfei;
id type value
1 1 aa
2 2 bb
3 3 cc
4 4 dd
5 7 ee
6 10 ff
insert into yunfei(type,value) values ('9','ddddddd');
[SQL]
[Err] 1062 - Duplicate entry '9' for key 'aa'
select * from yunfei;
id type value
1 1 aa
2 2 bb
3 3 cc
4 4 dd
5 7 ee
6 10 ff
update yunfei set value='ddddddd' where type=9;
[SQL]
Affected rows: 0
Time: 0.000s
select * from yunfei;
id type value
1 1 aa
2 2 bb
3 3 cc
4 4 dd
5 7 ee
6 10 ff
34 9 ddddddd
实验B
sessionA sessionB
begin; begin
select * from yunfei for update;
id type value
1 1 aa
2 2 bb
3 3 cc
4 4 dd
5 7 ee
6 10 ff
insert into yunfei(type,value) values ('9','ddddddd');
无法插入!会一直等待,直到session1 commit或rollback
insert into yunfei(type,value) values ('11','ddddddd');
无法插入!会一直等待,直到session1 commit或rollback
rollback;
begin;
select * from yunfei where 4<type<10 for update;
id type value
1 1 aa
2 2 bb
3 3 cc
4 4 dd
5 7 ee
6 10 ff
insert into yunfei(type,value) values ('9','ddddddd');
无法插入!会一直等待,直到session1 commit或rollback
insert into yunfei(type,value) values ('11','ddddddd');
[SQL]
Affected rows: 1
Time: 0.003s
结论(自己实验总结的,有错误请留言指正):
mysql innodb只在一定程度上避免了一些幻读,但明没有真正解决幻读。
快照读(普通select):
1:一个session永远读不到另外一个session提交的数据,避免了幻读
2:一个session在执行过程中另外一个session插入了一条记录并提交,那么在当前session重复插入的时候唯一索引冲突,明明没数据为什么冲突了?再次查询并无多数据,但是冲突提醒变相的出现了幻读。
3:一个session在执行过程中另外一个session插入了一条记录并提交,那么在当前session中修改另外一个session插入的数据提示会无数据受影响,但是再次查询多了条数据?出现幻读。
当前读(select for update):
1·:一个session在当前读过程中没有用到索引,其他session无法插入数据,update锁了全表,避免幻读。
2::一个session在当前读过程中用到了范围索引,那么其他session也会因为行锁(临键锁或者间隙锁)的情况无法插入,避免幻读。
mysql锁的几个实现原理:
意象锁:
当行锁存在时,表锁无法拿到。意象锁是一个标志,当标志位TRUE时表示已经有人拿到了意象锁,在这个时候就无法锁表了
临键锁(行锁默认算法):
当type=range使用范围查询索引update时候有数据命中,InnoDB会锁上Btree当前区间和下一个区间。
1、4、7、10 一共分为5个区间,当范围查询>5且<9的时候理论上只有7会上锁,实际上会锁(4,7]和(7,10]两个区间
间隙锁:
临键锁查询记录不存在的时候,会退化成间隙锁,锁到了一个闭区间,只存在RR隔离级别
1、4、7、10 一共分为5个区间,当范围查询>4且<6的时候没有索引命中,这个时候会锁上(4,7)区间。
记录锁:
当type=eq-ref 唯一性(主键、唯一)索引,条件为精确匹配,退化成记录锁