Does it lock the database?

Question

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

I would like knwo if the above line blocks the QCVALUES database

Answer 1

For me, the documentation is a bit unclear:

Internal locking methods suggest that in some cases it can be inserted into the MyISAM table, and another session is read from it:

The MyISAM storage engine supports simultaneous inserts to reduce discord between readers and writers for a given table: if the MyISAM table does not have 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 simultaneous INSERT and SELECT for a MyISAM table without locks. That is, you can insert rows into the MyISAM table while other clients are reading from it at the same time. Holes can be the result of rows being deleted or updated in the middle of the table. If there are openings, simultaneous inserts are disabled, but are automatically turned on when all openings were filled with new data.

However, Table lock problems shows the situation when the table is locked until SELECT is complete (this is appropriate for your situation):

The column is also disadvantageous in the following scenario:

• The session returns SELECT, which takes a long time to complete.
• Another session then throws UPDATE in the same table. This session is not over until SELECT.
• Another session invokes another SELECT statement in the same table. Since UPDATE has a higher priority than SELECT, this SELECT expects UPDATE to complete, after waiting for the first SELECT to complete.

The InnoDB table implements row-level locking, so only the row being read will be locked, not the entire table.

Instead of relying only on the documentation, I tried a little:

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

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

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