How to guarantee data integrity for concurrent Rails/Active Record operations

rubyonrails-talk
1

I need to implement a feature for a rails site that will involve reading and exporting most of my database.

I know this operation is going to take a while. That's fine-- I've got delayed job for that.

What I'm worried about is the data changing during the running of the job, and the resulting export being corrupted because of that.

My initial thought was to do all of the reads within a transaction. However, I would also like to be running the reads concurrently. ActiveRecord docs say that Transactions cannot be shared between Connections, and Connections cannot be shared between Threads. So it looks as though I am restricted to a single thread with this approach.

Any suggestions for a workaround? Is there another way to give the job a consistent view of the data that doesn't involve transactions? Or is there some alternative to ActiveRecord/Mysql out there that can distribute transactions across threads?

2

One possibility would be to have a slave replicating from the master. You can halt replication, do all your data dumping from the slave and then resume replicating. This might also be a good idea because it would mean that your export stuff wouldn't be impacting the main stuff your site does.

Fred

3

If this is a MySQL database just how long does it take to dump it from the command line? I would guess that the system supplied tools would be faster than anything you could possibly write in Ruby.

4

Thanks for the suggestion Fred-- that sounds like a good solution.

I've never setup slave replication before, but I'm hoping MySQL :: MySQL 8.0 Reference Manual :: 17 Replication will have the information I need.

~Rafe