Which tables will be locked with this query?

Question

I am migrating data between two tables using the query below. I had table locking issues, so I am considering changing the table engines from MyISAM to InnoDB. Which tables do I need to do that for? Just the table I am writing to, or both the table I am writing to and the one I am reading from?

INSERT INTO table1 (
  field1, field2, field2
)
SELECT
  field1, field2,
  (
    SELECT
      temp.field4
    FROM
      table1 AS temp
    WHERE
      temp.id = table2.id
    ORDER BY
      temp.something DESC
    LIMIT
      1
  ) + 1 AS field3
FROM
  table2
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      table1 AS temp
    WHERE
      temp.id = table2.id
  )

Answer 1

table 1 should be locked. Can someone confirm?

I only use Innodb. i forget what myisam does!

Either way, you should switch everything to Innodb. These days Innodb is just as fast for reads anyway.

Answer 2

What to lock

It depends. If you want to make sure that no new data can be written while you perform the update you should lock both tables. If it's just a matter of writing then only lock the table where you're doing the update.

Common missconception

It is wrong that MyISAM cannot do locks. It is only row level locking that MyISAM is not capable of. MyISAM is perfectly able to lock tables.

Make your desicion based on the following list.

Decision Helper

Is your table going to be inserted, deleted, and updated much more than it is going to be selected?
=> InnoDB

Do you need full-text search
=> MyISAM

Do you prefer/require clear foreign key constraints
=> InnoDB

Is disk-space or ram an issue?
=> MyISAM

In Doubt?
=>MyISAM

There is no winner.