关于innodb:技术分享-MySQL-同一事务中更新-innodb-和-myisam-表时应怎么办

作者：刘开洋

爱可生交付服务团队北京 DBA，对数据库及周边技术有浓重的学习趣味，喜爱看书，谋求技术。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

问题

共事在客户那里解决问题时发现一个报错，出于趣味就进行了钻研：

Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions,and never in the same statement as updates to transactional tables.

报错的意思是指业务端下发的事务违反了 GTID 的一致性。

起因是，非事务表和事务表在一条事务里进行了更新，MySQL 是不容许咱们这么做的，但业务非要胳膊掰大腿，怎么办？

官网阐明：

Only statements that can be logged using GTID safe statements can be logged when enforce_gtid_consistency is set to ON,so the operations listed here cannot be used with this option:

• Transactions or statements that update both transactional and nontransactional tables.

跟客户沟通确定了该报错事务波及到的两张表别离是 innodb 表和 myisam 表。咱们在本人环境上复现一下。

复现

应用 mysql 5.7.25，开启 gtid，

并别离创立一张 innodb 表和 myisam 表。

在同一个事务中对两张表进行更新：

没有报错呀，怎么和客户那里的报错解释不符？

咱们将两张表的提交程序换一下呢？

果然复现了客户环境中的报错。可是为什么先更新 myisam 引擎表能够更新 innodb 引擎表，然而反之则不行呢？

因为 myisam 引擎表不反对事务，在咱们下发 begin 之后，就曾经隐式提交了，这是个运维教训，无奈直接判断，专门求教了行业大牛失去了确认；

而持续更新 myisam 表时，MySQL 不认这个 insert myisam 表的操作是一个事务操作，只是给予一个记录 binlog 地位的 gtid，之后再更新 innodb 表的操作时，mysql 就认定这是在更新一条事务，在 commit 中就将这条事务提交了。

解决

解决办法有三种：

1. 在业务层面批改事务逻辑，将对 myisam 表和 innodb 表的更新语句拆分到不同事务中。
2. 将 myisam 存储引擎改为 innodb 存储引擎。
3. 敞开事务一致性参数 ENFORCE_GTID_CONSISTENCY。

接下来别离对三种解决方案进行验证：

1）批改业务逻辑，将 myisam 表的更新语句独自放在一个事务里执行。

这是最为稳当的一个办法。

2）批改 myisam 表的存储引擎为 innodb

1. 首先确认如果数据量比拟大，能够应用 mysqldump 导出数据，而后批改 SQL 文件中的存储引擎的类型和表名，删掉 drop table 的操作，之后保留 SQL 文件导入到数据库中。
2. percona 公司也有一款工具实用于这样的场景，有趣味的同学能够钻研应用下，以备生产环境的应用：pt-online-schema-change。
3. 如果表数据量很小，就能够应用惯例的 alter 语句间接批改：alert table sanguo engine = InnoDB。

留神：

myisam 存储引擎表不反对事务，innodb 表绝对于 myisam 表来说能反对更多的数据库高级操作，例如：事务、外键。随着 MySQL 版本的不断更新迭代，innodb 的劣势越来越大，倡议将所有 MySQL 表的存储引擎逐步替换为 innodb，两种存储引擎其余方面的优劣比照参考网上文档。

3）敞开事务一致性参数 ENFORCE_GTID_CONSISTENCY

OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions.

• 匿名事务依赖于二进制日志文件和地位来辨认特定的事务。
• 从 MySQL 5.7.6 开始，增加的 OFF_PERMISSIVE 和 ON_PERMISSIVE 模式容许在拓扑中混合应用这些事务类型。

对表的更新操作实现。

咱们去看看此时 binlog 中的写入：

敞开 ENFORCE_GTID_CONSISTENCY 参数后新事务的提交都是匿名的，且提交程序被打乱，数据库不再进行一致性查看；但此时复制会中断，数据库应用的 HA 将生效。权衡利弊，对于业务来说代价较高，不倡议应用。

附：

如果在一个生产环境中，不倡议同时更新 innodb 和 myisam 存储引擎表，对于 myisam 引擎表的更改是无奈进行回滚的，倡议业务将 SQL 上线前肯定做好查看。

参考

https://dev.mysql.com/doc/ref…

https://www.percona.com/doc/p…