How should I interpret the "lock time" within mysql slow query log?

Question

I'm trying to understand how to best interpret the lock time for the queries which show up on our MySQL slow query log.

For instance if an UPDATE query has 10 second lock time. I presume it is the total time after the update query acquires the lock. Even if it is waiting for previous select queries to complete but not executing the UPDATE action itself, the clock should be ticking since it is locking all the SELECT queries which have lined up after the UPDATE query.

And how about the SELECT query locks. How come some select queries have lock times? Is it because there is an UPDATE query following up hence they are locking a table together.

Answer 1

The lock_time in the slow query log is actually the amount of time the query spent waiting to acquire the lock it needs to run. For example, UPDATES queries need a write lock.

The locking also depends on the storage engine you are using in the table. When writing, InnoDB will use row-level locking and only lock the rows that are being changed. MyISAM will lock the entire table until the update/insert/delete is complete.

The Locking of the entire table for MyISAM is a big reason SELECT queries will have a lock_time in your slow query log.