MySQL Triggers – Does After Insert Trigger Cause Delay?

innodbMySQLmysql-5.7trigger

MySQL 5.7 innoDB

My plan: Create an after insert trigger on one table, that copies some of the inserted data to another table. Create an before insert trigger on second table that includes some evaluation (collect data from another table) before doing the insert in second table or depend on the evaluation doesn't do the insert on the second table at all.

Will the planned triggers cause any locks on the tables/records they are created? Especially is there a possibility that the before insert trigger on second table will cause a lock or insert delay on the first table?

Some background: I have a heavy loaded table with actually 120GB data (growing) in it. About 15-40 inserts/sec.

In some occasions client connections raise up explosive and cause the applications to stall.

Not 100% sure but what I see is: Some queries in some occasions on that huge table does locks and delay the inserts. That causes the client connections to raise up.

The plan above, will put some work that actually clients do into the database itself. Is there a chance that my plan does eliminate that problem.

For doing a test I need to copy that huge table on a sandbox environment and simulate the inserts. Uff. So I ask before, is there is a chance that my plan works before occupy the net with all that data load.

Best Answer

The question is not the same, but my answer is: https://dba.stackexchange.com/a/159012/1876

Don't use Triggers; instead batch the data and batch the processing. It is a lot more efficient than doing everything one row at a time, especially when a spike occurs.

Another issue... If you let "too many" connections get started, yes, the entire system can stall. This is sometimes because MySQL (and the OS) are carefully parceling out resources (CPU, I/O, etc) to more clients than the resources (cores, etc) can handle. Throughput may be fine, but latency suffers terribly.

Related Solutions

Sql-server – What would cause extra records to be inserted during a INSERT trigger

First, you don't need the IF EXISTS part in your trigger code. You have the exact same code in the second part so if there are 0 rows produced, you'll be inserting 0 rows anyway:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS 
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)              
    SELECT a.ID, a.Etc, 1 FROM Inserted a
        LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
        WHERE b.Flag IS NULL AND a.Flag = 0
END ; -- trigger

which can also be written as:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS 
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)              
    SELECT a.ID, a.Etc, 1 
        FROM Inserted a
        WHERE a.Flag = 0
          AND NOT EXISTS
              ( SELECT *
                FROM Foo b 
                WHERE b.Flag = 1 AND a.ID = b.ID
              )
END ; -- trigger

So, you want any insert on the table with Flag=0 to also trigger another insert, with same data but Flag=1 (unless there is already a row in Foo with that ID and Flag=1.

Second, and what the actual problem is about, Inserted is a special table with all the (just) "inserted" rows, not the full table you inserted into. It obviously has less (than or equal) rows as Foo after the insert. So a join to it will not give same results as a join to Foo.

The trigger that produces the multiple unwanted rows had Inserted b instead of Foo b so it does this:

For any insert on the table with Flag=0 it also triggered another insert, with same data but Flag=1 (unless this same insert had such a row inserted).

You can read more about the special Inserted and Deleted tables at the MSDN:
How to use the inserted and deleted Tables

Testing AFTER INSERT Trigger

Your analysis is correct. You don't need the trigger because the constraint is doing the same job. Anyway, to drop the constraint use:

Alter table train drop constraint train_len;

Best Answer

Related Solutions

Sql-server – What would cause extra records to be inserted during a INSERT trigger

Testing AFTER INSERT Trigger

Related Question