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.