数据库

mysql 事务没提交前 存放在哪？

Posted Cola🌀

转载：https://blog.51cto.com/u_16175432/8222297

MySQL是一种开源的关系型数据库管理系统，被广泛应用于各种Web应用程序和数据驱动的应用中。在开发过程中，我们常常需要利用MySQL的事务来确保数据的完整性和一致性。事务是一系列数据库操作的集合，要么全部成功提交，要么全部失败回滚。然而，当我们执行一个事务时，可能会遇到一个问题：事务在没有提交之前，存放在哪里？

在MySQL中，事务是在内存中进行的，也就是说，所有的数据操作都是在内存中进行的，并没有真正写入到磁盘上。这是为了提高事务的性能和效率。当我们执行一条SQL语句时，MySQL会将其保存在内存的事务缓冲区中，直到事务提交后才会将数据写入磁盘。

下面我们通过一个示例来说明事务是如何存放的。

首先，我们需要创建一个测试表，并插入一些数据：

CREATE TABLE test (
  id INT PRIMARY KEY,
  data VARCHAR(50)
);

INSERT INTO test (id, data) VALUES (1, 'data1');
INSERT INTO test (id, data) VALUES (2, 'data2');

接下来，我们开启一个事务，并进行一些数据操作：

START TRANSACTION;
INSERT INTO test (id, data) VALUES (3, 'data3');
UPDATE test SET data = 'data4' WHERE id = 1;

在此时，事务中的数据操作都只存在于内存中的事务缓冲区中，并没有写入到磁盘。我们可以使用以下SQL语句查看当前事务的状态：

SHOW ENGINE INNODB STATUS;

在返回的结果中，可以找到以下信息：

------------
TRANSACTIONS
------------
Trx id counter 1674
Purge done for trx's n:o < 1671 undo n:o < 0 state: running but idle
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1, OS thread handle 0x700001dbd000, query id 2 localhost root init
show engine innodb status
---TRANSACTION 1672, ACTIVE 11 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x700001dc0000, query id 3 localhost root cleaning up
Trx read view will not see trx with id >= 1673, sees < 1673

从上面的结果中，我们可以看到当前事务的状态为ACTIVE，表示事务正在执行中。

接下来，我们提交事务：

COMMIT;

在提交事务后，MySQL会将事务中的数据写入到磁盘，并释放事务缓冲区的内存。

总结一下，MySQL的事务在没有提交之前，存放在内存中的事务缓冲区中。只有在事务提交时，才会将数据写入到磁盘。这种机制可以提高事务的性能和效率，但也存在一定的风险。如果在事务提交之前发生系统崩溃或断电等异常情况，可能会导致数据丢失或不一致。

最后，我们用饼状图来表示MySQL事务存放的过程：


从图中可以看出，事务的存放过程中，60%的时间在内存事务缓冲区中，40%的时间在磁盘中。

通过本文的介绍，我们了解了MySQL事务在没有提交之前是存放在内存中的事务缓冲区中。这种机制既提高了事务的性能和效率，又存在一定的风险。因此，在实际应用中，我们需要根据具体情况来选择合适的事务提交时机，以确保数据的完整性和一致性。

补充

因为MySQL的事务管理主要是基于InnoDB存储引擎的，并且事务的状态（例如，是否已提交或回滚）通常是内部的、不直接暴露给用户的,但是可以通过一些方法间接地检查或诊断与事务相关的问题

查看正在运行的事务

使用SHOW ENGINE INNODB STATUS;命令可以查看InnoDB存储引擎的状态，其中包含了关于当前正在运行的事务的信息。在输出中，你可以查找TRANSACTIONS部分，它列出了当前活跃的事务。

我们把查询的status复制出来，看TRANSACTIONS部分,他表示当前活动的事务，包括等待和运行中的事务。

TRANSACTIONS
------------
Trx id counter 1171181
Purge done for trx's n:o < 1170292 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283462706467760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283462706466936, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283462706466112, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283462706465288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283462706464464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------

包含信息

  • Trx id：事务的唯一标识符，用于跟踪和识别特定的事务。
  • State：事务的当前状态，如RUNNING、SLEEPING、LOCK WAIT等。RUNNING表示事务正在执行，SLEEPING表示事务在等待某些条件成立（如等待锁释放），而LOCK WAIT表示事务正在等待获取锁。
  • MySQL thread id：与事务关联的MySQL线程ID，这有助于将事务与特定的客户端连接或查询关联起来。
  • OS thread id：操作系统级别的线程ID，用于在更底层进行调试和诊断。
  • Locked tables：事务当前锁定的表列表。如果事务锁定了多个表，这里会列出所有被锁定的表。
  • Type：事务的类型，例如只读事务或读写事务。
  • Started：事务开始的时间戳。

例子解读

  • Trx id counter 1171181 表示当前事务ID计数器的值为1171181。每次InnoDB开始一个新事务时，它都会从这个计数器中获取一个新的、唯一的事务ID。
  • Purge done for trx's n:o < 1171181undo n:o < 0 state: running but idle 意味着InnoDB的清除操作已经完成，已经清除了所有事务ID小于1171181的事务的撤销日志（undo logs）。撤销日志用于支持事务的回滚和MVCC（多版本并发控制）。running but idle状态表明清除操作当前正在运行，但当前没有执行任何任务（即它是空闲的）。
  • History list length 0 表示历史列表的长度为0。历史列表用于跟踪已经被清除但尚未从系统表空间物理删除的撤销日志记录。长度为0表示当前没有这样的记录。
  • LIST OF TRANSACTIONS FOR EACH SESSION: 下面的内容列出了每个会话的事务信息。在您提供的输出中，只有一个事务：
---TRANSACTION 283462706464464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

这个事务ID为283462706464464的事务尚未开始（not started）。它当前没有锁定任何结构（0 lock struct(s)），没有行级锁（0 row lock(s)），并且它的堆大小为1136字节。

如果未回滚的事务，TRANSACTIONS部分中状态为RUNNING或LOCK WAIT的事务，并且这些事务可能已经持续了一段时间

查看数据库进程

SHOW PROCESSLIST; 是一个在 MySQL 和 MariaDB 数据库管理系统中常用的命令。这个命令用于显示当前在服务器上运行的所有进程列表。这些进程可能包括正在执行的查询、正在等待的查询、休眠状态的线程等。

SHOW PROCESSLIST;

字段解释

  • Id: 进程的唯一标识符。
  • User: 执行该进程的 MySQL 用户。
  • Host: 用户的主机名或 IP 地址。
  • db: 当前数据库的名称（如果有的话）。
  • Command: 进程正在执行的命令类型（例如，Sleep、Query、Binlog Dump 等）。
  • Time: 进程已经运行的时间（以秒为单位）。
  • Info: 进程正在执行的 SQL 查询（如果有的话）。
  • State: 进程的当前状态（例如，running、Waiting for table metadata lock 等）。
    如果查询正在执行，State的值可能是running；
    如果查询正在等待资源或锁，State的值可能是Waiting for table metadata lock；
    如果连接处于空闲状态，State的值可能是Sleep。