MySQL面试题-锁相关

 

目录

 

1.MySQL 锁的类型有哪些呢？

2.如何使用全局锁

3.如果要全库只读，为什么不使用set global readonly=true的方式？

4.表级锁和行级锁有什么区别？

5.行级锁的使用有什么注意事项？

6.InnoDB 有哪几类行锁？

7.什么是共享锁和排他锁

8.意向锁有什么作用？

9.当前读和快照读有什么区别？

10.自增锁有了解吗？

11.如何处理死锁问题？

---

1.MySQL 锁的类型有哪些呢？

MySQL 中常见的锁类型可以分为全局锁、表级锁和行锁。下面分别介绍它们：

1. 全局锁（Global Lock）：锁定整个 MySQL 实例，用于执行全局操作时，比如备份整个数据库。全局锁会阻塞所有对数据库的操作，因此在使用全局锁时需要慎重考虑。

2. 表级锁（Table-level Lock）：锁定整张表，适用于只有少量访问，或者需要锁定整张表时。表级锁分为读锁（共享锁）和写锁（排他锁）。

   • 共享锁（Shared Lock）：允许多个事务同时读取同一张表，但是不能进行写操作。如果一个事务持有共享锁，其他事务也可以持有共享锁。共享锁之间不会互相阻塞。

   • 排他锁（Exclusive Lock）：只允许一个事务进行写操作，其他事务不能进行读取或写入操作。如果一个事务持有排他锁，其他事务无法获得共享锁或排他锁。排他锁会阻塞其他事务的读写操作，因此需要慎重使用。

3. 行级锁（Row-level Lock）：锁定表中的某一行，用于控制对表中某一行的并发访问。行级锁是在存储引擎层实现的，不同的存储引擎对行级锁的实现可能略有不同。行级锁分为共享锁和排他锁。

   • 共享锁（Shared Lock）：允许多个事务同时读取同一行，但是不能进行写操作。如果一个事务持有共享锁，其他事务也可以持有共享锁。共享锁之间不会互相阻塞。

   • 排他锁（Exclusive Lock）：只允许一个事务进行写操作，其他事务不能进行读取或写入操作。如果一个事务持有排他锁，其他事务无法获得共享锁或排他锁。排他锁会阻塞其他事务的读写操作，因此需要慎重使用。

2.如何使用全局锁

全局锁（Global Lock）是 MySQL 中一种最为粗暴的锁，可以锁定整个 MySQL 实例，用于执行全局操作时，比如备份整个数据库。全局锁会阻塞所有对数据库的操作，因此在使用全局锁时需要慎重考虑。

全局锁可以通过执行以下语句来获得：

FLUSH TABLES WITH READ LOCK;

执行该语句后，MySQL 将会阻塞所有对表的读写操作，直到当前连接释放锁为止。注意，FLUSH TABLES WITH READ LOCK 只能获得共享锁（读锁），不支持排他锁（写锁）。

3.如果要全库只读，为什么不使用set global readonly=true的方式？

1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。

2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

4.表级锁和行级锁有什么区别？

表级锁和行级锁是 MySQL 中最常用的两种锁类型，它们的区别在于锁定的粒度不同，可以根据具体情况选择使用不同的锁类型。

1.表级锁

表级锁是锁定整张表，通常在执行大量写操作时使用。表级锁可以分为读锁（共享锁）和写锁（排他锁）。

读锁（共享锁）：多个事务可以同时对同一张表进行读取操作，但是不能进行写入操作。在读取操作期间，其他事务也可以获取读锁，但是不能获取写锁。

写锁（排他锁）：只允许一个事务进行写入操作，其他事务不能进行读取或写入操作。在写入操作期间，其他事务不能获取读锁或写锁，直到当前事务释放写锁为止。

表级锁的优点是锁定粒度大，锁的管理和维护成本低，容易控制。但是，锁定整张表会导致其他事务等待锁的时间变长，降低并发性能。因此，在执行大量写操作时使用表级锁要慎重，需要考虑锁的粒度和并发性能的平衡。

2.行级锁

行级锁是锁定表中的某一行，通常在执行大量读操作时使用。行级锁可以分为共享锁和排他锁，具体实现依赖于存储引擎的实现。

共享锁：多个事务可以同时读取同一行数据，但是不能进行写操作。在读取操作期间，其他事务也可以获取共享锁，但是不能获取排他锁。

排他锁：只允许一个事务进行写入操作，其他事务不能进行读取或写入操作。在写入操作期间，其他事务不能获取共享锁或排他锁，直到当前事务释放排他锁为止。

相对于表级锁，行级锁的优点是锁定粒度更细，可以减少锁冲突的概率，提高并发性能。但是，行级锁会带来额外的开销，包括锁的管理和维护成本，因此需要权衡锁定粒度和性能开销，选择适合的锁类型。

需要注意的是，MySQL 在不同的存储引擎上实现锁的方式不同，比如 MyISAM 存储引擎使用表级锁，InnoDB 存储引擎使用行级锁和表级锁的组合，因此在使用锁时需要注意存储引擎的不同实现。

5.行级锁的使用有什么注意事项？

1.避免锁定过多行

行级锁的锁定粒度比表级锁要细，锁的管理和维护成本也更高，如果锁定了过多的行，可能会导致锁冲突和性能问题。因此，在使用行级锁时应该尽量减少锁定的行数，只锁定必要的行，避免锁的数量和时间过长。

2.尽量使用索引

行级锁在锁定某一行时，会对该行所在的索引加锁。如果没有使用索引，MySQL 将会对整张表进行扫描，导致锁定粒度变大，降低并发性能。因此，在使用行级锁时，应该尽量使用索引，提高锁定粒度，减少锁的数量和时间。

3.优化事务的大小

事务的大小会直接影响锁定的行数和时间，如果事务过大，将会导致锁定的行数过多，增加锁冲突和性能问题的概率。因此，在使用行级锁时，应该尽量优化事务的大小，尽可能减小事务的范围和时间，提高并发性能。

例如，有一个商品表 products，需要批量更新价格，如果事务过大，将会导致锁定的行数过多，性能较差，可以将大事务分为多个小事务，例如：

-- 批量更新前半部分商品价格
BEGIN;
UPDATE products SET price = price * 0.9 WHERE id BETWEEN 1 AND 500;
COMMIT;-- 批量更新后半部分商品价格
BEGIN;
UPDATE products SET price = price * 0.9 WHERE id BETWEEN 501 AND 1000;
COMMIT;

4.使用锁的超时机制

MySQL 支持锁的超时机制，当锁定时间超过指定的时间时，将自动释放锁，避免锁冲突和死锁问题。在使用行级锁时，应该合理设置锁的超时时间，避免长时间等待锁导致性能问题。

例如，有一个账户表 account，需要转账操作，如果锁定的时间过长，将会影响并发性能，可以使用锁的超时机制来避免锁定时间过长，例如：

BEGIN;
SELECT * FROM account WHERE id = 1 FOR UPDATE NOWAIT;
-- 更新账户表中 id=1 的记录
COMMIT;

这里使用 NOWAIT 关键字来设置锁的超时机制，如果获取不到锁，将立即返回错误，避免了锁定时间过长。

需要注意的是，使用锁的超时机制时，应该避免死锁的情况，如果出现死锁，超时机制可能会误判导致数据不一致。

6.InnoDB 有哪几类行锁？

实际上，InnoDB 行锁定不仅仅是对索引数据页上的记录加锁，而是对整个索引记录（包括数据页和索引页）进行加锁。当使用 InnoDB 表时，MySQL 提供了三种行级锁定方式，分别是：

1. 记录锁（Record Lock）：也称行锁，只锁定某个索引上的一行记录，即对某个记录加锁。

2. 间隙锁（Gap Lock）：锁定索引记录之间的间隙，但不包括记录本身，锁定某个范围之前的间隙或之后的间隙，但不包括指定范围的记录本身。例如，使用 WHERE 子句查询某个范围内的记录时，如果使用间隙锁定，将锁定范围之前或之后的间隙，避免其他事务插入相同范围的记录。

3. Next-Key 锁（Next-Key Lock）：锁定索引记录和索引记录之间的间隙，包括索引记录本身和之前的间隙，但不包括之后的间隙。Next-Key 锁包含了 Record Lock 和 Gap Lock，确保了同时避免幻读和更新丢失。

InnoDB 默认的隔离级别是 REPEATABLE-READ，行锁默认使用的是 Next-Key Lock。在 REPEATABLE-READ 隔离级别下，MySQL 会在读取数据时锁定所有读取到的数据行（记录锁），同时锁定所有查询范围之前的间隙（Gap Lock）。此外，MySQL 还会对每个查询的结果集中的第一个记录之前的间隙加上 Next-Key Lock，确保其他事务不会插入相同查询范围内的记录。这样可以避免幻读的问题。

需要注意的是，InnoDB 行级锁定是基于索引实现的，如果没有使用索引，MySQL 会自动添加一个隐藏的索引，然后再对该索引上的记录进行锁定。同时，InnoDB 行锁定是基于事务的，只有在事务隔离级别为可重复读或串行化时才会生效。

7.什么是共享锁和排他锁

共享锁和排他锁是数据库中常用的两种锁定方式，用于控制对共享资源的访问和修改，保证数据的一致性和完整性。

共享锁允许多个事务同时读取同一份数据，但是只有一个事务可以对数据进行修改。在获取共享锁之后，其他事务可以继续获取共享锁，但是不能获取排他锁。共享锁通常用于读取数据时使用，可以防止并发修改数据时出现不一致的情况。

排他锁只允许一个事务对数据进行修改，其他事务需要等待该事务释放锁后才能进行修改。在获取排他锁之后，其他事务不能获取共享锁和排他锁。排他锁通常用于修改数据时使用，可以避免并发修改数据时出现竞争的情况。

举个例子来说，假设有一个银行账户表，多个用户需要对该表进行读取和修改。在读取账户余额时，可以使用共享锁，多个用户可以同时读取账户余额；在修改账户余额时，需要使用排他锁，只有一个用户可以对账户余额进行修改。如果多个用户同时修改账户余额，就可能会导致数据不一致或数据丢失的问题，因此需要使用排他锁保证数据的完整性。

8.意向锁有什么作用？

在多用户环境下，事务之间可能会相互冲突，需要对访问的数据行进行加锁以防止冲突。但是在一个事务中，如果需要对多个数据行进行加锁，那么需要先获取这些数据行所在的数据页上的锁，然后再获取对应的行锁，这样就会产生很多额外的开销，影响系统性能。

为了减少这种开销，InnoDB使用意向锁来帮助事务减少对锁的获取次数。意向锁并不实际限制数据行的访问，而是用来指示一个事务即将获取的锁的类型（共享锁或排他锁）。当事务请求获取一个数据行上的行级别锁时，InnoDB会首先获取该数据页的意向锁，以确保该数据页上不会出现与该事务请求相冲突的其他锁。

在 InnoDB 存储引擎中，意向锁分为意向共享锁（Intention Shared Lock）和意向排他锁（Intention Exclusive Lock）两种。意向共享锁表示事务需要在一个数据页上获取多个行共享锁，而意向排他锁表示事务需要在一个数据页上获取行排他锁。

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁并不是一个实际存在的锁，它只是对一个事务请求所需的行级别锁的指示。意向锁是由数据库自己维护的，用户无法手动操作意向锁。它的作用是帮助数据库减少锁竞争，提高并发性能。

当一个事务需要对一个数据行加锁时，InnoDB 引擎会先获取该数据行所在的数据页上的意向锁，然后再获取对应的行级别锁。这样可以确保该数据页上不会出现与该事务请求相冲突的其他锁。

意向锁之间是互相兼容的。

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

9.当前读和快照读有什么区别？

当前读和快照读的区别主要在于读取的数据版本不同，以及读取的方式和时间不同。

1.数据版本不同

• 当前读：读取的是最新的数据版本。
• 快照读：读取的是事务开始时的数据版本，也称为一致性读。

2.取方式和时间不同

• 当前读：需要加锁，保证读取的数据是最新的，适用于修改、删除等操作，会阻塞其他事务的写操作，但不会阻塞读操作。
• 快照读：不需要加锁，可以并发读取数据，适用于查询操作。快照读的时间点是事务开始时，只能读取到该时间点之前的数据版本。

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读：

• 在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
• 在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

10.自增锁有了解吗？

自增锁是 InnoDB 存储引擎中的一种特殊的行级锁，主要用于保证自增列的唯一性。在 InnoDB 存储引擎中，自增列一般通过给自增列加一个独占锁来保证唯一性。这个独占锁就是自增锁。

当一个事务往一张表中插入一行数据时，如果这行数据的自增列值需要通过获取自增锁来实现自增，那么该事务会在该自增锁上加一个排他锁，然后获取自增值并插入到新的行中，最后释放锁。其他事务在获取自增值时会阻塞，直到持有自增锁的事务释放锁。

自增锁的作用是保证自增列的唯一性，避免出现重复的自增值。它的加锁范围是自增索引上的锁，锁定自增值的同时，也会锁定自增值的上一个值，避免其他事务在插入数据时重复使用自增值。

需要注意的是，由于自增锁的使用会造成事务的阻塞，因此在高并发的场景下，如果频繁地往表中插入数据，就可能会出现自增锁成为瓶颈的情况。在这种情况下，可以考虑使用非自增列，或者使用更高效的自增列实现方式，例如使用主键生成器来生成唯一的主键值。

11.如何处理死锁问题？

在数据库中，锁是为了保证数据的一致性和完整性而引入的机制。但是，当多个事务同时获取锁并且互相等待对方释放锁的时候，就会发生死锁问题。这时候，事务将无法继续执行下去，也无法回滚或提交，只能被强制终止。

解决策略：

等待：直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。

死锁检测：发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

死锁检测缺点：每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。要耗费大量的CPU资源