Understanding MySQL lock_time: How Table and Row Lock Wait Times Are Calculated

MySQL's Lock_time metric represents the total time spent waiting for locks during query execution. It is the sum of table‑level lock wait time and row‑level lock wait time, and its calculation involves several layers of the server and storage engine.

1. Overall Introduction

Lock_time consists of two parts:

Table lock wait time – the sum of wait times for all tables involved in the statement.

Row lock wait time – the sum of wait times for all rows locked during the statement.

InnoDB normally acquires row‑level shared (S) or exclusive (X) locks for DML/DQL operations, but table‑level locks can also be taken when LOCK TABLES is used or when the system variable innodb_table_locks is enabled.

2. Table Lock Wait Time

The call stack for acquiring table locks looks like this:

| > mysql_execute_command(THD*, bool) sql/sql_parse.cc:4688
| + > Sql_cmd_dml::execute(THD*) sql/sql_select.cc:574
| + - > lock_tables(...) sql/sql_base.cc:6899
| + - x > mysql_lock_tables(...) sql/lock.cc:337
| + - x = > lock_external(THD*, TABLE**, unsigned int) sql/lock.cc:393
| + - x = | > handler::ha_external_lock(THD*, int) sql/handler.cc:7841
| + - x = | + > ha_innobase::external_lock(THD*, int) storage/innobase/handler/ha_innodb.cc:18869

The lock_tables() function (excerpt below) records the start time, calls mysql_lock_tables() , and after lock_external() returns it records the end time, adding the difference to the thread’s lock‑wait counter.

bool lock_tables(THD *thd, Table_ref *tables, uint count, uint flags) {
  ...
  if (!thd->locked_tables_mode) {
    if (!(thd->lock = mysql_lock_tables(thd, start, (uint)(ptr - start), flags)))
      return true;
    ...
  }
  ...
}

mysql_lock_tables() measures the duration of lock_external() and adds it to THD::inc_lock_usec() :

MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, size_t count, uint flags) {
  ...
  ulonglong lock_start_usec = my_micro_time();
  ...
  if (sql_lock->table_count && lock_external(thd, sql_lock->table, sql_lock->table_count)) {
    reset_lock_data_and_free(&sql_lock);
    goto end;
  }
  ...
  ulonglong lock_end_usec = my_micro_time();
  thd->inc_lock_usec(lock_end_usec - lock_start_usec);
  ...
}

The storage‑engine side ha_external_lock() forwards to InnoDB’s ha_innobase::external_lock() , which performs three kinds of work: acquiring table‑level shared or exclusive locks, flushing dirty pages of the table’s tablespace, and executing lightweight initialization logic. The time spent in this function is counted as table‑lock wait time, even though part of it is not pure locking.

3. Row Lock Wait Time

When a row needs to be locked, InnoDB calls sel_set_rec_lock() . If another transaction holds the lock, the function returns DB_LOCK_WAIT and the engine invokes row_mysql_handle_errors() , which eventually calls lock_wait_suspend_thread() :

void lock_wait_suspend_thread(que_thr_t *thr) {
  srv_slot_t *slot;
  trx_t *trx;
  std::chrono::steady_clock::time_point start_time;
  ...
  if (thr->lock_state == QUE_THR_LOCK_ROW) {
    srv_stats.n_lock_wait_count.inc();
    srv_stats.n_lock_wait_current_count.inc();
    start_time = std::chrono::steady_clock::now();
  }
  ...
  os_event_wait(slot->event);
  ...
  if (thr->lock_state == QUE_THR_LOCK_ROW) {
    auto diff_time = std::chrono::steady_clock::now() - start_time;
    thd_set_lock_wait_time(trx->mysql_thd, diff_time);
  }
  ...
}

The measured duration is stored in the thread’s lock‑wait accumulator via thd_set_lock_wait_time() and later added to THD::inc_lock_usec() .

4. Accumulated Time

Both table‑level and row‑level wait times are accumulated into the thread’s m_lock_usec field by calling thd_set_lock_wait_time() :

void thd_set_lock_wait_time(THD *thd, std::chrono::steady_clock::duration value) {
  if (thd) {
    thd_storage_lock_wait(thd,
      std::chrono::duration_cast
(value).count());
  }
}

void thd_storage_lock_wait(MYSQL_THD thd, long long value) {
  thd->inc_lock_usec(value);
}

void THD::inc_lock_usec(ulonglong lock_usec) {
  m_lock_usec += lock_usec;
  MYSQL_SET_STATEMENT_LOCK_TIME(m_statement_psi, m_lock_usec);
}

5. lock_time

When a statement finishes, dispatch_command() eventually calls log_slow_statement() , which forwards to Query_logger::slow_log_write() . This function retrieves the accumulated lock time via thd->get_lock_usec() and writes it to the slow‑query log:

bool Query_logger::slow_log_write(THD *thd, const char *query, size_t query_length,
                                 bool aggregate, ulonglong lock_usec, ulonglong exec_usec) {
  if (aggregate) {
    query_utime = exec_usec;
    lock_utime = lock_usec;
  } else if (thd->start_utime) {
    query_utime = (current_utime - thd->start_utime);
    lock_utime = thd->get_lock_usec();
  } else {
    query_utime = 0;
    lock_utime = 0;
  }
  ...
  for (Log_event_handler **current_handler = slow_log_handler_list; *current_handler;) {
    error |= (*current_handler++)->log_slow(
        thd, current_utime, thd->start_time.tv_sec * 1000000ULL + thd->start_time.tv_usec,
        user_host_buff, user_host_len, query_utime, lock_utime, is_command,
        query, query_length);
  }
  ...
}

The final log line looks like:

# Query_time: 0.123  Lock_time: 0.001
# Rows_sent: 10  Rows_examined: 1000

6. Summary

Lock_time is the sum of table‑level and row‑level lock wait times. Table‑level wait time includes both pure lock acquisition and auxiliary operations such as flushing dirty pages or lightweight initialization. For FLUSH TABLES … WITH READ LOCK , it also covers the time spent flushing the tablespace to disk. Row‑level wait time is a pure measure of how long a transaction waited for row locks.