mysql的锁分析

1、MVCC：Snapshot Read vs Current Read

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)
(注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC读不加锁，读写不冲突。
在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

在MVCC并发控制中，读操作可以分成两类：快照读 (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锁 (排它锁)

update语句执行过程

一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，
然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。
待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。
一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。
因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。
Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，
因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，
做一些DML操作；然后在读取下一条加锁，直至读取完毕。

2、Clustered and Secondary Indexes(聚簇索引)

Clustered and Secondary Indexes
1、每个InnoDB表都有一个称为聚簇索引的特殊索引， 其中存储了行的数据。通常聚簇索引与主键同义。
为了优化查询，插入和其他数据库操作性能，您必须了解如何InnoDB使用聚簇索引来优化每个表的最常见查找和DML操作。
2、当数据表有定义主键时，InnoDB使用主键作为聚簇索引。一般建议每张表创建一个主键。
3、如果没有定义主键，MySQL会找到第一个UNIQUE索引，其中所有键列都是NOT NULL，而InnoDB将它用作聚簇索引。
4、如果既没有主键也没有UNIQUE索引，InnoDB会在内部生成一个名称为GEN_CLUST_INDEX的隐藏的包含行id的聚簇索引。

通过聚簇索引查找数据效率较高是因为聚簇索引包含了所有的行数据，在索引搜索时直接指向数据页。
如果表很大，与使用与索引记录不同的页面存储行数据的存储组织相比，聚簇索引体系结构通常会节省磁盘I / O操作。

Secondary Indexes Relate to the Clustered Index
除聚簇索引之外的所有索引都称为辅助索引(二级索引)。在InnoDB，辅助索引中的每个记录都包含该行的主键列，二级索引指定的列。
InnoDB使用此主键值来搜索聚簇索引中的行数据。如果主键很长，则二级索引使用更多空间，因此使用短主键是有利的。

传统RDBMS加锁的一个原则，就是2PL (二阶段锁)：Two-Phase Locking。
相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。
2PL就是将加锁/解锁分为两个完全不相交的阶段。
加锁阶段：只加锁，不放锁。
解锁阶段：只放锁，不加锁。
加锁过程

Isolation Level

1、Read Uncommited
基本不会使用
2、Read Committed (RC)
针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
3、Repeatable Read (RR)
针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
4、Serializable
从MVCC并发控制退化为基于锁的并发控制(Lock-Based Concurrency Control)。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。
Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

一条简单sql的加锁分析

1、select * from table where id=10;
     不加锁。因为MySQL是使用多版本并发控制的，读不加锁。
2、delete from table where id=10;
     1、id是否是主键?
     2、id不是主键上是否有索引?
     3、id上索引是否是唯一索引?
     4、数据库隔离级别是什么?
     5、sql执行计划是什么?(有索引一定会走索引吗?)
假设有RR和RC两种隔离级别,有索引时一定会走索引
     1、RC隔离界别，id是主键
     2、RC隔离界别，id不是主键，有索引，唯一索引
     3、RC隔离界别，id不是主键，有索引，非唯一索引
     4、RC隔离界别，id不是主键，没有索引
     5、RR隔离界别，id是主键
     6、RR隔离界别，id不是主键，有索引，唯一索引
     7、RR隔离界别，id不是主键，有索引，非唯一索引
     8、RR隔离界别，id不是主键，没有索引

1、RC隔离级别，id是主键，在id=10这条记录上加X锁

2、RC隔离界别，id不是主键，有索引，唯一索引，
在id=10的唯一索引上加X锁，在id=10的索引对应的聚簇索引记录上加X锁，也就是说需要加两个X锁，
之所以在唯一索引记录对应的聚簇索引记录上加X锁，是为了防止在删除id=10的同时，有sql更新id=10的聚簇索引对应的记录。
例如：update t1 set id = 100 where name = ‘d’;
此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

3、RC隔离界别，id不是主键，有索引，非唯一索引，若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

4、RC隔离界别，id不是主键，没有索引，若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束 。

5、RR隔离界别，id是主键，在id=10这条记录上加X锁。

6、RR隔离界别，id不是主键，有索引，唯一索引，两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。

7、RR隔离界别，id不是主键，有索引，非唯一索引，id列上有一个非唯一索引，
对应SQL：delete from t1 where id = 10;
首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

8、RR隔离界别，id不是主键，没有索引。首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。这个示例表，只有6条记录，一共需要6个记录锁，7个GAP锁。这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。semi-consistent read如何触发：要么是read committed隔离级别；要么是Repeatable Read隔离级别，同时设置了 innodb_locks_unsafe_for_binlog 参数。