生产环境下MySQL事务中BEFORE INSERT触发器失效问题排查

First, let's get straight to it: your 100 rows/second insertion speed isn't the root cause of the trigger failing. The issue lies in trigger logic gaps, environment differences between test and production, or transaction behavior. Let's break this down step by step.

Why the Trigger Works Manually but Fails in Batch/Production? #

1. Query Condition Mismatch (Most Likely for "No gmno Generated") #

Your trigger depends on finding a matching row in frtnumbercontrol using codetype = 'GMTY' and code = new.gmtype and new.gmdate between frdate and todate. If this query returns 0 rows, wno will be NULL, and gmno won't populate at all. Common production-specific issues here:

• Timezone discrepancies: Production servers might use a different timezone than your test environment, shifting gmdate outside the frdate/todate range.
• Data gaps: The frtnumbercontrol table in production might lack a matching record for the gmtype and date range used in your batch inserts (unlike your test dataset).

2. Non-Atomic Query + Update #

Your trigger first reads slno, then updates it separately. In high-concurrency production environments—even if you're running single transactions in batches—this creates a race condition: two concurrent triggers could read the same slno before either updates it. While this often causes duplicate gmno values, it can also lead to unexpected failures if locks are involved.

3. Transaction Isolation Level Behavior #

MySQL's default isolation level is REPEATABLE READ. If you're inserting multiple frtgoodsmovement rows in a single transaction, the trigger's SELECT on frtnumbercontrol will use a snapshot read—meaning it won't see the slno increment from previous trigger runs in the same transaction. This can lead to duplicate gmno values or failed lookups.

4. Missing Indexes #

If frtnumbercontrol has large datasets in production, the trigger's SELECT might be slow or fail to hit an index, leading to timeouts or unoptimized query behavior that doesn't surface in small test datasets.

Fixes to Resolve the Trigger Issue #

1. Validate Query Conditions First #

• Check production data: Run the trigger's SELECT manually with a sample gmtype and gmdate from your batch inserts to confirm it returns a valid row.
• Align timezones: Ensure gmdate is stored in the same timezone as frdate/todate in frtnumbercontrol.

2. Make the Trigger Logic Atomic #

Rewrite the trigger to combine the read and update into a single atomic operation to eliminate race conditions. This also guarantees you're reading the latest slno value:

DELIMITER $$
CREATE TRIGGER `sample`.`new_gm_sequence` BEFORE INSERT ON `sample`.`frtgoodsmovement`
FOR EACH ROW
BEGIN
    DECLARE wno VARCHAR(20);
    -- Update and read in one step to avoid race conditions
    UPDATE frtnumbercontrol
    SET slno = slno + 1,
        wno = CONCAT(numberprefix, CAST(slno + 1 AS CHAR))
    WHERE codetype = 'GMTY'
      AND code = NEW.gmtype
      AND NEW.gmdate BETWEEN frdate AND todate;
    
    -- Handle cases where no matching row was found
    IF wno IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No matching record found in frtnumbercontrol for this gmtype and date';
        -- Or set a default value if preferred: SET NEW.gmno = 'DEFAULT_' || NEW.gmtype;
    ELSE
        SET NEW.gmno = wno;
    END IF;
END $$
DELIMITER ;

3. Add a Robust Composite Index #

Create an index on frtnumbercontrol to speed up the trigger's query and avoid table scans in production:

CREATE INDEX idx_frtnumbercontrol_gm ON frtnumbercontrol(codetype, code, frdate, todate);

4. Adjust Transaction Isolation (If Needed) #

If you're inserting multiple frtgoodsmovement rows in one transaction and need to see incremental slno values, either switch the transaction isolation level to READ COMMITTED for that batch process, or modify the original trigger to use a current read:

-- Original trigger adjustment to force latest data read
SELECT CONCAT(numberprefix, CAST(slno+1 AS CHAR)) INTO wno
FROM frtnumbercontrol
WHERE codetype = 'GMTY'
  AND code = NEW.gmtype
  AND NEW.gmdate BETWEEN frdate AND todate
FOR UPDATE; -- Forces row lock and reads the latest data

Do You Need to Optimize Insert Speed? #

Your current 100 rows/second speed is reasonable for most workloads, but optimizing inserts can help with scalability. However, it won't fix the trigger failure directly. The fixes above address the root causes of the trigger not generating gmno in production batches. That said, if you want to optimize bulk inserts for table2:

• Replace PHP's逐行插入 with a single bulk INSERT INTO table2 (...) VALUES (...), (...), (...) statement
• Ensure you're using a single transaction for the entire batch (which you already are)
• Tune MySQL settings like innodb_buffer_pool_size and max_allowed_packet to match production workloads

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