关于索引结构,前面有一篇文章:MySQL索引原理总结
MVCC
MySQL InnoDB存储引擎,实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) 读不加锁,读写不冲突。(与MVCC相对的,是基于锁的并发控制,Lock-Based Concurrency Control)。
读操作分:快照读 (snapshot read)与当前读 (current read)。
快照读,读取的是记录的可见版本 (有可能是历史版本),不加锁。
当前读,读取的是记录的最新版本,并且,返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。
- 快照读:简单的select操作,属于快照读,一般不加锁。
- select * from table where ?;
- 当前读:插入/更新/删除操作(中包含一次读操作,读当前最新版本)或显式加锁,需要加锁。
- select * from table where ? lock in share mode;
- select * from table where ? for update;
- insert into table values (…);
- update table set ? where ?;
- delete from table where ?;
所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。
一个更新操作在数据库中的执行流程:
MySQL Server会根据where条件,读取第一条满足条件的记录,然后InnoDB引擎会将第一条记录返回,并加锁 (current read)。MySQL Server收到这条加锁的记录之后,会再发起一个Update请求,更新这条记录。一条记录操作完成,再读取下一条记录,直至没有满足条件的记录为止。因此,Update操作内部,就包含了一个当前读。Delete操作也一样。Insert操作会稍微有些不同,Insert操作可能会触发Unique Key的冲突检查,也会进行一个当前读。
2PL:Two-Phase Locking
加锁阶段只加锁,解锁阶段只解锁。
Isolation Level
MySQL/InnoDB定义的4种隔离级别:
Read Uncommited
Read Committed (RC)
select @@global.tx_isolation;
- 针对当前读,RC隔离级别保证对读取到的记录加锁 (记录锁),存在幻读现象。
Repeatable Read (RR)
- 针对当前读,RR隔离级别保证对读取到的记录加锁 (记录锁),同时保证对读取的范围加锁,新的满足查询条件的记录不能够插入 (间隙锁),不存在幻读现象。
Serializable
从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读,所有的读操作均为当前读,读加读锁 (S锁),写加写锁 (X锁)。
- Serializable隔离级别下,读写冲突,因此并发度急剧下降,在MySQL/InnoDB下几乎不会使用。
几种条件下加锁情况
- SQL1:select * from t1 where id = 10;
- SQL2:delete from t1 where id = 10;
- 组合一:id列是主键,RC隔离级别
- 组合二:id列是二级唯一索引,RC隔离级别
- 组合三:id列是二级非唯一索引,RC隔离级别
- 组合四:id列上没有索引,RC隔离级别
- 组合五:id列是主键,RR隔离级别
- 组合六:id列是二级唯一索引,RR隔离级别
- 组合七:id列是二级非唯一索引,RR隔离级别
- 组合八:id列上没有索引,RR隔离级别
- 组合九:Serializable隔离级别
两条sql在几种组合条件下的加锁情况:
组合一:id主键+RC
组合二:id唯一索引+RC
首先会将unique索引上的id=10索引记录加上X锁,同时,会根据读取到的name列,回主键索引(聚簇索引),然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。
为什么主键索引也要锁?别的根据主键修改感知不到。
组合三:id非唯一索引+RC
首先,id列索引上,满足id = 10查询条件的记录,都加锁。同时,这些记录对应的主键索引上的记录也都加锁。
组合四:id无索引+RC
由于id列上没有索引,因此只能走聚簇索引,进行全表扫描。从图中可以看到,满足删除条件的记录有两条,但是,聚簇索引上所有的记录,都被加上了X锁。无论记录是否满足条件,全部被加上X锁。
在实际的实现中,MySQL有一些改进,在MySQL Server过滤条件,发现不满足后,会调用unlock_row方法,把不满足条件的记录放锁 (违背了2PL的约束)。这样做,保证了最后只会持有满足条件记录上的锁,但是每条记录的加锁操作不会能省略。
组合五:id主键+RR
与组合一:[id主键,Read Committed]一致。
组合六:id唯一索引+RR
与组合二:[id唯一索引,Read Committed]一致。
组合七:id非唯一索引+RR
相对于组合三:[id列上非唯一锁,Read Committed]多了GAP锁,保证其他的事务不会插入新的满足条件的记录。
组合八:id无索引+RR
首先,聚簇索引上的所有记录,都被加上了X锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了GAP锁。
在这种情况下,这个表上,除了不加锁的快照度,其他任何加锁的并发SQL,均不能执行,不能更新,不能删除,不能插入,全表被锁死。
这个情况下,MySQL也做了一些优化,就是semi-consistent read。semi-consistent read开启的情况下,对于不满足查询条件的记录,MySQL会提前放锁。semi-consistent read生效条件:要么是read committed隔离级别;要么是Repeatable Read隔离级别,同时设置了 innodb_locks_unsafe_for_binlog 参数。但是semi-consistent read本身也会带来其他问题,不建议使用。
组合九:Serializable
对于SQL2:delete from t1 where id = 10; 来说,Serializable隔离级别与Repeatable Read隔离级别完全一致。
Serializable隔离级别,影响的是SQL1:select * from t1 where id = 10; 这条SQL,在RC,RR隔离级别下,都是快照读,不加锁。但是在Serializable隔离级别,SQL1会加读锁,也就是说快照读不复存在,MVCC并发控制降级为Lock-Based CC。
一个复杂条件的例子
- Index key:pubtime > 1 and puptime < 20。此条件,用于确定SQL在idx_t1_pu索引上的查询范围。
- Index Filter:userid = ‘hdc’ 。此条件,可以在idx_t1_pu索引上进行过滤,但不属于Index Key。
- Table Filter:comment is not NULL。此条件,在idx_t1_pu索引上无法过滤,只能在聚簇索引上过滤。
假设RR隔离级别
在Repeatable Read隔离级别下,由Index Key所确定的范围,被加上了GAP锁;Index Filter锁给定的条件 (userid = ‘hdc’)何时过滤,视MySQL的版本而定,在MySQL 5.6版本之前,不支持Index Condition Pushdown(ICP),因此Index Filter在MySQL Server层过滤,在5.6后支持了Index Condition Pushdown,则在index上过滤。若不支持ICP,不满足Index Filter的记录,也需要加上记录X锁,若支持ICP,则不满足Index Filter的记录,无需加记录X锁 (图中,用红色箭头标出的X锁,是否要加,视是否支持ICP而定);而Table Filter对应的过滤条件,则在聚簇索引中读取后,在MySQL Server层面过滤,因此聚簇索引上也需要X锁。最后,选取出了一条满足条件的记录[8,hdc,d,5,good],但是加锁的数量,要远远大于满足条件的记录数量。
参考:
http://blog.csdn.net/yanzi9016
何登成老师的原文:http://hedengcheng.com/?p=771