MySQL中的锁机制

转载：MySQL 三万字精华总结 + 面试100 问，和面试官扯皮绰绰有余（收藏系列） (juejin.cn)

1 锁的分类

（1）从对数据操作的类型分类：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响。
• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

（2）从对数据操作的粒度分类：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说，表级锁更适合于以查询为主，并且只有少量按索引条件更新数据的应用，如Web应用；

而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

2 MyISAM 表锁

MyISAM 的表锁有两种模式：

• 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求。
• 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作。

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

3 InnoDB 行锁

InnoDB 实现了以下两种类型的 行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。

4 乐观锁 和 悲观锁

乐观锁 与 悲观锁 是两种并发控制的思想，可用于解决丢失更新问题。

• 乐观锁会 “乐观地” 假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用**数据版本（Version）**记录机制实现，这是乐观锁最常用的一种实现方式。

• 悲观锁会 “悲观地” 假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

5 select for update有什么含义，会锁表还是锁行还是其他？

for update 仅适用于InnoDB引擎，且必须在事务块（BEGIN/COMMIT）中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

InnoDB这种行锁实现特点意味着：只有查询条件中有索引列明确的值的时候，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

行锁例子：

-- 明确指定主键，并且有此笔资料，row lock
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

表锁：

-- 无主键，table lock
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

-- 主键不明确，table lock
SELECT * FROM products WHERE id<>'3' FOR UPDATE;

-- 主键不明确，table lock
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;