##
InnoDB Lock与MySQL Shell数据导入案例
1、案例概述
今天遇到一个MySQL Shell数据导入案例，分享一下，数据来源于RDS，使用mysqlshell的dump schema获取数据，在数据库的导出过程中没有遇到任何错误或者警告，但是在导入本地数据库时出现问题，多次实验之后，数据导入过程百分比不同都会报错。错误信息如下：
MySQL localhost JS > util.loadDump('/mysql/backup/aaa', {threads: 16, deferTableIndexes: "all",ignoreVersion: true,characterSet:"utf8mb4"})
Loading DDL and Data from '/mysql/backup/aaa' using 16 threads.
Opening dump...
Target is MySQL 5.7.42-log. Dump was produced from MySQL 5.7.42-log
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
ERROR: [Worker001]: ebdb@watchdog@9.tsv.zst: MySQL Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction: LOAD DATA LOCAL INFILE '/mysql/backup/ aaa/ebdb@watchdog@9.tsv.zst' REPLACE INTO TABLE `ebdb`.`watchdog` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMIN ATED BY '\n' (`wid`, `uid`, `type`, `message`, @`variables`, `severity`, `link`, `location`, `referer`, `hostname`, `timestamp`) SET `variables` = FROM_BASE64(@`vari ables`)
ERROR: Aborting load...
1 thds loading \ 22% (1.36 GB / 5.93 GB), 30.05 MB/s, 301 / 301 tables done
333 chunks (2.31M rows, 1.36 GB) for 301 tables in 3 schemas were loaded in 35 sec (avg throughput 41.26 MB/s)
1 errors and 0 warnings messages were reported during the load.
Util.loadDump: Error loading dump (MYSQLSH 53005)
2、MYSQLSH 53005分析
根据导入过程最终的报错信息进行分析
-
Error number: `53005`; Symbol: `SHERR_LOAD_WORKER_THREAD_FATAL_ERROR` Message: Error loading dump
这是官网给的信息，似乎并不能帮我们解决任何问题。
3、Lock wait timeout exceeded分析
查看数据库的参数
MySQL localhost JS > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost SQL > show variables like '%wait%';
+---------------------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------------------+----------+
| innodb_lock_wait_timeout | 5 |
| innodb_spin_wait_delay | 6 |
| lock_wait_timeout | 31536000 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| wait_timeout | 1800 |
+---------------------------------------------------+----------+
6 rows in set (0.0007 sec)
怀疑是由于innodb_lock_wait_timeout参数太小导致数据导入失败。
尝试修改参数之后再次导入
4、处理过程记录
MySQL localhost SQL > set global innodb_lock_wait_timeout=120;
Query OK, 0 rows affected (0.0004 sec)
MySQL localhost SQL > \q
Bye!
[root@mydb01 ~]# mysqlsh
MySQL Shell 8.2.1
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@localhost'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 57
Server version: 5.7.42-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL localhost JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
MySQL localhost SQL > drop database ebdb;
\js
Query OK, 301 rows affected (1.4942 sec)
MySQL localhost SQL > \js
Switching to JavaScript mode...
MySQL localhost JS > util.loadDump('/mysql/backup/aaa', {threads: 8, deferTableIndexes: "all",ignoreVersion: true,characterSet:"utf8mb4"})
Loading DDL and Data from '/mysql/backup/aaa' using 8 threads.
Opening dump...
Target is MySQL 5.7.42-log. Dump was produced from MySQL 5.7.42-log
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
2 thds loading - 2 thds indexing / 100% (5.93 GB / 5.93 GB), 25.74 MB/s, 301 / 301 tables done
Executing common postamble SQL
Recreating indexes - done
432 chunks (3.44M rows, 5.93 GB) for 301 tables in 3 schemas were loaded in 1 min 43 sec (avg throughput 61.76 MB/s)
0 warnings were reported during the load.
数据成功导入并没有任何错误和警告。
5、innodb_lock_wait_timeout参数分析
当事务更新表中的一行或使用 锁定该行时 SELECT FOR UPDATE，InnoDB 会在该行上建立一个锁定列表或队列。类似地， InnoDB维护表上的表级锁的锁列表。如果第二个事务想要更新一行或以不兼容模式锁定已被前一个事务锁定的表，InnoDB则将该行的锁定请求添加到相应的队列中。对于要由事务获取的锁，必须删除先前进入该行或表的锁队列的所有不兼容的锁请求（这在持有或请求这些锁的事务提交或回滚时发生）。
一个事务可以对不同的行或表有任意数量的锁定请求。在任何给定时间，一个事务可能会请求另一个事务持有的锁，在这种情况下，它会被另一个事务阻塞。请求事务必须等待持有阻塞锁的事务提交或回滚。如果事务没有等待锁，则它处于某种RUNNING状态。如果事务正在等待锁，则它处于某种LOCK WAIT 状态。 （该INFORMATION_SCHEMA 表指示事务状态值。）
该INNODB_LOCKS表为每个事务保存一行或多行LOCK WAIT，指示阻止其进度的任何锁定请求。该表还包含一行，描述给定行或表的待处理锁队列中的每个锁。该 INNODB_LOCK_WAITS表显示了某个事务已持有的哪些锁是其他事务请求的阻塞锁。
6、官网信息分析
-
innodb_lock_wait_timeout
命令行格式 –innodb-lock-wait-timeout=# 系统变量 innodb_lock_wait_timeout 范围 全局、会话 动态的 是的 类型 整数 默认值 50 最小值 1 最大值 1073741824 单元 秒 InnoDB 事务在放弃之前等待行锁的 时间长度（以秒为单位） 。默认值为 50 秒。尝试访问被另一个事务锁定的行的事务 InnoDB最多等待这么多秒才能对该行进行写访问，然后发出以下错误：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction当发生锁等待超时时，将 回滚当前语句（而不是整个事务）。要回滚整个事务，请使用该 --innodb-rollback-on-timeout 选项启动服务器。另请参见第 14.22.4 节“InnoDB 错误处理”。
对于高度交互的应用程序或OLTP系统， 您可以减小此值，以快速显示用户反馈或将更新放入队列中以便稍后处理。您可以为长时间运行的后端操作增加此值，例如数据仓库中等待其他大型插入或更新操作完成的转换步骤。
innodb_lock_wait_timeout 仅适用于InnoDB行锁。 MySQL 表锁不会发生在内部InnoDB，并且此超时不适用于等待表锁。
启用（默认）时 ，锁定等待超时值不适用于 死锁，因为 会立即检测到死锁并回滚其中一个死锁事务。禁用时 ，发生死锁时依赖 事务回滚。请参见 第 14.7.5.2 节“死锁检测”。 innodb_deadlock_detectInnoDBinnodb_deadlock_detectInnoDBinnodb_lock_wait_timeout
innodb_lock_wait_timeout可以在运行时使用SET GLOBALor SET SESSION语句进行设置。更改 GLOBAL设置需要足够的权限来设置全局系统变量（请参见 第 5.1.8.1 节“系统变量权限”），并会影响随后连接的所有客户端的操作。任何客户端都可以更改 的SESSION设置 innodb_lock_wait_timeout，这仅影响该客户端。
官网居然给了报错信息案例
