2

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.

Improve this question
1
  • If I understand you right, you don't want ongoing DML statements to interfere with your data while you are reading it. If so, I would recommend setting the transaction isolation level to a more restrictive (I suppose you don't use SERIALIZABLE) state: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    – Łukasz Kastelik
    Commented Dec 16, 2015 at 12:49

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.