MySQL面试题-锁相关

最新推荐文章于 2024-08-31 18:53:42 发布

java路飞

最新推荐文章于 2024-08-31 18:53:42 发布

阅读量2.1k

点赞数 5

分类专栏： mysql 面试复习文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/qq_33129875/article/details/129362480

版权

面试复习同时被 2 个专栏收录

37 篇文章 36 订阅

订阅专栏

mysql

10 篇文章 2 订阅

订阅专栏

本文详细介绍了MySQL中的锁类型，包括全局锁、表级锁和行级锁，强调了不同锁的使用场景和注意事项。全局锁用于备份等全局操作，表级锁分为读写锁，行级锁则提供更高的并发性能。文章还讨论了InnoDB的行锁机制，如Next-Key锁，以及处理死锁的方法。此外，提到了共享锁和排他锁的概念，以及如何使用意向锁来优化并发性能。

摘要由CSDN通过智能技术生成

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

2.如何使用全局锁

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

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

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

全局锁（Global Lock）：锁定整个 MySQL 实例，用于执行全局操作时，比如备份整个数据库。全局锁会阻塞所有对数据库的操作，因此在使用全局锁时需要慎重考虑。
表级锁（Table-level Lock）：锁定整张表，适用于只有少量访问，或者需要锁定整张表时。表级锁分为读锁（共享锁）和写锁（排他锁）。
- 共享锁（Shared Lock）：允许多个事务同时读取同一张表，但是不能进行写操作。如果一个事务持有共享锁，其他事务也可以持有共享锁。共享锁之间不会互相阻塞。
- 排他锁（Exclusive Lock）：只允许一个事务进行写操作，其他事务不能进行读取或写入操作。如果一个事务持有排他锁，其他事务无法获得共享锁或排他锁。排他锁会阻塞其他事务的读写操作，因此需要慎重使用。
行级锁（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 提供了三种行级锁定方式，分别是：

记录锁（Record Lock）：也称行锁，只锁定某个索引上的一行记录，即对某个记录加锁。
间隙锁（Gap Lock）：锁定索引记录之间的间隙，但不包括记录本身，锁定某个范围之前的间隙或之后的间隙，但不包括指定范围的记录本身。例如，使用 WHERE 子句查询某个范围内的记录时，如果使用间隙锁定，将锁定范围之前或之后的间隙，避免其他事务插入相同范围的记录。
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 引擎会先获取该数据行所在的数据页上的意向锁，然后再获取对应的行级别锁。这样可以确保该数据页上不会出现与该事务请求相冲突的其他锁。

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