I’m lazy after I’m speakin’
I’m lazy after I stroll
I’m lazy after I’m dancin’
I’m lazy after I speak
X-Press 2 Feat. David Byrne – Lazy
Whereas getting ready a weblog submit to check how PostgreSQL and MySQL deal with locks, as a part of a collection protecting the totally different approaches to MVCC for these databases, I discovered an attention-grabbing habits relating to no-op updates. Whereas it isn’t a standard sample to situation no-op updates, utility logic that isn’t absolutely underneath the builders’ management typically results in such conditions. Understanding how totally different databases deal with these eventualities offers perception into their inside mechanisms and efficiency traits.
And what’s a no-op replace? A no-op replace in SQL is an replace operation that doesn’t truly change any knowledge: an UPDATE assertion that units columns to their present values, leading to no precise adjustments to the values saved within the database.
Replace in PostgreSQL
We create the desk and insert a row.
|
create desk do_nothing (id integer major key, sample_text varchar(255)); insert into do_nothing values (1,‘Pattern textual content for row 1’); |
As anticipated, the desk has one dwell row and no lifeless rows. And sure, the database identify is Batman:
|
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 carry out a no-op replace, setting the sample_text column to the identical worth it already has.
|
batman=# replace do_nothing set sample_text=‘Pattern textual content for row 1’ the place id=1; UPDATE 1 |
The shopper reviews that one row was up to date. However what’s the inside state of the desk?
|
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’s attention-grabbing: PostgreSQL creates a brand new model of the row, marking the earlier model as lifeless, regardless that the information hasn’t modified. This habits is because of PostgreSQL’s MVCC implementation, which treats any replace as a modification that requires a brand new row model. If we repeat the no-op replace, the lifeless row depend will increase once more.
|
batman=# replace do_nothing set sample_text=”Pattern textual content for row 1″ the place 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) |
Replace in MySQL
Let’s begin by creating the identical desk and inserting a row.
|
mysql> create desk do_nothing (id integer major key, sample_text varchar(255)); Question OK, 0 rows affected (0,02 sec)
mysql> insert into do_nothing values (1,‘Pattern textual content for row 1’); Question OK, 1 row affected (0,01 sec) |
Sadly, there isn’t any simple approach to examine the variety of rows within the desk and within the rollback segments in InnoDB. Nonetheless, we will monitor the variety of undo log pages written to disk, which provides us an thought of whether or not any adjustments have been made to the information. Clearly, this won’t work if there may be exercise from different periods or if we don’t have the corresponding metrics enabled. Due to this fact, be sure that you run this in isolation and with metrics enabled. There are different strategies that may very well be used, for instance, executing FLUSH TABLES and checking if the corresponding InnoDB file has modified, however this one is straightforward sufficient for our functions.
First, we reset the buffer web page metrics, buffer_page_written_undo_log belongs to this module.
|
SET GLOBAL innodb_monitor_reset = module_buffer_page;
mysql> SELECT identify,count_reset FROM information_schema.INNODB_METRICS WHERE NAME = ‘buffer_page_written_undo_log’; +——————————+————-+ | identify | count_reset | +——————————+————-+ | buffer_page_written_undo_log | 0 | +——————————+————-+ 1 row in set (0,00 sec) |
As anticipated, the metric reveals zero pages written to the undo log. Now we carry out a no-op replace, setting the sample_text column to its present worth.
|
mysql> replace do_nothing set sample_text=‘Pattern textual content for row 1’ the place id=1; Question OK, 0 rows affected (0,01 sec) Rows matched: 1 Modified: 0 Warnings: 0 |
We see that MySQL tells us that one row matched, however no rows have been modified. And now we recheck the metric.
|
mysql> SELECT identify,count_reset FROM information_schema.INNODB_METRICS WHERE NAME = ‘buffer_page_written_undo_log’; +——————————+————-+ | identify | count_reset | +——————————+————-+ | buffer_page_written_undo_log | 0 | +——————————+————-+ 1 row in set (0,00 sec) |
And no pages have been written to the undo log, indicating that no adjustments have been made to the information. Now we are going to run the replace, however we are going to truly change one row.
|
mysql> replace do_nothing set sample_text=‘Pattern textual content for row 2’ the place id=1; Question OK, 1 row affected (0,00 sec) Rows matched: 1 Modified: 1 Warnings: 0
mysql> SELECT identify,count_reset FROM information_schema.INNODB_METRICS WHERE NAME = ‘buffer_page_written_undo_log’; +——————————+————-+ | identify | count_reset | +——————————+————-+ | buffer_page_written_undo_log | 2 | +——————————+————-+ 1 row in set (0,00 sec) |
As we will see now, two pages have been written to the undo log, indicating that adjustments have been made to the information.
Bonus monitor
If the no-op replace in MySQL doesn’t modify any knowledge, does it lock the row? Let’s discover out by opening two periods. Within the first session, we begin a transaction and carry out the no-op replace.
|
19:40:24> begin transaction; Question OK, 0 rows affected (0,00 sec)
19:40:29> choose * from do_nothing; +—-+———————–+ | id | sample_text | +—-+———————–+ | 1 | Pattern textual content for row 1 | +—-+———————–+ 1 row in set (0,00 sec)
19:40:43> replace do_nothing set sample_text=‘Pattern textual content for row 1’ the place id=1; Question OK, 0 rows affected (0,00 sec) Rows matched: 1 Modified: 0 Warnings: 0 |
Within the second session, we try to replace the identical row.
|
19:41:26> set lock_wait_timeout=3; Question OK, 0 rows affected (0,00 sec)
19:41:42> choose * from do_nothing for replace; ERROR 1205 (HY000): Lock wait timeout exceeded; attempt restarting transaction 19:42:44> |
So, regardless that the no-op replace didn’t modify any knowledge, it nonetheless acquired a lock on the row, stopping different transactions from modifying it till the transaction is dedicated or rolled again. And the way does this occur? Nicely, you’ll have to attend until I end the weblog submit I discussed in the beginning!
