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

Mysqldump-all-databases-master-data=2-single-transaction-quick-R-events-uroot > / tmp/full.sql

Through the root trace, we can see:

8 Connect root@localhost on

8 Query / *! 40100 SET @ @ SQL_MODE='' * /

8 Query / *! 40103 SET TIME_ZONE='+00:00' * /

8 Query FLUSH / *! 40101 LOCAL * / TABLES

8 Query FLUSH TABLES WITH READ LOCK

8 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

8 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * /

8 Query SHOW VARIABLES LIKE 'gtid\ _ mode'

8 Query SHOW MASTER STATUS

8 Query UNLOCK TABLES

From the red part, you can see that the lock will still be added, in this process, other session also can not carry out dml, but the locking time is very short.

When using single-transaction, you must:

1 is effective only for storage engines that support transactions

2 the ddl operation cannot be performed because the ddl operation will change the dictionary table, and most dictionary tables are myisam

3 transaction isolation level is RR

When backing up mysqldump, the difference between adding single-transaction and not adding single-transaction:

You can see the process by opening general_log:

1 when adding single-transaction, it is realized by means of START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * /.

There are a lot of savepoint in it at the same time

2 data consistency is achieved by locking the table in the whole process without adding single-trsnsaction.

Single-transaction is added on the left, but not added on the right:

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.