Lock innoDB table temporarily

Question

Hi everyone,

I make bigger inserts consisting of a couple of thousand rows in my current web app and I would like to make sure that no one can do anything but read the table, until the inserts have been done.

What is the best way to do this while keeping the read availability open for normal, non-admin users?

Thanks!

Answer 1

Why not just do the insert in one big transaction? That would kind of prevent the need for any locking.

Answer 2

Since you're only inserting (not updating) this shouldn't be a problem. Just insert the records in a single transaction.

InnoDB supports row level locking if I recall correctly so even updates shouldn't be a problem.

Answer 3

You may also need to change default isolation level:

 set transaction isolation level serializable;
 start transaction;
 // insert data
 commit