How do I add millions of rows to a live production mysql table?

Question

I'm looking to add about 7 million rows to a live production database table that gets 1-2 writes per second. Can I do this without locking the database for writes? I think so because the table uses InnoDB?

Are there other considerations or do I just write the insert statement and let it rip?

Answer 1

If you're using InnoDB, you don't need to do anything special. Just run your inserts. InnoDB uses row level locking for these situations, it will not lock the entire table.

Of course your performance could still take a hit due to the parallel work.

To answer your other question:

"One confusion about transactions: If I am working on transaction A and a stack of writes B come in, do those writes get processed after I commit my transaction"

In general, no. It will not need to wait. This does depend if you are working within the same keyspace or not, and also what isolation level you are working within.