MySQL之Lock探索(一)

背景: 开发人员天天没事就把beta环境的数据库玩成死锁/metadata_lock, 然后无辜的和我说他也不知道为什么数据库就没响应了

原因: 数据库的锁本来也是一个可以, 并且需要了解的比较透彻的知识点

主要内容:  MySQL的锁类型, 锁的方式等

相关内容:  隔离级别, MVCC等

讨 论 范 围 仅 限 于 I n n o D B, 环境:MySQL-5.6.26

------------------------------------------------------------------------------正文------------------------------------------------------------------------------------------------

锁的基本类型:

说到数据库的锁, 在基本数据库原理里面, 锁的目的是用来保证数据的正确性, 不会丢失修改, 不会读到 '脏' 数据, 写入的数据可以重复读, 因此可以把锁看做一个数据的标示: 这一行数据我要用, 其他人不准碰

<<数据库基本原理>>里面描述了锁的基本类型(当年上大学用的那个版本, 没记错的话_(:з」∠)_), 分作X(写)锁和S(读)锁,这两种基本锁的关系如下表



 常用的关系型数据库使用的锁机制, 都是遵循了上面这两种基本锁, 同时还根据实际情况进行了一些其他的改进;

隔离级别:

有四种隔离级别, 不同的隔离级别会影响到锁的使用:

READ UNCOMMITTED

READ UNCOMMITTED是限制性最弱的隔离级别，因为该级别忽略其他事务放置的锁。使用READ UNCOMMITTED级别执行的事务，可以读取尚未由其他事务提交的修改后的数据值，这些行为称为“脏”读。
这是因为在Read Uncommitted级别下，读取数据不需要加S锁，这样就不会跟被修改的数据上的X锁冲突。
比如，事务1修改一行，事务2在事务1提交之前读取了这一行。如果事务1回滚，事务2就读取了一行没有提交的数据，这样的数据我们认为是不存在的。

READ COMMITTED

READ COMMITTED(Nonrepeatable reads)是SQL Server默认的隔离级别。该级别通过指定语句不能读取其他事务已修改但是尚未提交的数据值，禁止执行脏读。
在当前事务中的各个语句执行之间，其他事务仍可以修改、插入或删除数据，从而产生无法重复的读操作，或“影子”数据。
比如，事务1读取了一行，事务2修改或者删除这一行并且提交。如果事务1想再一次读取这一行，它将获得修改后的数据或者发现这一样已经被删除，因此事务的第二次读取结果与第一次读取结果不同，因此也叫不可重复读。

REPEATABLE READ

REPEATABLE READ是比READ COMMITTED限制性更强的隔离级别。该级别包括READ COMMITTED，并且另外指定了在当前事务提交之前，其他任何事务均不可以修改或删除当前事务已读取的数据。
并发性低于 READ COMMITTED， 因为已读数据的共享锁在整个事务期间持有，而不是在每个语句结束时释放。
比如，事务1读取了一行，事务2想修改或者删除这一行并且提交，但是因为事务1尚未提交，数据行中有事务1的锁，事务2无法进行更新操作，因此事务2阻塞。
如果这时候事务1想再一次读取这一行，它读取结果与第一次读取结果相同，因此叫可重复读。

SERIALIZABLE 

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。
该级别包括REPEATABLE READ，并增加了在事务完成之前，其他事务不能向事务已读取的范围插入新行的限制。
比如，事务1读取了一系列满足搜索条件的行。事务2在执行SQL statement产生一行或者多行满足事务1搜索条件的行时会冲突，则事务2回滚。
这时事务1再次读取了一系列满足相同搜索条件的行，第二次读取的结果和第一次读取的结果相同。

MVCC:

为了保证并发性能, 几乎所有的关系型数据库都实现了MVCC--基于多版本的并发控制协议 (Multi-Version Concurrency Control)

MVCC中, 数据的读取分为了快照读和当前读, 如 果没有带上特殊的参数和命令, 常用的select基本都属于快照读 , 读取的数据都是某个时间点'快照', 有可能是最新的数据, 也有可能是历史数据;

PS: 快照读可以参考oracle的经典错误ORA-01555: snapshot too old

当前读就包括了常见的insert, update, delete, select * from tb for update 等;

MySQL的聚簇索引, 二级索引:

InnoDB的数据组织方式是索引组织表, 具体的描述参考官方文档http://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html(有生之年系列,以后有空看看补一下)
这里贴出这边用到的一些点:
一般来说, 如果用户指定了主键PK, 那么这个PK就是这个表的聚簇索引, 除了PK的一般索引都是二级索引;
每一个二级索引中都包含了某一行数据锁对应的PK(聚簇索引)的值or引用;
( 这 种描述方法 不 确 定是否正确 ,附上 英文原文: In  InnoDB , each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.  )
MySQL的锁标记位是在索引的结构里面;(所以不走索引的时候, 会lock表的所有行;  P S:不是锁表, 是锁住表的所有行_(:з」∠)_ )

二阶段锁:

Two-Phase Locking, 文如其意, 加锁的操作被分为了两个阶段，以begin-->SQL-->end这种流程来看待一个事务的话, 在这个事务的生命周期内, 加锁这个操作是在SQL执行时进行的;
即执行一条SQL 语 句, 那么就对这个语句加对应的锁,执行多个语句, 那么就根据语句, 一次一次的上锁, 不是一次全部锁住;

简单的试验, 结合MVCC的快照读一起,使用for update进行当前读也会有同样的效果,隔离级别RR(REPEATABLE-READ) :


可以看到,左边的session1更早的开启了事务(begin), 但是session2插入的数据在session1中可以读取到, 图中的箭头代表了这些语句的实际执行顺序;
可见在进行快照读的时候, 采用的时间点是以语句实际执行的时候为准, (当前读加上写锁与快照读一致)


MySQL除了X锁和S锁以外, 还有什么锁?

MySQL的锁, 除了基础的X锁和S锁以外, 还有GAP锁和next key锁;

GAP锁是MySQL处于RR级别, 防止'幻读'的关键, GAP不同于基础的X锁和S锁, 并非加持于数据行本身, 而是加持在数据行与数据行之间的,
GAP锁会防止在对指定的行加持锁以后, 再有其他的事务在某行数据的前后/某几行数据的前后或者中间插入新的数据行, 从而导致同样的SQL语句在同一个事务的前后两次查询中出现不同的结果;


---------------------------- --------- -----------------------------------------分割线--------------------------------------------------- -------------------------------------- ---

实在是写不动了， 实际的案例就交给下一次吧......_(:з」∠)_......