5-1MySql事务隔离级别与锁机制（二）

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `balance` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of account
-- ----------------------------
BEGIN;
INSERT INTO `account` VALUES (1, 'LiLei', 450);
INSERT INTO `account` VALUES (2, 'hanmei', 1600);
INSERT INTO `account` VALUES (3, 'Lucy', 2000);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

MySql设置事务隔离级别

• sql8之前设置：set tx_isolation=’xxxx’

• sql8之后设置：set transaction_isolation=’xxxx’

  读未提交

• 1.打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值。set transaction_isolation=’read-uncommitted’

• 
• 2.客户端A的事务不提交，然后打开客户端B，设置同样的事务级别，更新表account，在事务B的修改下，id=1的记录的balance修改为350，事务B也不进行提交。
• 
• 3.然后在客户端A查询记录，可以看到客户端B修改后的数据，看到id=1的记录是事务B修改后的值350；
• 
• 4.客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据
• 
• 5.在客户端B回滚时，我们在客户端A执行语句update account set balance = balance - 100 where id =1，在客户端A查询id=1的banlance是350，那我们再次把id=1的banlance-100，应该是等于250，实际执行的结果是350，是因为最后修改的还是用数据库保存的值去修改，而不是用我们查询出来的值去修改。
• 
• 6.看着数据最终结果是一致的，但是我们可能在实际的代码中，是可能先查询到结果350，然后用350减去100的值250，然后直接把250的值设置更新，这样最后的结果就不一致了。
• 要想解决这个问题可以采用读已提交的隔离级别

读已提交

• 1.打开一个客户端A，并设置当前事务模式为read committed（读已提交），查询表account的所有记录。set transaction_isolation=’read-committed’
• 
• 2.事务A不提交，打开客户端B，修改account表,修改id=2的记录
• 
• 3.客户端B事务不提交，然后客户端A再去查询account的记录，这次，客户端A是查询不到客户端B的修改记录了(id=2的记录的balance还是1600)，解决了脏读问题。
• 
• 4.客户端B的事务提交
• 
• 5.客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题(即同一个事务，同一个语句，多次查询出来的结果不一致)，id=2提交修改的记录此时在事务A中查询出来了。
• 

可重复读

• 1.在客户端A，并设置当前事务模式为repeatable read，查询表account的所有记录。
• set transaction_isolation=’repeatable-read’
• 
• 客户端A不提交，在客户端B对account进行修改。对id=2的balance进行减100的操作，现在目标值是1400
• 
• 在客户端A查询表account的记录，与第一次的查询一致，没有出现不可重复读
• 
• 在客户端A，执行语句update account set balance = balance - 100 where id = 2。balance没有变成1500-100=1400的结果，而是用了客户端B修改后的值来减，也就是用了数据库最终保存的值来修改。可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本），insert、update和delete会更新版本号，是当前读（当前版本）。
• 
• 在客户端B插入一条记录
• 
• 然后在客户端A去查询account的记录，是查询不出来事务B新增的id=5的记录。
• 
• 验证幻读，可以看到直接修改id=5的记录，然后再次查询也能查询出来id=5的记录。
• 

串行化

• 1.客户端A，并设置当前事务模式为serializable，查询表account的初始值。
• set transaction_isolation=’serializable’;
• 
• 客户端B，并设置当前事务模式为serializable，其他客户端可以读取同一条记录
• 
• 客户端B修改id=1这条记录会被阻塞。
• 
• 客户端B修改id=2这条记录可以成功。
• 
• 串行模式下innodb的查询也会被加上行锁
• 如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围(就算该行数据还未被插入也会加锁，这种是间隙锁)都会被加锁。如果客户端B在该范围内插入数据都会被阻塞，所以就避免了幻读

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，间隙锁在某些情况下可以解决幻读问题。
account表的数据如下：

这里的间隙锁区间就有：（3，5）和（5，10）和（10，20）和（20，∞）

• 在客户端执行update account set name=’HaHa’ where id>8 and id <=18
• 
• 因为修改在的范围是id>8 &&id<=18,因为8落在（5，10）的区间，18落在（10，20）的区间，所以这里锁的范围是（5，20]之间，包括20。
• 验证在（5，20]能否修改数据。
• 
• 在客户端B对id=10的数据进行修改会阻塞，然后会返回错误。
• 
• 客户端B再去修改id=5的记录
• 
• 这是能修改成功的
• 间隙锁是在可重复读隔离级别下才会生效

  临键锁(Next-key Locks)

  临键锁是行锁与间隙锁的组合。像上面那个例子里的这个(5,20]的整个区间可以叫做临键锁

无索引行锁会升级为表锁

锁主要是加载索引上的，如果对非索引字段更新，行锁可能会变成表锁。

• 客户端A执行update account set balance=8000 where name=’LiLei’;
• 
• name不是索引字段，此时行锁会变成表锁。其他客户端对account的修改都会阻塞
• 客户端B想修改id=10的记录会被阻塞。
• 
• InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁
• 锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)

for update

锁定某一行用了for update，那其他的客户端只能读，任何的修改都会被阻塞。

• 客户端A，执行 select * from account where id=2 for update;
• 
• 客户端B去修改id=2的记录，此时会阻塞
• 

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了

行锁分析

show status like 'innodb_row_lock%';

锁优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
• 尽可能低级别事务隔离