MySQL面试篇章—MySQL锁机制

MySQL的锁机制

表级锁 & 行级锁

表级锁：对整张表加锁，开销小，加锁快，不会出现死锁；但是锁粒度大，发生锁冲突的概率高，并发度低

行级锁：对某行记录加锁，开销大，加锁慢，会出现死锁；但是锁粒度小，发生锁冲突的概率最低，并发度高。

排它锁和共享锁

排它锁（Exclusive），又称为X锁，写锁

共享锁（Shared），又称为S锁，读锁

X锁和S锁之间有以下的关系：SS锁可以兼容，但是XS、SX、XX之间是互斥的，会导致堵塞

• 一个事务对数据对象O加了S锁，可以对O进行读取操作，但是不能进行更新操作，加锁期间其他事务能对O加S锁，但不能加X锁
• 一个事务对数据对象O加了X锁，就可以对O进行读取和更新。但是加锁期间其他事务不能对O加任何的锁

# 显式加锁
select ... lock in share mode; # 强制获取共享锁select ... for update; # 获取排它锁

InnoDB行级锁

行级锁

InnoDB存储引擎支持事务处理，表支持行级锁定，并发能力更好

1、InnoDB行锁是通过给索引上的索引项加索来实现的，而不是给表的行记录加锁实现的，这就意味着只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁（因此如果过滤条件没有索引的话，默认加的就是表锁，不是行锁）

2、由于InnoDB的行锁是针对索引字段添加的锁，不是针对行记录加的锁，因此虽然访问的是InnoDB引擎下表的不同行，但是如果使用相同的索引字段作为过滤条件的话，依然会发生锁冲突，只能串行进行，不能并发进行

3、即使SQL使用了索引，但是经过MySQL的优化器后，如果认为全表扫描比使用索引效率更高，此时会放弃使用索引，因此也不会使用行锁，而是使用表锁，因此比如对一些很小的表，MySQL就不会去使用索引

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加索；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个”间隙“加索，这种锁机制就是所谓的间隙锁。例如：加入user表中只有101条记录，其userId的值分别是1，2，…，100，101，下面的SQL：

select * from user where userId > 100 for update;

这是一个范围条件的检索，InnoDB不仅会对符合条件的userId的值为101的记录加索，也会对userId大于101（虽然记录不存在）的”间隙“加锁，防止其他事务在表的末尾增加数据

InnoDB使用间隙锁的目的，是为了防止幻读，以满足串行化隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了userId大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读

意向共享锁和意向排它锁

意向共享锁（IS锁）：事务计划给记录加行共享锁，事务在给一行记录加共享锁前，必须要先获取该表的IS锁。

意向排它锁（IX锁）：事务计划给记录加行排它锁，事务在给一行记录加排它锁前，必须要先获取该表的IX锁。

	X	IX	S	IS
X	互斥	互斥	互斥	互斥
IX	互斥	兼容	互斥	兼容
S	互斥	互斥	兼容	兼容
IS	互斥	兼容	兼容	兼容

1、意向锁是由InnoDB存储引擎获取行锁之前自己获取的

2、意向锁之前都是兼容的，不会产生冲突（即IX和IS）

3、意向锁存在的意义是为了更高效的获取表锁（表格中的X和S指的是表锁，不是行锁！！！）

4、意向锁是表级锁，协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某行

InnoDB表级锁

在绝大部分情况下都应该使用行锁，因为事务和行锁往往是选择InnoDB的理由，但个别情况下也使用表级锁：

1）事务需要更新大部分或者全部数据，表又特别大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能会造成其他食物长时间等待和锁冲突

2）事务涉及多个表，比较复杂，很可能会引起死锁，造成大量事务回滚

例如：

lock table user read; 读锁锁表
lock table user write; 写锁锁表事务执行commit / rollback; 事务提交或者回滚
unlock tables; 本身自带提交事务，释放线程占用的所有表锁

死锁

MyISAM表锁是 deadlock free 的，这时因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但是在InnoDB中，除单个SQL组成的事务外，锁是逐步获取的，即锁的粒度比较小，这就决定了在InnoDB中发生死锁是可能的

select * from test_dead_lock where id = 1 for update;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction

注意：死锁问题一般都是由我们自己的应用造成的，和多线程编程的死锁情况相似，大部分都是由于我们多个线程在获取多个锁资源的时候，获取的顺序不同而导致的死锁问题。因此我们应用在数据库的多个表做更新的时候，不同的代码段，应对这些表按相同的顺序进行更新操作，以防止锁冲突导致死锁问题

锁的优化建议

• 尽量使用较低的隔离级别
• 设计合理的索引并尽量使用索引访问数据，使加锁更加准确，减少锁冲突的机会提高并发能力
• 选择合理的事务大小，小事务发生锁冲突的概率小
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行，这样可以大大减少死锁的机会
• 尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 除非必须，查询时不要显示加锁

MVCC多版本并发控制

MVCC是多版本并发控制（Multi-Version Concurrency Control，简称MVCC），是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现已提交读和可重复读隔离级别的实现，也经常被称为多版本数据库。MVCC机制会生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本（系统版本号和事务版本号）

MVCC多版本并发控制中，读操作可以分为两类：

1、快照读（snapshot read）

• 读的是记录的可见版本，不用加锁。如select

2、当前读（current read）

• 读取的是记录的最新版本，并且当前读返回的记录。如insert、delete、update、select … lock in share mode / for update

MVCC：每一行记录实际上有多个版本，每个版本的记录除了数据本身之外，增加了其他字段

DB_TRX_ID：记录当前事务ID

DB_ROLL_PTR：指向undo log日志上数据的指针

**已提交读：**每次执行语句的时候都重新生成一次快照（Read View），每次select查询时

**可重复读：**同一个事务开始的时候生成一个当前事务全局性的快照（Read View），第一次select查询时

快照内容读取原则：

1、版本未提交无法读取生成快照

2、版本已提交，但是在快照创建后提交的，无法读取

3、版本已提交，但是在快照创建前提交的，可以读取

4、当前事务内自己的更新，可以读到

MyISAM表级锁

MyISAM存储引擎不支持事务处理，因此它的并发比较简单，只支持到表锁的粒度，粒度特别大，并发能力一般，但不会引起死锁的问题，它支持表级共享的读锁和互斥的写锁

表级锁

• 对 MyISAM 表的读操作（共享锁），不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

• 对 MyISAM 表的写操作（排它锁），则会阻塞其他用户对同一表的读和写操作。

• MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。

• MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、 DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户控制，是MySQL Server 端自动完成的。

并发插入优化

• concurrent_insert优化

show variables like 'concurrent_insert';

普通情况下，MyISAM的读操作和写操作都是串行的，但是其实MyISAM也是支持读和写的并发操作的，上面的concurrent_insert变量就是开关，允许一个线程在读的时候，另外一个线程在尾部进行插入（但是不能并发进行删除delete和更新update）

锁调度优化

• low_priority_updates优化

在MyISAM存储引擎下，多个线程并发操作时，线程1试图获取读锁，线程2获取写锁，一般MyISAM认为写操作要比读操作重要，因此线程2几乎都会有限获取写锁，写操作完成后，线程1才会获取读锁。

即使线程1的读锁请求先到达，线程2的写锁请求到达后，那么线程2写锁的获取也会排在线程1读锁的前面

因此，MyISAM存储引擎不适合大量的更新操作和查询操作，因为查询操作获取读锁的优先级比较低，会导致客户端查询获取结果的过程很慢。当然MySQL提供了很多参数设置，可以调整读锁的获取优先级。