MySQL锁「连环问」1、什么是MySQL锁？可以从MySQL锁的定义、类型、实现、作用、优化、注意事项等方面回答：

1、什么是MySQL锁？

可以从MySQL锁的定义、类型、实现、作用、优化、注意事项等方面回答：

（定义：）MySQL锁是一种并发控制机制，用于保护共享资源的完整性和一致性。在多个线程或进程同时访问共享资源时，锁可以确保只有一个线程或进程可以访问共享资源，以避免竞争条件和数据不一致。（类型：）MySQL锁可以分为表级锁和行级锁两种类型，表级锁可以锁定整个表，行级锁可以锁定表中的某一行或某些行。（实现：）MySQL使用不同的锁机制来实现表级锁和行级锁，MyISAM使用表级锁，InnoDB使用行级锁来实现并发控制。（作用：）MySQL锁的作用是保护数据的一致性和可靠性，以确保多个事务不会同时修改相同的数据。 同时，锁还可以用于提高并发性能和避免死锁。（优化：）MySQL锁的性能是数据库性能的关键因素之一，需要通过优化查询语句、调整锁粒度、使用索引等方式来优化MySQL锁的性能。（注意事项：）在使用MySQL锁的过程中，需要注意避免死锁、避免长时间持有锁、避免过多的锁等问题。

2、MySQL锁分类

表锁：开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁
页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
行锁：
- 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
- 只锁记录，特定几行记录
记录锁：直接锁定某行记录
间隙锁：只锁间隙，前开后开区间(a,b)，对索引的间隙加锁，防止其他事务插入数据
临键锁：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁
插入意向锁：插入时使用的锁。插入意图锁，实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。
共享锁：S锁和S锁兼容，通过lock in share mode实现，这时候只能读不能写。
排他锁：又称为写锁，独占锁。阻塞其他写锁和读锁，只有获取排他锁的事务可以对数据进行读取和修改

表（级）锁：

共享锁（Shared Lock）
独占锁/排他锁（Exclusive Lock）
意向共享锁（Intent Share Lock）
意向独占锁（Intent Exclusive Lock）
读锁（Read Lock）
写锁（Write Lock）
表锁（Table Lock）

行（级）锁：

共享锁（Shared Lock）
独占锁/排他锁（Exclusive Lock）
意向共享锁（Intention Share Lock）
意向独占锁（Intention Exclusive Lock）
记录锁（Record Lock）

3、表级锁与行级锁

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。InnoDB 不光支持表级锁，还支持行级锁(row-level locking)，默认为行级锁。行级锁的粒度更小，仅对相关的记录中锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说，InnoDB 的性能更高。

表级锁：MySQL中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁：MySQL中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁!!!

InnoDB 支持三种行锁定方式：记录锁、间隙锁、临键锁

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

4、共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁（S 锁） ：又称读锁，多个事务对于同一数据可以共享一把锁（锁兼容，事务在读的时候获取共享锁），都能访问到数据，但是只能读不能修改。
排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。获得了排他锁的事务是可以对数据进行读取和修改的。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

	S锁	X锁
S锁	不冲突	冲突
X锁	冲突	冲突

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过，你可以通过以下语句显式加共享锁或排他锁。

-- 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
-- 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
-- 排他锁
SELECT ... FOR UPDATE;

注意：对于共享锁大家可能很好理解，就是多个事务只能读数据不能改数据，对于排他锁大家的理解可能就有些差别，这里有一个重点就是：排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。

mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加了排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

【面试题】MySQL中select语句后面加for update等关键词的用处是什么？

for update是在数据库中上锁用的，可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。

5、意向锁有什么作用？

意向锁是一个表级锁，不要和插入意向锁搞混。

意向锁的出现是为了支持InnoDB的多粒度锁，它解决的是表锁和行锁共存的问题。当我们需要给一个表加表锁的时候，我们需要根据去判断表中有没有数据行被锁定，以确定是否能加成功。

假如没有意向锁，那么我们就得遍历表中所有数据行来判断有没有行锁；有了意向锁这个表级锁之后，则我们直接判断一次就知道表中是否有数据行被锁定了。

意向锁共有两种：

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

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

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

	IS锁	IX锁
IS锁	兼容	兼容
IX锁	兼容	兼容

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

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

6、MyISAM和InnoDB之间的锁机制有什么不同？

MyISAM使用表级锁，而InnoDB使用行级锁。这意味着在MyISAM中，当一个事务正在修改表中的一行时，其他事务不能对该表进行任何读取或写入操作，而在InnoDB中，其他事务可以同时读取或修改表中的其他行。
MyISAM只支持共享锁和排他锁，而InnoDB支持共享锁、排他锁和意向锁。意向锁是一种表级锁，用于表示事务在请求行级锁时的意向，以便其他事务可以更好地进行并发控制。
MyISAM的锁定粒度比InnoDB的锁定粒度更大。在MyISAM中，当一个事务请求锁定时，整个表都将被锁定，而在InnoDB中，只有需要锁定的行被锁定。
MyISAM的锁定速度比InnoDB的锁定速度更快，但是在高并发的情况下，MyISAM的性能会受到影响。在高并发的情况下，InnoDB的行级锁可以提供更好的并发性和性能。

总之，MyISAM和InnoDB之间的锁机制存在很大的差异。在选择存储引擎时，应该根据应用程序的需求来选择合适的存储引擎。如果应用程序需要高并发性和性能，那么InnoDB可能是更好的选择。

7、如何使用InnoDB实现表级锁？

使用InnoDB实现表级锁，可以通过以下几个步骤：

确认表的存储引擎为InnoDB。可以通过以下语句查询表的存储引擎类型：
```
show create table table_name;
```
在需要进行并发控制的操作前，使用以下语句开启事务：
```
start transaction;
```
获取表级锁。可以使用以下语句获取排他锁：
```
lock tables table_name write;
```
或者使用以下语句获取共享锁：
```
lock tables table_name read;
```
write锁定模式表示对表进行写操作，而read锁定模式表示对表进行读操作。
在事务处理结束时，使用以下语句释放锁：
```
unlock tables;
```

使用InnoDB实现表级锁可以控制对整个表的读取和写入，但是需要注意锁的粒度和锁的持有时间，避免造成死锁或长时间阻塞。表级锁的实现方式在并发性和性能方面相对行级锁较差，因此在使用表级锁时需要根据具体的业务需求进行调整和优化

8、乐观锁和悲观锁

悲观锁（Pessimistic Concurrency Control）

悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能被改动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。

数据库中的行锁，表锁，读锁，写锁均为悲观锁。

乐观锁（Optimistic Concurrency Control）

乐观锁假设在多数情况下，多个事务在访问数据库时不会彼此冲突。它通常是通过在表中增加一个版本（version）或时间戳（timestamp）来实现，其中，版本最为常用。

乐观锁在更新数据时才进行版本检查，而不是在读取数据时就进行锁定。

事务在从数据库中取数据时，会将该数据的版本也取出来(v1)，当事务对数据变动完毕想要将其更新到表中时，会将之前取出的版本v1与数据中最新的版本v2相对比，如果v1=v2，那么说明在数据变动期间，没有其他事务对数据进行修改，此时，就允许事务对表中的数据进行修改，并且修改时version会加1，以此来表明数据已被变动。

如果，v1不等于v2，那么说明数据变动期间，数据被其他事务改动了，此时不允许数据更新到表中，一般的处理办法是通知用户让其重新操作。不同于悲观锁，乐观锁通常是由开发者实现的。

9、MySQL中如何加行锁？

使用 SELECT … FOR UPDATE 语句：在需要加锁的语句后面添加 FOR UPDATE，例如：

SELECT * FROM table WHERE id = 1 FOR UPDATE;

这样查询出的结果行将被锁定，直到事务提交或者回滚。

使用 BEGIN TRANSCATION 和 COMMIT TRANSACTION 语句：在事务中选择需要加锁的行并进行更新或删除操作，在事务提交或者回滚前，这些行将被锁定。例如：

BEGIN TRANSACTION; 
UPDATE table SET status = 'locked' WHERE id = 1; 
COMMIT;

在这个事务中，行 id=1 将被加锁并且更新其 status 字段。在 COMMIT 后，行将被解锁。

10、MySQL 遇到过死锁问题吗，你是如何解决的？

死锁是指两个或多个事务在同一资源集上相互占有资源，而又都在等待其他事务释放资源，导致它们之间相互等待，无法向前推进的情况。

排查死锁的一般步骤是这样的：

查看死锁日志 show engine innodb status;
找出死锁 sql
分析 sql 加锁情况
模拟死锁案发
分析死锁日志
分析死锁结果

当然，这只是一个简单的流程说明，实际上生产中的死锁千奇百怪，排查和解决起来没那么简单。

11、如何避免死锁？

死锁的四个必要条件：1、互斥 2、请求与保持 3、环路等待 4、不可剥夺。

合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
调整业务逻辑 SQL 执行顺序，避免 update/delete 长时间持有锁的 SQL 在事务前面。
避免大事务，将大事务拆成多个小事务
以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。
在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。
尽量用主键/索引去查找记录
优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，避免多个表join，将复杂 SQL 分解为多个简单的 SQL。