what lock will mysql issue for 'load data infile' on innodb?

Question

I'm using LOAD DATA INFILE on an InnoDB table, and I see "System lock" status in SHOW PROCESSLIST. What lock exactly does InnoDB lock in this situation? How can I check what is locked?

Answer 1

There are two parts to your question: What does LOAD DATA INFILE actually lock in InnoDB, and how can you examine locks.

The LOAD DATA INFILE command is basically just a slightly more efficient way to do a multi-row INSERT, and it will lock exactly the same things as the INSERT would. As the LOAD DATA INFILE is processed, for each index in the table being loaded into, the key from the row being loaded is locked with an exclusive row lock.

While the LOAD DATA INFILE is running, you will be able to see some overall statistics about its locking with SHOW ENGINE INNODB STATUS. If you enable the InnoDB lock monitor you can see more detail about each individual lock taken (up to a point).