MySQL学习四之锁

MySQL学习四之锁

一、为什么需要锁

锁机制用于对共享资源的并发访问。

当多个用户并发地存取数据时，在数据库中就可能会产生多个事务同时操作同一行数据的情况，若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据的一致性。

一种典型的并发问题——丢失更新（其他锁问题及解决方法会在后面说到）：

执行顺序	事务A	事务B
1	开启事务A
2		开始事务B
3	查询当前商品S库存为100
4		查询当前库存为100
5	业务逻辑处理，增加库存10
6		业务处理增加库存20
7	提交事务A
8		提交事务B

**异常结果：**商品S库存更新为120，但实际上针对商品S进行了两次入库操作，最终商品S库存应为100+10+20=130，但实际结果为120，首先提交的事务A的更新『丢失了』！！！所以就需要锁机制来保证这种情况不会发生。

二 、InnoDB锁类型

1、乐观锁与悲观锁是两种并发控制思想，可以用于解决上面提出的丢失更新问题：

可以通过在查询记录的时候记录当前记录的版本号或者时间戳，当更新的时候和当前数据库的时间戳和版本号做对比，如果一致执行更新，不一致报错。这里up认为mvcc也是乐观锁的一种。

2、InnoDB支持多种锁颗粒度，默认使用行锁，颗粒度最小，所冲突发生的概率最低，支持的并发度也是最高的，但是也会提升系统性能的消耗。

3、InnoDB在默认事务模式RR下通过Next-key-Lock算法，解决了幻读的问题。

4、意向锁是为了支持多颗粒度锁设计的。可以理解为是一种表锁。

三、行锁详解

InnoDB默认使用行锁，实现了两种标准的行锁–共享锁与排他锁。

行锁类型	锁功能	锁兼容性	加锁	释放锁
共享锁（S）	允许事务读取一行数据	与共享锁兼容与排他锁不兼容	只有Serializable隔离级别下默认为读加共享锁；其它隔离级别都不会，但是可以使用select …lock in share mode语句显示的加共享锁	在事务提交或者回滚自动同事释放。
排它锁（X）	允许事务删除或者修改一行数据	都不兼容	在默认的RR隔离级别下，InnoDB会自动为增删改操作的行加排他锁；也可以使用select … for update 来显示的加排他锁。	在事务提交或者回滚自动同事释放。

注意：

1. 除了显示加锁的情况，其他情况下的加锁与解锁都无需人工干预。
2. InnoDB所有的行锁算法都是基于索引实现的，锁定的也都是索引或者索引区间。

四、当前读和快照读

**1、当前读：**即加锁读，读取记录的最新版本，会加锁保证其他并发事务不能修改当前记录，直至获取锁的事务释放锁；

使用当前读的操作主要包括：显式加锁的读操作与插入/更新/删除等写操作，如下所示：

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

注：当执行Update Sql的时候，mysql server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁，待MySQL Server收到这条加锁的记录之后，会再发起一个update 请求，更新这条记录。一条记录操作完成，在读取下一条记录，直至没有满足条件的记录为止。因此update 内部包含了当前读。同理delete也是一样。Insert 操作会稍微有些不同，简单来说就是对唯一键的一个唯一性检验操作，如果不存在锁定待插入的唯一键防止其它事务插入，所以也进行了一次待插入唯一键的当前读。

**2、快照都：**即不加锁读，读取记录的快照版本而非最新版本，通过MVCC实现；

InnoDB默认的RR事务隔离级别下，不显式加『lock in share mode』与『for update』的『select』操作都属于快照读，保证事务执行过程中只有第一次读之前提交的修改和自己的修改可见，其他的均不可见；原理是通过undo日志来实现的。

五、 MVCC

MVCC的最大好处：读不加任何锁，读写不冲突，对于读操作多于写操作的应用，极大的增加了系统的并发性能；

InnoDB默认的RR事务隔离级别下，不显式加『lock in share mode』与『for update』的『select』操作都属于快照读，使用MVCC，保证事务执行过程中只有第一次读之前提交的修改和自己的修改可见，其他的均不可见；

如果读取的行正在执行DELETE或UPDATE操作， 这时读取操作不会因此去等待行上锁的释放。 相反地， InnoDB存储引擎会去读取行的一个快照数据。 如下图所示。

对于Insert操作，InnoDB为新插入的每一行保存当前系统版本号作为行版本号；

对于DELETE操作，InnoDB为删除的每一行保存当前系统版本号作为删除标识，而不是立即删除该数据，因为有可能其它的事务在读取这行数据。真正的删除任务有purge线程处理。

对于update操作来说，是先执行删除，在执行插入。

在事务隔离级别READ COMMITTED和REPEATABLE READ（InnoDB存储引擎的默认事务隔离级别） 下， InnoDB存储引擎使用非锁定的一致性读。 然而， 对于快照数据的定义却不相同。 在READ COMMITTED事务隔离级别下， 对于快照数据， 非一致性读总是读取被锁定行的最新一份快照数据。 而在REPEATABLE READ事务隔离级别下， 对于快照数据， 非一致性读总是读取事务开始时的行数据版本。

六、锁算法

InnoDB主要实现了三种锁算法：

行锁算法	锁定内容
Record Lock	记录锁，锁定一个行记录
Gap Lock	间隙锁，锁定一个范围，不包括本身
Next-key	记录锁+间隙锁 ，锁定指定记录和区间

InnoDB所有的行锁算法都是基于索引实现的，锁定的也都是索引或索引区间；其实这个可以理解为所有的操作都需要经过索引，即使是全部搜索也是走的聚簇索引。

不同的事务隔离级别、不同的索引类型、是否为等值查询，使用的行锁算法也会有所不同；下面仅以InnoDB默认的RR隔离级别、等值查询为例，介绍几种行锁算法：

等值查询使用的索引类型	锁定内容
主键（聚簇索引）	对聚簇索引记录加Record Lock
唯一索引	对辅助索引记录加Record Lock 对聚簇索引记录加Record Lock
普通索引	对相关辅助索引加Next-Key Lock 对聚簇索引记录加Record Lock
不使用索引	对聚簇索引全表加Next-Key Lock

七、锁问题

MySQL锁会带来如下几种问题，如果我们能解决他们，就可以保证并发情况下不会出现问题；

锁问题	锁问题描述	会出现锁问题的隔离级别	解决办法
脏读	一个事务中会读到其他并发事务未提交的数据，违反了事务的隔离性	Read Uncommited	提高事务隔离级别至Read Committed及以上；
不可重复读	一个事务会读到其他并发事务已提交的数据，违反了数据库的一致性要求；可能出现的问题为幻读，幻读是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能返回之前不存在的行记录；	Read Uncommitted、Read Committed	1、当前读：Next-Key Lock机制对相关索引记录及索引间隙加锁，防止并发事务修改数据或插入新数据到间隙；（详情参见第六章节『锁算法』）2、版本读：MVCC，保证事务执行过程中只有第一次读之前提交的修改和自己的修改可见，其他的均不可见；
丢失更新		Read Uncommitted、Read Committed、Repeatable Read	默认的RR隔离级别下 ，解决办法分为两种情况：1、乐观锁：数据表增加version字段，读取数据时记录原始version，更新数据时，比对version是否为原始version，如不等，则证明有并发事务已更新过此行数据，则可回滚事务后重试直至无并发竞争；2、悲观锁：读加排他锁，保证整个事务执行过程中，其他并发事务无法读取相关记录，直至当前事务提交或回滚释放锁；
幻读		RR

MVCC并不能完全解决幻读。比如：事务中，先读数据，B事务插入数据,并提交事务。A事务在读取，此时由于MVCC会读取最新老数据。A事务进行更新，这里会触发当前读。A在读取数据，就会读取到B事务插入的数据。 解决方案就是在查询的时候加锁，select for update

全解决幻读。比如：事务中，先读数据，B事务插入数据,并提交事务。A事务在读取，此时由于MVCC会读取最新老数据。A事务进行更新，这里会触发当前读。A在读取数据，就会读取到B事务插入的数据。 解决方案就是在查询的时候加锁，select for update。