Shulou(Shulou.com)06/01 Report--

Background

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.4

Binlog_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 procedure

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 0.00 log iUniverse second 0242ac110002:11 row in set * 1. Row * * 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 procedure

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 Row * * 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)

Summary

In binlog_format=statement and binlog_row_image=FULL, the update statement is carefully executed within InnoDB, that is, the operation "change this value to (1999)", the lock that should be locked, the update that should be updated.

Well, the above is the whole content of this article. I hope the content of this article has a certain reference and learning value for your study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.