MySQL触发器引发表锁，寻求并行插入冲突解决方案

问题根源 #

原触发器在触发时会对ch54b_fields_values表持有锁，而Joomla扩展本身的插入操作也会访问该表，并行场景下锁冲突导致其他插入被阻塞。

改写方案 #

1. 优化插入语句（最小改动）

如果ch54b_fields_values表的field_id和item_id是联合唯一键，改用INSERT ... ON DUPLICATE KEY UPDATE语句，避免重复插入导致的锁竞争：

IF (new.catid = '131') THEN
    INSERT INTO ch54b_fields_values (field_id, item_id, value) 
    VALUES ('2', new.id, new.created_by)
    ON DUPLICATE KEY UPDATE value = new.created_by;
END IF;

该语句会优先尝试插入，若存在重复记录则执行更新，既保证数据正确性，又缩短锁持有时间。

2. 调整事务隔离级别

MySQL默认隔离级别为REPEATABLE READ，会扩大锁范围。可临时将会话隔离级别改为READ COMMITTED，减少锁粒度：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

注意：需测试该调整是否影响Joomla其他业务逻辑，READ COMMITTED可能导致不可重复读，但不会出现幻读。

3. 异步插入（彻底解决锁冲突）

若允许数据有轻微延迟，采用临时表+事件调度器实现异步插入，避免触发器在主事务中持有目标表锁：

• 创建临时表存储待同步数据：

  CREATE TABLE IF NOT EXISTS temp_fields_values (
      id INT AUTO_INCREMENT PRIMARY KEY,
      field_id INT,
      item_id INT,
      value VARCHAR(255),
      processed TINYINT DEFAULT 0
  );
  

• 修改触发器插入临时表：

  IF (new.catid = '131') THEN
      INSERT INTO temp_fields_values (field_id, item_id, value) 
      VALUES ('2', new.id, new.created_by);
  END IF;
  

• 创建事件调度器定期同步数据：

  DELIMITER //
  CREATE EVENT sync_fields_values
  ON SCHEDULE EVERY 10 SECOND
  DO
  BEGIN
      START TRANSACTION;
      INSERT INTO ch54b_fields_values (field_id, item_id, value)
      SELECT field_id, item_id, value FROM temp_fields_values WHERE processed = 0;
      UPDATE temp_fields_values SET processed = 1 WHERE processed = 0;
      COMMIT;
  END //
  DELIMITER ;
  

该方案将插入操作从主事务剥离，彻底消除并行插入的锁冲突。

方案选择建议 #

• 优先尝试方案1，改动最小且见效快；
• 若方案1无效，再考虑调整隔离级别；
• 对并行性要求高且可接受数据延迟时，选择方案3。

内容的提问来源于stack exchange，提问作者peter ashford