MySQL面试 锁篇

什么是锁？MySQL 中提供了几类锁？

锁是实现数据库并发控制的重要手段，可以保证数据库在多人同时操作时能够正常运行。MySQL 提供了全局锁、行级锁、表级锁。其中 InnoDB 支持表级锁和行级锁，MyISAM 只支持表级锁。

什么是死锁？

是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的过程称为死锁。

为什么会产生死锁，举个栗子

不同线程并发访问同一张表时，未按照顺序访问会导致死锁。
例如：
两个session分别执行就会发生死锁
select * from t18 where a=1 for update;

session1 在等待 session2 释放 a=2 的行锁，而 session2 在等待 session1 释放 a=1 的行锁。两个 session 互相等待对方释放资源，就进入了死锁状态。

所以对于程序多个并发访问同一张表时，如果事先确保每个线程按固定顺序来处理记录，可以降低死锁的概率。

session1 session2
set session transaction_isolation=‘REPEATABLE-READ’;
/* 设置会话隔离级别为 RR / set session transaction_isolation=‘REPEATABLE-READ’;
/ 设置会话隔离级别为 RR /
begin; begin;
select * from t18 where a=1 for update;
…
1 row in set (0.00 sec) select * from t18 where a=2 for update;
…
1 row in set (0.00 sec)
insert into t18(a,b,c) values (2,3,3);/ SQL3 /
等待
（session3 提示死锁回滚后，SQL1 成功返回结构） insert into t18(a,b,c) values (1,4,4);/ SQL4 */
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
commit; commit;

<RR 隔离级别下的非唯一索引查询>，可以知道 SQL3 需要等待 a=2 获得的间隙锁，而 SQL4 需要等待 a=1 获得的间隙锁，两个 session 互相等待对方释放资源，就进入了死锁状态。

类似这种情况，可以考虑将隔离级别改成 RC（这里各位读者可以尝试在 RC 隔离级别下，做上面的实验），降低死锁的概率。

如何处理死锁？

通过 innodb_lock_wait_timeout 来设置超时时间，一直等待直到超时；
发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。

如何查看死锁？

使用命令 show engine innodb status 查看最近的一次死锁。
设置 innodb_print_all_deadlocks = on 可以在 err log 中记录全部死锁信息

如何查看表锁？

查看是否有表锁：show OPEN TABLES where In_use > 0;
查看所有进程：show processlist;
查看正在锁的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
查看服务器状态 show status like ‘%lock%’;
查看超时时间： show variables like ‘%timeout%’;

如何避免死锁？

为了在并发写入时避免死锁，可以在事务开始时通过使用 SELECT … FOR UPDATE 语句来获取必要的锁。

在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

如果事务需要修改或锁定多个表，则应在每个事务应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

通过 SELECT … LOCK IN SHARE MODE 获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。

改变事务隔离级别将 RR 隔离级别调整为 RC 隔离级别。

更新 SQL 的 where 条件尽量用索引。

基于 primary 或 unique key 更新数据。

减少范围更新，尤其非主键、非唯一索引上的范围更新。

InnoDB 默认是如何对待死锁的？

InnoDB 中解决死锁问题有两种方式：
(1)检测到死锁的循环依赖，立即返回一个错误（这个报错内容请看下面的实验），将参数 innodb_deadlock_detect 设置为 on 表示开启这个逻辑。

(2)等查询的时间达到锁等待超时的设定后放弃锁请求。这个超时时间由 innodb_lock_wait_timeout 来控制。默认是 50 秒。

一般线上业务都建议使用的第 1 种策略，因为第 2 种策略锁等待时间是 50 秒，对于高并发的线上业务是不能接受的。

但是第 1 种策略，也会有死锁检测时的额外 CPU 开销的，比如电商中的秒杀场景。这种情况就可以根据业务开发商量优化程序，如果可以确保业务一定不会出现死锁，可以临时把死锁检测关掉，以提高并发效率。

如何开启死锁检测？

设置 innodb_deadlock_detectt 设置为 on 可以主动检测死锁，在 Innodb 中这个值默认就是 on 开启的状态。

什么是全局锁？它的应用场景有哪些？

全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份。这个命令可以使整个库处于只读状态。使用该命令后，数据更新语句、数据定义语句、更新类事务的提交语句等操作都会被阻塞。

什么是共享锁？

共享锁又称读锁 (read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁。

什么是排它锁？

排他锁 exclusive lock（也叫 writer lock）又称写锁。
若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

排它锁是悲观锁的一种实现，在上面悲观锁也介绍过。
若事务 1 对数据对象 A 加上 X 锁，事务 1 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到事物 1 释放 A 上的锁。这保证了其他事务在事物 1 释放 A 上的锁之前不能再读取和修改 A。排它锁会阻塞所有的排它锁和共享锁。

使用全局锁会导致什么问题？

如果在主库备份，在备份期间不能更新，只能暂停业务，所以更新业务会处于等待状态。
如果在从库备份，在备份期间不能执行主库同步的 binlog，导致主从延迟。

全局锁一般什么时候会用到？

全局锁一般用在整个库（包含非事务引擎表）做备份（mysqldump 或者 xtrabackup）时。也就是说，在整个备份过程中，整个库都是只读的，其实这样风险挺大的。如果是在主库备份，会导致业务不能修改数据；而如果是在从库备份，就会导致主从延迟。

好在 mysqldump 包含一个参数 –single-transaction，可以在一个事务中创建一致性快照，然后进行所有表的备份。因此增加这个参数的情况下，备份期间可以进行数据修改。但是需要所有表都是事务引擎表。所以这也是建议使用 InnoDB 存储引擎的原因之一。

而对于 xtrabackup，可以分开备份 InnoDB 和 MyISAM，或者不执行 --master-data，可以避免使用全局锁。

如何处理逻辑备份时，整个数据库不能插入的情况？

如果使用全局锁进行逻辑备份就会让整个库成为只读状态，幸好官方推出了一个逻辑备份工具 MySQLdump 来解决了这个问题，只需要在使用 MySQLdump 时，使用参数 -single-transaction 就会在导入数据之前启动一个事务来保证数据的一致性，并且这个过程是支持数据更新操作的。

如何设置全局只读锁？

使用命令 flush tables with read lock（简称 FTWRL）就可以实现设置数据库为全局只读锁。
使用 unlock tables 可以解锁。

除了 FTWRL 可以设置数据库只读外，还有什么别的方法？

除了使用 FTWRL 外，还可以使用命令 set global readonly=true 设置数据库为只读。

FTWRL 和 set global readonly=true 有什么区别？

FTWRL 和 set global readonly=true 都是设置整个数据库为只读状态，但他们最大的区别就是，当执行 FTWRL 的客户端断开之后，整个数据库会取消只读，而 set global readonly=true 会一直让数据处于只读状态。

如何实现表锁？

MySQL 里表级锁有两种：表级锁、元数据锁（meta data lock）简称 MDL。

表锁的语法是 lock tables t read/write。

可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。
lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

MDL：不需要显式使用，在访问一个表的时候会被自动加上。
MDL 的作用：保证读写的正确性。

在对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
读锁之间不互斥，读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

什么场景下会用表锁

事务需要更新某张大表的大部分或全部数据。如果使用默认的行锁，不仅事务执行效率低，而且可能造成其它事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高事务执行速度；

事务涉及多个表，比较复杂，可能会引起死锁，导致大量事务回滚，可以考虑表锁避免死锁。

表锁又分为表读锁和表写锁，命令分别是：

表读锁：
lock tables 表 read;

表写锁：
lock tables 表write;

对表加了表读锁后，所有线程都可读，当前线程写会报错，其他线程会处于等待状态。
对表加了表写锁后，当前线程可以读写，其他现在读写都会阻塞。

为什么要有元数据锁，它解决了什么问题？

在 MySQL 中，DDL 是不属于事务范畴的。如果事务和 DDL 并行执行同一张表时，可能会出现事务特性被破坏、binlog 顺序错乱等 bug。为了解决这类问题，从 MySQL 5.5.3 开始，引入了元数据锁（Metadata Locking，简称：MDL 锁）

MDL 锁的出现解决了同一张表上事务和 DDL 并行执行时可能导致数据不一致的问题。

所以对于开发来说，在工作中应该尽量避免慢查询、尽量保证事务及时提交、避免大事务等，当然对于 DBA 来说，也应该尽量避免在业务高峰执行 DDL 操作。

列举一个元数据锁是示例，以及如何解决

session1 session2 session3
select id,a,b,sleep(100) from t14 limit 1;/* sql5 /
alter table t14 add column c int;/ sql6 /
（等待） select id,a,b from t14 limit 1;/ sql7 /
（等待）
select id,a,b,sleep(100) from t14 limit 1;/ sql5 /
…
1 row in set (1 min 40.00 sec)
（100秒后 sql5 返回结果） alter table t14 add column c int;/ sql6 /
Query OK, 0 rows affected (1 min 33.98 sec)
Records: 0 Duplicates: 0 Warnings: 0
（session1 的查询语句执行完成后，sql6 立马执行完毕） select id,a,b from t14 limit 1;/ sql7 */
…
1 row in set (1 min 26.65 sec)
（session1 的查询语句执行完成后，sql7 立马执行完毕）

我们在 session1 查询了表 t14 的数据，其中使用了 sleep(100) ，表示在 100 秒后才会返回结果；
然后在 session2 执行 DDL 操作时会等待（原因是 session1 执行期间会对表 t14 加一个 MDL，而 session2 又会跟 session1 争抢 MDL）；
而 session3 执行查询时也会继续等待。

因此如果 session1 的语句一直没结束，其它所有的查询都会等待。这种情况下，如果这张表查询比较频繁，很可能短时间把数据库的连接数打满，导致新的连接无法建立而报错，如果是正式业务，影响是非常恐怖的。

如果出现这种情况，假如你还有 session 连着数据库，可以 kill 掉 session1 中的语句或者终止 session2 中的 DDL 操作，可以让业务恢复。但是出现这种情况的根源其实是：session1 中有长时间未提交的事务。

悲观锁和乐观锁有什么区别？

顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。

正因为如此，悲观锁需要耗费较多的时间，另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。

共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

乐观锁是用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。

何谓数据版本？

即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 version 字段来实现。

当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加 1。

当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据。

乐观锁有什么优点和缺点？

因为没有加锁所以乐观锁的优点就是执行性能高。

它的缺点就是有可能产生 ABA 的问题，ABA 问题指的是有一个变量 V 初次读取的时候是 A 值，并且在准备赋值的时候检查到它仍然是 A 值，会误以为没有被修改会正常的执行修改操作，实际上这段时间它的值可能被改了其他值，之后又改回为 A 值，这个问题被称为 ABA 问题。

InnoDB 中有几种类型的锁？

(1)记录锁（Record Lock）
(2)间隙锁（Gap Lock）
(3)临键锁（Next-key Lock）
(4)插入意向锁（Insert Intention Lock）
(5)自增锁（Auto-Inc Lock）
(6)谓词锁（Predicate Lock）

InnoDB 有几种锁算法？

Record Lock：记录锁 锁住一条记录。
由于 InnoDB 是索引组织表，在 InnoDB 的表中，扫描数据时是先扫描表中的 B + 树索引，因此 InnoDB 的记录锁是在索引上加锁的，不是锁住数据行。

Gap Lock：间隙锁，锁定一个索引范围，但不包括本身。
间隙锁加在不存在的空闲空间，这个空间可以是两个索引记录之间，也可能是第一个索引记录之前或者最后一个索引记录之后的空间，但不包括索引记录本身。

间隙锁在 InnoDB 中的作用是保证某个间隙的数据，在锁定的情况下不会发生任何变化，以此来防止幻读的产生。也因此间隙锁并不分共享锁和排他锁。

Next-Key Lock：Gap Lock + Record Lock 临建锁 锁定一个范围，并且锁定记录本身。

InnoDB 的行级锁是通过给索引项加锁来实现的。所以，在使用 InnoDB 存储引擎时，只有通过索引检索时，才会使用行级锁，否则会直接升级为表锁。

另外，MySQL 加锁会受到隔离级别的影响。
在 REPEATABLE READ 隔离级别下，MySQL 使用 临建锁Next-Key 进行加锁；
但是在 READ-COMMIT 隔离级别下，除了外键约束和唯一性检查仍然会加间隙锁Gap Lock;
其它情况均只加记录锁Record Lock。

记录锁示例：
SQL按照唯一索引进行数据的检索时，查询条件等值匹配且查询的数据是存在，这时SQL语句加上的锁就是记录锁Record locks，锁住具体的索引项。
SELECT * FROM my_table WHERE id = 2021 FOR UPDATE;

间隙锁示例：
sql查询的数据不存在时，这时SQL语句加上的锁就是 Gap locks，锁住索引不存在的区间（左开右开的区间）Gap只在RR事务隔离级别/repeatable read存在。
BEGIN；
SELECT * FROM my_table WHERE id > 2001 AND id < 2021 FOR UPDATE; --排它锁的间隙锁算法
SELECT * FROM my_table WHERE id = 2021 FOR UPDATE; – 另一种方式
ROLLBACK;

临建锁示例：
查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间（左开右闭的区间）

BEGIN；
SELECT * FROM my_table WHERE id > 2001 AND id < 2021 FOR UPDATE; --临建锁的间隙锁算法

– 其他事务执行
set session autocommit=off;
select * from t2 where id=4 for update; --没锁住
select * from t2 where id=7 for update; --锁住
select * from t2 where id=10 for update; --锁住
INSERT INTO t2 (id, name) VALUES (9, ‘9’);
ROLLBACK;

InnoDB 如何实现行锁？

行级锁是 MySQL 中粒度最小的一种锁，他能大大减少数据库操作的冲突。

INNODB 的行级锁有共享锁（S LOCK）和排他锁（X LOCK）两种。

共享锁允许事物读一行记录，不允许任何线程对该行记录进行修改。
排他锁允许当前事物删除或更新一行记录，其他线程不能操作该记录。

共享锁： SELECT … LOCK IN SHARE MODE，MySQL 会对查询结果集中每行都添加共享锁，前提是当前线程没有对该结果集中的任何行使用排他锁，否则申请会阻塞。

排他锁： select * from t where id=1 for update，其中 id 字段必须有索引，MySQL 会对查询结果集中每行都添加排他锁，在事物操作中，任何对记录的更新与删除操作会自动加上排他锁。前提是当前没有线程对该结果集中的任何行使用排他锁或共享锁，否则申请会阻塞。

InnoDB 行锁模式

InnoDB 实现了以下两种类型的行锁：
共享锁（S）：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁；
排他锁（X）：允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的共享读锁和排他写锁。

对于普通 select 语句，InnoDB 不会加任何锁，事务可以通过以下语句显式给记录集加共享锁或排他锁：
共享锁（S）：select * from table_name where … lock in share mode;
排他锁（X）：select * from table_name where … for update。

优化锁方面你有什么建议？

尽量使用较低的隔离级别。
精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会。
选择合理的事务大小，小事务发生锁冲突的几率也更小。
给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会。
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
不要申请超过实际需要的锁级别。
除非必须，查询时不要显示加锁。 MySQL 的 MVCC 可以实现事务中的查询不用加锁，优化事务性能；MVCC 只在 COMMITTED READ（读提交）和 REPEATABLE READ（可重复读）两种隔离级别下工作。
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。