如何查询mysql事务未提交

打开全日志后可以看。

打开的方法是：

1、50的版本

在配置文件的mysqld段中，增加

log=/var/log/mysqllog(或者其他的你想放日志的路径)

然后重启数据库

2、如果是51的版本

在配置文件的mysqld段中，增加

general_log_file=/var/log/mysqllog

然后重启数据库

51版本也可以不用重启，全局设置一下，方法是：

set global general_log=1;

set global general_log_file=/var/log/mysqllog;

打开日志后，查看未提交事务的方法为：

连接到数据库后，会分配一个连接id，然后追踪此连接id，找到此连接执行的所有sql，如果有begin，而没有commit，那么这个语句就是未提交的。

查看 MySQL 客户端的事务提交方式命令：select @@autocommit;

修改 MySQL 客户端的事务提交方式为手动提交命令：set @@autocommit = 0;

（注：0 表示手动提交，即使用 MySQL 客户端执行 SQL 命令后必须使用commit命令执行事务，否则所执行的 SQL 命令无效，如果想撤销事务则使用 rollback 命令。1 表示自动提交，即在 MySQL 客户端不在需要手动执行 commit 命令。）

MySQL 在自动提交模式下，每个 SQL 语句都是一个独立的事务。

注意：

1、手动设置set @@autocommit = 0，即设定为非自动提交模式，只对当前的mysql命令行窗口有效，打开一个新的窗口后，默认还是自动提交；

2、对于非自动提交模式，比如在命令行中添加一条记录，退出命令行后在重新打开命令行，之前插入的记录是不在的。（用select from + 表名 验证一下就可以了）

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

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

用户反馈某功能页面报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_schemainnodb_trx中查看到。在事务没有完成之前，表上的锁不会释放，alter table同样获取不到metadata的独占锁。

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

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

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

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

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

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

1、前言

面试官：我看你简历上写了熟悉redis，看来工作中用的很多吧？

我：是的，我们项目中经常用到redis（来，随便问，看我分分钟秒杀你）

面试官：那你给我说说redis的事务和mysql的事务有什么区别吧

我：额。。。事务还有区别？？？？

面试官：比如说redis的事务是不支持原子性和持久性的，包括他们的实现原理等方面也是有很大区别的。

我：学到了。。。。。。

2、正文

事务的四大特性

ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

说的是一个事物内所有 *** 作就是最小的一个 *** 作单元，要么全部成功，要么全部失败。这是最基本的特性，保证了因为一些其他因素导致数据库异常，或者宕机。

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。

一致性有下面特点：

在现实中，事务系统遭遇并发请求时，这种串行化是有成本的， Amdahl法则描述如下：它是描述序列串行执行和并发之间的关系。

“一个程序在并行计算情况下使用多个处理器所能提升的速度是由这个程序中串行执行部分的时间决定的。”

大多数数据库管理系统选择（默认情况下）是放宽一致性，以达到更好的并发性。

事物的隔离性，基于原子性和一致性，因为事物是原子化，量子化的，所以，事物可以有多个原子包的形式并发执行，但是，每个事物互不干扰。

但是，由于多个事物可能 *** 作同一个资源，不同的事物为了保证隔离性，会有很多锁方案，当然这是数据库的实现，他们怎么实现的，我们不必深究。

持久性，当一个事物提交之后，数据库状态永远的发生了改变，即这个事物只要提交了，哪怕提交后宕机，他也确确实实的提交了，不会出现因为刚刚宕机了而让提交不生效，是要事物提交，他就像洗不掉的纹身，永远的固化了，除非你毁了硬盘。

事务命令

mysql：

Begin：显式的开启一个事务

Commit：提交事务，将对数据库进行的所有的修改变成永久性

Rollback：结束用户的事务，并撤销现在正在进行的未提交的修改

redis：

Multi：标记事务的开始

Exec：执行事务的commands队列

Discard：结束事务，并清除commands队列

默认状态

mysql：

mysql会默认开启一个事务，且缺省设置是自动提交，即每成功执行sql，一个事务就会马上commit，所以不能rollback，

redis：

redis默认不会开启事务，即command会立即执行，而不会排队，并不支持rollback

使用方式

mysql（包含两种方式）：

用Begin、Rollback、commit显式开启并控制一个 新的 Transaction

执行命令 set autocommit=0，用来禁止当前会话自动commit，控制 默认开启的事务

redis：

用multi、exec、discard，显式开启并控制一个Transaction。

（注意：这里没有强调 “新的” ，因为默认是不会开启事务的）。

实现原理

mysql：

mysql实现事务，是基于undo/redo日志

undo记录修改前状态，rollback基于undo日志实现

redo记录修改后的状态，commit基于redo日志实现

既然是基于redo日志实现记录修改后的状态，那么大家应该也知道，redo日志是innodb专有的，所以innodb会支持事务

在mysql中无论是否开启事务，sql都会被立即执行并返回执行结果，只是事务开启后执行后的状态只是记录在redo日志，执行commit之后，数据才会被写入磁盘

（以上内容后面我会详细在mysql篇给大家讲到，大家可以先简单了解下）

所以，上述代码，insertSelective 将会被立即赋值（无论是否开启事务，只是结果或未被写入磁盘）：

redis:

redis实现事务，是基于commands队列

如果没有开启事务，command将会被立即执行并返回执行结果，并且直接写入磁盘

如果事务开启，command不会被立即执行，而是排入队列，并返回排队状态（具体依赖于客户端（例如：spring-data-redis）自身实现）。

调用exec才会执行commands队列

以上代码如果没有开启事务， *** 作被立即执行，a将会被立即赋值（true/false）

如果开启事务， *** 作不会被立即执行，将会返回null值，而a的类型是boolean，所以将会抛出异常：

Redis事务不支持Rollback（重点）

事实上Redis命令在事务执行时可能会失败，但仍会继续执行剩余命令而不是Rollback（事务回滚）。如果你使用过关系数据库，这种情况可能会让你感到很奇怪。然而针对这种情况具备很好的解释：

redis 事务中的错误

事务期间，可能会遇到两种命令错误：

客户端会在EXEC调用之前检测第一种错误。 通过检查排队命令的状态回复（注意：这里是指排队的状态回复，而不是执行结果），如果命令使用QUEUED进行响应，则它已正确排队，否则Redis将返回错误。如果排队命令时发生错误，大多数客户端将中止该事务并清除命令队列。然而：

这是由于INCR命令的语法错误，将在调用EXEC之前被检测出来，并终止事务（version265+）。

EXEC命令执行之后发生的错误并不会被特殊对待：即使事务中的某些命令执行失败，其他命令仍会被正常执行。

术式之后皆为逻辑，一切皆为需求和实现。希望此文能从需求、现状和解决方式的角度帮大家理解隔离级别。

隔离级别的产生

在串型执行的条件下，数据修改的顺序是固定的、可预期的结果，但是并发执行的情况下，数据的修改是不可预期的，也不固定，为了实现数据修改在并发执行的情况下得到一个固定、可预期的结果，由此产生了隔离级别。

所以隔离级别的作用是用来平衡数据库并发访问与数据一致性的方法。

事务的4种隔离级别

READ UNCOMMITTED       未提交读，可以读取未提交的数据。READ COMMITTED         已提交读，对于锁定读(select with for update 或者 for share)、update 和 delete 语句，                       InnoDB 仅锁定索引记录，而不锁定它们之间的间隙，因此允许在锁定的记录旁边自由插入新记录。                       Gap locking 仅用于外键约束检查和重复键检查。REPEATABLE READ        可重复读，事务中的一致性读取读取的是事务第一次读取所建立的快照。SERIALIZABLE           序列化

在了解了 4 种隔离级别的需求后，在采用锁控制隔离级别的基础上，我们需要了解加锁的对象(数据本身&间隙)，以及了解整个数据范围的全集组成。

数据范围全集组成

SQL 语句根据条件判断不需要扫描的数据范围（不加锁）；

SQL 语句根据条件扫描到的可能需要加锁的数据范围；

以单个数据范围为例，数据范围全集包含：（数据范围不一定是连续的值，也可能是间隔的值组成）

1 数据已经填充了整个数据范围：（被完全填充的数据范围，不存在数据间隙）

整形，对值具有唯一约束条件的数据范围 1～5 ，

已有数据1、2、3、4、5，此时数据范围已被完全填充；

整形，对值具有唯一约束条件的数据范围 1 和 5 ，

已有数据1、5，此时数据范围已被完全填充；

2 数据填充了部分数据范围：（未被完全填充的数据范围，是存在数据间隙）

整形的数据范围 1～5 ，

已有数据 1、2、3、4、5，但是因为没有唯一约束，

所以数据范围可以继续被 1～5 的数据重复填充；

整形，具有唯一约束条件的数据范围 1～5 ，

已有数据 2，5，此时数据范围未被完全填充，还可以填充 1、3、4 ；

3 数据范围内没有任何数据（存在间隙）

如下：

整形的数据范围 1～5 ，数据范围内当前没有任何数据。

在了解了数据全集的组成后，我们再来看看事务并发时，会带来的问题。

无控制的并发所带来的问题

并发事务如果不加以控制的话会带来一些问题，主要包括以下几种情况。

1 范围内已有数据更改导致的：

更新丢失：当多个事务选择了同一行，然后基于最初选定的值更新该行时，

由于每个事物不知道其他事务的存在，最后的更新就会覆盖其他事务所做的更新；

脏读： 一个事务正在对一条记录做修改，这个事务完成并提交前，这条记录就处于不一致状态。

这时，另外一个事务也来读取同一条记录，如果不加控制，

第二个事务读取了这些“脏”数据，并据此做了进一步的处理，就会产生提交的数据依赖关系。

这种现象就叫“脏读”。

2 范围内数据量发生了变化导致：

不可重复读：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，

却发现其读出的数据已经发生了改变，或者某些记录已经被删除了。

这种现象就叫“不可重复读”。

幻读：一个事务按相同的查询条件重新读取以前检索过的数据，

却发现其他事务插入了满足其查询条件的新数据，这种现象称为“幻读”。

可以简单的认为满足条件的数据量变化了。

因为无控制的并发会带来一系列的问题，这些问题会导致无法满足我们所需要的结果。因此我们需要控制并发，以实现我们所期望的结果(隔离级别)。

MySQL 隔离级别的实现

InnoDB 通过加锁的策略来支持这些隔离级别。

行锁包含：

Record Locks

索引记录锁，索引记录锁始终锁定索引记录，即使表中未定义索引，

这种情况下，InnoDB 创建一个隐藏的聚簇索引，并使用该索引进行记录锁定。

Gap Locks

间隙锁是索引记录之间的间隙上的锁，或者对第一条记录之前或者最后一条记录之后的锁。

间隙锁是性能和并发之间权衡的一部分。

对于无间隙的数据范围不需要间隙锁，因为没有间隙。

Next-Key Locks

索引记录上的记录锁和索引记录之前的 gap lock 的组合。

假设索引包含 10、11、13 和 20。

可能的next-key locks包括以下间隔，其中圆括号表示不包含间隔端点，方括号表示包含端点：

(负无穷大, 10]    (10, 11]    (11, 13]    (13, 20]    (20, 正无穷大)        对于最后一个间隔，next-key将会锁定索引中最大值的上方，

左右滑动进行查看

"上确界"伪记录的值高于索引中任何实际值。

上确界不是一个真正的索引记录，因此，实际上，这个 next-key 只锁定最大索引值之后的间隙。

基于此，当获取的数据范围中，数据已填充了所有的数据范围，那么此时是不存在间隙的，也就不需要 gap lock。

对于数据范围内存在间隙的，需要根据隔离级别确认是否对间隙加锁。

默认的 REPEATABLE READ 隔离级别，为了保证可重复读，除了对数据本身加锁以外，还需要对数据间隙加锁。

READ COMMITTED 已提交读，不匹配行的记录锁在 MySQL 评估了 where 条件后释放。

对于 update 语句，InnoDB 执行 "semi-consistent" 读取，这样它会将最新提交的版本返回到 MySQL，

以便 MySQL 可以确定该行是否与 update 的 where 条件相匹配。

总结&延展：

唯一索引存在唯一约束，所以变更后的数据若违反了唯一约束的原则，则会失败。

当 where 条件使用二级索引筛选数据时，会对二级索引命中的条目和对应的聚簇索引都加锁；所以其他事务变更命中加锁的聚簇索引时，都会等待锁。

行锁的增加是一行一行增加的，所以可能导致并发情况下死锁的发生。

例如，

在 session A 对符合条件的某聚簇索引加锁时，可能 session B 已持有该聚簇索引的 Record Locks，而 session B 正在等待 session A 已持有的某聚簇索引的 Record Locks。

session A 和 session B 是通过两个不相干的二级索引定位到的聚簇索引。

session A 通过索引 idA，session B通过索引 idB 。

当 where 条件获取的数据无间隙时，无论隔离级别为 rc 或 rr，都不会存在间隙锁。

比如通过唯一索引获取到了已完全填充的数据范围，此时不需要间隙锁。

间隙锁的目的在于阻止数据插入间隙，所以无论是通过 insert 或 update 变更导致的间隙内数据的存在，都会被阻止。

rc 隔离级别模式下，查询和索引扫描将禁用 gap locking，此时 gap locking 仅用于外键约束检查和重复键检查（主要是唯一性检查）。

rr 模式下，为了防止幻读，会加上 Gap Locks。

事务中，SQL 开始则加锁，事务结束才释放锁。

就锁类型而言，应该有优化锁，锁升级等，例如rr模式未使用索引查询的情况下，是否可以直接升级为表锁。

就锁的应用场景而言，在回放场景中，如果确定事务可并发，则可以考虑不加锁，加快回放速度。

锁只是并发控制的一种粒度，只是一个很小的部分：

从不同场景下是否需要控制并发，(已知无交集且有序的数据的变更，MySQL 的 MTS 相同前置事务的多事务并发回放)

并发控制的粒度，(锁是一种逻辑粒度，可能还存在物理层和其他逻辑粒度或方式)

相同粒度下的优化，(锁本身存在优化，如IX、IS类型的优化锁)

粒度加载的安全&性能(如获取行锁前，先获取页锁，页锁在执行获取行锁 *** 作后即释放，无论是否获取成功)等多个层次去思考并发这玩意。

mysql的4种事务隔离级别，如下所示：

1、未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据。

2、提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。

3、可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读，但是innoDB解决了幻读。

4、串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

相关简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

以上就是关于如何查询mysql事务未提交全部的内容，包括:如何查询mysql事务未提交、如何查看和修改MySQL数据库的事务提交方式、MySQL数据库表被锁、解锁，删除事务等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！