MySql table locking

If I have an operation as follows:

begin transaction (auto commit = false)

select * from foo;

iterate above result set:
  insert into bar (insert the values from the above result set into bar);

commit

In the above operation will the table foo be locked until all the inserts into bar are done? Also, while the inserts are going on, will the table bar be locked, as in, will no one be able to read bar (not bothered about inserts)?

Assume the db is using isolation level, REPEATABLE READ and the storage engine 开发者_高级运维is InnoDB.

No, an innodb operation like that will not lock any tables (Assuming default tx isolation level).

Normally there are no table-level locks at all, but there are some types of locking which can look like it.

With the default isolation level in innodb, readers never block writers and writers never block readers. This is by design.

And if you do an INSERT ... SELECT it'd be more efficient, but probably no different in terms of locking.

There is the InnoDB auto-inc mutex which you may want to read about (search the docs for more info), but that is not a table-lock.

In a REPEATABLE READ serialization level, the selected data from foo would be locked (but you could still append to foo, or alter lines that were not selected). If you only insert into bar, I do not believe any locks would happen on the existing rows in the table.

Also, do you have a reason not to use an insert-select ?

insert into bar (...) select ... from foo;