Mysql – Selecting only from multiple tables in a transaction

MySQLtransaction

I need to be able to extract rows from multiple tables in a way that I don't get half changed transactions from other users whilst I'm extracting data. The extraction will take some time (over 100 tables to extract data from).

The purpose is to be able to copy a single users data from one database to another (mysql – non linked, not federated). From a single userID I can collect all the data from the different tables – exporting to a TABBED delimited file.

It would seem that using START TRANSACTION WITH CONSISTENT SNAPSHOT; would be the way to go, but before I go off developing something, is this going to achieve what I want?

Using mysql and all tables are using the INNODB engine.

Best Answer

"consistent snapshot" and "repeatable reads" consistency seem to do exactly what you are asking, even mysqldump uses it with its --single-transaction option.

Because InnoDB implements MVCC, the DML running in parallel won't bother you when all the selects are non-locking (and from InnoDB tables).

The only way how to get "outside" is using SELECT .. FOR UPDATE which will be executed in the "read commited" isolation mode even when the transaction is set to "repeatable reads". But that should not be needed for extraction of data.

Related Question