Mysql锁

1.Mysql索引

1.1为什么索引结构用B tree

索引本身也是以文件的形式存储在磁盘上，索引查找过程中存在I/O消耗，采用B tree结构可以减少I/O。

1.1.1 磁盘存取原理

局部性原理与磁盘预读：当一个数据被用到时，其附近的数据也很快会被用到。
预读的长度一般为页的整数倍，主存和磁盘以页为单位交换数据。

磁盘和磁头构成了存取物理结构。同心圆环是磁道，磁道被划分成一个个段，叫扇区，扇区是最小存储单元。
i/o读取数据的过程：磁盘旋转到指定扇区，磁头移动到指定磁道。
所以减少磁盘旋转或者减少磁头移动就能减少i/o。
1.1.2 B-/+Tree索引的性能分析
B Tree利用局部性原理可减少i/o次数。
根据B Tree定义可知，检索一次最多需要访问h个节点，h-1次i/o，h是树高，mysql设计者巧妙的将一个节点大小设置为等于一个页大小，每次新建节点都申请一个页的空间，这样就保证一个节点物理上也存储在一个页，这样每个节点只需要一次i/o就可以完全载入。

参考： MySQL索引背后的数据结构及算法原理.

1.2 Mysql索引

1.2.1 MyISAM索引实现
非聚集索引。索引结构只存储数据地址，不存储真正的数据。

1.2.2 InnoDB索引实现
使用聚集索引。
主键索引和数据在一起。

普通索引存储的是主键索引。

2.Mysql事物的隔离级别

2.1 READ UNCOMMITTED (未提交读)

存在脏读问题
存在不可重复读问题
存在幻读问题

2.2 READ COMMITTED (提交读)

只读已经事物提交的数据，不存在脏读。
存在不可重复读问题
存在幻读问题

2.3 REPEATABLE READ (可重复读)

是mysql默认的隔离级别
mvcc解决幻读

2.4 SERIALIZABLE (可串行化)

读写串行化，效率太低，没人使用。

3MVCC

3.1 MVCC原理

只在READ COMMITTED (提交读)、REPEATABLE READ (可重复读)下工作。不兼容其他隔离级别。
mvcc特性：读不加锁，读写不冲突。
原理：在数据表后加三个隐藏列，事物版本、删除版本，回滚指针。每开启一个事物版本号自增1；
这里假设这三个隐藏字段的名字是def_create_version，db_roll_ptr，def_delete_version。

下面看下这两个默认字段在增删改查中的用处。假设当前事物版本为current_version
select: 查询会自动加where条件 and current_version>=def_create_version and (def_delete_version = ‘undefined’ or def_delete_version>current_version )
delete: 更新def_delete_version = current_version
update: 先执行delete再执行insert
insert: 插入数据并且默认def_create_version = current_version and def_delete_version = ‘undefined’

3.2 read view判断当前版本数据可见性

RC和RR都采用MVCC实现数据可见新，实现效果的不同是用read view来实现的。RC总是可以看到已提交事物的快照，RR看到的总是事物开始时的快照。

在innodb中，创建事物的时候会将系统中活跃的事物创建一个副本，叫做read view，当前事物版本不在read view中。read view中的事物按照版本号排列，最小的是low_limit_id，最大的是up_limit_id。当前事物版本号为cur_id，则版本号为data_id的数据行是否可见呢，可见性如下图：

• data_id < low_limit_id，说明data_id数据行在当前事物之前就已经commit，所以可见。
• data_id > up_limit_id，说明data_id数据行所在事物在当前事物之后才开启，所以不可见。这里说的不可见是当前版本不可见，但是该数据行可能存在可见的版本，怎么找那个可见版本呢？在db_roll_ptr中取出最新的undo-log的版本号赋值给data_id，重新判断。
• low_limit_id <= data_id <= up_limit_id，cur_id也在这个范围但是不在read view中。read view中的事物不可见，当前版本可见。所以如果data_id=cur_id则可见，否则不可见。

在RC和RR下，read view的可见性判断依据都一样，如上。不一样的是创建read view 的时机。

• RC，每次查询的时候重新创建read view。这样就可以根据当前的全局事物链表创建read view，实现RC。
• RR, 在创建事物的时候生成read view，一直维持到事物结束。

参考：
轻松理解MYSQL MVCC 实现机制.
MySQL数据库事务各隔离级别加锁情况.

4 Mysql中的锁

4.1 锁类型

• 共享锁（S）:锁住一行，阻止其他事物获得相同数据集的排他锁
• 排他锁（X）:锁住一行，阻止其他事物获得相同数据集的共享锁和排他锁

为了实现表锁和行锁共存，InnoDB内部还使用了意向锁，这两种意向锁都是表锁。

• 意向共享锁（IS）:获得数据行的S锁前要先获得表的IS锁
• 意向排他锁（IX）:获得数据行的X锁前要先获得表的IX锁

兼容矩阵

项目	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突		冲突
S	冲突	冲突
IS	冲突

为什么要使用意向锁？
在没有意向锁情况下，事物T1申请了行排他锁，如果事物T2要申请表排他锁，就要遍历表的所有锁看有没有行排他锁，这是耗费性能的。有了意向锁，判断是否有意向排他锁就可以了。

意向共享锁可以多个并存，意向排他锁只能有一个。

在兼容矩阵中，为什么IX和IX是兼容的？
因为IX存在的条件是表中有行拍他锁，IX存在的目的是阻止其他事物申请表排他锁，但是不组织其他事物申请行排他锁。
所以，当表中存在IX锁，在申请行排他锁时，会先申请IX锁并且申请成功
但是如果申请表排他锁则申请失败。

什么SQL加什么锁？
意向锁是InnoDB自动加的，无需用户干预。
update、delete、insert，InnoDB自动加排他锁。
非显式加锁的select，InnoDB不会加锁。

select加锁：
共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。

insert加锁：
insert会在insert所在行加排他锁，这是一个record lock。
不过，在insert之前会先加一种锁，官方称为insertion intention gap lock，也就是意向gap锁。意向gap锁可以同时存在多个，两个事物可以给相同的gap加意向gap锁，只要插入的记录唯一键不同，两个事物的insert都会成功。
为什么需要意向gap锁？
（个人理解）如果没有意向gap锁，insert时需要对比所有未提交的insert记录，看是不是唯一键冲突。有gap锁，则只需要和获得这个gap锁的事务比较唯一键。

4.2 Record lock、gap lock、next-key lock

三种类型的排他锁，锁定数据范围不同：
Record lock:只锁一行数据，按唯一键加锁
gap lock：锁住两个索引之间的间隙，但不包含记录本身
只有两种情况才有单纯的gap锁：
next-key lock：Record lock+gap lock。

举个例子来看这三种排他锁.如下表，id为PK。

id	name
10	a
20	b
50	c

Record Lock：select * from tab where id = 10 for update; //对id=10单行进行加锁
Gap Lock锁范围：（- ∞，10）（10，20）（20，50）（50，+∞）
Next-Key Lock锁范围：（- ∞，10）[10，20）[20，50）[50，+∞）

只有两种场景会有单纯的gap lock:

• update t set name =‘d’ where id > 60; 锁定 (60,+∞),这里只有gap锁，没有索引本身的锁。
• update t set name =‘d’ where id < 1; 锁定 (- ∞,1),这里只有gap锁，没有索引本身的锁。

4.3 锁都加在哪些资源上

• 表
• 索引（主键索引、唯一索引、普通索引）
• 索引之间的gap

为什么任何session都insert不进去？
可能是，表数据少，有一个事物在执行update时全表扫描，把表锁住了。

这种逻辑：先select再update，有什么问题？
select是快照读，update是当前读，可能会出现幻读导致数据不一致。
解决办法：

• 乐观锁 update set version=v+1 where version=v
• 悲观锁 select * from t for update

4.4 各种条件下的加锁

给定SQL：delete from t1 where id = 10;
那么，当前的事物隔离级别RC、RR。id的索引情况。有如下讨论：

-	RC	RR
主键索引	只需要将主键上，id = 10的记录加上X锁即可	只需要将主键上，id = 10的记录加上X锁即可
唯一索引	此组合中，id是unique索引，而主键是name列。首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。	此组合中，id是unique索引，而主键是name列。首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。
普通索引	id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁	与左图的唯一不同在于：在主键上加了gap锁。
非索引	进行全部扫描。聚簇索引上所有的记录，都被加上了X锁	全表记录加X锁，聚簇索引的每个gap都加gap锁

参考原文:Mysql加锁过程详解.