9. Transactions and Concurrency

MIRAJ runs every statement under a transaction. When none has been opened explicitly, each statement forms its own transaction, committed automatically as soon as it succeeds (autocommit mode, enabled by default). An explicit transaction groups several statements that must succeed or fail together.

This chapter describes:

transaction control (START TRANSACTION, COMMIT, ROLLBACK, autocommit);
MIRAJ's concurrency model, serializable isolation with no row locks and no waiting, and what an application must do about it;
explicit table locks (LOCK TABLES / UNLOCK TABLES);
the status of SAVEPOINT;
three-valued logic (TRUE, FALSE, UNKNOWN) and its pitfalls with NULL.

9.1 Autocommit and explicit transactions#

Autocommit#

By default, autocommit is 1 (enabled) for each new session: any statement that modifies data (INSERT, UPDATE, DELETE, DDL) is committed on its own, immediately after it runs. This is the usual mode for an application that does not need to group several writes.

SET autocommit = 0;   -- each statement opens or extends a transaction, until the
                       -- next explicit COMMIT or ROLLBACK
SET autocommit = 1;   -- back to the default mode

START TRANSACTION / BEGIN#

START TRANSACTION;
-- equivalent:
BEGIN [WORK];

START TRANSACTION options:

START TRANSACTION READ ONLY;              -- transaction declared read-only
START TRANSACTION READ WRITE;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

READ ONLY declares an intent; a transaction opened by START TRANSACTION or by autocommit = 0 remains, in all cases, subject to the same concurrency rules described in §9.2.

COMMIT and ROLLBACK#

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE];
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE];

COMMIT commits all the writes of the transaction: they become visible to other sessions and durable (written to the log before being published).
ROLLBACK cancels all the writes of the transaction: each modified row reverts to its value from before the transaction.
AND CHAIN immediately opens a new transaction with the same characteristics (READ ONLY/READ WRITE) as the one that has just ended.
RELEASE also ends the session after the commit or rollback.

START TRANSACTION;
INSERT INTO commandes (client_id, montant) VALUES (42, 199.90);
UPDATE clients SET solde = solde - 199.90 WHERE id = 42;
COMMIT;

If an error occurs in the middle of a transaction (constraint violated, concurrency conflict, etc.), none of the statements already executed is automatically rolled back by MIRAJ: it is up to the application to decide, based on the error code, whether to continue, fix and retry the offending statement, or cancel (ROLLBACK) the whole transaction. The case of the concurrency conflict (error 1213) is described below and systematically requires a ROLLBACK followed by a new attempt.

9.2 Concurrency model#

MIRAJ isolates transactions at the strictest level of the SQL standard, SERIALIZABLE: everything behaves as if the transactions ran one after the other, never interleaved, regardless of the number of sessions active at the same time. This level is usually obtained at the price of row locks that make transactions wait behind one another. MIRAJ achieves the same result differently, through optimistic multiversion control: each transaction works on its own snapshot of the data, never taking a row lock or making anyone wait.

What to remember, from the point of view of a client application:

Situation	Behavior
A `SELECT` while another session is writing	never blocks, never sees a half-written state, and is never cancelled because of that write
Two sessions modify the same row at the same time	the first to commit wins; the second is aborted right away (no waiting)
Two transactions that, taken together, could not be replayed in any valid order (crossed writes, an `INSERT` that contradicts a range read by another transaction, etc.)	one of the two is aborted at commit
Deadlock (two transactions that would wait for each other)	cannot happen: no transaction ever waits for another for a row

In practice:

Readers are never blocked and never cancelled. A SELECT always reads a consistent snapshot, taken at a precise instant, regardless of the writes in progress elsewhere.
A write conflict never waits: as soon as it is detected, the losing transaction is aborted immediately rather than queued behind the other.
A standalone statement (autocommit mode) that hits a conflict is automatically replayed by the engine, up to three times, with a wait that increases on each attempt (50 µs, 200 µs, then 800 µs). The application sees nothing in the vast majority of cases.
An explicit transaction (opened by START TRANSACTION or by autocommit = 0) cannot be replayed by the engine: it has not remembered the sequence of statements already sent by the client. If it hits a conflict — its own or one after its automatic replay attempts are exhausted — it is cancelled in its entirety and the client receives the following error.

Error 1213#

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

This code (ER_LOCK_DEADLOCK, SQLSTATE 40001) is the same one that a usual transactional server returns for a deadlock — existing drivers already recognize it and report it as "replay the transaction". MIRAJ never produces a deadlock in the strict sense (no transaction ever waits for another) but returns this code whenever a transaction is cancelled by a concurrency conflict at commit: it is the signal that no data has been lost or corrupted, only that this particular transaction must be restarted from the beginning.

What an application receiving 1213 must do: rerun the entire transaction from START TRANSACTION (or its application-level equivalent), not just the last statement. A typical pseudo-code:

-- Application pseudo-code
tentative := 0
répéter
    tentative := tentative + 1
    essayer
        START TRANSACTION;
        UPDATE comptes SET solde = solde - 100 WHERE id = 1;
        UPDATE comptes SET solde = solde + 100 WHERE id = 2;
        COMMIT;
        sortir de la boucle          -- success
    intercepter erreur 1213
        ROLLBACK;                    -- as a precaution; the transaction is already cancelled server-side
        si tentative >= 5 alors relancer l'erreur au niveau supérieur
        sinon recommencer la boucle (idéalement après une courte pause aléatoire)

A transfer between two accounts is a good example: if two concurrent transfers touch the same account, one of them may receive 1213 even though, taken separately, each is correct. Restarting it is enough; there is nothing to fix in the business logic.

docs/concurrency.md (internal to the repository) documents the details of the mechanism for anyone who wants to go deeper: snapshots, validation by read and scan sets, version purge watermark.

Deferred update#

A counter, or the stock of an item sold at every checkout, is a row that many transactions modify at the same time: with the sole rule "the first to write wins", all but one would receive 1213. MIRAJ therefore handles the UPDATEs that qualify separately: such an UPDATE does not take the row; it is recomputed at COMMIT on the last committed value, then written. Two transactions that run UPDATE stocks SET qte = qte - 1 WHERE id = 7 then both commit, and the result is that of running them one after the other, in COMMIT order.

-- qte is 100
-- Session A                                   -- Session B
START TRANSACTION;                              START TRANSACTION;
UPDATE stocks SET qte = qte - 1 WHERE id = 7;
                                                UPDATE stocks SET qte = qte - 2 WHERE id = 7;
                                                COMMIT;   -- qte = 98
COMMIT;   -- qte = 97, no error

An UPDATE is deferred when it targets a row designated by its key (equalities on all columns of the primary key or of a UNIQUE index), when it modifies neither a key, nor a foreign key, nor an AUTO_INCREMENT or BLOB column, when it calls neither a subquery nor a non-deterministic function (RAND(), UUID()… ; NOW() and CURDATE() are allowed), and when the triggers of the table allow it (BEFORE UPDATE triggers that only compute NEW.col, AFTER UPDATE triggers that do not read the modified columns). Otherwise, it takes the row as described above; the result is the same, only the concurrency changes. ROW_COUNT() is 1, the transaction rereads its own value, and AFTER UPDATE triggers run right away.

What changes for the application:

Errors at COMMIT. If the row was deleted in the meantime, if the WHERE condition no longer holds, or if another transaction still holds the row, the COMMIT fails with 1213: the transaction must be replayed, like any other. If the recomputation itself fails (value out of range, 1264; NOT NULL column, 1048; CHECK constraint, 4025…), the COMMIT fails with that code; in both cases, the whole transaction is cancelled.
Reading before writing. A transaction that reads the row (for example to check available stock) before updating it remains exposed to 1213 if another transaction modifies that row before its COMMIT: what it read is no longer true. An UPDATE that carries its own condition (… WHERE id = 7 AND qte >= 1) fails only if the condition no longer holds at COMMIT.
Setting. SET deferred_update = OFF (session), SET GLOBAL deferred_update = OFF (subsequent sessions) or miraj-server --deferred-update OFF restore immediate row acquisition; ON is the default value. information_schema.MIRAJ_DEFERRED_UPDATE counts the deferred UPDATEs, the failures at COMMIT by cause, and the UPDATEs that could not be deferred (see 11.6.2).

DDL and table locks versus an open transaction#

An explicit transaction that has read, written or held a table protects that table's definition until it ends. A DDL on that table (ALTER TABLE, TRUNCATE, DROP TABLE, RENAME TABLE, CREATE OR REPLACE TABLE, DROP DATABASE) issued by another session in the meantime is refused right away (after automatic replay on the engine side: error 1213 in the end) rather than queued; the open transaction is not affected. Likewise, LOCK TABLES t WRITE (or READ as applicable) is refused immediately with 1213 if an open transaction already holds t in an incompatible way. None of these situations makes anyone wait: the first to arrive wins.

9.3 LOCK TABLES / UNLOCK TABLES#

Unlike the transactions above, LOCK TABLES is an explicit lock, taken at the client's request, and it is the only situation where a statement actually waits for another session (up to lock_wait_timeout seconds, 50 by default, then error 1205).

LOCK TABLES stocks WRITE, commandes READ;
-- ... statements that use stocks (read/write) and commandes (read-only) ...
UNLOCK TABLES;

WRITE reserves the table for the session that locks it (read and write); other sessions that need it wait up to lock_wait_timeout, then fail with 1205 (ER_LOCK_WAIT_TIMEOUT) — only their statement fails, their transaction stays open.
READ allows concurrent reads by other sessions but blocks their writes in the same way.
UNLOCK TABLES releases the tables locked by the session; closing a session or disconnecting releases them too.
KILL QUERY on a session waiting for a table lock gives 1317; KILL CONNECTION, 1927.

Typical use case: a batch process that must see one or more tables frozen for its whole duration without going through a full explicit transaction — for example a consistent export of several related tables, or an application maintenance operation that must exclude any other write while it runs.

SET [SESSION] lock_wait_timeout = n; changes the session's wait timeout (DEFAULT reverts to the server value, set by --lock-wait-timeout); SET GLOBAL lock_wait_timeout = n; changes it for subsequent sessions.

9.4 SAVEPOINT#

SAVEPOINT name is accepted by the syntax parser, but has no effect at all: it does not create an intermediate restore point in the transaction.

ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT are not supported in this version and return the error:

ERROR 1235: This version of MIRAJ doesn't yet support 'SAVEPOINT'

A transaction can therefore only be cancelled as a whole (ROLLBACK), never partially back to an intermediate point. An application that needs to cancel only part of its work must, for now, structure its logic into shorter transactions rather than rely on intermediate savepoints.

9.5 Three-valued logic (NULL in conditions)#

MIRAJ follows the SQL standard: a condition is not just TRUE or FALSE, but can also be UNKNOWN as soon as it compares a NULL value. A WHERE, HAVING or ON clause, or a CASE condition, retains a row only if its condition is TRUE — UNKNOWN is treated as FALSE for filtering, but remains distinct from FALSE in the result of a logical expression.

Pitfall 1: comparing with NULL#

SELECT * FROM clients WHERE email = NULL;      -- NEVER returns a row, even if email is NULL
SELECT * FROM clients WHERE email IS NULL;     -- the correct form
SELECT * FROM clients WHERE email IS NOT NULL; -- the inverse

column = NULL is always UNKNOWN, never TRUE, whatever the value of column: NULL represents an unknown value, and there is no way to know whether it equals another unknown value.

Pitfall 2: NOT on an UNKNOWN condition#

SELECT * FROM produits WHERE NOT (prix_promo > 10);

If prix_promo is NULL for a row, prix_promo > 10 is UNKNOWN, and NOT UNKNOWN is still UNKNOWN (not TRUE): the row is not returned, whereas one might expect "the opposite of a condition that fails" to bring the row back.

Pitfall 3: NULL in IN / NOT IN#

SELECT * FROM commandes WHERE client_id NOT IN (1, 2, NULL);

As soon as a NULL appears in the list of a NOT IN, the result is UNKNOWN for every row (including those whose client_id is neither 1 nor 2): the query never returns any row. IN with a NULL in the list can still return TRUE for a value that actually matches one of the other elements, but yields UNKNOWN (not FALSE) for the others. Prefer filtering the NULLs out of the list, or add AND client_id IS NOT NULL depending on the intended behavior.

Pitfall 4: aggregates and NULL#

SELECT COUNT(*), COUNT(remise) FROM ventes;

COUNT(*) counts all rows; COUNT(column) counts only the rows where column is not NULL. The functions SUM, AVG, MIN, MAX silently ignore NULL values (they treat them neither as zero nor as an error); SUM over an entirely NULL column (or with no rows) returns NULL, not 0.

Pitfall 5: joins and NULL#

An ON t1.a = t2.a clause never matches two rows whose a is NULL on both sides — as with = in general, NULL = NULL is UNKNOWN. This is intentional: two unknown values are not deemed equal.