Whether the update modified data in MySQL is the same as the original data will be executed again.
Editor to share with you whether the update modification data in MySQL is the same as the original data will be implemented again, I believe most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!
The main test of this article is that when MySQL executes update statements, will update statements that are the same as the original data (that is, unmodified) be re-executed within MySQL?
Test environment MySQL5.7.25Centos 7.4binlog_format is 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) root@localhost: (none) 04:54:03 > show engine Innodb status\ Gshow master status\ G...---LOG---Log sequence number 12090390Log flushed up to 12090390Pages flushed up to 12090390Last checkpoint at 120903810 pending log flushes 0 pending chkp writes33 log i/o's done, 0.00 log iThink * 1. Row * * File: mysql-bin.000001 Position: 154Binlog_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.01sec) Rows matched: 1 Changed: 1 Warnings: 0root@localhost: (none) 04:54:03 > show engine innodb status\ Gshow master status\ G...---LOG---Log sequence number 12091486Log flushed up to 12091486Pages flushed up to 12091486Last checkpoint at 120914770 pending log flushes, 0 pending chkp writes39 log i/o's done File: mysql-bin.000001 Position: 500 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:11 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: 0root@localhost: (none) 04:54:03 > show engine innodb status\ Gshow master status\ G...---LOG---Log sequence number 12091486Log flushed up to 12091486Pages flushed up to 12091486Last checkpoint at 120914770 pending log flushes, 0 pending chkp writes39 log i/o's done File: mysql-bin.000001 Position: 500 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:11 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) summary
In binlog_format=row and binlog_row_image=FULL, because MySQL needs to record all the fields in binlog, all the data will be read out when reading the data, so the update of duplicate data will not be executed. That is, MySQL invokes the interface "modified to (1cm55)" provided by the InnoDB engine, but the engine finds that the value is the same as the original, does not update, and returns directly
Binlog_format is STATEMENT
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) 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 12092582Log flushed up to 12092582Pages flushed up to 12092582Last checkpoint at 120925730 pending log flushes 0 pending chkp writes45 log i/o's done, 0.00 log iThink * 1. Row * * File: mysql-bin.000001 Position: 154Binlog_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: 0root@localhost: (none) 05:18:47 > show engine innodb status\ Gshow master status\ G...---LOG---Log sequence number 12093678Log flushed up to 12093678Pages flushed up to 12093678Last checkpoint at 120936690 pending log flushes, 0 pending chkp writes51 log i/o's done File: mysql-bin.000001 Position: 438 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:11 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: 0root@localhost: (none) 05:20:03 > show engine innodb status\ Gshow master status\ G...---LOG---Log sequence number 12094504Log flushed up to 12094504Pages flushed up to 12094504Last checkpoint at 120944950 pending log flushes, 0 pending chkp writes56 log i/o's done File: mysql-bin.000001 Position: 438 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:11 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.01sec) 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) above is the update modified data in MySQL compared with the original data All the contents that will be executed again. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
