1

I have three tables in a MySQL database, bids, offers and matched. The matched table contains bids and offers which have been matched. There is an event that runs the logic to do the matching shown below which works.

CREATE EVENT bid_offer_matching
ON SCHEDULE EVERY 1 SECOND  
STARTS CURRENT_TIMESTAMP
DO
    INSERT INTO Matched(
        consumer_id,        /* Bidder */
        producer_id,        /* Offer */
        bid_id,             /* bid id */
        offer_id,           /* offer id */ 
        volume,             /* Volume */
        price,              /* Price */
        market_time)        /* Market Time */
    SELECT  
        bids.user_id,               /* Bidder */
        offers.user_id,             /* Offer */
        bids.bid_id,                /* Bid ID */
        offers.offer_id,            /* Offer ID */
        bids.bid_volume,            /* Volume */
        bids.bid_price,             /* Price */
        bids.market_time            /* Market Time */
    FROM  
        Bids bids
    INNER JOIN (
        SELECT *, ROW_NUMBER() 
            OVER (
                PARTITION BY 
                    offer_volume /* Partition by the volume in the offer */
                ORDER BY 
                    /* Order each partiton by the price low to high, this makes row 1 in each partition the lowest price offer*/ 
                    offer_price 
            )rn /* Get the row number also back */ 
        FROM Offers
    ) offers
    ON
        /* Volume must be equal */ 
        bids.bid_volume = offers.offer_volume AND
        /*Price must be at least asking price */ 
        bids.bid_price >= offers.offer_volume AND
        /* Market time must be same */ 
        bids.market_time >= offers.market_time
    WHERE
        /* This is important, it matches only the above condition to the lowest offer price resulting in best price for bidder */
        offers.rn = 1;

The problem occurs when I create a trigger on the matched table to delete the bid and offer inserted from the bids and offers table. I want to delete them as they are no longer needed once they are matched. The logic for this trigger is shown below.

/*Crete the trigger*/
DELIMITER $$
CREATE TRIGGER match_insert_trigger
AFTER INSERT
ON Matched FOR EACH ROW 
BEGIN 
    /* DELETE THE BID*/
    DELETE FROM 
        Bids
    WHERE 
        bid_id=NEW.bid_id; /* The bid ID is inserted into the matched table to be used as reference, not a foreign key though */

    /* DELETE THE OFFER */
    DELETE FROM 
        Offers
    WHERE 
        offer_id=NEW.offer_id; /* The offer ID is inserted into the matched table to be used as reference, not a foreign key though */
END$$

DELIMITER ;

The error now occurs however when trying to execute the event to match bids and offers

Error Code: 1442. Can't update table 'Bids' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I am only updating the Bids table in the event match_insert_trigger but the bids table is not used to trigger anything so bit unsure what is wrong here?

EDIT

I have achieved the functionality I need by placing the logic to delete the matched bids/offers in the event bid_offer_matching. However I still do not understand how the above error is occurring if anyone knows.

New event code below

DELIMITER $$
CREATE EVENT bid_offer_matching
ON SCHEDULE EVERY 1 SECOND  
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    INSERT INTO Matched(
        consumer_id,        /* Bidder */
        producer_id,        /* Offer */
        bid_id,             /* bid id */
        offer_id,           /* offer id */ 
        volume,             /* Volume */
        price,              /* Price */
        market_time)        /* Market Time */
    SELECT  
        bids.user_id,               /* Bidder */
        offers.user_id,             /* Offer */
        bids.bid_id,                /* Bid ID */
        offers.offer_id,            /* Offer ID */
        bids.bid_volume,            /* Volume */
        bids.bid_price,             /* Price */
        bids.market_time            /* Market Time */
    FROM  
        Bids bids
    INNER JOIN (
        SELECT *, ROW_NUMBER() 
            OVER (
                PARTITION BY 
                    offer_volume /* Partition by the volume in the offer */
                ORDER BY 
                    /* Order each partiton by the price low to high, this makes row 1 in each partition the lowest price offer*/ 
                    offer_price 
            )rn /* Get the row number also back */ 
        FROM Offers
    ) offers
    ON
        /* Volume must be equal */ 
        bids.bid_volume = offers.offer_volume AND
        /*Price must be at least asking price */ 
        bids.bid_price >= offers.offer_volume AND
        /* Market time must be same */ 
        bids.market_time >= offers.market_time
    WHERE
        /* This is important, it matches only the above condition to the lowest offer price resulting in best price for bidder */
        offers.rn = 1;

/* *****DELETE STATEMENT ***** */
    /* DELETE THE OFFERS */
    DELETE FROM  
        Offers 
    WHERE 
        offer_id  
    IN 
        (SELECT offer_id FROM Matched);
        
    /* DELETE THE BIDS */
    DELETE FROM  
        Bids 
    WHERE 
        bid_id  
    IN 
        (SELECT bid_id FROM Matched);

END$$
        
DELIMITER ;
2
  • In event procedure - select from Bids to temptable, then insert into Matched. Commented Mar 22, 2021 at 16:33
  • @Akina can you explain a bit more why I need a temp table? Commented Mar 22, 2021 at 16:37

1 Answer 1

2

The code in your event body does an INSERT INTO Matched... SELECT ... FROM Bids...

https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html says:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Notice it says "for reading or writing." This means that by reading from the Bids table as you are doing in your INSERT...SELECT statement, this conflicts with the rule that you can't modify tables in the trigger that are being read from in the statement that invoked the trigger.

Sign up to request clarification or add additional context in comments.

3 Comments

Hi Bill, thanks for this it answers my question, would the suggested approach by @Akina in the comments of a temp table be the way to go you think?
I wouldn't use a trigger. I'd load a temp table as Akina described, and then use that temp table for all three actions, inserting to Matched and deleting from the other two tables. Making your event code more explicit helps the reader understand how it all works. Triggers make it hard to read the code.
Ok I need to look more into temp tables to understand the benefit of them I think, I have shown what I edited in the event now, it is working of three different tables (bids,offers,matched) which I assume judging from the suggestions is bad practice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.