Transactions

Transactions (all-or-nothing changes)

A transaction groups several SQL statements so they either all happen or none do. It prevents your data ending up half-updated when something fails midway.

The classic example: transferring money

Player A gives player B $1000. That's two changes:

UPDATE accounts SET money = money - 1000 WHERE id = 1;   -- take from A
UPDATE accounts SET money = money + 1000 WHERE id = 2;   -- give to B

If the server crashes (or the second query fails) between these, money vanished from A but never reached B. A transaction makes both apply together or neither:

START TRANSACTION;
UPDATE accounts SET money = money - 1000 WHERE id = 1;
UPDATE accounts SET money = money + 1000 WHERE id = 2;
COMMIT;        -- both are saved together

If anything goes wrong before COMMIT, you ROLLBACK and nothing changed.

Doing it from Pawn

The simplest reliable way is one multi-statement transaction sent as a single query (so it runs atomically on one connection, in order). Build it with mysql_format using %d for the integer values:

new q[256];
mysql_format(g_DB, q, sizeof q,
    "START TRANSACTION; \
     UPDATE accounts SET money = money - %d WHERE id = %d; \
     UPDATE accounts SET money = money + %d WHERE id = %d; \
     COMMIT;",
    amount, fromId, amount, toId);
mysql_execute(g_DB, q, "OnTransferDone", "d", playerid);

You must allow multi-statements for this, because by default omp-MySQL blocks ;-stacked queries as an injection defense. Enable it only on a connection where you control the SQL (never with raw player input):

mysql_config_set(cfg, MULTI_STATEMENTS, 1);   // before mysql_connect

Because every value above is a %d integer (not player text), this is safe.

Guarding the transfer (don't go negative)

Check funds first, or make the UPDATE itself refuse to overdraw:

UPDATE accounts SET money = money - 1000 WHERE id = 1 AND money >= 1000;

If A doesn't have $1000, that row updates 0 rows — then you'd roll back. For correctness under heavy concurrency, do the check inside the transaction.

When you actually need transactions

• Moving value between rows (money/items between players).
• Multi-table saves that must stay consistent (e.g. create an account row and its starter inventory rows together).
• Anything where a half-done state would be a bug.

For a single UPDATE of one player, you don't need a transaction — one statement is already atomic.

Requirements & notes

• Your table engine must be InnoDB (the default in modern MySQL). MyISAM doesn't support transactions.
• Keep transactions short — they hold locks; a long one can stall other writers.
• All statements in one transaction must run on the same connection — sending them as one multi-statement query (above) guarantees that.

Advanced: separate statements

You can send START TRANSACTION, the updates, and COMMIT as separate mysql_execute calls on the same handle (they're serialized per handle, so order is preserved). But the single multi-statement form above is simpler and clearly atomic. If a step's callback detects failure, send a ROLLBACK.

Next: Performance · Cookbook