does this lock the database?

Question

insert into test.qvalues 
  select * from qcvalues.qvalues;

i would like to knwo if the above line locks the database QCVALUES

Answer 1

To me, the documentation is a little unclear:

Internal Locking Methods suggests that, in some circumstances, it is possible to insert into a MyISAM table while another session is reading from it:

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks. That is, you can insert rows into a MyISAM table at the same time other clients are reading from it. Holes can result from rows having been deleted from or updated in the middle of the table. If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data.

However, Table Locking Issues shows a situation where the table will be locked until the SELECT is complete (this fits with your situation):

Table locking is also disadvantageous under the following scenario:

• A session issues a SELECT that takes a long time to run.
• Another session then issues an UPDATE on the same table. This session waits until the SELECT is finished.
• Another session issues another SELECT statement on the same table. Because UPDATE has higher priority than SELECT, this SELECT waits for the UPDATE to finish, after waiting for the first SELECT to finish.

InnoDB table implement row-level locks, so only the row being read will be locked, rather than the whole table.

Rather than relying just on the documentation, I tried a little test:

1. Create two tables with the same structure: table_a and table_b.
2. Fill table_a with 500,000 rows.
3. Copy data from table_a to table_b using an INSERT INTO ... SELECT statement.
4. During copy process, use another session to insert a new row into table_a.
5. Check if table_b contains the new record.

When both tables where MyISAM, table_b did not contain the new record after the copy. When both tables where InnoDB, table_b did contain the new record after the copy. I have repeated this three times, and, as expected, the result was the same each time.

So, in short, if your table is MyISAM, it will be locked. If it's InnoDB, it won't. Of course, this test does not consider updates, but I expect the results will be similar.