Debian11/12下MariaDB跨表触发器锁表执行报#1442错误求助

问题背景 #

在Debian 11（MariaDB 10.5.19-0+deb11u2）和Debian 12（MariaDB 10.11.4-1~deb12u1）系统中，使用LOCK TABLES操作带有触发器的MyISAM表时，会触发#1442 - Can't update table '_trigger_test_log' in stored function/trigger because it is already used by statement which invoked this stored function/trigger错误；无LOCK TABLES时操作正常，且该问题在Ubuntu各版本、Debian 10中未出现。

复现步骤 #

1. 创建两个MyISAM表：

CREATE TABLE `_trigger_test` (
  `rowid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`rowid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `_trigger_test_log` (
  `rowid` int(11) NOT NULL AUTO_INCREMENT,
  `original_rowid` int(11) NOT NULL,
  `action` varchar(255) NOT NULL,
  `ts` datetime NOT NULL,
  `new_value` varchar(255) NOT NULL,
  PRIMARY KEY (`rowid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

2. 创建触发器：

DROP TRIGGER IF EXISTS `after_delete`;
DELIMITER //
CREATE TRIGGER `after_delete` AFTER DELETE ON `_trigger_test`
 FOR EACH ROW INSERT INTO _trigger_test_log (original_rowid, action, ts, new_value) VALUES (OLD.rowid, 'AFTER DELETE', NOW(), OLD.name)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `after_insert`;
DELIMITER //
CREATE TRIGGER `after_insert` AFTER INSERT ON `_trigger_test`
 FOR EACH ROW INSERT INTO _trigger_test_log (original_rowid, action, ts, new_value) VALUES (NEW.rowid, 'AFTER INSERT', NOW(), NEW.name)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `after_update`;
DELIMITER //
CREATE TRIGGER `after_update` AFTER UPDATE ON `_trigger_test`
 FOR EACH ROW INSERT INTO _trigger_test_log (original_rowid, action, ts, new_value) VALUES (NEW.rowid, 'AFTER UPDATE', NOW(), NEW.name)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `before_delete`;
DELIMITER //
CREATE TRIGGER `before_delete` BEFORE DELETE ON `_trigger_test`
 FOR EACH ROW INSERT INTO _trigger_test_log (original_rowid, action, ts, new_value) VALUES (OLD.rowid, 'BEFORE DELETE', NOW(), OLD.name)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `before_insert`;
DELIMITER //
CREATE TRIGGER `before_insert` BEFORE INSERT ON `_trigger_test`
 FOR EACH ROW INSERT INTO _trigger_test_log (original_rowid, action, ts, new_value) VALUES (NEW.rowid, 'BEFORE INSERT', NOW(), NEW.name)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `before_update`;
DELIMITER //
CREATE TRIGGER `before_update` BEFORE UPDATE ON `_trigger_test`
 FOR EACH ROW INSERT INTO _trigger_test_log (original_rowid, action, ts, new_value) VALUES (OLD.rowid, 'BEFORE UPDATE', NOW(), OLD.name)
//
DELIMITER ;

3. 执行带锁表的操作触发错误：

LOCK TABLES _trigger_test WRITE;
INSERT INTO _trigger_test (name) VALUES ('James');
UPDATE _trigger_test SET name = 'John' WHERE name = 'James';
DELETE FROM _trigger_test WHERE name = 'John';
UNLOCK TABLES;

解决方案 #

方案1：显式同时锁定主表和日志表 #

MariaDB新版本中，LOCK TABLES会严格限制会话仅能访问显式锁定的表，触发器隐式访问的日志表未被锁定时会触发锁冲突。修改锁表语句为同时锁定两个表：

LOCK TABLES _trigger_test WRITE, _trigger_test_log WRITE;
INSERT INTO _trigger_test (name) VALUES ('James');
UPDATE _trigger_test SET name = 'John' WHERE name = 'James';
DELETE FROM _trigger_test WHERE name = 'John';
UNLOCK TABLES;

若仍出现偶发失败，可调整lock_wait_timeout参数延长锁等待时间。

方案2：迁移至InnoDB存储引擎 #

MyISAM是表级锁引擎，本身不支持事务；而InnoDB支持行锁和事务，无需依赖LOCK TABLES保证操作原子性，同时触发器的锁机制更稳定。修改表引擎：

ALTER TABLE `_trigger_test` ENGINE=InnoDB;
ALTER TABLE `_trigger_test_log` ENGINE=InnoDB;

之后可去掉LOCK TABLES语句，改用事务包裹操作保证原子性：

START TRANSACTION;
INSERT INTO _trigger_test (name) VALUES ('James');
UPDATE _trigger_test SET name = 'John' WHERE name = 'James';
DELETE FROM _trigger_test WHERE name = 'John';
COMMIT;

这是长期最优解决方案，可避免MyISAM表锁带来的各类冲突问题。

方案3：调整auto_increment锁模式 #

若错误与自增锁机制有关，可修改myisam_autoinc_lock_mode参数，将锁模式调整为更宽松的模式（如设置为2），但此方案仅针对特定场景，需结合实际测试验证。

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