MySQL Update Behavior with Identical Data under ROW and STATEMENT Binlog Formats

Background

The article tests whether MySQL re‑executes an UPDATE statement that does not change any data when the new values are identical to the existing ones.

Test Environment

MySQL 5.7.25

CentOS 7.4

binlog_format=ROW

Parameters

root@localhost : (none) 04:53:15> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)

root@localhost : (none) 04:53:49> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

root@localhost : test 05:15:14> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

Test Steps

session1

root@localhost : test 04:49:48> begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 04:49:52> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 | 999  | 871  | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

... (innodb status output) ...

session2

root@localhost : test 04:47:45> update test set sid=55 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

... (innodb status output) ...

session1 (repeat update)

root@localhost : test 04:49:57> update test set sid=55 where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

... (innodb status output) ...

root@localhost : test 04:52:05> commit;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 04:52:52> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 | 55   | 871  | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

Summary (ROW)

When binlog_format=ROW and binlog_row_image=FULL, MySQL reads all column values for the row; if the UPDATE does not change any value, the engine detects no difference and skips the actual data modification.

binlog_format=STATEMENT

Parameters

root@localhost : (none) 04:53:15> show variables like 'binlog_row_image';
... (same as above) ...

root@localhost : (none) 05:16:08> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

root@localhost : test 05:15:14> show variables like 'transaction_isolation';
... (same as above) ...

Test Steps

session1

root@localhost : test 05:16:42> begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 05:16:44> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 | 111  | 871  | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

... (innodb status output) ...

session2

root@localhost : test 05:18:30> update test set sid=999 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

... (innodb status output) ...

session1 (repeat update)

root@localhost : test 05:16:47> update test set sid=999 where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

... (innodb status output) ...

root@localhost : test 05:19:33> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 | 999  | 871  | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

Summary (STATEMENT)

With binlog_format=STATEMENT and binlog_row_image=FULL, InnoDB actually executes the UPDATE statement, acquiring locks and performing the row modification even if the new value matches the old one.