0

I'm working on fixing up a messed up database. I have several MyISAM and InnoDB tables in this DB. I need to write a query that uses the following 3 tables:

table_a => InnoDB
table_b => InnoDB
table_c => MyISAM

The query takes about a minute to run and I am worries about the issues this could create on our production site if table level locking will occur in my query. I'm not sure if it will based on the docs. I know that MyISAM locks tables and InnoDB locks rows, but I am using them together. Here is the query:

INSERT INTO 
    table_a (`x`, `y`, `z`)
SELECT 
    table_b.x, table_b.y, table_c.z 
FROM 
    table_b, table_c 
WHERE 
    table_b.id = table_c.id AND
    table_b.value IS NOT NULL AND
    table_b.id NOT IN (SELECT table_a.id FROM table_a WHERE 1);

I'm not sure what will happen here when this query is executed. I think that table locking will not happen because the write occurs on table_a which is InnoDB. However I'm not sure how this will be managed because that write operation is based on reads that occur on both MyISAM and InnoDB. I'm not yet very knowledgeable on how locking is managed and I need to find out if any of these tables will lock while this query executes. Thank you to anyone that helps.

usumoio
  • 3,500
  • 6
  • 31
  • 57

1 Answers1

1

The whole table_c (MyISAM) will be locked in "read mode" during the course of your query. This means that concurrent reads on this table will be possible, but concurrent writes will be held.

If a single write is put on hold, then all subsequent reads will also be put on hold (unless they are issued in HIGH_PRIORITY).

The other two InnoDB tables will exhibit a very similar behaviour, but possibly not on the full tables. Only "some" rows will be locked if a suitable index can be used to honor the match clauses and the JOIN conditions (it is still possible that the whole tables become locked if table scans are required -- if no suitable index can be used, that is).

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • Perhaps you should migrate your MyISAM table to InnoDB. Nowadays, there are not many cases where using MyISAM is really beneficial. – RandomSeed Jul 22 '13 at 22:56
  • It looks like reads and writes will be okay on table_b, but other sessions cannot write table_a. Other sessions can read table_b and table_c. And table_a can be read too, just not written to until the operation is over. I'm trying to test every combination of reads and writes across sessions. – usumoio Jul 23 '13 at 01:46
  • @IamJohnGalt Please beware my typo in the last version. I meant `table_c`. The whole `table_a` is locked in "read mode" probably because the whole table is scanned in the last subquery. You should replace the `NOT IN` by a `LEFT OUTER JOIN`. – RandomSeed Jul 23 '13 at 16:21
  • Good call with the left outer join. Thanks, I have reviewed my work separately and I agree with you. – usumoio Jul 23 '13 at 17:27