引言

之前遇到過 mysqldump 導(dǎo)致鎖表，后來才發(fā)現(xiàn) insert select 也會給源表加鎖，具體加鎖類型是 S 型 next-key lock。本文分析加鎖現(xiàn)象與原因，并提供優(yōu)化建議。

現(xiàn)象

時間：20231124 09:58

數(shù)據(jù)庫版本：MySQL 5.7.24

現(xiàn)象：insert select 備份表導(dǎo)致 update 鎖等待

查看監(jiān)控

其中：

• 鎖等待顯示每秒平均等待時間將近一小時
• 慢 SQL 顯示 insert select 期間鎖表，阻塞業(yè)務(wù) update 語句

測試

測試準(zhǔn)備

mysql> show create table t3_bak \G
*************************** 1. row ***************************
       Table: t3_bak
Create Table: CREATE TABLE `t3_bak` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `a` int(10) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `b` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_name_a` (`name`,`a`)
) ENGINE=InnoDB AUTO_INCREMENT=10000000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t3_bak limit 3;
+----+------+------+------+
| id | a    | name | b    |
+----+------+------+------+
| 11 |   11 | test |    0 |
| 12 |   12 | abc  |    0 |
| 13 |   13 | test |    0 |
+----+------+------+------+
3 rows in set (0.00 sec)

mysql> create table t3_bak_1124 like t3_bak;
Query OK, 0 rows affected (0.02 sec)

復(fù)現(xiàn)

操作流程，其中事務(wù) 1 備份全表，事務(wù) 2 update 其中一行數(shù)據(jù)。

時刻 2 查看鎖信息

其中：

• information_schema.innodb_locks 表中記錄鎖等待相關(guān)信息，顯示事務(wù) 1 持有主鍵 S 型 next-key lock，事務(wù) 2 申請同一行數(shù)據(jù)的 X 型 next-key lock，因此發(fā)生鎖等待。

由于查詢?nèi)頃r加鎖過多，為了查看事務(wù) 1 insert select 完整的鎖信息，下面單獨執(zhí)行 insert select limit 語句。

SQL

mysql> insert into t3_bak_1124 select * from t3_bak limit 3;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看鎖等待信息

其中：

• 給 t3_bak 表中掃描的每行數(shù)據(jù)的主鍵索引加 S 型 next-key lock。

分析

執(zhí)行流程

從 trace 中可以明確看到 insert select 的執(zhí)行可以分兩步：

• select
• insert

詳見下圖。

select

insert

加鎖函數(shù)

給 sel_set_rec_lock 函數(shù)設(shè)置斷點，查看堆棧用于定位加鎖操作。

其中：

• sel_set_rec_lock 函數(shù)入?yún)?mode=2, type=0，表示 S 型 next-key lock；
• row_search_mvcc 調(diào)用 sel_set_rec_lock 函數(shù)加鎖，因此給 row_search_mvcc 函數(shù)設(shè)置斷點，堆棧如下所示。

其中：

• row_search_mvcc 函數(shù)用于行記錄加鎖判斷，相關(guān)代碼如下所示，其中判斷是否加 gap lock。

if (prebuilt->select_lock_type != LOCK_NONE) {
  /* Try to place a lock on the index record; note that delete
  marked records are a special case in a unique search. If there
  is a non-delete marked record, then it is enough to lock its
  existence with LOCK_REC_NOT_GAP. */

  /* If innodb_locks_unsafe_for_binlog option is used
  or this session is using a READ COMMITED isolation
  level we lock only the record, i.e., next-key locking is
  not used. */

  ulint lock_type;

  // 不加gap鎖的場景
  if (!set_also_gap_locks
      || srv_locks_unsafe_for_binlog
      || trx->isolation_level <= TRX_ISO_READ_COMMITTED
      || (unique_search && !rec_get_deleted_flag(rec, comp))
      || dict_index_is_spatial(index)) {

   goto no_gap_lock;
  } else {
   lock_type = LOCK_ORDINARY;
  }
}

其中：

• 對于 RR，未開啟 innodb_locks_unsafe_for_binlog 時，根據(jù) prebuilt->select_lock_type 字段判斷是否加 gap lock，如果為空，使用 record lock，否則使用 next-key lock；
• prebuilt->select_lock_type 表示加鎖的類型，對應(yīng) lock_mode 枚舉類型，常見取值包括：

1. 5（LOCK_NONE），如普通 select 快照讀；
2. 2（LOCK_S），如 select lock in share mode 當(dāng)前讀禁止寫；
3. 3（LOCK_X），如 select for update 當(dāng)前讀禁止讀寫。

• 對于 insert select 語句，由于 prebuilt->select_lock_type = 2，因此加鎖類型為 S 型 next-key lock。

如下所示，sel_set_rec_lock 函數(shù)中加鎖時 lock_mode 同樣使用 prebuilt->select_lock_type，個人判斷行鎖類型與表鎖類型有關(guān)。

  err = sel_set_rec_lock(pcur,
             rec, index, offsets,
             prebuilt->select_lock_type,
             lock_type, thr, &mtr);

因此重點在于 prebuilt->select_lock_type 字段的賦值操作，定位到對應(yīng)堆棧如下所示。

其中：

• sql_command = 6 = SQLCOM_INSERT_SELECT，表示 insert select 語句；
• thr_lock_type = TL_WRITE_CONCURRENT_INSERT，對應(yīng)表鎖，表示允許在表的末尾進(jìn)行插入操作，同時其他線程可以讀取表中的數(shù)據(jù)；
• m_prebuilt->select_lock_type = LOCK_S，對應(yīng)行鎖，表示使用行共享鎖。

ha_innobase::store_lock 函數(shù)中根據(jù) lock_type 與 sql_command 判斷需要是否加 S 鎖，相關(guān)代碼如下所示。

// storge/innobase/handler/ha_innodb.cc

/* Check for LOCK TABLE t1,...,tn WITH SHARED LOCKS */
// 首先根據(jù) lock_type 判斷
} else if ((lock_type == TL_READ && in_lock_tables)
     || (lock_type == TL_READ_HIGH_PRIORITY && in_lock_tables)
     || lock_type == TL_READ_WITH_SHARED_LOCKS
     || lock_type == TL_READ_NO_INSERT
     || (lock_type != TL_IGNORE
         && sql_command != SQLCOM_SELECT)) {

  /* The OR cases above are in this order:
  1) MySQL is doing LOCK TABLES ... READ LOCAL, or we
  are processing a stored procedure or function, or
  2) (we do not know when TL_READ_HIGH_PRIORITY is used), or
  3) this is a SELECT ... IN SHARE MODE, or
  4) we are doing a complex SQL statement like
  INSERT INTO ... SELECT ... and the logical logging (MySQL
  binlog) requires the use of a locking read, or
  MySQL is doing LOCK TABLES ... READ.
  5) we let InnoDB do locking reads for all SQL statements that
  are not simple SELECTs; note that select_lock_type in this
  case may get strengthened in ::external_lock() to LOCK_X.
  Note that we MUST use a locking read in all data modifying
  SQL statements, because otherwise the execution would not be
  serializable, and also the results from the update could be
  unexpected if an obsolete consistent read view would be
  used. */

  /* Use consistent read for checksum table */
 
  // 然后根據(jù) sql_command 判斷
  if (sql_command == SQLCOM_CHECKSUM
      || ((srv_locks_unsafe_for_binlog
    || trx->isolation_level <= TRX_ISO_READ_COMMITTED)
    && trx->isolation_level != TRX_ISO_SERIALIZABLE
    && (lock_type == TL_READ
        || lock_type == TL_READ_NO_INSERT)
    && (sql_command == SQLCOM_INSERT_SELECT // insert select 語句
        || sql_command == SQLCOM_REPLACE_SELECT
        || sql_command == SQLCOM_UPDATE
        || sql_command == SQLCOM_CREATE_TABLE))) {

    /* If we either have innobase_locks_unsafe_for_binlog
    option set or this session is using READ COMMITTED
    isolation level and isolation level of the transaction
    is not set to serializable and MySQL is doing
    INSERT INTO...SELECT or REPLACE INTO...SELECT
    or UPDATE ... = (SELECT ...) or CREATE  ...
    SELECT... without FOR UPDATE or IN SHARE
    MODE in select, then we use consistent read
    for select. */

    m_prebuilt->select_lock_type = LOCK_NONE;
    m_prebuilt->stored_select_lock_type = LOCK_NONE;
  } else {
    m_prebuilt->select_lock_type = LOCK_S;
    m_prebuilt->stored_select_lock_type = LOCK_S;
  }

其中：

• 根據(jù) lock_type 與 sql_command 判斷，以下 SQL 可能需要加鎖：

1. LOCK TABLES ... READ LOCAL
2. SELECT ... IN SHARE MODE
3. INSERT INTO ... SELECT / REPLACE INTO...SELECT / CREATE  ... SELECT

• 滿足以下條件時不需要加鎖，否則需要加 S 型鎖：

1.事務(wù)隔離級別不是 SERIALIZABLE，并開啟 innodb_locks_unsafe_for_binlog

2.事務(wù)隔離級別是 RC

前面提到兩個枚舉類型，下面展示定義。

首先是 enum_sql_command，表示 SQL 的類型，比如 insert select = 6 = SQLCOM_INSERT_SELECT。

enum enum_sql_command {
  SQLCOM_SELECT,
  SQLCOM_CREATE_TABLE,
  SQLCOM_CREATE_INDEX,
  SQLCOM_ALTER_TABLE,
  SQLCOM_UPDATE,
  SQLCOM_INSERT,
  SQLCOM_INSERT_SELECT,
  ...
};

然后是 lock_mode，表示加鎖的模式，比如 insert select = 2 = LOCK_S。

/* Basic lock modes */
enum lock_mode {
 LOCK_IS = 0, /* intention shared */
 LOCK_IX, /* intention exclusive */
 LOCK_S,  /* shared */
 LOCK_X,  /* exclusive */
 LOCK_AUTO_INC, /* locks the auto-inc counter of a table
   in an exclusive mode */
 LOCK_NONE, /* this is used elsewhere to note consistent read */
 LOCK_NUM = LOCK_NONE, /* number of lock modes */
 LOCK_NONE_UNSET = 255
};

加鎖原因

下面分析 insert select 語句加 S 型 next-key lock 的原因。

首先參考官方文檔。  

INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.

對于 insert t select s 語句，其中 t、s 分別表示表名。

執(zhí)行過程中給 t 表加 record lock，具體是隱式鎖，而給 s 表的加鎖類型與事務(wù)隔離級別及參數(shù)配置有關(guān)：

• 如果事務(wù)隔離級別是 READ COMMITTED，不加鎖；
• 如果事務(wù)隔離級別不是 SERIALIZABLE，并開啟 innodb_locks_unsafe_for_binlog，不加鎖；
• 如果事務(wù)隔離級別是 REPEATABLE-READ，加鎖，類型是 S 型 next-key lock。

然后參考 MySQL 45 講。

創(chuàng)建測試表

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t;

在 RR 事務(wù)隔離級別下，binlog_format = statement 時執(zhí)行以下語句時，為什么需要對 t 的所有行和間隙加鎖呢？

insert into t2(c,d) select c,d from t;

原因是需要保證日志與數(shù)據(jù)的一致性，否則將導(dǎo)致主從不一致。

假設(shè) insert select 時 t 表存在并發(fā) insert，其中假設(shè) session B 先執(zhí)行。

其中：

• 由于該 SQL 會給 t 表主鍵索引 (-∞, 1] 加 next-key lock，因此 session A 將阻塞直到 session B 執(zhí)行完成；
• 如果不加鎖，可能出現(xiàn) session B 的 insert 先執(zhí)行，后寫入 binlog 的場景。在 binlog_format = statement 時，binlog 中的語句序列如下所示。

insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;

因此從庫執(zhí)行時，會將 id=-1 的記錄也寫入 t2 表中，從而導(dǎo)致主從不一致。

參考 chatgpt，insert t select * from s 給 s 表加鎖的原因如下所示，顯示與 45 講中一致。

在MySQL中，執(zhí)行"insert ... select"語句時，會對選擇的表S進(jìn)行鎖定以確保在整個選擇和插入過程中的數(shù)據(jù)一致性。

理論上說，"insert ... select"操作包含兩個步驟：第一步是從表S中選擇數(shù)據(jù)；第二步是將選擇的數(shù)據(jù)插入到目標(biāo)表。在這兩個步驟之間，如果表S的數(shù)據(jù)被其他事務(wù)或操作更改，那么從表S選擇的數(shù)據(jù)可能就不再準(zhǔn)確或一致，插入到目標(biāo)表的數(shù)據(jù)也會出現(xiàn)問題。

因此，為了在整個選擇和插入過程中保持?jǐn)?shù)據(jù)的一致性，MySQL在執(zhí)行"insert ... select"操作時會對表S進(jìn)行鎖定。這樣在鎖定期間，其他事務(wù)或操作就不能更改表S的數(shù)據(jù)，從而保證了數(shù)據(jù)的一致性。

參考文章 mysql- insert select帶來的鎖問題，由于復(fù)制的實現(xiàn)機制不同，針對 insert select 語句，oracle 中不需要鎖定源表。

MySQL 中可以通過開啟 innodb_locks_unsafe_for_binlog 來避免這個現(xiàn)象，顯然可能導(dǎo)致主從不一致，因此不建議使用。

針對給源表加鎖的問題，建議使用 select ... into outfile 和 load data file 的組合來代替 insert select 語句，從而避免操作期間鎖表。

需要注意的是如果主從版本不一致，也有可能導(dǎo)致主從不一致，原因是不同版本的加鎖規(guī)則不同。

官方文檔顯示 5.7 中 CREATE TABLE ... SELECT 語句與 INSERT ... SELECT 語句加鎖規(guī)則相同，也就是給源表加鎖。

CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

早期版本（個人理解比如 5.5，未驗證）中不給源表加鎖，因此假如主庫是 5.5，從庫是 5.6+，對于 binlog_format = statement，主庫不加鎖從庫加鎖，導(dǎo)致主從不一致。

針對該問題，有兩種方案，使用 binlog_format = row 或?qū)⒅鲙焐墳?5.7。

MySQL 5.7 does not allow a CREATE TABLE ... SELECT statement to make any changes in tables other than the table that is created by the statement. Some older versions of MySQL permitted these statements to do so; this means that, when using replication between a MySQL 5.6 or later replica and a source running a previous version of MySQL, a CREATE TABLE ... SELECT statement causing changes in other tables on the source fails on the replica, causing replication to stop. To prevent this from happening, you should use row-based replication, rewrite the offending statement before running it on the source, or upgrade the source to MySQL 5.7. (If you choose to upgrade the source, keep in mind that such a CREATE TABLE ... SELECT statement fails following the upgrade unless it is rewritten to remove any side effects on other tables.)

執(zhí)行計劃

參考 MySQL 45 講，對比以下三條語句的執(zhí)行計劃。

其中：

• SQL 1，insert select，執(zhí)行計劃顯示有兩條記錄，且 ID 相同，正常情況下 ID 相同時從上往下執(zhí)行，但是個人理解這里先執(zhí)行第二條的 select，具體待定；
• SQL 2，insert select limit，執(zhí)行計劃顯示 rows 沒變化，原因是 limit 語句的執(zhí)行計劃中 rows 不準(zhǔn)確；
• SQL 3，insert 循環(huán)寫入，查詢與寫入是同一張表，extra 顯示使用臨時表。

下面分別測試驗證。

首先是 insert select 全表，顯示 Innodb_rows_read 值的變化與慢查詢中的掃描行數(shù)相等，且等于表的大小。

然后是 insert select limit，顯示 Innodb_rows_read 值的變化與慢查詢中的掃描行數(shù)相等，且等于 3。

最后是 insert 循環(huán)寫入，顯示 Innodb_rows_read 值的變化與慢查詢中的掃描行數(shù)不相等，后者是前者的兩倍。

原因是 insert 循環(huán)寫入的執(zhí)行流程為：

• 創(chuàng)建臨時表；
• 按照索引掃描 t 表，由于 limit 3，因此僅取前三行數(shù)據(jù)，Rows_examined = 3；
• 最后將臨時表的數(shù)據(jù)全部插入 t 表，因此 Rows_examined 加 3，等于 6。

顯然，insert select 相同表與不同表的主要區(qū)別是后者需要使用臨時表，原因是如果讀出來的數(shù)據(jù)直接寫回原表，可能導(dǎo)致讀取到新插入的記錄，注意事務(wù)隔離級別為 RR 時，事務(wù)可以看到自己修改的數(shù)據(jù)。

注意這里的測試結(jié)果與 45 講中不同，45 講中 limit 失效， t 表全表掃描，limit 在從臨時表插回原表時生效。

參考文章 關(guān)于MySQL insert into ... select 的鎖情況，判斷原因是 select 語句中使用主鍵排序與非主鍵排序時的加鎖規(guī)則不同。其中：

• 使用主鍵排序，逐行鎖定掃描的記錄，limit 失效，臨時表中寫入 limit 數(shù)據(jù)；
• 非主鍵排序，一次性鎖定全表的記錄，limit 生效，臨時表中寫入全表數(shù)據(jù)。

如下所示，對比測試使用主鍵排序與非主鍵排序。

其中：

• 使用主鍵排序，執(zhí)行成功，Rows_examined = 6；
• 使用非主鍵排序，執(zhí)行失敗，Rows_examined = 5190999，報錯臨時表打滿。

因此，使用 insert select 時需要重點關(guān)注是否使用主鍵排序，減少掃描行數(shù)與加鎖行數(shù)。

知識點

innodb_locks_unsafe_for_binlog

row_search_mvcc 函數(shù)中判斷加鎖類型時，如果開啟 innodb_locks_unsafe_for_binlog 參數(shù)，只會對行加鎖，而不會鎖間隙。

innodb_locks_unsafe_for_binlog 參數(shù)用于控制查詢與索引掃描時是否使用 gap lock。默認(rèn) 0，表示使用 gap lock。

RR 開啟 innodb_locks_unsafe_for_binlog 參數(shù)時相當(dāng)于退化為 RC，但有兩點不同：

• innodb_locks_unsafe_for_binlog 是全局參數(shù)，不支持 session 級別配置；
• innodb_locks_unsafe_for_binlog 是靜態(tài)參數(shù)，不支持動態(tài)修改。

開啟 innodb_locks_unsafe_for_binlog 時，將導(dǎo)致幻讀，原因是間隙沒有加鎖，因此其他事務(wù)可以插入。

注意與 RC 相同，開啟 innodb_locks_unsafe_for_binlog 參數(shù)時，外鍵沖突檢測與唯一性檢查時依然需要使用 gap lock。

Enabling innodb_locks_unsafe_for_binlog does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.

除了影響查詢語句的加鎖規(guī)則，開啟 innodb_locks_unsafe_for_binlog 參數(shù)時也會影響更新操作，具體規(guī)則為：

• 對于 update / delete 語句，提前釋放不滿足 where 條件的記錄上的鎖，優(yōu)點是可以減少鎖沖突，缺點是違背兩階段加鎖協(xié)議；
• 對于 update 語句，如果發(fā)現(xiàn)行記錄被鎖定，使用半一致性讀（semi-consistent read），具體是先不發(fā)生鎖等待，而是先返回最新已提交的數(shù)據(jù)，然后判斷是否滿足條件，如果不滿足條件，就不需要加鎖，否則發(fā)生鎖等待。因此 semi-consistent read 是 read committed 與 consistent read 兩者的結(jié)合。

由于開啟 innodb_locks_unsafe_for_binlog 參數(shù)時可能導(dǎo)致主從數(shù)據(jù)不一致，因此官方不建議使用，8.0.0 中已刪除該參數(shù)，如果需要使用，建議使用 RC。

那么，針對 insert select，RC 中會存在數(shù)據(jù)不一致的問題嗎？

實際上不會，原因是 RC 不支持 binlog_format=statement。具體操作中 RC 雖然可以將 binlog_format 修改為 statement，但是寫入時報錯。

參考官方文檔，RC 中 binlog_format 僅支持 ROW 格式。

Only row-based binary logging is supported with the READ COMMITTED isolation level. If you use READ COMMITTED with binlog_format=MIXED, the server automatically uses row-based logging.

thr_lock_type

thr_lock_type 是表鎖的一種類型，從名稱判斷是多線程鎖數(shù)據(jù)結(jié)構(gòu)。

盡管 MySQL 對外展示出現(xiàn)的只有讀鎖與寫鎖兩種類型，但實際上內(nèi)部枚舉類型中定義了 14 種多線程鎖類型，詳見下表。

其中：

• select lock in share mode 對應(yīng) TL_READ_WITH_SHARED_LOCKS；
• insert select 對應(yīng) TL_WRITE_CONCURRENT_INSERT，表示允許在表的末尾進(jìn)行插入操作，同時其他線程可以讀取表中的數(shù)據(jù)。

具體不同類型的區(qū)別還不太清楚，待后續(xù)分析。

LOCK_AUTO_INC

前面關(guān)注的都是 insert select 中給源表的加鎖規(guī)則，其實目標(biāo)表的加鎖規(guī)則也需要關(guān)注，比如自增鎖 LOCK_AUTO_INC。

LOCK_AUTO_INC 也是表鎖的一種類型，用于給自增計數(shù)器加鎖，從而保證自增列（AUTO_INCREMENT）值的唯一性與連續(xù)性。

自增鎖的鎖定范圍是 SQL 級別，但是鎖的釋放時間與自增鎖模式有關(guān)，通過參數(shù)innodb_autoinc_lock_mode控制。

取值包括：

• 0，傳統(tǒng)加鎖模式（traditional），用于兼容 5.1 版本引入該參數(shù)之前的策略，具體是所有 insert 類型的語句，都在 SQL 執(zhí)行結(jié)束時釋放表級鎖，因此對于 binlog_format=statement，可以保證主從數(shù)據(jù)的一致性；
• 1，連續(xù)加鎖模式（consecutive），5.7 中的默認(rèn)值，普通 insert 與批量 insert 的釋放時間不同。具體為：

• 普通 insert，由于可以提前確定插入行數(shù)，因此可以在分配自增值后立即釋放鎖，使用 mutex (a light-weight lock)；
• 批量 insert，由于無法提前確定插入行數(shù)，因此依然需要在 SQL 執(zhí)行結(jié)束后釋放鎖，使用 table-level AUTO-INC lock。

“bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements.

• 2，交叉加鎖模式（interleaved），進(jìn)一步放寬加鎖模式，所有 insert 類型的語句，都在分配后立即釋放鎖，優(yōu)點是允許批量插入，缺點是存在以下兩個問題：

• 對于 binlog_format = statement，可能導(dǎo)致主從數(shù)據(jù)不一致；
• 對于批量插入語句，有可能多條語句交叉分配自增值，因此可能不連續(xù)。

LOCK_AUTO_INC 加鎖函數(shù)是 ha_innobase::innobase_lock_autoinc，實現(xiàn)邏輯見下圖，其中通過加鎖模式與 SQL 類型選擇加鎖實現(xiàn)。

從 trace 中也可以看到，ha_innobase::write_row 函數(shù)中 row_ins 函數(shù)開始前后分別調(diào)用函數(shù) handler::update_auto_increment 與 ha_innobase::innobase_lock_autoinc。

代碼注釋顯示 ha_innobase::write_row 函數(shù)中在插入開始前獲取當(dāng)前自增值，并在插入結(jié)束后更新當(dāng)前自增值。

// storge/innobase/handler/ha_innodb.cc
 
  /* Step-3: Handling of Auto-Increment Columns. */
  // 內(nèi)部調(diào)用 ha_innobase::innobase_lock_autoinc 函數(shù)
  update_auto_increment()
  
  /* Step-4: Prepare INSERT graph that will be executed for actual INSERT
 (This is a one time operation) */
  /* Build the template used in converting quickly between
  the two database formats */
  build_template(true);
  
 /* Step-5: Execute insert graph that will result in actual insert. */
 // 內(nèi)部調(diào)用 row_ins 函數(shù)
 error = row_insert_for_mysql((byte*) record, m_prebuilt);

 /* Step-6: Handling of errors related to auto-increment. */
  auto_inc = innobase_next_autoinc(
    auto_inc,
    1, increment, offset,
    col_max_value);
 
 // 內(nèi)部調(diào)用 ha_innobase::innobase_lock_autoinc 函數(shù)
  err = innobase_set_max_autoinc(
    auto_inc);

結(jié)論

insert select 語句的執(zhí)行分兩步，先 select 后 insert，其中 select 階段需要給源表加 S 型 next-key lock。

原因是數(shù)據(jù)查詢階段中判斷加鎖類型時：

• 判斷 prebuilt->select_lock_type 是否為空，如果是，不加鎖，表示快照讀，否則繼續(xù)判斷；
• 判斷事務(wù)隔離級別與 innodb_locks_unsafe_for_binlog，如果 RC 或開啟 innodb_locks_unsafe_for_binlog，不加鎖，同樣使用快照讀，否則加 next-key lock。

其中 prebuilt->select_lock_type 對應(yīng) thr_lock_type，表示表鎖的類型，其中對于 insert select，對應(yīng) S 型鎖。

而在行鎖加鎖時 lock_mode 同樣使用 prebuilt->select_lock_type，個人判斷行鎖類型與表鎖類型有關(guān)。

關(guān)于加鎖類型，有兩個參數(shù)需要關(guān)注：

• innodb_locks_unsafe_for_binlog 參數(shù)控制查詢源表時是否使用間隙鎖，RR 開啟該參數(shù)時相當(dāng)于 RC。對于 update 語句，使用半一致性讀（semi-consistent read），semi-consistent read 是 read committed 與 consistent read 兩者的結(jié)合；
• LOCK_AUTO_INC 參數(shù)控制目標(biāo)表中自增鎖的加鎖模式，實際上是自增鎖的釋放時間，默認(rèn) 1，對于批量插入的場景，由于無法提前確定插入行數(shù)，因此需要在 SQL 執(zhí)行結(jié)束后釋放鎖，否則可以在分配自增值后立即釋放。

insert select 給源表加鎖的原因是保證日志與數(shù)據(jù)的一致性，否則 binlog_format = statement 時可能導(dǎo)致主從數(shù)據(jù)不一致。

針對 insert select 給源表加鎖的問題，有以下幾個優(yōu)化建議：

• RR 中開啟 innodb_locks_unsafe_for_binlog，但是 binlog_format = statement 時可能導(dǎo)致主從數(shù)據(jù)不一致，因此不建議使用；
• 使用 RC，RC 中 binlog_format 僅支持 ROW 格式，因此不會導(dǎo)致主從不一致；
• 使用 select ... into outfile 和 load data file 的組合來代替 insert select 語句。

即使使用 insert select，也需要注意以下兩點：

• 是否使用主鍵排序，如果使用非主鍵排序，可能導(dǎo)致全表掃描與直接鎖表；
• 如果主從數(shù)據(jù)庫版本不一致，依然可能導(dǎo)致主從不一致，原因是早期版本中不加鎖，5.6+ 中加鎖。

待辦

• thr_lock_type