慢查詢?nèi)罩局械?Lock_Time 從哪里來(lái)？
經(jīng)常關(guān)注慢查詢?nèi)罩镜淖x者，和 Lock_time 應(yīng)該算是老相識(shí)了，大家對(duì)這位老相識(shí)了解有多少呢？
研究 Lock_time 之前，我對(duì)它的了解，僅限于它表示鎖等待時(shí)間。至于它包含哪些鎖等待時(shí)間、怎么計(jì)算得到的，我并不清楚。
所以，我一直有個(gè)困惑：為什么有些 SQL 執(zhí)行時(shí)間很長(zhǎng)，Lock_time 卻很?。ɡ纾?.001 秒）?
今天我們就一起來(lái)看看，Lock_time 包含哪些鎖等待時(shí)間、以及是怎么計(jì)算得到的？
正文
整體介紹
Lock_time 由兩部分相加得到：
- 表鎖等待時(shí)間，如果 SQL 中包含多個(gè)表，則是多個(gè)表鎖等待時(shí)間之和。
- 行鎖等待時(shí)間，如果 SQL 執(zhí)行過(guò)程中需要對(duì)多條記錄加鎖，則是多個(gè)行鎖等待時(shí)間之和。
對(duì) InnoDB 來(lái)說(shuō)，DML、DQL 對(duì)記錄進(jìn)行增刪改查操作時(shí)，如需加鎖，都是加行級(jí)別的共享鎖、排他鎖，而不加表級(jí)別的共享鎖、排他鎖。
共享鎖又稱作 S 鎖，排他鎖又稱作 X 鎖。
那么，InnoDB 有表級(jí)別的共享鎖、排他鎖嗎？
別說(shuō)，還真有！
不過(guò)，不常有！
只有執(zhí)行 LOCK TABLES ... [READ | WRITE]，并且系統(tǒng)變量 innodb_table_locks = 1、auto_commit = 0，InnoDB 才會(huì)加表級(jí)別的共享鎖、排他鎖。
從代碼注釋和官方文檔對(duì) innodb_table_locks 的介紹來(lái)看，執(zhí)行存儲(chǔ)過(guò)程和觸發(fā)器時(shí)，InnoDB 也可能會(huì)加表級(jí)別的共享鎖、排他鎖，我們就不展開介紹了。
如果 InnoDB 加了表級(jí)別的共享鎖、排他鎖，Lock_time 包含表鎖等待時(shí)間，我們比較好理解。
如果我們執(zhí)行 DML、DQL，InnoDB 沒有加表級(jí)別的共享鎖、排他鎖，Lock_time 里還包含表鎖等待時(shí)間嗎？
這個(gè)問(wèn)題，就得看用什么標(biāo)準(zhǔn)了：
- 嚴(yán)格來(lái)說(shuō)，Lock_time 就不包含表鎖等待時(shí)間了。
- 不嚴(yán)格來(lái)說(shuō)，Lock_time 還是包含表鎖等待時(shí)間的（InnoDB 采用了這個(gè)標(biāo)準(zhǔn)）。
接下來(lái)，我們通過(guò)源碼，進(jìn)入表鎖、行鎖等待時(shí)間的實(shí)現(xiàn)邏輯，來(lái)一睹芳容。
表鎖等待時(shí)間
我們先來(lái)看一下表鎖等待時(shí)間實(shí)現(xiàn)邏輯的堆棧：
| > 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:18869Sql_cmd_dml::execute() 調(diào)用 lock_tables() 對(duì)多個(gè)表加鎖。
// sql/sql_base.cc
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;
...
}
...
}lock_tables() 調(diào)用 mysql_lock_tables() 對(duì)多個(gè)表加鎖。
// sql/lock.cc
MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, size_t count,
uint flags) {
...
// 記錄開始時(shí)間
ulonglong lock_start_usec = my_micro_time();
...
if (sql_lock->table_count &&
lock_external(thd, sql_lock->table, sql_lock->table_count)) {
/* Clear the lock type of all lock data to avoid reusage. */
reset_lock_data_and_free(&sql_lock);
goto end;
}
...
// lock_external() 執(zhí)行完成之后
// 當(dāng)前時(shí)間減去開始時(shí)間
// 就是表鎖等待時(shí)間
ulonglong lock_end_usec = my_micro_time();
thd->inc_lock_usec(lock_end_usec - lock_start_usec);
...
}mysql_lock_tables() 調(diào)用 lock_external() 之前，先把當(dāng)前時(shí)間記錄下來(lái)，作為表鎖等待的開始時(shí)間。
然后調(diào)用 lock_external() 對(duì)多個(gè)表加鎖。
最后，調(diào)用 thd->inc_lock_usec() 把表鎖等待時(shí)間累加到 server 層線程對(duì)象（thd）的 m_lock_usec 屬性中。
// sql/lock.cc
static int lock_external(THD *thd, TABLE **tables, uint count) {
...
// 循環(huán) SQL 中的表
for (i = 1; i <= count; i++, tables++) {
assert((*tables)->reginfo.lock_type >= TL_READ);
// 默認(rèn)鎖類型為寫鎖
// 對(duì)應(yīng)到 InnoDB 的鎖類型就是排他鎖（X）
lock_type = F_WRLCK; /* Lock exclusive */
// 如果以只讀方式打開表的數(shù)據(jù)文件（.ibd）或者
// lock_type 大于等于 TL_READ(2) 并且
// lock_type 小于等于 TL_READ_NO_INSERT(5)
// 則說(shuō)明是只讀操作，加讀鎖
// 對(duì)應(yīng)到 InnoDB 的鎖類型就是共享鎖（S）
if ((*tables)->db_stat & HA_READ_ONLY ||
((*tables)->reginfo.lock_type >= TL_READ &&
(*tables)->reginfo.lock_type <= TL_READ_NO_INSERT))
lock_type = F_RDLCK;
if ((error = (*tables)->file->ha_external_lock(thd, lock_type))) {
// ha_external_lock() 返回非 0 值
// 說(shuō)明執(zhí)行 ha_external_lock() 方法出現(xiàn)了錯(cuò)誤
// 這里處理善后工作
...
return error;
} else {
(*tables)->db_stat &= ~HA_BLOCK_LOCK;
(*tables)->current_lock = lock_type;
}
}
return 0;
}lock_external() 會(huì)迭代 SQL 中的表，每迭代一個(gè)表，都調(diào)用 ha_external_lock() 對(duì)表進(jìn)行加鎖。
// sql/handler.cc
int handler::ha_external_lock(THD *thd, int lock_type) {
...
MYSQL_TABLE_LOCK_WAIT(PSI_TABLE_EXTERNAL_LOCK, lock_type,
{ error = external_lock(thd, lock_type); })
...
}handler::ha_external_lock() 調(diào)用表對(duì)應(yīng)存儲(chǔ)引擎的 external_lock() 方法。
對(duì) InnoDB 來(lái)說(shuō)，調(diào)用的是 ha_innobase::external_lock()，這個(gè)方法的代碼比較多，算是個(gè)大雜燴，可以分為三類：
- 加表級(jí)別的共享鎖、排他鎖。
- 把當(dāng)前迭代表所屬表空間的臟頁(yè)，同步刷新到磁盤。
- 一些初始化邏輯（執(zhí)行快，花費(fèi)時(shí)間極少）。
ha_innobase::external_lock() 的執(zhí)行時(shí)間會(huì)計(jì)入表鎖等待時(shí)間，因?yàn)槠渲锌赡馨剿⑴K頁(yè)操作、執(zhí)行一些初始化邏輯花費(fèi)的時(shí)間，所以，表鎖等待時(shí)間并不純粹。
對(duì)需要加表鎖的 SQL 來(lái)說(shuō)，表鎖等待時(shí)間包含兩部分：
- 加表級(jí)別的共享鎖、排他鎖的等待時(shí)間。
- 執(zhí)行一些初始化邏輯花費(fèi)的時(shí)間。
如果是 FLUSH TABLES ... WITH READ LOCK 語(yǔ)句，表鎖等待時(shí)間還包含：把其中涉及的表所屬表空間的臟頁(yè)同步刷新到磁盤所花費(fèi)的時(shí)間。
對(duì)不需要加表鎖的 SQL 來(lái)說(shuō)，表鎖等待時(shí)間就是執(zhí)行 ha_innobase::external_lock() 中一些初始化邏輯花費(fèi)的時(shí)間。
我們來(lái)看看 ha_innobase::external_lock() 主要包含哪些代碼邏輯，對(duì)這部分細(xì)節(jié)不感興趣的讀者，可以跳過(guò)這個(gè)小節(jié)。
這個(gè)小節(jié)的代碼都來(lái)自于 ha_innobase::external_lock()，文件路徑 storage/innobase/handler/ha_innodb.cc。
update_thd(thd);以上代碼，創(chuàng)建 InnoDB 的事務(wù)對(duì)象（trx_t），保存到 server 層的用戶線程對(duì)象（thd）中。
// lock_type == F_WRLCK，意味著需要加寫鎖
// 這里用于表示需要記錄 binlog
if (lock_type == F_WRLCK &&
// 表示不支持 STATEMENT 格式的 binlog
// table_flags() 方法會(huì)判斷事務(wù)隔離級(jí)別
!(table_flags() & HA_BINLOG_STMT_CAPABLE) &&
// 系統(tǒng)變量 binlog_format = STATEMENT
// 表示用戶需要記錄 STATEMENT 格式的 binlog
thd_binlog_format(thd) == BINLOG_FORMAT_STMT &&
// 表示需要為當(dāng)前連接指定的數(shù)據(jù)庫(kù)記錄 binlog
// use <db> 或者連接數(shù)據(jù)庫(kù)時(shí)指定了數(shù)據(jù)庫(kù)
thd_binlog_filter_ok(thd) &&
// 表示當(dāng)前執(zhí)行的 SQL 會(huì)產(chǎn)生 ROW 格式的 binlog
thd_sqlcom_can_generate_row_events(thd)) {
bool skip = false;
...
if (!skip) {
...
return HA_ERR_LOGGING_IMPOSSIBLE;
}
}上面代碼的判斷條件有點(diǎn)多，我們用一句話來(lái)概括一下代碼邏輯：
事務(wù)隔離級(jí)別為 READ_UNCOMMITTED、READ_COMMITTED 時(shí)，如果 SQL 會(huì)產(chǎn)生 ROW 格式的 binlog，而用戶設(shè)置系統(tǒng)變量 binlog_format 的值為 STATEMENT，要求記錄 STATEMENT 格式的 binlog，ha_innobase::external_lock() 會(huì)返回 HA_ERR_LOGGING_IMPOSSIBLE，因?yàn)?MySQL 無(wú)法處理這樣矛盾的場(chǎng)景。
if (lock_type == F_WRLCK) {
/* If this is a SELECT, then it is in UPDATE TABLE ...
or SELECT ... FOR UPDATE */
m_prebuilt->select_lock_type = LOCK_X;
m_stored_select_lock_type = LOCK_X;
}InnoDB 讀取記錄時(shí)，會(huì)根據(jù) m_prebuilt->select_lock_type 的值確定是否加行鎖、加共享鎖還是排他鎖。
lock_type 等于 F_WRLCK，表示 server 層要求加寫鎖，對(duì)應(yīng)到 InnoDB 的鎖類型，就是排他鎖，設(shè)置加鎖類型為 LOCK_X。
if (lock_type == F_RDLCK) {
...
// 如果當(dāng)前表是數(shù)據(jù)字典表
// 或者被標(biāo)識(shí)為不需要加鎖（no_read_locking = true)
// 設(shè)置加鎖類型為 LOCK_NONE
if (m_prebuilt->table->is_dd_table || m_prebuilt->no_read_locking) {
m_prebuilt->select_lock_type = LOCK_NONE;
m_stored_select_lock_type = LOCK_NONE;
// 如果事務(wù)隔離級(jí)別是可串行化
} else if (trx->isolation_level == TRX_ISO_SERIALIZABLE &&
// 并且當(dāng)前 SQL 還沒有確定加鎖類型
m_prebuilt->select_lock_type == LOCK_NONE &&
// 并且當(dāng)前事務(wù)需要手動(dòng)提交
thd_test_options(thd, OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) {
// 設(shè)置加鎖類型為共享鎖
m_prebuilt->select_lock_type = LOCK_S;
m_stored_select_lock_type = LOCK_S;
} else {
// Retain value set earlier for example via store_lock()
// which is LOCK_S or LOCK_NONE
ut_ad(m_prebuilt->select_lock_type == LOCK_S ||
m_prebuilt->select_lock_type == LOCK_NONE);
}
}lock_type 等于 F_RDLCK，表示 server 層要求加讀鎖，對(duì)應(yīng)到 InnoDB 的鎖類型，就是共享鎖，分兩種情況設(shè)置 InnoDB 的加鎖類型：
- 對(duì)于 ACL 表，m_prebuilt->no_read_locking 會(huì)被設(shè)置為 true，表示讀取記錄時(shí)不加鎖。
- 如果事務(wù)隔離級(jí)別是可串行化，并且當(dāng)前事務(wù)需要手動(dòng)執(zhí)行 COMMIT 語(yǔ)句提交，以及還沒有確定讀取該表記錄時(shí)加什么類型的行鎖，設(shè)置 InnoDB 加鎖類型為共享鎖。
ACL 表用于訪問(wèn)權(quán)限控制，包含如下這些表：
- user
- db
- tables_priv
- columns_priv
- procs_priv
- proxies_priv
- role_edges
- default_roles
- global_grants
- password_history
switch (m_prebuilt->table->quiesce) {
case QUIESCE_START:
/* Check for FLUSH TABLE t WITH READ LOCK; */
if (!srv_read_only_mode && sql_command == SQLCOM_FLUSH &&
lock_type == F_RDLCK) {
...
row_quiesce_table_start(m_prebuilt->table, trx);
...
}
break;
...
}只有執(zhí)行 FLUSH TABLES ... WITH READ LOCK 語(yǔ)句時(shí)，才會(huì)命中代碼中的 case 分支。
row_quiesce_table_start() 會(huì)調(diào)用 buf_LRU_flush_or_remove_pages()，并把當(dāng)前加表鎖的表所屬表空間對(duì)象傳給該方法，表示把該表空間的臟頁(yè)刷新到磁盤。
行鎖等待時(shí)間
我們先來(lái)看看對(duì)一條記錄加行鎖的等待時(shí)間是怎么計(jì)算的。
InnoDB 讀取一條記錄時(shí)，如需加行鎖，會(huì)調(diào)用 sel_set_rec_lock() 進(jìn)行加鎖。
如果其它事務(wù)持有該記錄的行鎖，sel_set_rec_lock() 會(huì)返回 DB_LOCK_WAIT，row_search_mvcc() 調(diào)用 row_mysql_handle_errors() 處理鎖等待邏輯。
row_mysql_handle_errors() 調(diào)用 lock_wait_suspend_thread()，行鎖等待邏輯由這個(gè)方法實(shí)現(xiàn)。
// storage/innobase/lock/lock0wait.cc
void lock_wait_suspend_thread(que_thr_t *thr) {
srv_slot_t *slot;
trx_t *trx;
// 聲明變量，用于保存行鎖等待的開始時(shí)間
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();
// 設(shè)置行鎖等待的開始時(shí)間
start_time = std::chrono::steady_clock::now();
}
...
// 等待行鎖
os_event_wait(slot->event);
...
// 運(yùn)行到這里，有兩種情況：
// 1. 鎖等待超時(shí)
// 2. 已經(jīng)獲取到行鎖
if (thr->lock_state == QUE_THR_LOCK_ROW) {
// 用當(dāng)前時(shí)間減去行鎖等待的開始時(shí)間
// 就是一條記錄的行鎖等待時(shí)間
const auto diff_time = std::chrono::steady_clock::now() - start_time;
...
/* Record the lock wait time for this thread */
// 累加線程的行鎖等待時(shí)間
// 保存到 mysql_thd 線程中
// mysql_thd 是 server 層的線程
thd_set_lock_wait_time(trx->mysql_thd, diff_time);
...
}
...
}從上面代碼可以看到，計(jì)算一條記錄的行鎖等待時(shí)間，邏輯比較簡(jiǎn)單： 先保存當(dāng)前行鎖等待的開始時(shí)間，獲取到行鎖或等待行鎖超時(shí)之后，再用當(dāng)前時(shí)間減去開始時(shí)間，就得到了一條記錄的行鎖等待時(shí)間。
累計(jì)時(shí)間
一滴水的夢(mèng)想是終有一天能夠匯入大海。
表鎖、行鎖等待時(shí)間的歸宿是累加起來(lái)，最終成為 lock_time，這個(gè)過(guò)程是通過(guò)調(diào)用 thd_set_lock_wait_time() 實(shí)現(xiàn)的。
// storage/innobase/handler/ha_innodb.cc
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<std::chrono::microseconds>(value).count());
}
}thd_set_lock_wait_time() 調(diào)用 thd_storage_lock_wait() 累加表鎖、行鎖等待時(shí)間。
// sql/sql_thd_api.cc
void thd_storage_lock_wait(MYSQL_THD thd, long long value) {
thd->inc_lock_usec(value);
}真正干活的是 THD::inc_lock_usec() 方法。
// sql/sql_class.cc
void THD::inc_lock_usec(ulonglong lock_usec) {
m_lock_usec += lock_usec;
MYSQL_SET_STATEMENT_LOCK_TIME(m_statement_psi, m_lock_usec);
}server 層每獲取到一個(gè)表鎖，都會(huì)調(diào)用 thd_set_lock_wait_time()，累加表鎖等待時(shí)間。
最終會(huì)調(diào)用 THD::inc_lock_usec() 把表鎖等待時(shí)間累加到 server 層的線程對(duì)象 thd 的 m_lock_usec 屬性中。
InnoDB 每獲取到一條記錄的行鎖，或者行鎖等待超時(shí)，都會(huì)調(diào)用 thd_set_lock_wait_time()，累加行鎖等待時(shí)間。
最終會(huì)調(diào)用 THD::inc_lock_usec() 把行鎖等待時(shí)間累加到 server 層的線程對(duì)象 thd 的 m_lock_usec 屬性中。
lock_time
SQL 執(zhí)行完成之后，dispatch_command() 調(diào)用 log_slow_statement() 記錄慢查詢到文件中。
log_slow_statement() 也不是真正干活的，經(jīng)過(guò)多級(jí)，最終調(diào)用 Query_logger::slow_log_write() 記錄慢查詢到文件中。
// sql/log.cc
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;
}
...
bool error = false;
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);
}
...
}Query_logger::slow_log_write() 被調(diào)用時(shí)，參數(shù) aggregate 的值都是 false，上面代碼不會(huì)進(jìn)入 if (aggregate) 分支。
if (thd->start_utime) 分支，lock_utime = thd->get_lock_usec()，從當(dāng)前線程對(duì)象（thd）中獲取之前累加的表鎖、行鎖等待時(shí)間。
然后，調(diào)用 log_slow() 記錄慢查詢到文件中。
// sql/log.cc
bool Log_to_file_event_handler::log_slow(
THD *thd, ulonglong current_utime, ulonglong query_start_utime,
const char *user_host, size_t user_host_len, ulonglong query_utime,
ulonglong lock_utime, bool is_command, const char *sql_text,
size_t sql_text_len) {
if (!mysql_slow_log.is_open()) return false;
Silence_log_table_errors error_handler;
thd->push_internal_handler(&error_handler);
bool retval = mysql_slow_log.write_slow(
thd, current_utime, query_start_utime, user_host, user_host_len,
query_utime, lock_utime, is_command, sql_text, sql_text_len);
thd->pop_internal_handler();
return retval;
}Log_to_file_event_handler::log_slow() 最終調(diào)用 mysql_slow_log.write_slow() 記錄慢查詢到文件中。
// sql/log.cc
bool File_query_log::write_slow(...) {
...
if (!thd->copy_status_var_ptr) {
if (my_b_printf(&log_file,
"# Query_time: %s Lock_time: %s"
" Rows_sent: %lu Rows_examined: %lu\n",
query_time_buff, lock_time_buff,
(ulong)thd->get_sent_row_count(),
(ulong)thd->get_examined_row_count()) == (uint)-1)
goto err; /* purecov: inspected */
}
...
}經(jīng)?？绰樵?nèi)罩镜淖x者，想必對(duì)這 2 行會(huì)非常熟悉：
Query_time: %s Lock_time: %s
Rows_sent: %lu Rows_examined: %lu其中的 Lock_time 就是本文的主題，介紹到這里，總算是和文章標(biāo)題遙相呼應(yīng)上了。
總結(jié)
Lock_time 由表鎖、行鎖等待時(shí)間相加得到。
表鎖等待時(shí)間并不純粹，其中包含執(zhí)行一些初始化操作花費(fèi)的時(shí)間。
對(duì) FLUSH TABLES ... WITH READ LOCK 語(yǔ)句來(lái)說(shuō)，還包含把其中涉及的表所屬表空間的臟頁(yè)同步刷新到磁盤所花費(fèi)的時(shí)間。
行鎖等待時(shí)間很純粹，就是多條記錄的行鎖等待時(shí)間之和，或者一條記錄的行鎖等待時(shí)間。
本文轉(zhuǎn)載自微信公眾號(hào)「一樹一溪」，可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系一樹一溪公眾號(hào)。
