I’m lazy when I’m speakin’
I’m lazy when I walk
I’m lazy when I’m dancin’
I’m lazy when I talk
X-Press 2 Feat. David Byrne – Lazy
While preparing a blog post to compare how PostgreSQL and MySQL handle locks, as part of a series covering the different approaches to MVCC for these databases, I found an interesting behavior regarding no-op updates. While it is not a common pattern to issue no-op updates, application logic that is not fully under the developers’ control often leads to such situations. Understanding how different databases handle these scenarios provides insight into their internal mechanisms and performance characteristics.
And what is a no-op update? A no-op update in SQL is an update operation that doesn’t actually change any data: an UPDATE statement that sets columns to their current values, resulting in no actual changes to the values stored in the database.
Update in PostgreSQL
We create the table and insert a row.
|
1 2 |
create table do_nothing (id integer primary key, sample_text varchar(255)); insert into do_nothing values (1,'Sample text for row 1'); |
As expected, the table has one live row and no dead rows. And yes, the database name is Batman:
|
1 2 3 4 5 6 7 8 9 |
batman=# SELECT relname, batman-# pg_stat_get_live_tuples(oid) as live_rows, batman-# pg_stat_get_dead_tuples(oid) as dead_rows batman-# FROM pg_class batman-# WHERE relname = 'do_nothing'; relname | live_rows | dead_rows ------------+-----------+----------- do_nothing | 1 | 0 (1 row) |
Now we perform a no-op update, setting the sample_text column to the same value it already has.
|
1 2 |
batman=# update do_nothing set sample_text='Sample text for row 1' where id=1; UPDATE 1 |
The client reports that one row was updated. But what is the internal state of the table?
|
1 2 3 4 5 6 7 8 9 |
batman=# SELECT relname, batman-# pg_stat_get_live_tuples(oid) as live_rows, batman-# pg_stat_get_dead_tuples(oid) as dead_rows batman-# FROM pg_class batman-# WHERE relname = 'do_nothing'; relname | live_rows | dead_rows ------------+-----------+----------- do_nothing | 1 | 1 (1 row) |
The result is interesting: PostgreSQL creates a new version of the row, marking the previous version as dead, even though the data hasn’t changed. This behavior is due to PostgreSQL’s MVCC implementation, which treats any update as a modification that requires a new row version. If we repeat the no-op update, the dead row count increases again.
|
1 2 3 4 5 6 7 8 9 10 11 |
batman=# update do_nothing set sample_text='Sample text for row 1' where id=1; UPDATE 1 batman=# SELECT relname, batman-# pg_stat_get_live_tuples(oid) as live_rows, batman-# pg_stat_get_dead_tuples(oid) as dead_rows batman-# FROM pg_class batman-# WHERE relname = 'do_nothing'; relname | live_rows | dead_rows ------------+-----------+----------- do_nothing | 1 | 2 (1 row) |
Update in MySQL
Let’s start by creating the same table and inserting a row.
|
1 2 3 4 5 |
mysql> create table do_nothing (id integer primary key, sample_text varchar(255)); Query OK, 0 rows affected (0,02 sec) mysql> insert into do_nothing values (1,'Sample text for row 1'); Query OK, 1 row affected (0,01 sec) |
Unfortunately, there is no straightforward way to check the number of rows in the table and in the rollback segments in InnoDB. However, we can monitor the number of undo log pages written to disk, which gives us an idea of whether any changes were made to the data. Obviously, this will not work if there is activity from other sessions or if we don’t have the corresponding metrics enabled. Therefore, ensure that you run this in isolation and with metrics enabled. There are other methods that could be used, for example, executing FLUSH TABLES and checking if the corresponding InnoDB file has changed, but this one is simple enough for our purposes.
First, we reset the buffer page metrics, buffer_page_written_undo_log belongs to this module.
|
1 2 3 4 5 6 7 8 9 |
SET GLOBAL innodb_monitor_reset = module_buffer_page; mysql> SELECT name,count_reset FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_page_written_undo_log'; +------------------------------+-------------+ | name | count_reset | +------------------------------+-------------+ | buffer_page_written_undo_log | 0 | +------------------------------+-------------+ 1 row in set (0,00 sec) |
As expected, the metric shows zero pages written to the undo log. Now we perform a no-op update, setting the sample_text column to its current value.
|
1 2 3 |
mysql> update do_nothing set sample_text='Sample text for row 1' where id=1; Query OK, 0 rows affected (0,01 sec) Rows matched: 1 Changed: 0 Warnings: 0 |
We see that MySQL tells us that one row matched, but no rows were modified. And now we recheck the metric.
|
1 2 3 4 5 6 7 |
mysql> SELECT name,count_reset FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_page_written_undo_log'; +------------------------------+-------------+ | name | count_reset | +------------------------------+-------------+ | buffer_page_written_undo_log | 0 | +------------------------------+-------------+ 1 row in set (0,00 sec) |
And no pages were written to the undo log, indicating that no changes were made to the data. Now we will run the update, but we will actually change one row.
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> update do_nothing set sample_text='Sample text for row 2' where id=1; Query OK, 1 row affected (0,00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT name,count_reset FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_page_written_undo_log'; +------------------------------+-------------+ | name | count_reset | +------------------------------+-------------+ | buffer_page_written_undo_log | 2 | +------------------------------+-------------+ 1 row in set (0,00 sec) |
As we can see now, two pages were written to the undo log, indicating that changes were made to the data.
Bonus track
If the no-op update in MySQL does not modify any data, does it lock the row? Let’s find out by opening two sessions. In the first session, we start a transaction and perform the no-op update.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
19:40:24> start transaction; Query OK, 0 rows affected (0,00 sec) 19:40:29> select * from do_nothing; +----+-----------------------+ | id | sample_text | +----+-----------------------+ | 1 | Sample text for row 1 | +----+-----------------------+ 1 row in set (0,00 sec) 19:40:43> update do_nothing set sample_text='Sample text for row 1' where id=1; Query OK, 0 rows affected (0,00 sec) Rows matched: 1 Changed: 0 Warnings: 0 |
In the second session, we attempt to update the same row.
|
1 2 3 4 5 6 |
19:41:26> set lock_wait_timeout=3; Query OK, 0 rows affected (0,00 sec) 19:41:42> select * from do_nothing for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 19:42:44> |
So, even though the no-op update did not modify any data, it still acquired a lock on the row, preventing other transactions from modifying it until the transaction is committed or rolled back. And how does this happen? Well, you’ll have to wait till I finish the blog post I mentioned at the beginning!
About the Author
Pep PlaPep has been working with databases all his life. Born in a small village by the Mediterranean, he currently lives in Barcelona. He loves tech, traveling, good food, music and, all things NASA. He hates talking about himself in the third person and has a particular sense of humor. Happily married, he is the father of three boys and three cats.
