mysql自增主键锁导致的导入数据超时问题

恬淡虚无真气从之

已于 2024-06-17 22:28:17 修改

阅读量1.1k

点赞数

分类专栏： mysql 文章标签：数据库 dba

于 2022-04-01 17:12:26 首次发布

本文链接：https://blog.csdn.net/u012565458/article/details/123902040

版权

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

脚本程序在导入数据时，并发使用使用“ load data local infile ”导入数据时，报错显示锁超时 Lock wait timeout exceeded; try restarting transaction；

报错信息如下：

root@information_schema 04:21:55>select * from INNODB_TRX limit 10\G;
*************************** 1. row ***************************
trx_id: 188346027249
trx_state: LOCK WAIT
trx_started: 2022-04-01 16:21:57
trx_requested_lock_id: 188346027249:1895
trx_wait_started: 2022-04-01 16:21:57
trx_weight: 3
trx_mysql_thread_id: 64111
trx_query: load data local infile  "/data1/tmp_dir/test_abc_id/splittmp_local_ind_abc/prefix_local_ind_abc_0029" into table local_ind_abc character set utf8
trx_operation_state: setting auto-inc lock
trx_tables_in_use: 1
trx_tables_locked: 2
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0

导入的sql为：

"load data local infile "/data1/tmp_dir/test_abc_id/splittmp_local_ind_abc/prefix_local_ind_abc_0029" into table local_ind_abc character set utf8".

是python 开启了多线程导入数据，并行导入的线程是2，mysql的隔离级别是RR，被导入的表是个空表，表结构如下：

CREATE TABLE `local_ind_abc` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name_id` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `name_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_id` (`name_id`,`name_type`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

运行如下sql看下是什么导致的锁：

select d.trx_id as 当前事务ID,
       d.trx_state as 状态,
       d.trx_mysql_thread_id as 当前事务的会话ID,
       e.trx_mysql_thread_id as 堵塞当前并持有锁的事务会话ID,
       concat('kill ',e.trx_mysql_thread_id,';') as kill_持有锁的事务会话ID,
       d.blocking_trx_id as 持有锁事务ID,
       d.trx_started as 当前事务开始时间,
       d.trx_wait_started as 事务开始等待时间,
       (unix_timestamp(now()) -unix_timestamp(d.trx_started)) as 事务已执行时长,
       (unix_timestamp(now()) -unix_timestamp(d.trx_wait_started)) as 事务处于等待时长,
       d.trx_query as 当前事务执行SQL,
       s.SQL_TEXT as 持有锁事务最后一条SQL,
       d.trx_operation_state as 当前状态,
       d.trx_tables_in_use as 表使用个数,
       d.trx_tables_locked as 请求表锁个数,
       d.trx_lock_structs as 事务锁结构,
       d.trx_lock_memory_bytes,
       d.trx_rows_locked 等待行数个数,
       d.lock_mode as 持有锁模式,
       d.lock_type as 持有锁类型,
       d.lock_table as 被锁表名
  from 
  (
  SELECT *
  FROM information_schema.INNODB_TRX a
  left join INFORMATION_SCHEMA.INNODB_LOCK_WAITS b on a.trx_id= b.requesting_trx_id
  left join INFORMATION_SCHEMA.INNODB_LOCKS c on b.blocking_trx_id= c.lock_trx_id
  ) d
  left join information_schema.INNODB_TRX e on d.blocking_trx_id= e.trx_id
  left join performance_schema.threads t on e.trx_mysql_thread_id=t.PROCESSLIST_ID
  left join performance_schema.events_statements_current s ON  s.THREAD_ID=t.THREAD_ID order by d.trx_started \G;

得到结果如下：

当前事务ID: 188346291088
状态: LOCK WAIT
当前事务的会话ID: 64923
堵塞当前并持有锁的事务会话ID: 64885
          kill_持有锁的事务会话ID: kill 64885;
                        持有锁事务ID: 188346279351
                 当前事务开始时间: 2022-04-01 17:01:43
                 事务开始等待时间: 2022-04-01 17:01:43
                    事务已执行时长: 41
                 事务处于等待时长: 41
                    当前事务执行SQL: load data local infile  "prefix_local_ind_abc_0031" into table local_ind_abc character set utf8
           持有锁事务最后一条SQL: load data local infile  "prefix_local_ind_abc_0008" into table local_ind_abc character set utf8
                             当前状态: setting auto-inc lock
                          表使用个数: 1
                       请求表锁个数: 2
                          事务锁结构: 2
                    trx_lock_memory_bytes: 1136
                       等待行数个数: 0
                          持有锁模式: AUTO_INC
                          持有锁类型: TABLE
                             被锁表名: `test_user_abc`.`local_ind_abc`

果然是并行的2个导入数据的sql导致的锁等待。锁等待类型竟然是 setting auto-inc lock；

理论上来看，应该要导入的数据都有主键，不应该报锁 auto-inc lock 。

查找mysql的手册：

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

发现AUTO-INC lock 竟然是表级锁。

可是为啥为啥出现表级锁呢，查资料发现参数 innodb_autoinc_lock_mode 来控制自增锁的。

innodb_autoinc_lock_mode有3种模式：

设置为0时：sql 语句执行结束才会释放自增表级锁(auto-inc lock)。这种为传统模式，所有的insert 语句（simple insert sql 也会）都会持有 table-level AUTO-INC lock也就是加锁，一直到语句执行完成，分配的自增列值也是连续的，可预知的。同时，在statement-based的复制模式下，可以保证主从自增列一致。
设置为1时(默认设置为1)：官方手册里介绍“ This is the default lock mode. In this mode, “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. ”

意思是：设置为1是MySQL默认的设置模式，当使用 INSERT ... SELECT 和 REPLACE ... SELECT和 LOAD DATA ，这3种sql时，会加自增表级锁(auto-inc lock)，且等sql语句执行完才会释放自增表级锁(auto-inc lock)。

在这种模式下，对 simple insert做了一些优化，使用信号量来互斥一次性获取需要的自增值，于是可以一次性生成几个连续的值用在对应的插入 SQL 语句上，这样就可以提前释放掉 AUTO-INC 锁，可以减少锁等待，提高并发插入效率。

因此，我们在使用正常的insert的时候，是不会出现表级锁的，是因为。

设置为2时: 这种模式下，所有的insert-like语句都不会持有table-level AUTO-INC lock。由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

结论：我们的mysql是默认设置innodb_autoinc_lock_mode=1的，通过以上分析，所以在使用load data 并发导入数据时，加的就是表级锁(auto-inc lock)，因此，就会报锁超时了。

面对这个问题，解决方案有2种：

第一种：设置innodb_autoinc_lock_mode=2, MySQL 8.0的时候已经 innodb_autoinc_lock_mode 默认值为2，这种方式是最优的。

第二种：被导入的表临时去掉auto increment；这个会有损业务，可以作为备选项。

最后有个问题：

假如，innodb_autoinc_lock_mode=1 模式下在被导入的表设置一个较大的increment 自增值，大于我们要导入的数据的最大值，这样可以避免在导数据的时候出现锁等待的问题，可以吗？

答案是不可以的，因为innodb_autoinc_lock_mode=1 在导入数据时，加的是表级别的锁，是会锁表的。