Mysqldump--single-transaction Option Resolution

First, the analysis

View general_log:

206 Query setsession TRANSACTION Isolation level repeatable READ

     ## Change the isolation level (take a snapshot to ensure consistent read in transactions)

206 Query START transaction/*!40100 with consistent SNAPSHOT */

## Transaction Start

206 Query Unlocktables

206 Init DB Sanguo_single

206 Query SAVEPOINTSP

          ## Set Save Point

206 Query Showtables

206 Query showtable Status like ' achievements ' 4

206 Query setsql_quote_show_create=15

206 Query setsession character_set_results = ' binary ' 5

206 Query showcreate table ' achievements '

  ## Get Table Structure

206 Query setsession character_set_results = ' UTF8 '

## Setting Backup Parameters

206 Query Showfields from ' achievements '

206 Query select/*!40001 Sql_no_cache */* from ' achievements ' 6

     ## get data for a table

206 Query setsession character_set_results = ' binary '

206 Query use ' Sanguo_single '

206 Query [email protected] @collation_database

206 Query showtriggers like ' achievements '

206 Query setsession character_set_results = ' UTF8 '

206 Query ROLLBACK tosavepoint sp

           ## Rollback Save Point (undo Select for Achievements impact of the table)

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

# # Achievements table backup ends, other tables and so on. It can be seen that --single-transaction is a single table for backup and then one for each undo Select

Second, the experiment

1.--single-transaction option to execute DML statements when backing up

Transaction One	Show Processlist;	Transaction Two
Mysqldump--single-transactionsanguo_single >sanguo_single.sql	| 213 | Root | localhost | Sanguo_single |     Query | 5 | Sending Data | SELECT/*!40001sql_no_cache */* from ' player_pve_battles ' | +-----+------+-----------+---------------+---------+-------+--------------+------------------------------------ -------------------------+ 5 rows in Set (0.00 sec)	Mysql> Delete from Player_pve_battles; Query OK, 52646 rows Affected (18.24 sec)

------------

Transactions

------------

Trx Idcounter 12887

Purgedone for Trx's N:o < 12794 undo N:o < 0 state:running but idle

Historylist Length 639

LIST oftransactions for each SESSION:

---TRANSACTION0, not started

Mysqlthread ID 178, OS thread handle 0x7f61312f1700, query ID 48564 localhost rootinit

Showengine InnoDB Status

---TRANSACTION12793, not started

Mysqlthread ID 208, OS thread handle 0x7f608bfff700, query ID 39534 localhost rootcleaning up

---TRANSACTION12886, ACTIVE 4 sec starting index read

Mysqltables in use 1, locked 1

20188lock struct (s), heap size 1799720, 64197 row lock (s)

MySQL thread ID, OS thread handle 0x7f613122e700, Queryid 48563 localhost root updating # # #Delete not locked, Then he added a lock to the player_pve_battles .

Deletefrom Player_pve_battles

---TRANSACTION12792, ACTIVE 625 sec

Mysqlthread ID 207, OS thread handle 0x7f61312b0700, query ID 39535 localhost rootcleaning up

TRX Read View won't see Trx with ID >= 12793, sees< 12793

2.--single-transaction option to execute DDL statements when backing up

Transaction One	Transaction Two
Mysqldump--single-transactionsanguo_single >sanguo_single.sql	mysql> drop table player_pve_battles; ^CCTRL-C--Sending "KILL QUERY210" to server ... CTRL-C--Query aborted. ERROR 1317 (70100): Query execution wasinterrupted

Conclusion: Because -single-transaction the option to back up involves Select statement, so DML The statement is allowed, DDL not allowed until rollback save point revocation Select Statement

This article from "diligence and desolate in the hippie" blog, reprint please contact the author!

Mysqldump--single-transaction Option Resolution