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 存储引擎
事务与锁是不同的。事务具有ACID（原子性、一致性、隔离性和持久性），锁是用于解决隔离性的一种机制。事务的隔离级别通过锁的机制来实现。另外锁有不同的粒度，同时事务也是有不同的隔离级别的（一般有四种：读未提交Read uncommitted，读已提交Read committed，
可重复读Repeatable read，
可串行化Serializable）。
在具体的程序设计中，开启事务其实是要数据库支持才行的，如果数据库本身不支持事务，那么仍然无法确保你在程序中使用的事务是有效的。
锁可以分为乐观锁和悲观锁：
悲观锁：认为在修改数据库数据的这段时间里存在着也想修改此数据的事务；
乐观锁：认为在短暂的时间里不会有事务来修改此数据库的数据；
我们一般意义上讲的锁其实是指悲观锁，在数据处理过程中，将数据置于锁定状态（由数据库实现）
如果开启了事务，在事务没提交之前，别人是无法修改该数据的；如果rollback，你在本次事务中的修改将撤消（不是别人修改的会没有，因为别人此时无法修改）。当然，前提是你使用的数据库支持事务。还有一个要注意的是，部分数据库支持自定义SQL锁覆盖事务隔离级别默认的锁机制，如果使用了自定义的锁，那就另当别论。
重点：一般事务使用的是悲观锁（具有排他性）
在程序员的职业生涯中，总会遇到数据库表被锁的情况，前些天就又撞见一次。由于业务突发需求，各个部门都在批量 *** 作、导出数据，而数据库又未做读写分离，结果就是：数据库的某张表被锁了！
用户反馈系统部分功能无法使用，紧急排查，定位是数据库表被锁，然后进行紧急处理。这篇文章给大家讲讲遇到类似紧急状况的排查及解决过程，建议点赞收藏，以备不时之需。
用户反馈某功能页面报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的锁表其实还有很多其他场景，我们在实践的过程中尽量避免锁表情况的发生，当然这需要一定经验的支撑。但更重要的是，如果发现锁表我们要能够快速的响应，快速的解决问题，避免影响正常业务，避免情况进一步恶化。所以，本文中的解决思路大家一定要收藏或记忆一下，做到有备无患，避免突然状况下抓瞎。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）