我正在尝试添加一个触发器来更新journal_summary表中的自动凭证号。同时，为了防止重复，我尝试添加一个事务锁定。以下是我到目前为止的触发器。在没有事务锁定的情况下，它工作正常。当我添加锁时，它给出错误Transaction characteristics can not be changed while a transaction is in progress-在添加锁后，不能提交任何插入。
我还检查了是否有任何当前事务与此查询，但其结果为零。SHOW OPEN TABLES WHERE In_use > 0;
我做错了什么

DELIMITER $$
CREATE TRIGGER update_journal_no
BEFORE INSERT ON journal_summary
FOR EACH ROW
   BEGIN
        DECLARE v_count INT;
        DECLARE v_journal_no VARCHAR(15);
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- set the isolation level to serializable to use row-level locks
        SELECT COUNT(*) + 1 INTO v_count FROM journal_summary WHERE JournalDate = NEW.JournalDate AND concern_id = NEW.concern_id FOR UPDATE; -- use SELECT ... FOR UPDATE to lock the selected rows
         IF v_count > 99999 THEN -- exceeding 5 digits
         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot insert record: Too many journals for this date and concern.';
         END IF;
         SET v_journal_no = CONCAT(DATE_FORMAT(NEW.JournalDate, '%Y-%m-%d'), '-', LPAD(v_count, 5, '0'));
         UPDATE journal_summary SET journal_no = v_journal_no WHERE id = NEW.id; -- update the journal_no column with the calculated value
    END$$
DELIMITER ;