一文详解-MySQL 事务和锁

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

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 存储引擎

此文章主要是对Oracle数据库锁机制的详细研究 首先我们要介绍的是Oracle数据库锁的类型 同时也阐述 在实际应用中我们经常会遇到的与锁相关的异常情况 特别对经常遇到的由于等待锁而使事务被挂起的问题进行了定位及解决 并对死锁这一比较严重的现象 提出了相应的解决方法和具体的分析过程

数据库是一个多用户使用的共享资源 当多个用户并发地存取数据时 在数据库中就会产生多个事务同时存取同一数据的情况 若对并发 *** 作不加控制就可能会读取和存储不正确的数据 破坏数据库的一致性

加锁是实现数据库并发控制的一个非常重要的技术 当事务在对某个数据对象进行 *** 作前 先向系统发出请求 对其加锁 加锁后事务就对该数据对象有了一定的控制 在该事务释放锁之前 其他的事务不能对此数据对象进行更新 *** 作

在数据库中有两种基本的锁类型 排它锁（Exclusive Locks 即X锁）和共享锁（Share Locks 即S锁） 当数据对象被加上排它锁时 其他的事务不能对它读取和修改 加了共享锁的数据对象可以被其他事务读取 但不能修改 数据库利用这两种基本的锁类型来对Oracle数据库的事务进行并发控制

在实际应用中经常会遇到的与锁相关的异常情况 如由于等待锁事务被挂起 死锁等现象 如果不能及时地解决 将严重影响应用的正常执行 而目前对于该类问题的解决缺乏系统化研究和指导 本文在总结实际经验的基础上 提出了相应的解决方法和具体的分析过程

Oracle数据库的锁类型

根据保护的对象不同 Oracle数据库锁可以分为以下几大类 DML锁（data locks 数据锁） 用于保护数据的完整性 DDL锁（dictionary locks 字典锁） 用于保护数据库对象的结构 如表 索引等的结构定义 内部锁和闩（internal locks and latches） 保护数据库的内部结构

DML锁的目的在于保证并 *** 况下的数据完整性 本文主要讨论DML锁 在Oracle数据库中 DML锁主要包括TM锁和TX锁 其中TM锁称为表级锁 TX锁称为事务锁或行级锁

当Oracle执行DML语句时 系统自动在所要 *** 作的表上申请TM类型的锁 当TM锁获得后 系统再自动申请TX类型的锁 并将实际锁定的数据行的锁标志位进行置位 这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志 而只需检查TM锁模式的相容性即可 大大提高了系统的效率

TM锁包括了SS SX S X等多种模式 在Oracle数据库中用 － 来表示 不同的SQL *** 作产生不同类型的TM锁 如表 所示

在数据行上只有X锁（排他锁） 在 Oracle数据库中 当一个事务首次发起一个DML语句时就获得一个TX锁 该锁保持到事务被提交或回滚 当两个或多个会话在表的同一条记录上执行DML语句时 第一个会话在该条记录上加锁 其他的会话处于等待状态 当第一个会话提交后 TX锁被释放 其他会话才可以加锁

当Oracle数据库发生TX锁等待时 如果不及时处理常常会引起Oracle数据库挂起 或导致死锁的发生 产生ORA 的错误 这些现象都会对实际应用产生极大的危害 如长时间未响应 大量事务失败等

TX锁等待的分析

在介绍了有关地Oracle数据库锁的种类后 下面讨论如何有效地监控和解决锁等待现象 及在产生死锁时如何定位死锁的原因

监控锁的相关视图 数据字典是Oracle数据库的重要组成部分 用户可以通过查询数据字典视图来获得数据库的信息 和锁相关的数据字典视图如表 所示

TX锁等待的监控和解决在日常工作中 如果发现在执行某条SQL时数据库长时间没有响应 很可能是产生了TX锁等待的现象 为解决这个问题 首先应该找出持锁的事务 然后再进行相关的处理 如提交事务或强行中断事务

死锁的监控和解决在数据库中 当两个或多个会话请求同一个资源时会产生死锁的现象 死锁的常见类型是行级锁死锁和页级锁死锁 Oracle数据库中一般使用行级锁 下面主要讨论行级锁的死锁现象

当Oracle检测到死锁产生时 中断并回滚死锁相关语句的执行 报ORA 的错误并记录在Oracle数据库的日志文件alertSID log中 同时在user_dump_dest下产生了一个跟踪文件 详细描述死锁的相关信息

在日常工作中 如果发现在日志文件中记录了ora 的错误信息 则表明产生了死锁 这时需要找到对应的跟踪文件 根据跟踪文件的信息定位产生的原因

如果查询结果表明 死锁是由于bitmap索引引起的 将IND_T_PRODUCT_HIS_STATE索引改为normal索引后 即可解决死锁的问题

表 Oracle的TM锁类型

锁模式 锁描述 解释 SQL *** 作

none

NULL 空 Select

SS(Row S) 行级共享锁 其他对象只能查询这些数据行 Select for update Lock for update Lock row share

SX(Row X) 行级排它锁 在提交前不允许做DML *** 作 Insert Update Delete Lock row share

S(Share) 共享锁 Create index Lock share

SSX(S/Row X) 共享行级排它锁 Lock share row exclusive

lishixinzhi/Article/program/Oracle/201311/18509

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

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

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

---