MySQL隔离级别与MySQL的锁
MySQL隔离级别
测试隔离级别
数据库准备
数据库如下图所示，所有字段都是int(方便测试)，id为主键索引，name为普通索引（唯一索引），age没有索引
Read Uncommitted（读取未提交内容）
打开两个mysql终端，都设置session级别的隔离级别为读取未提交内容（本次会话有效）
set session transaction isolation level read uncommitted;如下表所示，
事务B在第4步进行了修改（还没有提交或者回滚）,
事务A在第5步就已经可以读取到修改（未提交或者回滚）的内容，出现了（脏读）
事务B在第6步回滚了， 撤销了修改操作，那么第5步读的就不正确了
步骤 | 事务A | 事务B |
|---|---|---|
1 | BEGIN; | |
2 | BEGIN; | |
3 | SELECT * from student WHERE id = 1; | |
4 | UPDATE student SET `name` = 11 WHERE id = 1; | |
5 | SELECT * from student WHERE id = 1; | |
6 | ROLLBACK; | |
7 | SELECT * from student WHERE id = 1; | |
8 | COMMIT; |
Read Committed（读取提交内容）
打开两个mysql终端，都设置session级别的隔离级别为读取提交内容（本次会话有效）
set session transaction isolation level read committed;如下表所示，
事务B在步骤4修改了数据
事务A在步骤5读取数据，（解决了脏读问题）
事务B在步骤6提交了修改内容
事务A在步骤7读取的数据和步骤5读取的数据不一样（出现了不可重复读问题）
步骤 | 事务A | 事务B |
|---|---|---|
1 | BEGIN; | |
2 | BEGIN; | |
3 | SELECT * from student WHERE id = 1; | |
4 | UPDATE student SET `name` = 11 WHERE id = 1; | |
5 | SELECT * from student WHERE id = 1; | |
6 | COMMIT; | |
7 | SELECT * from student WHERE id = 1; | |
8 | COMMIT; |
Repeatable Read（可重读）
打开两个mysql终端，都设置session级别的隔离级别为可重读（本次会话有效）
set session transaction isolation level repeatable read;下面的例子有点不恰当，下面的例子有点不恰当，下面的例子有点不恰当
如下表所示，
事务A在步骤3和步骤6读取的数据一样（解决了不可重复度）
事务B在步骤4插入主键为4的数据
事务A在步骤7插入主键为4的数据报错 （出现了幻读）
步骤 | 事务A | 事务B |
|---|---|---|
1 | BEGIN; | |
2 | BEGIN; | |
3 | SELECT * from student ; | |
4 | INSERT INTO `student` (`id`, `name`, `age`) VALUES (4, 4, 4) | |
5 | COMMIT; | |
6 | SELECT * from student ; | |
7 | INSERT INTO `student` (`id`, `name`, `age`) VALUES (4, 44, 44) 出错 |
Serializable（可串行化）
打开两个mysql终端，都设置session级别的隔离级别为串行化（本次会话有效）
set session transaction isolation level serializable;如下表所示，
事务B在步骤5遇到了阻塞，性能差
步骤 | 事务A | 事务B |
|---|---|---|
1 | BEGIN; | |
2 | BEGIN; | |
3 | SELECT * from student WHERE id = 1; | |
4 | SELECT * from student WHERE id = 1; | |
5 | UPDATE student SET `name` = 11 WHERE id = 1;（阻塞，直到事务A提交） | |
6 | COMMIT; | |
7 | COMMIT; |
MySQL的锁
innodb行锁，锁的是什么？
答：锁的是索引。有索引时锁索引，没有索引的时候锁表。
如下表所示，id有主键索引，name有唯一索引，age无索引
步骤 | 事务A | 事务B |
|---|---|---|
BEGIN; | ||
BEGIN; | ||
SELECT * from student WHERE id = 1 for update; | ||
SELECT * from student WHERE id = 2 for update; | ||
COMMIT; | ||
COMMIT; |
步骤 | 事务A | 事务B |
|---|---|---|
BEGIN; | ||
BEGIN; | ||
SELECT * from student WHERE age = 1 for update; | ||
SELECT * from student WHERE age = 2 for update; (阻塞)(阻塞)(阻塞) |
乐观锁和悲观锁
乐观锁
乐观锁与数据库无关
如下表所示，有一个字段是verison 版本号
###伪代码,无竞争逻辑
student = select * from student where id = 1;
currentVersion = student.version
update student set age = 11 , version = version + 1 where id = 1 and version = currentVersion ;
###伪代码,有竞争逻辑
student = select * from student where id = 1;
currentVersion = student.version
//此时中间有另一个客户端又修改了这条记录，version+1了
update student set age = 11 , version = version + 1 where id = 1 and version = currentVersion ;//这条修改失败，因为currentVersion已经过时，不存在
悲观锁
说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。
共享锁和排它锁
共享锁
select * from student WHERE id = 1 lock in share mode;步骤 | 事务A | 事务B |
|---|---|---|
BEGIN; | ||
BEGIN; | ||
SELECT * from student WHERE id = 1 lock in share mode; | ||
SELECT * from student WHERE id = 1 lock in share mode; | ||
COMMIT; | ||
COMMIT; |
步骤 | 事务A | 事务B |
|---|---|---|
BEGIN; | ||
BEGIN; | ||
SELECT * from student WHERE id = 1 lock in share mode; | ||
UPDATE student SET `name` = 22 WHERE id = 1;（阻塞） | ||
COMMIT; |
排它锁
增、删、改默认添加的是排它锁。
select * from student WHERE id = 1 for update;给一条记录上了排它锁后，其他事务不能给改条记录上共享锁和排它锁。
意向锁
意向锁是表级别锁，意向锁不是人为的，是数据库自动的。
以意向排它锁为例，当事务A给记录1上锁时，先获取表的意向排它锁，然后在给记录1上锁；此时事务B给表上表锁，先获取表的意向排它锁，然后在锁表，但是此时意向排它锁在被事务A获取，所示事务B锁表失败。