root@testdb:3306 12:14:27>truncate table users; Query OK, 0 rows affected (0.08 sec) root@testdb:3306 12:14:29>truncate table class; Query OK, 0 rows affected (0.04 sec) root@testdb:3306 12:14:50>insert into users values(1,'刘备',2,null),(2,'曹操',1,null),(3,'孙 权',3,null),(4,'关羽',2,null),(5,'司马懿',1,null); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 root@testdb:3306 12:15:10>insert into class values(1,'魏',null),(2,'蜀',null),(3,'吴',null),(4,'晋',''); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
再将binlog日志名目改为STATAMENT名目(全局及会话级都改一下,可能修改全局变量后从头登录也行,虽然 只改会话级此外也可以测试),然后 再次举办测试。
步调说明如下:
步调1 - 别离查察两个会话中的事务断绝级别及binlog名目(断绝级别均为RC,binlog为ROW名目)
步调2 - SESSION A 开启事务,更新users 表中c_id字段存在于class表中的记录,功效为5笔记录均更新,并将c_note内容更新为 t1
步调3- SESSION B 开启事务,筹备删除class表中 c_id便是2的记录,此时不会像RR事务断绝级别那样处于阻塞状态,而是可以直接执行通过
步调4- 此时SESSION A查察class数据照旧删除前的,因为session B 暂未提交
步调5- SESSION B 提交事务,
步调6- 更新users 表中c_id字段存在于class表中的记录,功效为3笔记录更新乐成,并将c_note内容更新为 t2
步调7- 在从库查察users、class表中的内容,数据与主库一致
步 骤 SESSION A SESSION B1
root@testdb:3306 01:25:24>show variables like '%iso%';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.01 sec)
root@testdb:3306 01:25:36>show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
root@testdb:3306 01:24:57>show variables like '%iso%';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.01 sec)
root@testdb:3306 01:25:39>show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
2
root@testdb:3306 01:27:55>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@testdb:3306 01:28:27>update users set c_note='t1' where c_id in (select c_id from class);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
3
root@testdb:3306 01:26:07>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@testdb:3306 01:28:37>delete from class where c_id=2;
Query OK, 1 row affected (0.00 sec)
4
root@testdb:3306 01:28:27>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 2 | 蜀 | NULL |
| 3 | 吴 | NULL |
| 4 | 晋 | |
+------+--------+--------+
4 rows in set (0.00 sec)
5
root@testdb:3306 01:28:41>commit;
Query OK, 0 rows affected (0.00 sec)
6
root@testdb:3306 01:28:59>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 3 | 吴 | NULL |
| 4 | 晋 | |
+------+--------+--------+
3 rows in set (0.01 sec)
root@testdb:3306 01:29:13>update users set c_note='t2' where c_id in (select c_id from class);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
root@testdb:3306 01:29:26>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 3 | 吴 | NULL |
| 4 | 晋 | |
+------+--------+--------+
3 rows in set (0.00 sec)
root@testdb:3306 01:29:31>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 刘备 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孙 权 | 3 | t2 |
| 4 | 关羽 | 2 | t1 |
| 5 | 司马懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
root@testdb:3306 01:29:38>commit;
7
查察从库数据
root@testdb:3307 01:40:32>select * from users;
+----+-----------+------+--------+
| id | user_name | c_id | c_note |
+----+-----------+------+--------+
| 1 | 刘备 | 2 | t1 |
| 2 | 曹操 | 1 | t2 |
| 3 | 孙 权 | 3 | t2 |
| 4 | 关羽 | 2 | t1 |
| 5 | 司马懿 | 1 | t2 |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
root@testdb:3307 01:40:35>select * from class;
+------+--------+--------+
| c_id | c_name | c_note |
+------+--------+--------+
| 1 | 魏 | NULL |
| 3 | 吴 | NULL |
| 4 | 晋 | |
+------+--------+--------+
3 rows in set (0.00 sec)
也就是此时主从功效也是一致的。
3.2 STATEMENT名目因为当前版本已经不支持RC+STATEMENT组合下数据的操纵,不然将报如下错误: