MySQL binlog_row_image不同取值影响查询结果

╰半夏微凉° 2023-05-21 07:27 26阅读 0赞

介绍一个前提，对于一个事务内，DML是当前读，而且DML所做的更新，对本事务也是可见的。  
例如，假设sam表只有一行数据是col1=1,当全表update后，虽然事务还未提交，而且即使在RR级别下，SQL1建立了一致性视图，但经过SQL2更新后，SQL3是能够看到自身更新的数据，这是因为SQL2更新后，所有数据行的trx\_id都变为当前事务的trx\_id，自然是可见的。  
也可以这样理解，我自己做的操作，要是都不能看见，那不是见鬼了？

begin;
    select * from sam where col1=1;   --SQL1
    update sam set col1=1;            --SQL2
    select * from sam where col1=1;   --SQL3

其实，还真有“鬼”。一起来看看恐怖片吧。  
1.自己更新的数据，竟然看不见？  
本例子基于以下环境和数据：

mysql> show variables like 'transaction_isolation';
    +-----------------------+-----------------+
    | Variable_name         | Value           |
    +-----------------------+-----------------+
    | transaction_isolation | REPEATABLE-READ |
    +-----------------------+-----------------+
    1 row in set (0.01 sec)
    
    mysql> show variables like 'binlog_row_image';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | binlog_row_image | FULL  |
    +------------------+-------+
    1 row in set (0.00 sec)
    
    mysql> select * from sam.sam;
    +----+------+------+
    | id | col1 | col2 |
    +----+------+------+
    |  1 |    1 |    1 |
    |  2 |    2 |    2 |
    |  3 |    3 |    3 |
    +----+------+------+
    3 rows in set (0.00 sec)

会话1，看一下col2=1有哪些行，返回id=1这一行，不提交事务，那么就在一个一致性视图里了。

mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from sam where col2=1;
    +----+------+------+
    | id | col1 | col2 |
    +----+------+------+
    |  1 |    1 |    1 |
    +----+------+------+
    1 row in set (0.00 sec)

会话2，执行更新，将id=3的col2修改为1，提交事务

mysql> update sam set col2=1 where id=3;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

会话1在原来的事务内，执行和会话2同样的更新，将id=3的col2修改为1，更新后查看col2=1有哪些行

mysql> update sam set col2=1 where id=3;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    
    mysql> select * from sam where col2=1;
    +----+------+------+
    | id | col1 | col2 |
    +----+------+------+
    |  1 |    1 |    1 |
    +----+------+------+
    2 rows in set (0.00 sec)

what?我自己更新了id=3的col2了，怎么看不见呢？

2.自己更新的数据，竟然又看见了？  
本例子基于以下环境和数据：

mysql> show variables like 'transaction_isolation';
    +-----------------------+-----------------+
    | Variable_name         | Value           |
    +-----------------------+-----------------+
    | transaction_isolation | REPEATABLE-READ |
    +-----------------------+-----------------+
    1 row in set (0.01 sec)
    
    mysql> show variables like 'binlog_row_image';
    +------------------+---------+
    | Variable_name    | Value   |
    +------------------+---------+
    | binlog_row_image | MINIMAL |
    +------------------+---------+
    1 row in set (0.00 sec)
    
    mysql> select * from sam;
    +----+------+------+
    | id | col1 | col2 |
    +----+------+------+
    |  1 |    1 |    1 |
    |  2 |    2 |    2 |
    |  3 |    3 |    3 |
    +----+------+------+
    3 rows in set (0.00 sec)

会话1，看一下col2=1有哪些行，返回id=1这一行，不提交事务，那么就在一个一致性视图里了。

会话2，执行更新，将id=3的col2修改为1，提交事务

mysql> update sam set col2=1 where id=3;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

会话1在原来的事务内，执行和会话2同样的更新，将id=3的col2修改为1，更新后查看col2=1有哪些行

what?我自己更新的数据，又出来了？这两个例子不是自相矛盾吗？真见“鬼”了。

世界虽然还有很多未知的领域，但我们要相信科学啊，同学们。我们来看看这其中的缘由吧。  
实际上这涉及到，对于一个已经是我想要更新的样子的数据行，那我执行更新的时候，到底还要不要更新呢？  
根据本文开篇的前提，如果是我自己更新的，那么数据行的trx\_id就是我自己事务的trx\_id了，根据MVCC的原理，那我自己肯定能看到。  
出现看不到的原因，一定是数据行的当前trx\_id是在我之后提交的事务。  
那么对比以上两个例子，区别在于参数binlog\_row\_image的不一样，一个是full，一个是minimal。  
1.当binlog\_row\_image=full，那么我们会话2执行`update sam set col2=1 where id=3`时，binlog是以下这样的

### UPDATE `sam`.`sam`
    ### WHERE
    ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
    ###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
    ###   @3=3 /* INT meta=0 nullable=1 is_null=0 */
    ### SET
    ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
    ###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
    ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */

也就是说需要将id=3的整行都读出来  
而当我们会话1同样执行`update sam set col2=1 where id=3`，并且提交，会发现，binlog里并没有该update的相关binlog。  
为什么呢？其实会话1执行`update sam set col2=1 where id=3`跟会话2一样，会将id=3的所有数据读出来，MySQL发现col2已经是1了，那就不执行更新，直接返回。  
所以这里看不到自己更新的数据，其实是更新根本就没执行。

2.当binlog\_row\_image=minimal，那么我们会话2执行`update sam set col2=1 where id=3`时，binlog是以下这样的

### UPDATE `sam`.`sam`
    ### WHERE
    ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
    ### SET
    ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */

也就是说，对于`update sam set col2=1 where id=3`，由于binlog只需要记录id和col2列，那么其实只需要读id列，而MySQL就会机械地把col2更新为1。  
所以，这时是实际执行了更新的，所以事务内可以看到。