Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
@DTest Thank you for the answer. Somehow I cannot login as the account which created this question. I'll flag the question to get some moderator attention and help to mark your answer as correct. –  Haluk Feb 22 '12 at 11:35
    
I've merged the 2 accounts - you should be able to mark your accepted answer now... –  Jack Douglas Feb 22 '12 at 12:35

1 Answer 1

up vote 9 down vote accepted

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.

share|improve this answer
2  
Nothing to add here, moving along (+1)... –  RolandoMySQLDBA Feb 21 '12 at 16:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.