SQLx Threaded Queries in AMXX (Without Freezing the Server)

The single most common way plugin authors wreck server performance is a blocking database query. AMXX runs your Pawn on the main game thread — the same thread that simulates the world — so a synchronous SQL_Query that waits 200 ms for MySQL freezes every player for 200 ms. The fix is SQL_ThreadQuery: it hands the query to a background worker and calls you back when the result is ready, with the main thread never blocking. This guide shows the correct pattern, including the handle lifetime rules that trip people up.

1. Load the module and make a connection tuple

You need the sqlx module (see install the MySQL module). A tuple holds the connection details; create it once and keep it for the plugin's lifetime — do not rebuild it per query:

#include <amxmodx>
#include <sqlx>

new Handle:g_tuple

public plugin_init()
{
    register_plugin("SQLx Demo", "1.0", "CSB")
    g_tuple = SQL_MakeDbTuple("127.0.0.1", "csuser", "secret", "cs_stats")
}

public plugin_end()
{
    SQL_FreeHandle(g_tuple)
}

SQL_MakeDbTuple does not connect; it just stores credentials. The connection is opened by the worker when a threaded query runs.

2. Fire a threaded query

SQL_ThreadQuery(tuple, callback, query) queues the query and returns immediately. Build the SQL with formatex, and always escape untrusted values with SQL_QuoteString first:

public save_kills(id, kills)
{
    new name[32], safe[64]
    get_user_name(id, name, charsmax(name))
    SQL_QuoteString(g_tuple, safe, charsmax(safe), name)

    new query[256]
    formatex(query, charsmax(query),
        "INSERT INTO stats (name, kills) VALUES ('%s', %d)          ON DUPLICATE KEY UPDATE kills = kills + %d",
        safe, kills, kills)

    SQL_ThreadQuery(g_tuple, "qh_save", query)
}

Never build a query by pasting a player name straight into it — a name containing a quote breaks the SQL and is a classic injection vector. SQL_QuoteString is not optional.

3. The callback

The worker calls your handler with a fixed signature. Check the failstate first, always — a dropped connection is reported here, not thrown:

public qh_save(failstate, Handle:query, error[], errnum, data[], size, Float:queuetime)
{
    if (failstate == TQUERY_CONNECT_FAILED)
    {
        log_amx("SQL connect failed: %s (%d)", error, errnum)
        return
    }
    if (failstate == TQUERY_QUERY_FAILED)
    {
        log_amx("SQL query failed: %s (%d)", error, errnum)
        return
    }

    // success: query is done
}

Do not call SQL_FreeHandle on the query handle in a threaded callback. The module owns it and frees it automatically when your handler returns. Freeing it yourself is a double-free. This is the opposite of the non-threaded API, and the rule people get wrong most often.

4. Reading a result set

For a SELECT, walk the rows with SQL_MoreResults and SQL_NextRow, reading columns by index or name:

public qh_load(failstate, Handle:query, error[], errnum, data[], size, Float:queuetime)
{
    if (failstate)
    {
        log_amx("load failed: %s", error)
        return
    }

    while (SQL_MoreResults(query))
    {
        new kills = SQL_ReadResult(query, SQL_FieldNameToNum(query, "kills"))
        server_print("loaded %d kills", kills)
        SQL_NextRow(query)
    }
}

5. Passing data to the callback

The worker runs later, so a local like a player index is gone by the time the callback fires — and the player may have disconnected. Pass what you need through the data array, and guard against the player leaving:

new data[1]
data[0] = get_user_userid(id)     // survives across the async gap
SQL_ThreadQuery(g_tuple, "qh_load", query, data, sizeof data)

Store the userid, not the raw client index — indices get reused when someone reconnects, and by the time the result arrives, index 5 may be a different person. In the callback, resolve it with find_player using the "k" flag and confirm they are still connected before writing to their HUD.

Common errors

• Server hitches on every save — you used SQL_Query (blocking) instead of SQL_ThreadQuery. Move it to the threaded API.
• Crash after a query — you freed the query handle in the threaded callback. Remove the SQL_FreeHandle(query) call; only free the tuple, and only in plugin_end.
• TQUERY_CONNECT_FAILED every time — wrong host, credentials, or the MySQL user is not allowed from the server's IP. The error string carries the MySQL message; log it.
• Writing to the wrong player — you passed a client index through data and it was reused. Pass the userid and re-resolve.
• Query silently truncated — your query buffer was too small. Size it for the longest possible statement and check the formatex return.

Verification

Watch the server console FPS while a query runs — a threaded query should cause no dip, a blocking one an obvious stutter. Confirm the row actually landed by checking the table directly in MySQL. Force a failure (stop MySQL, or use bad credentials) and confirm your failstate branch logs a clean message instead of crashing. For a full stats implementation built on this pattern, see CSB Rank MySQL.