好了，简单说一下背景故事。我们有一个主从设置，每天多次，我们都会在从数据库上看到类似这样的情况，尝试复制来自主数据库的内容
Id User Host db Command Time State Info
------ ----------- ----------------------------------- ------ ------- ------ -------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 system user foodb Connect 59079 Locked UPDATE foo SET bar = 1 WHERE baz = 2;
2 system user (NULL) Connect 62730 Waiting for master to send event (NULL)
940 foouser ip-00-000-000-00.ec2.internal:55555 foodb Sleep 4 (NULL)
941 foouser ip-00-000-000-00.ec2.internal:55555 foodb Sleep 3 (NULL)
查询（非常简单，每次手动运行只需不到 1 秒）似乎挂在“连接”命令上，并且从未到达查询命令。
有人知道它为什么会挂在这里吗？
另一件值得注意的事情是，更新查询每天运行超过 3000 次，并且大多数时间，查询运行良好并且不会锁定。
我知道人们会问索引，但不幸的是，这是机密信息，我只能说我很确定它被正确索引了。我已经多次检查了解释计划和索引。
innodb 设置
"Variable_name" "Value"
"innodb_adaptive_hash_index" "ON"
"innodb_additional_mem_pool_size" "1048576"
"innodb_autoextend_increment" "8"
"innodb_autoinc_lock_mode" "1"
"innodb_buffer_pool_size" "8388608"
"innodb_checksums" "ON"
"innodb_commit_concurrency" "0"
"innodb_concurrency_tickets" "500"
"innodb_data_file_path" "ibdata1:10M:autoextend"
"innodb_data_home_dir" ""
"innodb_doublewrite" "ON"
"innodb_fast_shutdown" "1"
"innodb_file_io_threads" "4"
"innodb_file_per_table" "OFF"
"innodb_flush_log_at_trx_commit" "1"
"innodb_flush_method" ""
"innodb_force_recovery" "0"
"innodb_lock_wait_timeout" "50"
"innodb_locks_unsafe_for_binlog" "OFF"
"innodb_log_buffer_size" "1048576"
"innodb_log_file_size" "5242880"
"innodb_log_files_in_group" "2"
"innodb_log_group_home_dir" "./"
"innodb_max_dirty_pages_pct" "90"
"innodb_max_purge_lag" "0"
"innodb_mirrored_log_groups" "1"
"innodb_open_files" "300"
"innodb_rollback_on_timeout" "OFF"
"innodb_stats_on_metadata" "ON"
"innodb_support_xa" "ON"
"innodb_sync_spin_loops" "20"
"innodb_table_locks" "ON"
"innodb_thread_concurrency" "8"
"innodb_thread_sleep_delay" "10000"
"innodb_use_legacy_cardinality_algorithm" "ON"
答案1
首先让我们看看SHOW PROCESSLIST;
Id User Host db Command Time State Info
------ ----------- ----------------------------------- ------ ------- ------ -------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 system user foodb Connect 59079 Locked UPDATE foo SET bar = 1 WHERE baz = 2;
2 system user (NULL) Connect 62730 Waiting for master to send event (NULL)
940 foouser ip-00-000-000-00.ec2.internal:55555 foodb Sleep 4 (NULL)
941 foouser ip-00-000-000-00.ec2.internal:55555 foodb Sleep 3 (NULL)
复制的工作方式是，您将看到属于 的两个线程system user：I/O 线程和 SQL 线程。进程 ID #1 是 SQL 线程，因为它正在尝试运行 SQL 语句，并且db是foodb。
正如您在问题评论中所述，目标表正在使用 MyISAM。
在什么情况下 MyISAM 表会被锁定？对 MyISAM 表的任何 INSERT、UPDATE 或 DELETE 都会导致全表锁定。
请查找任何对foo表进行适度写入的 crontab 作业。另外，检查操作系统以查看是否正在进行大量磁盘交换。