Mysql - Spring Boot - Unknown delay between Stored procedure execution from code and actual start-CodePudding

I have a Spring Boot application with mysql as db. To achieve some requirement, I have implemented a stored procedure in MySql and triggering it through my spring boot code.

However, what I am observing is, After my spring boot code is triggered, mysql is idle for around 90 seconds. After that procedure execution starts. This is slowing the system down. Any suggestions if this is expected or I am doing something wrong.

Below is my spring code to invoke procedure

@PersistenceContext
    private EntityManager entityManager;
@Transactional
    public void backFillClosingStock(String dbName, String id_list) throws Exception {
        try {
            ThreadLocalStorage.setTenantName(dbName);
            log.info("Starting backfilling closing stock");

            StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("update_closing_stock");
            storedProcedure.registerStoredProcedureParameter("id_list", String.class, ParameterMode.IN);
            storedProcedure.setParameter("id_list", id_list );
            storedProcedure.execute();
            ThreadLocalStorage.setTenantName(null);
            log.info("Completed backfilling closing stock");
        } catch (Exception e) {
            throw new Exception("something went wrong!");

        }

And below is my stored procedure.

DELIMITER //
DROP PROCEDURE IF EXISTS update_closing_stock//
CREATE PROCEDURE update_closing_stock(id_list TEXT)
         BEGIN
            DECLARE done INT DEFAULT FALSE;
            DECLARE entryid1 decimal;
            DECLARE oldClosingStock decimal;
            DECLARE newClosingStock decimal;
            DECLARE isValueChanged INT DEFAULT 0; 
            DECLARE cur CURSOR FOR SELECT entryid FROM inward_outward_entries WHERE FIND_IN_SET(productId,id_list)>0 AND is_deleted=0;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
            SET autocommit=0; 
            TRUNCATE temp;
            INSERT INTO temp VALUES (CONCAT('Procedure Started ',SYSDATE()));
            OPEN cur;
            ins_loop: LOOP
            FETCH cur INTO entryid1;
            IF done THEN
                LEAVE ins_loop;
            END IF;
            SELECT  
            bs1.closingStock as oldClosingStock,
            CASE WHEN bs1.type='Inward' THEN(
            (SELECT CASE WHEN SUM(bs2.quantity) IS NULL THEN 0 ELSE SUM(bs2.quantity) END FROM backfill_closing_stock bs2 
            WHERE bs2.id>=bs1.id AND bs2.type='Inward' AND bs1.Productid=bs2.productid AND bs2.warehouseid=bs1.warehouseid)
            -
            (SELECT CASE WHEN SUM(bs2.quantity)IS NULL THEN 0 ELSE SUM(bs2.quantity) END  FROM backfill_closing_stock bs2 
            WHERE bs2.id>bs1.id AND bs2.type!='Inward' AND bs1.Productid=bs2.productid AND bs2.warehouseid=bs1.warehouseid)
            )
            ELSE (
            (SELECT CASE WHEN SUM(bs2.quantity) IS NULL THEN 0 ELSE SUM(bs2.quantity) END  FROM backfill_closing_stock bs2 
            WHERE bs2.id>bs1.id AND bs2.type='Inward' AND bs1.Productid=bs2.productid AND bs2.warehouseid=bs1.warehouseid)
            -
            (SELECT CASE WHEN SUM(bs2.quantity) IS NULL THEN 0 ELSE SUM(bs2.quantity) END  FROM backfill_closing_stock bs2 
            WHERE bs2.id>=bs1.id AND bs2.type!='Inward' AND bs1.Productid=bs2.productid AND bs2.warehouseid=bs1.warehouseid)
            )
            END AS closingStock
            INTO oldClosingStock,newClosingStock
            FROM backfill_closing_stock bs1 WHERE bs1.entryid=entryid1;
           
             IF newClosingStock>=0 AND newClosingStock<>oldClosingStock THEN
                UPDATE inward_outward_entries SET closingStock = newClosingStock WHERE entryid=entryid1;
            END IF;
         END LOOP;
         CLOSE cur;
            TRUNCATE all_inventory_table;
            INSERT INTO all_inventory_table SELECT * FROM all_inventory;
        SET autocommit=1; 
END;

I have two commands in line #13 and #14 resp. for truncating a table and inserting record to it for debugging purpose. I can see table getting truncated instantly but record with text "Procedure Started " is getting inserted to temp table only after 90 sec of execution. Not Sure where it is going wrong

Below is snippet from my logs

2022-01-27 09:49:33,165 INFO  [Async-1] com.ec.application.service.AsyncServiceInventory: Before Procedure Start -2022-01-27T10:12:58.616

This shows that procedure was triggered from spring at 10:12:58

But when I check temp table, I do not see any record getting inserted for next 90 seconds. After that, say at 10:14:30, I see a record getting inserted with past time "Procedure Started 2022-01-27 10:12:58'"

I am not sure whats happening for this 90 seconds. any suggestions?

CodePudding user response：

TRUNCATE (and other DDL statements) interrupts commits -- at least before MySQL 8.0

autocommit=0 is error-prone -- if you forget to COMMIT later, bad things can happen.

So, don't bother with autocommit and get the TRUNCATEs over with. Then do

START TRANSACTION;
do all the rest of the work
COMMIT;

But for real performance gains, find a way to rewrite it so you don't need a cursor. Cursors are slow.