Does a transaction with no net effect touch the database on-disk?

Question

I have a circumstance where I need to update some table rows, marking the ones that do not appear in an external data-source as disabled (i.e. update active=0). The straight-forwards solution is to BEGIN a transaction, UPDATE every row to active=0, and then scan the remote data, doing an UPDATE for each entry that should be active=1 to put it back. I have around 1k rows, so this should be a relatively quick operation, even if there is a lot of inefficient query parsing.

However, this data will often not change at all. Hence, in the majority of cases, the net effects of the transaction will be zero change. If the database engine will resolve the whole thing, detect that nothing is changing, and not change anything as a result, that would be ideal. However, if it is going to go through and actually update every row, every time, I would rather find another solution.

Answer 1

Here's a demo. I created a table with just a simple integer in a row.

mysql> create table t ( i int );
mysql> insert into t set i = 42;

I check the current number of log writes.

mysql> show status like 'innodb_log_write_requests';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| Innodb_log_write_requests | 5432152 |
+---------------------------+---------+

Then change the value in the row with an UPDATE and confirm it resulted in a log write:

mysql> update t set i = 43;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show status like 'innodb_log_write_requests';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| Innodb_log_write_requests | 5432153 |
+---------------------------+---------+

Next, make an UPDATE that has no net effect.

mysql> update t set i = 43;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Notice Changed: 0.

Look at the log writes, it is also unchanged:

mysql> show status like 'innodb_log_write_requests';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| Innodb_log_write_requests | 5432153 |
+---------------------------+---------+