mysql binlog格式与事务级别read committed的关系
来源:互联网 发布:linux web扫描工具 编辑:程序博客网 时间:2024/05/16 19:28
前言
binlog有三种格式,分别是STATEMENT、row、mixed。每种格式的区别可以去看复制那篇文章,那它分别与read committed 有什么关系呢。下面以例子来分析
1、数据库版本
mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.45, for unknown-linux-gnu (x86_64) using EditLine wrapper
Connection id: 2
Current database: xinying
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.45-VS-log XinYing
Protocol version: 10
Connection: Localhost via UNIX socket
Insert id: 2
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 1 hour 40 min 14 sec
2、改变事务级别为read committed
mysql>set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
3、改变二进制日志格式
mysql>set binlog_format=STATEMENT;
Query OK, 0 rows affected (0.00 sec)
4、创建测试表
mysql>CREATE TABLE `slevin` (
->`id` int(10) NOT NULL AUTO_INCREMENT,
->`book` char(10) DEFAULT NULL,
->PRIMARY KEY (`id`)
->) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
5、插入数据测试
mysql>insert into slevin(book) values('wuli');
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
看到没,提示出错,那我们尝试把事务基本改为REPEATABLE READ
mysql>set session transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql>insert into slevin(book) values('wuli');
Query OK, 1 row affected (0.00 sec)
改个事务级别就成功了,那试试仍旧把它改为read committed,把binlog格式改了试试
mysql>set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql>set session binlog_format=row; 改为行格式
Query OK, 0 rows affected (0.00 sec)
mysql>insert into slevin(book) values('wuli');
Query OK, 1 row affected (0.00 sec)
mysql>set session binlog_format=mixed; 改为混合格式
Query OK, 0 rows affected (0.00 sec)
mysql>insert into slevin(book) values('wuli');
Query OK, 1 row affected (0.00 sec)
把上面改为两种格式都成功,唯独STATEMENT格式不行,所以以后要注意read committed与binlog格式的关系,否则会导致插入不了数据。为何会导致这种情况呢,那是因为read committed可能会导致不可重复读,也就是说可以读取到后面进入并提交的数据,如果基于STATEMENT格式的话,会导致主从数据不一样,因为STATEMENT是基于SQL语句的复制模式。
本文出自 “新颖” 博客,请务必保留此出处http://xinying.blog.51cto.com/441770/314203
- mysql binlog格式与事务级别read committed的关系
- Mysql事务和隔离级别(read committed, repeatable read)
- java 默认事务级别read committed对binlog_format的需求
- Java 默认事务级别read committed对binlog_format的需求
- java默认事务级别read committed对binlog_format的需求
- Mysql的READ COMMITTED隔离级别的问题
- mysql事务之提交读(Read Committed)
- informix隔离级别:committed read last committed与committed read介绍
- MySQL的REPEATABLE READ事务隔离级别
- 数据库隔离级别---MySQL的默认隔离级别就是Repeatable,Oracle默认Read committed,最高级别Serializable
- MYSQL之事务隔离级别与锁的关系
- Read Committed与Read committed snapshot的区别以及Read Committed Snapshot与Sanpshot的区别
- 事务隔离级别与并发的关系
- mysql的repeatable read事务隔离级别的测试
- mysql的repeatable read事务隔离级别的测试
- mysql read committed
- MYSQL:Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
- MYSQL下read committed 和repeatable read级别下一致性非锁定读笔记+实测
- HangOver
- 再出發,再出發吧...
- 稳定的完成端口开发细节讨论
- flex4(flash builder)调用Amf远程对象动态添加LineChart图表线条
- ASP.NET 基础结构HTTP 处理程序和HTTP 模块
- mysql binlog格式与事务级别read committed的关系
- According to TLD or attribute directive in tag file, attribute value does not accept any expressions异常原因及解决方法
- 文本框中文字的部分高亮显示
- 关于m_pfnCreateObject
- powershell2.0学习系列
- ASP.NET 状态管理概述
- ubuntu 10.04 Tex Live 2010 + XeTex + ctex中文配置
- 关于LIS系统与HIS系统的接口方案
- ASP.NET 状态管理概述之配置文件属性