MySQL锁表和解锁 *** 作

1、确定mysql有锁表的情况则使用以下命令查看锁表进程

2、杀掉查询结果中已经锁表的trx_mysql_thread_id

扩展：

1、查看锁的事务

2、查看等待锁的事务

3、查询是否锁表：

4、查询进程

当多个用户访问同一份数据时，一个用户在更改数据的过程中，可能有其他用户同时发起更改请求，为保证数据库记录的更新从一个一致性状态变为另外一个一致性状态，使用事务处理是非常必要的，事务具有以下四个特性：

MySQL 提供了多种事务型存储引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事务。为了支持事务，InnoDB 存储引擎引入了与事务处理相关的 REDO 日志和 UNDO 日志，同时事务依赖于 MySQL 提供的锁机制

事务执行时需要将执行的事务日志写入日志文件，对应的文件为 REDO 日志。当每条 SQL 进行数据更新 *** 作时，首先将 REDO 日志写进日志缓冲区。当客户端执行 COMMIT 命令提交时，日志缓冲区的内容将被刷新到磁盘，日志缓冲区的刷新方式或者时间间隔可以通过参数 innodb_flush_log_at_trx_commit 控制

REDO 日志对应磁盘上的 ib_logifleN 文件，该文件默认为 5MB，建议设置为 512MB，以便容纳较大的事务。MySQL 崩溃恢复时会重新执行 REDO 日志的记录，恢复最新数据，保证已提交事务的持久性

与 REDO 日志相反，UNDO 日志主要用于事务异常时的数据回滚，具体内容就是记录数据被修改前的信息到 UNDO 缓冲区，然后在合适的时间将内容刷新到磁盘

假如由于系统错误或者 rollback *** 作而导致事务回滚，可以根据 undo 日志回滚到没修改前的状态，保证未提交事务的原子性

与 REDO 日志不同的是，磁盘上不存在单独的 UNDO 日志文件，所有的 UNDO 日志均存在表空间对应的 .ibd 数据文件中，即使 MySQL 服务启动了独立表空间

在 MySQL 中，可以使用 BEGIN 开始事务，使用 COMMIT 结束事务，中间可以使用 ROLLBACK 回滚事务。MySQL 通过 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等语句支持本地事务

MySQL 定义了四种隔离级别，指定事务中哪些数据改变其他事务可见、哪些数据该表其他事务不可见。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少

InnoDB 系统级事务隔离级别可以使用以下语句设置：

查看系统级事务隔离级别：

InnoDB 会话级事务隔离级别可以使用以下语句设置：

查看会话级事务隔离级别：

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据称为脏读（Dirty Read），即是：首先开启 A 和 B 两个事务，在 B 事务更新但未提交之前，A 事务读取到了更新后的数据，但由于 B 事务回滚，导致 A 事务出现了脏读现象

所有事务只能看见已经提交事务所做的改变，此级别可以解决脏读，但也会导致不可重复读（Nonrepeatable Read）：首先开启 A 和 B 两个事务，A事务读取了 B 事务的数据，在 B 事务更新并提交后，A 事务又读取到了更新后的数据，此时就出现了同一 A 事务中的查询出现了不同的查询结果

MySQL 默认的事务隔离级别，能确保同一事务的多个实例在并发读取数据时看到同样的数据行，理论上会导致一个问题，幻读（Phontom Read）。例如，第一个事务对一个表中的数据做了修改，这种修改会涉及表中的全部数据行，同时第二个事务也修改这个表中的数据，这次的修改是向表中插入一行新数据，此时就会发生 *** 作第一个事务的用户发现表中还有没有修改的数据行

InnoDB 通过多版本并发控制机制（MVCC）解决了该问题：InnoDB 通过为每个数据行增加两个隐含值的方式来实现，这两个隐含值记录了行的创建时间、过期时间以及每一行存储时间发生时的系统版本号，每个查询根据事务的版本号来查询结果

通过强制事务排序，使其不可能相互冲突，从而解决幻读问题。简而言之，就是在每个读的数据行上加上共享锁实现，这个级别会导致大量的超时现象和锁竞争，一般不推荐使用

为了解决数据库并发控制问题，如走到同一时刻客户端对同一张表做更新或者查询 *** 作，需要对并发 *** 作进行控制，因此产生了锁

共享锁的粒度是行或者元组（多个行），一个事务获取了共享锁以后，可以对锁定范围内的数据执行读 *** 作

排他锁的粒度与共享锁相同，一个事务获取排他锁以后，可以对锁定范围内的数据执行写 *** 作

有两个事务 A 和 B，如果事务 A 获取了一个元组的共享锁，事务 B 还可以立即获取这个元组的共享锁，但不能获取这个元组的排他锁，必须等到事务 A 释放共享锁之后。如果事务 A 获取了一个元组的排他锁，事务 B 不能立即获取这个元组的共享锁，也不能立即获取这个元组的排他锁，必须等到 A 释放排他锁之后

意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁和意向排他锁。意向共享锁表示一个事务有意对数据上共享锁或者排他锁。有意表示事务想执行 *** 作但还没真正执行

锁的粒度主要分为表锁和行锁

表锁的开销最小，同时允许的并发量也是最小。MyISAM 存储引擎使用该锁机制。当要写入数据时，整个表记录被锁，此时其他读/写动作一律等待。一些特定的动作，如 ALTER TABLE 执行时使用的也是表锁

行锁可以支持最大的并发，InnoDB 存储引擎使用该锁机制。如果要支持并发读/写，建议采用 InnoDB 存储引擎

在程序员的职业生涯中，总会遇到数据库表被锁的情况，前些天就又撞见一次。由于业务突发需求，各个部门都在批量 *** 作、导出数据，而数据库又未做读写分离，结果就是：数据库的某张表被锁了！

用户反馈系统部分功能无法使用，紧急排查，定位是数据库表被锁，然后进行紧急处理。这篇文章给大家讲讲遇到类似紧急状况的排查及解决过程，建议点赞收藏，以备不时之需。

用户反馈某功能页面报502错误，于是第一时间看服务是否正常，数据库是否正常。在控制台看到数据库CPU飙升，堆积大量未提交事务，部分事务已经阻塞了很长时间，基本定位是数据库层出现问题了。

查看阻塞事务列表，发现其中有锁表现象，本想利用控制台直接结束掉阻塞的事务，但控制台账号权限有限，于是通过客户端登录对应账号将锁表事务kill掉，才避免了情况恶化。

下面就聊聊，如果当突然面对类似的情况，我们该如何紧急响应？

想象一个场景，当然也是软件工程师职业生涯中会遇到的一种场景：原本运行正常的程序，某一天突然数据库的表被锁了，业务无法正常运转，那么我们该如何快速定位是哪个事务锁了表，如何结束对应的事物？

首先最简单粗暴的方式就是：重启MySQL。对的，网管解决问题的神器——“重启”。至于后果如何，你能不能跑了，要你自己三思而后行了！

重启是可以解决表被锁的问题的，但针对线上业务很显然不太具有可行性。

下面来看看不用跑路的解决方案：

遇到数据库阻塞问题，首先要查询一下表是否在使用。

如果查询结果为空，那么说明表没在使用，说明不是锁表的问题。

如果查询结果不为空，比如出现如下结果：

则说明表（test）正在被使用，此时需要进一步排查。

查看数据库当前的进程，看看是否有慢SQL或被阻塞的线程。

执行命令：

该命令只显示当前用户正在运行的线程，当然，如果是root用户是能看到所有的。

在上述实践中，阿里云控制台之所以能够查看到所有的线程，猜测应该使用的就是root用户，而笔者去kill的时候，无法kill掉，是因为登录的用户非root的数据库账号，无法 *** 作另外一个用户的线程。

如果情况紧急，此步骤可以跳过，主要用来查看核对：

如果情况紧急，此步骤可以跳过，主要用来查看核对：

看事务表INNODB_TRX中是否有正在锁定的事务线程，看看ID是否在show processlist的sleep线程中。如果在，说明这个sleep的线程事务一直没有commit或者rollback，而是卡住了，需要手动kill掉。

搜索的结果中，如果在事务表发现了很多任务，最好都kill掉。

执行kill命令：

对应的线程都执行完kill命令之后，后续事务便可正常处理。

针对紧急情况，通常也会直接 *** 作第一、第二、第六步。

这里再补充一些MySQL锁相关的知识点：数据库锁设计的初衷是处理并发问题，作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则，而锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。MySQL中表级别的锁有两种：一种是表锁，一种是元数据锁（metadata lock，MDL)。

表锁是在Server层实现的，ALTER TABLE之类的语句会使用表锁，忽略存储引擎的锁机制。表锁通过lock tables… read/write来实现，而对于InnoDB来说，一般会采用行级锁。毕竟锁住整张表影响范围太大了。

另外一个表级锁是MDL（metadata lock），用于并发情况下维护数据的一致性，保证读写的正确性，不需要显式的使用，在访问一张表时会被自动加上。

常见的一种锁表场景就是有事务 *** 作处于：Waiting for table metadata lock状态。

MySQL在进行alter table等DDL *** 作时，有时会出现Waiting for table metadata lock的等待场景。

一旦alter table TableA的 *** 作停滞在Waiting for table metadata lock状态，后续对该表的任何 *** 作（包括读）都无法进行，因为它们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。如果核心表出现了锁等待队列，就会造成灾难性的后果。

通过show processlist可以看到表上有正在进行的 *** 作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。

通过show processlist看不到表上有任何 *** 作，但实际上存在有未提交的事务，可以在information_schema.innodb_trx中查看到。在事务没有完成之前，表上的锁不会释放，alter table同样获取不到metadata的独占锁。

处理方法：通过 select * from information_schema.innodb_trxG, 找到未提交事物的sid，然后kill掉，让其回滚。

通过show processlist看不到表上有任何 *** 作，在information_schema.innodb_trx中也没有任何进行中的事务。很可能是因为在一个显式的事务中，对表进行了一个失败的 *** 作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。

处理方法：通过performance_schema.events_statements_current找到其sid，kill 掉该session，也可以kill掉DDL所在的session。

总之，alter table的语句是很危险的（核心是未提交事务或者长事务导致的），在 *** 作之前要确认对要 *** 作的表没有任何进行中的 *** 作、没有未提交事务、也没有显式事务中的报错语句。

如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。

关于MySQL的锁表其实还有很多其他场景，我们在实践的过程中尽量避免锁表情况的发生，当然这需要一定经验的支撑。但更重要的是，如果发现锁表我们要能够快速的响应，快速的解决问题，避免影响正常业务，避免情况进一步恶化。所以，本文中的解决思路大家一定要收藏或记忆一下，做到有备无患，避免突然状况下抓瞎。

---