Mac本地MariaDB大表操作失败：锁等待超时问题求助

问题场景 #

本地MacBook的MariaDB数据库中，向_stats_leads_servicos表插入数据至1200万行后无法继续写入，执行插入、删除、截断甚至删除表操作时，进程无响应最终报错：

Lock wait timeout exceeded; try restarting transaction

需排查问题原因并解决，同时避免服务器上同类型大表（需存储远超1200万行数据）出现同样故障。

表结构定义 #

create table _stats_leads_servicos
(
    id_lead           int            null,
    id_servico        int            null,
    id_tipo           char(5)        not null comment '''live'' - real time
''final'' - final',
    id_subtipo        char(10)       null comment 'id_tipo = live
    arquivo - já existe novo
    live - atualizado

id_tipo = final
    arquivo - encerrado
    live - ainda aberto',
    sf_data           datetime       not null,
    sf_id_utilizador  int            null comment 'null - geral ; id_utilizador ut a que diz respeito',
    sf_id_cargo       char(100)      null,
    sf_tipo_processo  char(100)      not null,
    sf_id_tipo        char(100)      not null,
    sf_id_subtipo     char(100)      not null,
    sf_id_ramo        char(100)      not null,
    sf_id_seguradora  int            not null,
    sf_id_fraude      char(100)      null,
    sf_id_estado      char(100)      not null,
    data              date           not null,
    hora              time           null,
    n_diligencias_1   int            null,
    n_diligencias_0   int            null,
    sla_diligencias_1 int            null comment 'segundos',
    n_informacoes_1   int            null,
    n_informacoes_fp  int            null,
    n_informacoes_0   int            null,
    sla               decimal(11, 2) null,
    sla_fp            decimal(11, 2) null,
    estados_fp        tinyint(1)     null
);

create index lead_servico
    on _stats_leads_servicos (id_lead, id_servico);

create index seguradora
    on _stats_leads_servicos (sf_id_seguradora);

create index `sf_tipo-sf_subtipo`
    on _stats_leads_servicos (sf_id_tipo, sf_id_subtipo);

create index tipo
    on _stats_leads_servicos (id_tipo);

create index tipo_data
    on _stats_leads_servicos (id_tipo, data);

create index utilizador_cargo
    on _stats_leads_servicos (sf_id_utilizador, sf_id_cargo);

原因分析 #

1. 长事务阻塞锁资源：插入过程中可能出现进程中断、事务未正常提交/回滚的情况，导致未完成的事务长时间持有表级锁，后续操作无法获取锁资源引发超时。
2. 索引维护开销过高：表上存在6个索引，写入操作（插入、删除）需要同步更新所有索引，MacBook磁盘IO性能有限，索引更新耗时过长且占用锁资源，加剧锁等待。
3. 无主键/聚集索引：该表未定义主键，InnoDB会自动生成隐藏聚集索引，大表场景下隐藏索引的维护效率极低，易引发锁冲突。
4. 磁盘空间不足：本地磁盘剩余空间不足，导致写入操作卡住，进而触发锁等待超时。
5. 数据库配置限制：默认的innodb_lock_wait_timeout（50秒）可能不足以支撑大表操作的IO耗时；innodb_buffer_pool_size设置过小，数据无法高效缓存，IO瓶颈进一步加重锁等待。

解决方案 #

一、紧急解决本地卡死表问题 #

1. 终止阻塞事务
   • 执行以下命令查询锁等待和未提交事务：

     SHOW ENGINE INNODB STATUS;
     SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
     

   • 从结果中找到持有锁的事务ID（trx_id），执行KILL [trx_id];终止该事务，之后再尝试操作表。
2. 重启MariaDB服务
   若事务终止无效，直接重启本地MariaDB服务，未提交的事务会自动回滚，释放锁资源。
3. 清理磁盘空间
   检查MacBook磁盘剩余空间，确保有足够空间供数据库执行写入、删除等操作。

二、服务器大表预防方案 #

1. 添加主键/聚集索引
   为表添加自增主键，InnoDB的聚集索引可大幅提升读写效率，减少锁冲突：

   ALTER TABLE _stats_leads_servicos ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
   

2. 精简索引数量
   评估现有索引的实际使用场景，删除未被查询用到的索引（例如utilizador_cargo若极少被使用可删除），降低写入时的索引维护开销。
3. 分批写入数据
   插入大量数据时，采用分批写入策略（每次插入1万-10万行），每批次提交事务，避免长时间持有锁资源。
4. 优化数据库配置
   • 调整innodb_buffer_pool_size：设置为服务器内存的50%-70%（例如32G内存的服务器设置为16G-22G），提升数据缓存效率，减少磁盘IO。
   • 增大innodb_lock_wait_timeout：根据业务场景，可将默认50秒调整至120秒，避免正常操作因IO延迟超时。
   • 开启innodb_file_per_table：确保每个表拥有独立表空间，便于后续单独维护。
5. 使用分区表
   针对超大规模表，按data字段（日期）进行分区（例如按月分区），操作单个分区时不会锁定整个表，提升操作效率：

   ALTER TABLE _stats_leads_servicos 
   PARTITION BY RANGE (TO_DAYS(data)) (
       PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
       PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
       -- 按需添加更多分区
       PARTITION p_max VALUES LESS THAN MAXVALUE
   );
   

6. 监控长事务
   在服务器上配置监控规则，及时发现并终止未提交的长事务，避免锁资源被长时间占用。

内容的提问来源于stack exchange，提问作者Daniel Novo