Forgottenserver: [Question] Difference between db.query and db.asyncQuery

[Riverlance]

What is the difference between db.query and db.asyncQuery?
I know that may something about sychronizing, but when should I use query and when asyncQuery?
I made a search on lua scripts that uses both, but I cannot understand the reason to use each other.

Thanks in advance!

[jo3bingham]

@Riverlance, not sure why you closed this, but I'll answer it for you anyway.

query and storeQuery are used for synchronous queries and asyncQuery and asyncStoreQuery are used for asynchronous queries. Synchronous queries are done when you need to do something in the database and you can't, or don't want to, continue executing code until it's done. Asynchronous queries are done when you don't need to wait for the query to complete. Asynchronous queries should be preferred over synchronous ones when acceptable.

I'll use the unban script as an example and place comments inside:

function onSay(player, words, param)
    if not player:getGroup():getAccess() then
        return true
    end

        -- A synchronous query is done here because we need the result in the async queries below.
        -- By using a synchronous query, execution of code is halted until the query returns.
        -- If asyncStoreQuery was used here instead, execution would continue even if the query hadn't returned yet and could cause inconsistent results.
    local resultId = db.storeQuery("SELECT `account_id`, `lastip` FROM `players` WHERE `name` = " .. db.escapeString(param))
    if resultId == false then
        return false
    end

        -- Asynchronous queries are used here because we don't need to wait for them to be executed.
        -- Continuing execution and returning from the function is more important than waiting for `DELETE FROM` ban tables to finish executing.
    db.asyncQuery("DELETE FROM `account_bans` WHERE `account_id` = " .. result.getDataInt(resultId, "account_id"))
    db.asyncQuery("DELETE FROM `ip_bans` WHERE `ip` = " .. result.getDataInt(resultId, "lastip"))
    result.free(resultId)
    player:sendTextMessage(MESSAGE_EVENT_ADVANCE, param .. " has been unbanned.")
    return false
end

Let's say you implement Tibia Coins into your server. Ideally, like with gold, you would load the player's Tibia Coin balance from the database when they log in, and persist their balance in memory until they log out. However, like most servers, you allow players to purchase Tibia Coins from your website, and having players log out, and back in, to update their Tibia Coin balance in-game is not ideal. So, to remedy this, you must store and modify their Tibia Coin balance in the database, and query the database any time the player does something that needs to know their Tibia Coin balance (Store, Market, etc).

In this example, we'll use the act of transferring Tibia Coins via the Store:
A player has 0 Tibia Coins, but has a friend who would like 1000 Tibia Coins to purchase something in the Store, but doesn't have the ability to purchase Tibia Coins from your website. They agree that the player will purchase 1000 Tibia Coins and transfer them to their friend in exchange for 50kk gold. The player logs into your website, while simultaneously being logged in to the game, and purchases 1000 Tibia Coins and you update their balance in the database. They switch back to the client and open the Store to be greeted by a Tibia Coin balance of 1000 because you queried the database to get their up-to-date Tibia Coin balance. They then want to transfer those 1000 Tibia Coins to their friend; what do you need to do?

1. [Sync] Query the database again to confirm their Tibia Coin balance.
2. [Sync] Update their balance to reflect their new balance.
3. [Async/Sync] Update their friend's balance accordingly.

Why does step 1 have to be done synchronously? Because you need to know the player's Tibia Coin balance to confirm that they have enough for the transfer. Maybe they purchased something form the Store right before the transfer and you did an asynchronous transaction that hasn't completed.
Why does step 2 have to be done synchronously? Because an asynchronous update to their Tibia Coin balance could take time, and it's possible that the player could finish this transfer and then do another one before their Tibia Coin balance is updated in the database. This is only worrisome when decreasing a player's Tibia Coin balance.
Why can step 3 be done asynchronously or synchronously? This is a personal preference. If you want their friend's Tibia Coin balance to be updated as soon as possible, and you don't care about blocking your code execution, then you want to do this synchronously. If you don't care that their balance may not be updated immediately, and you'd rather your code to execute as quickly as possible, then you would just do this asynchronously.

[joseluis2g]

@jo3bingham
Beautiful, thanks.

[djarek]

@jo3bingham
As a sidenote: if MySQL transactions are supported in the lua API (I don't remember if that's the case), then it's possible for all 3 steps to be written asynchronously (by chaining callbacks), but care must be taken to avoid timing errors.

Another warning: if you use callbacks with async queries, DO NOT retain any references to userdata (Players, Items, etc.) because they may be invalidated between the initiation of the async operation and the execution of the handler.

[jo3bingham]

@djarek
Question; there's no example use of asyncStoreQuery in TFS' datapack, but am I right in assuming it works the same in Lua as it does in some other languages?

local resultId = db.storeQuery("SELECT `account_id`, `lastip` FROM `players` WHERE `name` = " .. db.escapeString(param))
if resultId == false then
    return false
end

For example, in the code above, if storeQuery was replaced with asyncStoreQuery would execution be blocked by the conditional in the if statement because of the use of resultId, or do you have to do something special in Lua to wait for the async call to complete?

[diath]

@jo3bingham
asyncStoreQuery doesn't return a result, it accepts a function callback as the second parameter which it will call with the result when it's ready.

[djarek]

For example, in the code above, if storeQuery was replaced with asyncStoreQuery would execution be blocked by the conditional in the if statement because of the use of resultId, or do you have to do something special in Lua to wait for the async call to complete?

The execution of a normal lua function will not be suspended by any async* operation. Such behavior could be achieved by using lua threads (coroutines), but I've never touched them, so no idea if they would even work with our implementation.

 local playerId = 0xDEADBEEF
 db.asyncQuery("SELECT `account_id`, `lastip` FROM `players` WHERE `name` = " .. db.escapeString(param),
  function(resultId)
    local player = Player(playerId)
    --this closure is called once the query succeeds or fails
    if resultId == false then
      -- query failed, do sth with it (e.g. send msg to user that sth failed)
      return
    end
    -- do sth with result, or perform another query
  end)

[Riverlance]

First of all, I would like to thank everyone who answered. All the answers were strictly helpful to me.

@jo3bingham I closed it because I'm not usually answered on this forum, don't really know why, and, as a friend had already explained to me (seeming enough, but from what I've read here, it does not seem to have been that enough as I though), I did not want to bother anyone. Thanks for the answer, means a lot to me.

What I understood of you guys teached me, let me see if I get that:

Example
Let's say that a player have 1000 cash points. He will buy something that costs exactly 900 points and, then, he will try to buy something that costs 200 points instantly after the previous bought.

ALL QUERIES AS SYNC of this example

• Player had 1000 points and bought something that costs 900 points. We does ensures that, after that bought, he will have 100 points (1000 - 900 = 100). It happens because the code after the query WILL NOT be executed until the query to be executed.
• Now, he tries to buy something that costs 200 points. He cannot buy it because we ensured that the previous query were executed. Which means he have 100 points, no enough points to buy the second item that costs 200 points.

ALL QUERIES AS ASYNC of this example

• Player had 1000 points and bought something that costs 900 points. We does NOT ensures that, after that bought, he will have 100 points. It happens because the code after query WILL be executed anyway, without waiting the previous query be executed. The query will be added into a "waiting list" of queries executions and keep running the rest of the code.
• Now, he tries to buy something that costs 200 points. MAYBE he will still be able to buy it, because we cannot ensures that the previous query were executed. The previous query would still be on the "waiting list". So this second query could be added also into the waiting list if the previous one were not executed, because we said that the player still have 1000 points, so he still are able to buy the second item that costs 200 points. Let's says that's the case (previous query are still on the "waiting list" and his balance is still 1000 points). The player received both items, because we allowed him to buy them, and both queries are on the waiting list. Now, no matter the order of executions of both queries (async does not ensures the query execution's order of the "waiting list" also), he will have -200 of cash balance ((1000 - 900) - 200 = -100 or (1000 - 200) - 900 = -100). So, it's horrible the result if we had as asynchronous: the player received both items and he have now -100 of cash balance.
  It's possible to make this case worse. It would be -100, as the mentioned, if we used both payment queries as the bellow:
  db.asyncQuery("UPDATE accounts SET cash = cash - " .. price .. " WHERE id = " .. accountId)
  So, it would at least ensures that, no matter the execution's order of both queries, it would be -100 on the final result. Because it will get the actual balance of cash at the moment of queries execution's act, no matter the order of them.
  Now let's see how to make this case worse. Let's says we'd use both payment queries as the bellow:
  local cash = 0
local resultId = db.storeQuery("SELECT cash FROM accounts WHERE id = " .. accountId)
if resultId == false then return end
cash = result.getDataLong(resultId, "cash")
result.free(resultId)
db.asyncQuery("UPDATE accounts SET cash = " .. balance .. " - " .. price .. " WHERE id = " .. accountId)
  On this case, we cannot ensures that the balance will have the right value we do expect. Because, if both queries are still on the "waiting list", it means, in both case, the balance value will be 1000. So, it means that 2 cases may happens:
  1: First query be executed previously:
  -- He'd have 1000 as balance and will pay 900 for the first item: 1000 - 900 = 100.
  -- He still do have 1000 as balance and will pay 200 for the second item: 1000 - 200 = 800.
  -- Final Result: Player would get both items anyway and the final balance would be 800 of cash.
  2: Second query be executed previously:
  -- He'd have 1000 as balance and will pay 200 for the second item: 1000 - 200 = 800.
  -- He still do have 1000 as balance and will pay 900 for the first item: 1000 - 900 = 100.
  -- Final Result: Player would get both items anyway and the final balance would be 100 of cash.

In a short way:

• Synchronous: Query is the priority. It WILL NOT execute any code until finishes the query execution.
• Asynchronous: Query IS NOT the priority. It will add the query to a "waiting list" of queries executions and continue to execute the rest of the code without waiting the added query to be executed.
• Async looks great about its efficiency, but it's pretty hard to make it work properly.

---

@diath Thanks, bro. Didn't know about that.

---

@djarek
You meant asyncStoreQuery instead asyncQuery on the last code you sent, don't you?
@jo3bingham said that have 4 ones: query, storeQuery, asyncQuery and asyncStoreQuery.

Also, about the sidenote that you said:

As a sidenote: if MySQL transactions are supported in the lua API (I don't remember if that's the case), then it's possible for all 3 steps to be written asynchronously (by chaining callbacks), but care must be taken to avoid timing errors.

... could you show me how it could be done as an example?
I would love to learn how it could be done.
You may use the 3 steps example of @jo3bingham .

[djarek]

-- sadly not in TFS
function db.asyncCoroStoreQuery(query)
    local co, isMain = coroutine.running()
    local result = {}
    db.asyncStoreQuery(query, function(resultId)
        result.id = resultId
        coroutine.resume(co)
    end)
    coroutine.yield()
    return result.id
end

-- sadly not in TFS
function db.asyncTransactionBegin() end
function db.asyncTransactionCommit() end
function db.asyncTransactionRollback() end

local co = coroutine.create(function()
    db.asyncTransactionBegin() -- begin transaction
    local status, err = pcall(function() -- catch errors
        -- calling asyncCoroStoreQuery suspends this unnamed function and returns execution to the main coroutine
        local resultId = db.asyncCoroStoreQuery("q1")
        -- this coroutine has been resumed by the async database engine
        print(resultId)
        if not resultId then
            error({code="Empty resultId"})
        end
        -- parse results from query q1

        -- calling asyncCoroStoreQuery suspends this unnamed function and returns execution to the main coroutine
        resultId = db.asyncCoroStoreQuery("q2")
        -- this coroutine has been resumed by the async database engine
        print(resultId)
        if not resultId then
            error({code="Empty resultId"})
        end
        -- parse results from query q2

        -- calling asyncCoroStoreQuery suspends this unnamed function and returns execution to the main coroutine
        resultId = db.asyncCoroStoreQuery("q3")
        -- this coroutine has been resumed by the async database engine
        print(resultId)
        if not resultId then
            error({code="Empty resultId"})
        end
        -- parse results from query q3
    end)

    if status then 
        db.asyncTransactionCommit()
    else
        db.asyncTransactionRollback() -- rollback if the coroutine threw an error
    end
end)

coroutine.resume(co) -- starts the coroutine
-- execution resumes almost immediately, once the coroutine co yields when starting its query

I haven't tested this in TFS, just using a standalone interpreter with a mocked database. Unfortunately the lua database API does not seem to have transactions implemented, so it's even harder to have this work (needs carefully written SQL to be correct without transactions).

[djarek]

Do you have more questions in this topic, or can this issue be closed?

[Riverlance]

@djarek Sorry. I have no time for now to try to understand your answer. Thank you very much for answer and help me. I really appreciate it. I will close it for now. If I get questions when I get time to read it, I open this thread again, ok? Thanks a lot! 😋