Will MySQL execute the update statement again if it has the same data as the original one?

Will MySQL execute the update statement again if it has the same data as the original one?

background

This article mainly tests whether the update statement that is identical to the original data (i.e., unmodified) will be re-executed inside MySQL when MySQL executes the update statement.

Test environment

  • MySQL 5.7.25
  • Centos 7.4

binlog_format is ROW

parameter

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)

root@localhost : (none) 04:54:03> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12090390
Log flushed up to 12090390
Pages flushed up to 12090390
Last checkpoint at 12090381
0 pending log flushes, 0 pending chkp writes
33 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
  File:mysql-bin.000001
  Position: 154
 Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

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

root@localhost : (none) 04:54:03> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12091486
Log flushed up to 12091486
Pages flushed up to 12091486
Last checkpoint at 12091477
0 pending log flushes, 0 pending chkp writes
39 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
  File:mysql-bin.000001
  Position: 500
 Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
1 row in set (0.00 sec)

session1

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

root@localhost : (none) 04:54:03> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12091486
Log flushed up to 12091486
Pages flushed up to 12091486
Last checkpoint at 12091477
0 pending log flushes, 0 pending chkp writes
39 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
  File:mysql-bin.000001
  Position: 500
 Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
1 row in set (0.00 sec)

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

root@localhost : test 04:52:42> 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)

Summarize

When binlog_format=row and binlog_row_image=FULL , MySQL needs to record all fields in the binlog, so all data will be read when reading data, and updates of duplicate data will not be executed. That is, MySQL calls the "change to (1,55)" interface provided by the InnoDB engine, but the engine finds that the value is the same as the original one, does not update it, and directly returns

binlog_format is STATEMENT

parameter

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) 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';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+----------------------+-----------------+
1 row in set (0.00 sec)

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)

root@localhost : (none) 05:16:51> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12092582
Log flushed up to 12092582
Pages flushed up to 12092582
Last checkpoint at 12092573
0 pending log flushes, 0 pending chkp writes
45 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
    File:mysql-bin.000001
   Position: 154
  Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

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

root@localhost : (none) 05:18:47> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12093678
Log flushed up to 12093678
Pages flushed up to 12093678
Last checkpoint at 12093669
0 pending log flushes, 0 pending chkp writes
51 log i/o's done, 0.14 log i/o's/second

*************************** 1. row ***************************
    File:mysql-bin.000001
   Position: 438
  Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
1 row in set (0.00 sec)

session1

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

root@localhost : (none) 05:20:03> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12094504
Log flushed up to 12094504
Pages flushed up to 12094504
Last checkpoint at 12094495
0 pending log flushes, 0 pending chkp writes
56 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
    File:mysql-bin.000001
   Position: 438
  Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
1 row in set (0.00 sec)

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)

root@localhost : test 05:20:44> commit;
Query OK, 0 rows affected (0.01 sec)

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

Summarize

When binlog_format=statement and binlog_row_image=FULL, InnoDB executes the update statement carefully, that is, the operation of "change this value to (1,999)", locking what needs to be locked and updating what needs to be updated.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • How to get the query time of MySQL SQL statement in PHP
  • How is a SQL statement executed in MySQL?

<<:  Vue realizes web online chat function

>>:  How to set PATH environment variable in Linux system (3 methods)

Recommend

Ubuntu 18.04 one-click upgrade of all third-party Python packages and installation of Python packages

1. What is pip pip is a Python package management...

Implementation of inserting millions of records into MySQL database within 10 seconds

First, let’s think about a question: To insert su...

js to implement verification code interference (dynamic)

This article example shares the specific code of ...

Detailed explanation of the use and underlying principles of MySQL table partitions

Table of contents What is a partition table Parti...

Webpack builds scaffolding to package TypeScript code

Create a folder Directory structure: dabaots Init...

Nodejs combined with Socket.IO to realize websocket instant communication

Table of contents Why use websocket Socket.io Ope...

MySQL table deletion operation implementation (differences between delete, truncate, and drop)

This article mainly discusses the differences bet...

React hooks introductory tutorial

State Hooks Examples: import { useState } from &#...

Vue Element front-end application development to obtain back-end data

Table of contents Overview 1. Acquisition and pro...

Use of Linux gzip command

1. Command Introduction The gzip (GNU zip) comman...

Example of converting spark rdd to dataframe and writing it into mysql

Dataframe is a new API introduced in Spark 1.3.0,...

Differences between FLOW CHART and UI FLOW

Many concepts in UI design may seem similar in wo...

How to use jconsole to monitor remote Tomcat services

What is JConsole JConsole was introduced in Java ...

Detailed graphic explanation of mysql query control statements

mysql query control statements Field deduplicatio...

Example of how to set up a multi-column equal height layout with CSS

Initially, multiple columns have different conten...