Oracle 和 MySQL 锁行为异同分析

数据库业界，按照从低到高将隔离级别分为：读未提交、读已提交、可重复度和可串行化，每个隔离级别按照在该级别下禁止发生的异象来定义。这些异常现象包括：

·脏读，指一个事务在执行过程中读到并发的、还没有提交的事务所修改的内容；

·不可重复读，指在同一个事务中，先后两次读到的同一条记录的内容发生了变化；

·幻读，指在同一个事务中，先后两次执行的范围查询，返回的结果不同。

Oracle 锁行为分析

Oracle数据库中的默认隔离级别为读已提交，根据上述表格的说明，读已提交的隔离级别允许发生不可重复读的现象。以下是一个简单的测试：

这个例子中，会话1两次查询看到的值是不一样的，对于读已提交的隔离级别，这被认为是一个正常的现象。

为了提升系统的并发能力，在DML操作时数据库会尽量将加锁范围降低到最小。Oracle的锁为行级锁，相关的更新仅影响被DML语句操作的行，其他行则不受影响。不论是指定值的DML操作，还是针对某一个范围的更新，都只会锁定命令发出时所能看到的行，并不会影响其他会话对这个范围数据的插入操作。

MySQL锁行为分析

而MySQL中的默认隔离级别为可重复读，不允许发生不可重复读的现象，同时在这个隔离级别下，也可以很大程度上避免幻读现象的发生，其实现原理就是引入了更高级别的锁。

InnoDB下有三种锁算法：

·Record Lock ：单个行记录加锁

·Gap Lock ：间隙锁，锁定一个范围，但不含记录本身

·Next-Key Lock ：Record Lock + Gap Lock

MySQL可重复读隔离级别中，加锁的基本单位是next-key lock，是一个前开后闭区间；查找过程中访问到的对象均会加锁，所使用的加锁算法，根据访问方式和索引类型的不同，会在前述三种算法中进行转换。

下面通过具体的例子说明MySQL中的锁。

session1和session2分别进行相应的操作，进行相应的插入和修改。

上述操作的加锁分析，

1. 会话1开启一个新的事务；

2. 查找>=10且<11的记录并锁定，开始执行的时候，要找到第一个 id=10 的行，按照MySQL的加锁规则，本该是next-key lock(5,10]。由于id列是主键，在5-10的区间中不会再有其他值等于10，所以退化成行锁，只加了id=10 这一行的行锁；在判断id<11的条件时，需要扫描到10-15区间的值，因此这个范围均会被加上Gap锁；

3. 基于第二点的加锁分析，会话2尝试插入id为8的值，成功；

4. 基于第二点的加锁分析，会话2尝试插入id为11的值，被阻塞，直到锁超时；

5. 会话2尝试插入id为16的值，成功；

6. 当会话1提交后，相关的锁释放，会话2插入id为11的值成功。

7. 以上通过一个例子说明MySQL中的间隙锁的使用，根据访问方式和索引类型的不同，加锁范围也有所区别，一个原则就是，访问到的区间都会被加锁。

总结

MySQL由于其默认的隔离级别和Oracle不同，所以其锁定行为也有很大的区别，实际使用过程中要尽可能减少记录的扫描范围和事务的锁定时间，尽量做到快进快出，避免大事务操作。