Mysql中事务隔离级别与binlog_format的一点理解

最新推荐文章于 2024-04-19 13:36:59 发布

weixin_34362790

最新推荐文章于 2024-04-19 13:36:59 发布

阅读量351

点赞数

文章标签：数据库 java python

原文链接：https://my.oschina.net/realfighter/blog/353883

版权

2019独角兽企业重金招聘Python工程师标准>>>

原文地址：Mysql中事务隔离级别与binlog_format的一点理解

首先回顾一下那伤心的往事：涉及支付接口和用户资金，最初在支付接口回调时，需要更新用户以及资金的一些记录，需要同时对多张表进行更新，最初的代码遗漏了对事务的控制，导致了数据库偶尔出现表数据未更新的bug，但是在代码中增加完事务后，却总是伴随着这样的异常信息：

    java.sql.SQLException: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

简单翻译一下：事务隔离级别为READ-COMMITTED的InnoDB引擎，在binlog方式为STATEMENT时并不安全。

开发框架使用的是开源的JFinal，最初总以为是JFinal的问题，于是立刻在服务器上去除事务的控制代码，恢复最初版本的代码，开始解决问题，经过一段时间的百度和google，在这里找到了答案关于JFinal中的事务处理，了解到了Mysql低版本存在的一个bug，官方的bug反馈：http://bugs.mysql.com/bug.php?id=40360。

造成问题的原因可能是由于InnoDB与binlog不兼容导致，服务器上的mysql版本也是5.1，于是乎，采用了最简单的办法来解决这个问题，数据库升级，这里感谢我的同事，花费了一些时间将数据库版本升级到了最新的稳定版5.7，以为问题解决了，重新控制事务，在线上运行。

好吧，你猜到了，问题更严重了，所有的支付都出现了问题，这时出现的异常信息是：

    Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

简单翻译一下：无法写入二进制文件，因为BINLOG_FORMAT方式为STATEMENT，存在一个或多个表使用的存储引擎是基于行的日志记录，InnoDB数据库引擎，当事务隔离级别为READ COMMITTED 或READ UNCOMMITTED 时，只限定于binlog为Row方式。

先来了解一下Mysql的数据库存储引擎，有很多中，不用常用的有以下两种：MyISAM和InnoDB，两者最主要的区别是：MyISAM是默认的错处引擎，不支持事务，InnoDB支持事务。

这里有一篇详细的介绍文章：MySQL: InnoDB 还是 MyISAM?

所谓的事务隔离级别有以下四种，高性能Mysql中有详细的说明，来自（ MySQL数据库事务隔离级别(Transaction Isolation Level)）：

翻看JFinal的源码，在控制事务的时候默认的是READ_COMMITTED，数据库在更新的时候，由于默认的binlog_format为STATEMENT，所以才导致了异常的出现，问题的根源是找到了，那么应该怎么解决呢？我们先来了解一下binlog：

BINLOG就是一个记录SQL语句的过程，和普通的LOG一样，它以二进制存储，普通的LOG以十进制存储，binlog有以下几种方式：STATEMENT、ROW、MIXED，默认的日志记录方式，即binlog_format为STATEMENT，当数据库存储引擎为InnoDB时，在使用到事务控制，并且事务隔离级别为就READ COMMITTED 或READ UNCOMMITTED 时，就可能出现上面的异常，三种方式的不同区别，请参阅（MySQL binlog_format (Mixed,Statement,Row) ）。

最后得出的解决方案是，修改binlog_format，即设置为ROW或MIXED，最终将数据库中的binlog设置为'MIXED'，使用的依据是：

具体是否能够解决当前的错误，还需要等待明天的测试，希望一切顺利吧。同样的对于binlog和事务隔离级别的测试，也可以参阅这里：mysql binlog格式与事务级别read committed的关系

这里具体的修改可以通过修改my.ini或 my.cnf文件，也可以在sql命令模式下，使用：

    #查看事务隔离级别
    select @@tx_isolation;
    #查看binlog方式
    select @@binlog_format;

    #设置session级别的BINLOG
    SET SESSION binlog_format = 'MIXED' ;
    #设置系统级别的binlog
    SET GLOBAL binlog_format = 'ROW';

转载于:https://my.oschina.net/realfighter/blog/353883