0

I'm confused as to why this happen, because to my understanding a before insert trigger will get fired for every new row, which do work when I run the code synchronously. But because my data can be quite a lot, I'm trying to make it asynchronous.

For example, I tried to insert two transaction headers which should fire the trigger twice to generate each transaction number, but it only generate one number for both rows.

enter image description here

Below is my trigger code :

CREATE DEFINER=`root`@`%` TRIGGER `generate_transaction_number` 
BEFORE INSERT ON `transaction` 
FOR EACH ROW BEGIN
    DECLARE v_max_transaction_num VARCHAR(17);
    DECLARE v_separator VARCHAR(1) DEFAULT '/';
    SELECT MAX(transaction_number) INTO v_max_transaction_num from transaction t WHERE DATE(t.transaction_date) = CURDATE();
    IF(v_max_transaction_num IS NULL OR v_max_transaction_num = '') THEN
        SET v_max_transaction_num = '0';
    END IF;
    SET new.transaction_number = concat('INV', v_separator, DATE_FORMAT(CURDATE(),'%Y%m%d'), v_separator, LPAD(SUBSTRING_INDEX(v_max_transaction_num,v_separator,-1)+1,4,'0'));
END

Below is the javascript async code :

let promises = [];

for (const transactionHeader of transactionHeaders){  
  promises.push(mTransactions.addTransaction(transactionHeader));
}

await Promise.allSettled(promises);

My goal is to make it asynchronous, but and make the trigger fired for every row.

Nick
  • 138,499
  • 22
  • 57
  • 95
Shira Ishikawa
  • 140
  • 4
  • 17
  • Promise.all/allSettled returns a Promise that resolves only when ALL promises passed to it resolve, and like any Promise, it only resolves once - not sure what "the trigger" is, there is nothing "triggered" in your code – Bravo Jun 29 '22 at 01:10
  • @Bravo The trigger is in the mysql database, and the INSERT statement is inside mTransactions.addTransaction model, that get called by the promise statement. So the await here is to wait all INSERT statement to be done, which should've logically using different mysql transaction for every promise. – Shira Ishikawa Jun 29 '22 at 01:16
  • Sorry, I don't understand what your issue is ... are you saying that each `mTransactions.addTransaction(transactionHeader)` should cause this trigger in mysql, and it doesn't? what does promise.all have to do with it? it's the for of loop that is doing the triggering, the promise.all just waits for them all to complete – Bravo Jun 29 '22 at 01:20
  • could it also be that you need to wait for the previous `mTransactions.addTransaction(transactionHeader)` before running the next one? again, that would have nothing to do with promise.all – Bravo Jun 29 '22 at 01:21
  • The trigger does fire for every inserted row, otherwise you would not see a transaction id value there at all. The problem with the trigger code is that it assumes serialised insertion of data (rows are inserted one after the other) as it uses max() to calculate the next transaction id. – Shadow Jun 29 '22 at 01:25
  • so the issue is with the trigger code getting it wrong because the requests are coming in parallel rather than in series - I don't think this is an asynchrony issue, I think this is your database not handling data coming in "too quickly" – Bravo Jun 29 '22 at 01:27
  • @Bravo no, this is an issue with the idea of using database to generate a transaction id consisting of a prefix and an auto incremented value in a single field. – Shadow Jun 29 '22 at 01:30
  • @Shadow - so, the issue is naff all to do with the javascript is what I'm getting at - though, except that it's sending transactions "too fast" - I still think the issue is serial vs parallel - naff all to do with Promises and asynchrony – Bravo Jun 29 '22 at 01:33
  • @Bravo serialisation of inserts can happen on the application side (and probably should). That would be a javascript question. But it definitely does not have anything to do with promises. – Shadow Jun 29 '22 at 01:35
  • @Shadow - I meant to say it's nothing to do with Promises/asynchrony - I can't imagine making synchronous requests to a database - it's the terminology that the OP is getting wrong – Bravo Jun 29 '22 at 01:37
  • This is what I got confused about, the issue disappeared when I remove the promise and just run it with await for each loop, so I thought the problem is the promise (or how I implement it). I can't think any other reason because the autogenerated transaction_id (which is mysql auto increment) do increased means the insert do work in sequence, it just that the trigger unable to read the previously generated transaction_number. – Shira Ishikawa Jun 29 '22 at 02:55
  • 1
    @ShiraIshikawa mysql uses special locks for auto increment fields to ensure the value does increase. You do not employ any locking for your field. To be honest, using locks for this purpose (unless this is a regulatory requirement) is a huge waste of resources. Just serialise the inserts in the application logic, rather than in the database. Or even better, do away with this requirement to have a transaction id generated with a prefix and an auto incremented id part in a single field. – Shadow Jun 29 '22 at 07:34
  • @Shadow So the problem is since the transactions sent in the same time, they see the same last row from table lock? Thank you, I think I got it. I'll check the linked duplicate question. – Shira Ishikawa Jun 30 '22 at 02:31

0 Answers0