MySQL会自动回滚未提交事务以保证数据一致性，重启时InnoDB通过redo log进行崩溃恢复，回滚未完成事务；可通过INFORMATION_SCHEMA表查看未提交事务，长时间运行的事务会导致锁争用、资源消耗和日志膨胀，应通过缩短事务、设置超时等措施避免。
MySQL 恢复未提交事务，通常是指在系统崩溃或意外中断后，如何将那些尚未明确提交（COMMIT）的事务回滚，以保证数据的一致性和完整性。简单来说，MySQL会自动处理这种情况，但了解其背后的机制能帮你更好地应对潜在问题。
MySQL通过其事务日志（通常是InnoDB的redo log）来实现事务的持久性和回滚。
解决方案
MySQL的InnoDB存储引擎会自动处理未提交事务的回滚。当你重启MySQL服务器后，InnoDB会扫描事务日志，找到所有未完成的事务，并将其回滚到事务开始之前的状态。这个过程被称为崩溃恢复。
具体步骤：
-
InnoDB 事务日志： InnoDB使用事务日志记录所有事务的更改。这些日志是顺序写入的，包含了事务的开始、修改和提交信息。
-
崩溃检测： 当MySQL服务器启动时，InnoDB会检查事务日志，确定是否存在未完成的事务。
-
回滚未提交事务： 对于每个未提交的事务，InnoDB会读取事务日志中的记录，并将数据恢复到事务开始之前的状态。这个过程会撤销所有未提交的更改。
-
完成恢复： 一旦所有未提交的事务都被回滚，MySQL服务器就可以正常提供服务了。
需要注意的是，如果MySQL服务器在写入事务日志的过程中崩溃，可能会导致部分事务日志损坏。在这种情况下，InnoDB可能会尝试修复事务日志，但可能会丢失部分数据。因此，定期备份数据库是非常重要的。
如何查看当前是否有未提交的事务？
虽然MySQL会自动处理回滚，但你可能想知道当前是否有未提交的事务。你可以通过以下方式查看：
-
SHOW OPEN TABLES
命令： 这个命令可以显示当前打开的表以及它们的状态。如果一个表被一个未提交的事务锁定，你可能会看到
in use
的状态。
SHOW OPEN TABLES WHERE in_use > 0;
-
INFORMATION_SCHEMA.INNODB_LOCKS
和
INFORMATION_SCHEMA.INNODB_TRX
表： 这两个表包含了关于InnoDB锁和事务的详细信息。你可以查询这些表来查找未提交的事务以及它们持有的锁。
SELECT trx.trx_id, trx.trx_started, trx.trx_mysql_thread_id, lock.lock_table, lock.lock_type FROM INFORMATION_SCHEMA.INNODB_TRX trx INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS lock ON trx.trx_id = lock.lock_trx_id WHERE trx.trx_state = 'RUNNING';
-
performance_schema
： 如果启用了
performance_schema
，你可以使用它来监控事务的执行情况。
SELECT * FROM performance_schema.events_transactions_current;
理解这些信息可以帮助你诊断长时间运行的事务或潜在的死锁问题。
长时间运行的未提交事务会带来哪些问题？
长时间运行的未提交事务可能会导致以下问题：
-
锁定： 未提交的事务会持有数据库锁，阻止其他事务访问或修改相同的数据。这会导致性能下降，甚至死锁。
-
资源消耗： 长时间运行的事务会占用数据库资源，如内存和CPU，影响其他事务的执行。
-
数据一致性： 如果一个事务长时间未提交，其他事务可能无法看到最新的数据，导致数据不一致。
-
日志增长： 未提交的事务会持续写入事务日志，导致日志文件增长过快。
如何避免长时间运行的未提交事务？
-
保持事务简短： 尽量将事务分解成更小的单元，减少事务的持续时间。
-
尽早提交： 在事务完成后尽快提交，释放锁和资源。
-
使用合适的隔离级别： 选择合适的事务隔离级别，避免不必要的锁定。例如，如果不需要可重复读，可以使用读已提交隔离级别。
-
监控事务： 定期监控数据库中的事务，查找长时间运行的事务并进行处理。
-
设置超时： 可以设置事务的超时时间，防止事务无限期地运行。
SET innodb_lock_wait_timeout = 60; -- 设置InnoDB锁等待超时时间为60秒
-
优化SQL查询： 优化SQL查询可以减少事务的执行时间。
通过以上措施，可以有效地避免长时间运行的未提交事务，提高数据库的性能和稳定性。