Node MySQL second query gets executed faster than first query

Question

I have a problem. In my NodeJS API. I use the Node-Binance-API package to use the API from Binance. In this API I can subscribe to account changes using a websocket. The function to subscribe to my account looks like the following:

exports.subscribeAccount = async (agentId) => {
    binance.websockets.userData((response) => {
        if (eventType === "outboundAccountPosition") {
            console.log("outboundAccountPosition")
        }
        else if (eventType === "executionReport") {
            const order = Order.executionReportToOrder(response, agentId);
            order.save();
        }
    })
}

The order.save() method looks like this:

save() {
    let sql = `
    INSERT INTO \`Order\` (
                orderId, agentId, symbol, clientOrderId, side, orderType, timeInForce, orderQuantity, orderPrice, stopPrice, icebergQuantity, originalClientOrderId, currentExecutionType, currentOrderStatus, orderRejectReason, lastExecutedQuantity, cumulativeFilledQuantity, lastExecutedPrice, commissionAmount, commissionAsset, transactionTime, tradeId, isOrderOnBook, isTradeMakerSide, creationTime, cumulativeQuoteAssetTransactedQuantity, lastQuoteAssetTransactedQuantity, quoteOrderQuantity
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE currentExecutionType=VALUES(currentExecutionType), currentOrderStatus=VALUES(currentOrderStatus), orderRejectReason=VALUES(orderRejectReason), lastExecutedQuantity=VALUES(lastExecutedQuantity), cumulativeFilledQuantity=VALUES(cumulativeFilledQuantity), lastExecutedPrice=VALUES(lastExecutedPrice), commissionAmount=VALUES(commissionAmount), commissionAsset=VALUES(commissionAsset), transactionTime=VALUES(transactionTime), tradeId=VALUES(tradeId), isOrderOnBook=VALUES(isOrderOnBook), isTradeMakerSide=VALUES(isTradeMakerSide), creationTime=VALUES(creationTime), cumulativeQuoteAssetTransactedQuantity=VALUES(cumulativeQuoteAssetTransactedQuantity), lastQuoteAssetTransactedQuantity=VALUES(lastQuoteAssetTransactedQuantity), quoteOrderQuantity=VALUES(quoteOrderQuantity);`;

    return db.execute(sql, [
        this.orderId,
        this.agentId,
        this.symbol,
        this.clientOrderId,
        this.side,
        this.orderType,
        this.timeInForce,
        this.orderQuantity,
        this.orderPrice,
        this.stopPrice,
        this.icebergQuantity,
        this.originalClientOrderId,
        this.currentExecutionType,
        this.currentOrderStatus,
        this.orderRejectReason,
        this.lastExecutedQuantity,
        this.cumulativeFilledQuantity,
        this.lastExecutedPrice,
        this.commissionAmount,
        this.commissionAsset,
        this.transactionTime,
        this.tradeId,
        this.isOrderOnBook,
        this.isTradeMakerSide,
        this.creationTime,
        this. cumulativeQuoteAssetTransactedQuantity,
        this.lastQuoteAssetTransactedQuantity,
        this.quoteOrderQuantity
    ]);
}

But now I have the following problem. The websocket works great and it pushes updates about orders. When I enter an order that gets executed instantly, this method gets called twice, right after each other. The first call is the NEW order and the second one is the FILLED order. the order.save() function writes the object to the database using the db.execute() function of the mysql2 package, but I can see that sometimes the second query gets executed faster than the first, so the final state of the order in my database is NEW. How can I prevent this from happening? Can I cancel the first query when I see the second one coming in or let them execute after each other?

TABLE

The SHOW CREATE TABLE Order results in:

CREATE TABLE `Order` (
  `orderId` bigint(20) NOT NULL,
  `agentId` int(11) NOT NULL,
  `symbol` varchar(25) NOT NULL,
  `clientOrderId` varchar(255) NOT NULL,
  `side` enum('BUY','SELL') NOT NULL,
  `orderType` enum('MARKET','LIMIT','STOP_LOSS','STOP_LOSS_LIMIT','TAKE_PROFIT','TAKE_PROFIT_LIMIT') NOT NULL,
  `timeInForce` enum('GTC','IOC','FOK') NOT NULL,
  `orderQuantity` decimal(16,8) NOT NULL,
  `orderPrice` decimal(16,8) NOT NULL,
  `stopPrice` decimal(16,8) NOT NULL,
  `icebergQuantity` decimal(16,8) NOT NULL,
  `originalClientOrderId` varchar(255) NOT NULL,
  `currentExecutionType` enum('NEW','CANCELED','REPLACED','REJECTED','TRADE','EXPIRED') NOT NULL,
  `currentOrderStatus` enum('NEW','FILLED','CANCELED','EXPIRED','PENDING_CANCEL','PARTIALLY_FILLED') NOT NULL,
  `orderRejectReason` varchar(255) NOT NULL,
  `lastExecutedQuantity` decimal(16,8) NOT NULL,
  `cumulativeFilledQuantity` decimal(16,8) NOT NULL,
  `lastExecutedPrice` decimal(16,8) NOT NULL,
  `commissionAmount` decimal(16,8) NOT NULL,
  `commissionAsset` varchar(15) DEFAULT NULL,
  `transactionTime` bigint(20) NOT NULL,
  `tradeId` bigint(20) NOT NULL,
  `isOrderOnBook` tinyint(1) NOT NULL,
  `isTradeMakerSide` tinyint(1) NOT NULL,
  `creationTime` bigint(20) NOT NULL,
  `cumulativeQuoteAssetTransactedQuantity` decimal(16,8) NOT NULL,
  `lastQuoteAssetTransactedQuantity` decimal(16,8) NOT NULL,
  `quoteOrderQuantity` decimal(16,8) NOT NULL,
  PRIMARY KEY (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Is there a way to put those queries in some kind of queue?, what you need is a transaction. i hope this answer can guide you to the right steps. you might want to ensure the FILLED order can made if and only if the NEW order exists. — Bagus Tesa
– Bagus Tesa, Commented Apr 4, 2022 at 9:21
But I thought that a transaction couldn't work, because I just start 2 transactions, because the same function gets called twice right? And a single transaction can have multiple queries, but I only know one response per function call. Could you please write me a simple example in my case how I should use the transaction? And what if a NEW order doesn't exist? How do I wait for the NEW order and then execute the other query again? — A. Vreeswijk
– A. Vreeswijk, Commented Apr 4, 2022 at 11:40
Please provide SHOW CREATE TABLE. Are both queries the same IODKU? (I think I know the answer, but I need to see these to be sure.) — Rick James
– Rick James, Commented Apr 4, 2022 at 23:43
I have added the result of that query to the bottom of my question :) — A. Vreeswijk
– A. Vreeswijk, Commented Apr 4, 2022 at 23:49
Just to make sure... Both queries are the same, because the same method is called twice! — A. Vreeswijk
– A. Vreeswijk, Commented Apr 5, 2022 at 0:04

Markus · Accepted Answer · 2022-04-08 17:54:43Z

1

+100

If this is not a binance bug, it is pretty sure that the websocket callback events come in the right order as explained here. As a first step you should double-check this with console.log() in the beginning of the callback function. Further I assume that the Order.executionReportToOrder is simple synchronous glue code.

If all this is true the overtaking must appear later in the following asychronous processing. Then you can try to use a mutex-lib like async-mutex to preserve the order of the callback execution like this:

exports.subscribeAccount = async (agentId) => {
    binance.websockets.userData((response) => {
        console.log("Add some meaningful debug information here!")
        mutex.runExclusive(() => {
            console.log("Add some meaningful debug information here!")
            if (eventType === "outboundAccountPosition") {
                console.log("outboundAccountPosition")
            }
            else if (eventType === "executionReport") {
              const order = Order.executionReportToOrder(response, agentId);
              order.save();
            }
        })
    })
}

answered Apr 8, 2022 at 17:54

Markus

6,3185 gold badges10 silver badges28 bronze badges

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

4 Comments

A. Vreeswijk Over a year ago

The order of the response is correct, so the overtaking indeed takes place in the callback handling

Markus Over a year ago

What do you mean exactly by "... takes place in the callback handling"? So you have checked the order in the websocket network communication and there it is correct? And you have checked the order with console.log in the beginning of the callback-handler? Is it still correct there? Or sometimes already wrong? Please be very precise!

A. Vreeswijk Over a year ago

Sorry, my bad! I have checked the order of the incoming responses from Binance, which turned out to be the correct order. Than I printed the same response right before I execute the query. At that point the order is still correct. But when they both call Order.save() the second callback gets executed faster (sometimes). Then it leaves an order in the database with status=NEW instead of status=FILLED

Markus Over a year ago

Then I hope the mutex-solution above works for you. If not, there are still other possiblities to solve such synchroniszation issues by using DB-features e.g. insert into ... select ... where not exists (...) article. If you need help on this, please tell!

Collectives™ on Stack Overflow

Node MySQL second query gets executed faster than first query

TABLE

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

TABLE

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related