14. 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);
savepoints (SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT);
XA transactions, committed in two phases by an external coordinator;
three-valued logic (TRUE, FALSE, UNKNOWN) and its pitfalls with NULL.

14.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

Like every boolean SET variable, autocommit accepts only ON, OFF, 1, 0, TRUE, FALSE (case-insensitive) and DEFAULT; any other value (2, -1, 'x', 'yes', '', NULL) is refused (1231) and leaves the value unchanged.

START TRANSACTION / BEGIN#

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

START TRANSACTION options:

START TRANSACTION READ ONLY;              -- read-only transaction
START TRANSACTION READ WRITE;             -- default
START TRANSACTION WITH CONSISTENT SNAPSHOT;
START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT;   -- options separated by commas

In a READ ONLY transaction, any write is refused:

START TRANSACTION READ ONLY;
UPDATE comptes SET solde = 0 WHERE id = 2;

ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

Whatever its mode, a transaction opened by START TRANSACTION or by autocommit = 0 is subject to the concurrency rules described in §14.2 (WITH CONSISTENT SNAPSHOT is accepted: every transaction reads a consistent snapshot anyway).

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.

14.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, without taking any row lock. Only a row that the client explicitly asks to hold (SELECT … FOR UPDATE, see below) makes others 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)
A `SELECT … FOR UPDATE` on a row that another transaction holds or modifies	waits for that transaction to end, then reads the committed value
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 `SELECT … FOR UPDATE` that would wait for each other)	detected immediately: the one that would close the cycle receives 1213 and is rolled back; the other continues

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 does not wait: as soon as it is detected, the losing transaction is aborted immediately rather than queued behind the other, unless the row is held by a SELECT … FOR UPDATE: the write then waits, too, for the holder to finish.
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 whose 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 (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 returns it for a real deadlock between locking reads, and whenever a transaction is cancelled by a concurrency conflict: it is the signal that no data has been lost or corrupted, only that this particular transaction must be restarted from the beginning.

Example: session A reads a balance, session B modifies it and commits, then A writes in turn.

-- solde is 500
-- Session A                                   -- Session B
START TRANSACTION;
SELECT solde FROM comptes WHERE id = 1;       -- 500.00
                                                UPDATE comptes SET solde = solde + 50 WHERE id = 1;
                                                -- committed immediately (autocommit)
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
COMMIT;

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

The balance is then 550.00: transaction A is cancelled in its entirety, B's is kept. What A had read (500.00) was no longer true at commit time; replayed, it will read 550.00.

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
attempt := 0
repeat
    attempt := attempt + 1
    try
        START TRANSACTION;
        UPDATE comptes SET solde = solde - 100 WHERE id = 1;
        UPDATE comptes SET solde = solde + 100 WHERE id = 2;
        COMMIT;
        exit the loop                -- success
    catch error 1213
        ROLLBACK;                    -- as a precaution; the transaction is already cancelled server-side
        if attempt >= 5 then rethrow the error to the caller
        else restart the loop (ideally after a short random pause)

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.

Isolation level. There is only one, SERIALIZABLE (SELECT @@transaction_isolation returns SERIALIZABLE): SET TRANSACTION ISOLATION LEVEL … and the transaction_isolation variable are accepted (client tools send them on connection) but have no effect. Their scope is the one tools expect: without a keyword, SET TRANSACTION … (level, READ ONLY, READ WRITE) applies only to the next transaction and is refused during a transaction (ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress); SET SESSION TRANSACTION … applies to the session and SET GLOBAL TRANSACTION … to sessions opened afterward. The transaction_isolation and transaction_read_only variables (and their former names tx_isolation, tx_read_only) follow the same rule depending on the form written: SET @@transaction_read_only = 1, without scope, applies only to the next transaction (1568 during a transaction, and the value read by @@transaction_read_only does not change), whereas SET transaction_read_only = 1, SET SESSION transaction_read_only = 1 and SET @@session.transaction_read_only = 1 apply to the session, and SET GLOBAL … / SET @@global.… to sessions opened afterward. Their values are checked whatever the scope: transaction_isolation accepts only the four levels ('READ-UNCOMMITTED', 'READ-COMMITTED', 'REPEATABLE-READ', 'SERIALIZABLE', case-insensitive, or their rank from 0 to 3) and transaction_read_only only 0, 1, ON, OFF; any other value, NULL included, is refused (1231) and changes nothing.

Server concurrency model. This behavior is that of the default model, mvocc (optimistic multiversion control). miraj-server --concurrency pessimistic (or the concurrency key of miraj_config.xml) restores the former row-lock model, where a writer waits for the previous one and then fails with 1205; this is a temporary option, for comparing the two models, and this whole chapter describes mvocc. information_schema.MIRAJ_CONCURRENCY shows the model in use (MODEL column) and the current activity:

SELECT * FROM information_schema.MIRAJ_CONCURRENCY;

+-------+---------------------+-------+-----------+--------------------+-----------------+------------------+
| MODEL | ACTIVE_TRANSACTIONS | CLOCK | WATERMARK | OLDEST_TRANSACTION | OLDEST_SNAPSHOT | VERSIONS_PENDING |
+-------+---------------------+-------+-----------+--------------------+-----------------+------------------+
| mvocc |                   0 |    16 |        16 |               NULL |            NULL |                0 |
+-------+---------------------+-------+-----------+--------------------+-----------------+------------------+

What lies outside the transaction. A sequence (NEXTVAL, chapter 7) does not depend on it: a served value is never given back by ROLLBACK, and two transactions calling it at the same time do not interfere. Named locks (GET_LOCK, chapter 10) are the same: neither COMMIT nor ROLLBACK releases them, only the end of the session does. Disk tables (ENGINE = Aria, MyISAM, DISK) and system-versioned tables (chapter 12) obey the same rules as other tables; the history of a versioned table is written in the transaction of the modification and shares its fate.

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). A system-versioned table (chapter 12) is never concerned. 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 16.6.2).

Locking reads: `SELECT … FOR UPDATE`#

SELECT … FOR UPDATE (and FOR SHARE, LOCK IN SHARE MODE) reads the rows and holds them until the end of the transaction: no other transaction can write them or hold them in turn (FOR SHARE: other FOR SHARE readers remain allowed). This is the way to prevent the lost update of a "read then write":

START TRANSACTION;
SELECT solde FROM comptes WHERE id = 1 FOR UPDATE;   -- the row is held
-- ... application check of the balance ...
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
COMMIT;                                               -- the row is released

A row that is already held is waited for, as on the reference server: another SELECT … FOR UPDATE, or an UPDATE / DELETE of that row, waits for the end of the transaction that holds it, then runs on the value it committed. Two transactions that always take their rows in the same order therefore receive no error.
Deadlock: if the wait would close a cycle (A holds 1 and waits for 2, B holds 2 and asks for 1), the transaction that would close it immediately receives 1213 and is rolled back; the other continues.
Timeout: after innodb_lock_wait_timeout seconds (50 by default; a single value with lock_wait_timeout), the statement fails with 1205; only the statement fails, the transaction stays open with what it held. SET SESSION innodb_lock_wait_timeout = 5 changes this timeout.
NOWAIT: 1205 right away if a row is held (ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction). WAIT n: wait limited to n seconds. SKIP LOCKED: held rows are left out of the result (the "job queue" pattern).
KILL QUERY during the wait: 1317; KILL CONNECTION: 1927. SHOW PROCESSLIST shows the session in the Waiting for row lock state.
A row modified and committed by another transaction since the start of its own is not an error: Miraj checks that nothing the transaction has already read has changed, then lets it continue as if it had started at that moment, on the committed value. If a row it had already read has changed, it is a real conflict: 1213.
In a stored procedure (SELECT … INTO … FOR UPDATE, cursors), only the statement of the body waits: what the procedure has already done is not redone.

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.

14.3 LOCK TABLES / UNLOCK TABLES#

Unlike the transactions above, LOCK TABLES is an explicit lock, taken at the client's request; with SELECT … FOR UPDATE, 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.
The same applies to a statement waiting behind another session's long statement (write to a table being read, read of a table being written, DDL): this wait is bounded by the session's lock_wait_timeout (then 1205), and if interrupted during the wait, it writes nothing. A session that is stopped or a query that is interrupted before its commit commits nothing: the autocommit write is rolled back; a COMMIT interrupted by KILL QUERY returns 1317 and leaves the transaction open (COMMIT again, or ROLLBACK); KILL CONNECTION rolls the transaction back.

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.

14.4 SAVEPOINT#

SAVEPOINT name;
ROLLBACK [WORK] TO [SAVEPOINT] name;
RELEASE SAVEPOINT name;

SAVEPOINT name sets a restore point in the open transaction; a savepoint with the same name (case-insensitive) is replaced. With autocommit = 0, it opens the implicit transaction. In autocommit mode, outside a transaction, it has no effect: it only lives for the statement.
ROLLBACK [WORK] TO [SAVEPOINT] name undoes everything the transaction has written since the savepoint was set (rows, deferred UPDATEs, notifications), removes the savepoints set after it, and keeps this savepoint and the transaction open. Locks taken since remain held until the end of the transaction; the AUTO_INCREMENT counter is not restored.
RELEASE SAVEPOINT name removes the savepoint and those set after it, without undoing anything.
COMMIT and ROLLBACK erase all the savepoints of the transaction.

An unknown name returns:

ERROR 1305 (42000): SAVEPOINT name does not exist

Example:

START TRANSACTION;
INSERT INTO mouvements (article, qte) VALUES (7, -1);
SAVEPOINT avant_remise;
INSERT INTO mouvements (article, qte) VALUES (7, -5);
ROLLBACK TO SAVEPOINT avant_remise;   -- only the second row is undone
COMMIT;                               -- the first one is committed

Cluster edition: SAVEPOINT and ROLLBACK TO SAVEPOINT are refused (9041) in a transaction that writes the partitions of another node.

14.5 XA transactions#

XA transactions let an external transaction manager (application server, distributed transaction coordinator) commit in two phases a transaction that touches several resources: each resource prepares its branch, then the coordinator commits them all or rolls them all back.

XA {START | BEGIN} xid [JOIN | RESUME]
XA END xid [SUSPEND [FOR MIGRATE]]
XA PREPARE xid
XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid
XA RECOVER [FORMAT = 'RAW' | 'SQL']

xid is gtrid [, bqual [, formatID]]: gtrid and bqual are strings of at most 64 bytes ('text', X'0AFF', 0x0AFF), bqual defaults to empty and formatID to 1. A given XID can be held by only one branch at a time (1440).

A branch goes through three states:

State	Reached by	Allowed	Refused (1399)
`ACTIVE`	`XA START`	reads, writes, `SAVEPOINT`	`COMMIT`, `ROLLBACK`, `BEGIN`, DDL and any implicit commit, `XA ROLLBACK`
`IDLE`	`XA END`	`XA PREPARE`, `XA COMMIT … ONE PHASE`, `XA ROLLBACK`, `XA START … RESUME`, expressions and `SET`	any statement that reads or writes a table
`PREPARED`	`XA PREPARE`	`XA COMMIT`, `XA ROLLBACK`	any statement that reads or writes a table, `XA COMMIT … ONE PHASE` (1398)

XA START 'commande-42';
UPDATE stock SET qte = qte - 1 WHERE article = 7;
INSERT INTO mouvements (article, qte) VALUES (7, -1);
XA END 'commande-42';
XA PREPARE 'commande-42';
-- the coordinator has obtained the preparation of all the resources
XA COMMIT 'commande-42';

Preparation. XA PREPARE does everything that could make the commit fail: the transaction's reads are proved at that moment (§14.2) and the deferred updates are recomputed and written. If this proof fails, the branch is rolled back and XA PREPARE returns 1614. Once prepared, the branch can no longer fail at commit. Until XA COMMIT or XA ROLLBACK, its written rows remain held, and what it read remains true: a row that was read can be neither modified nor deleted, and no row can enter or leave a scanned range. A write that hits a prepared branch waits for its decision, up to innodb_lock_wait_timeout (1205).

Durability. XA PREPARE writes the branch's rows to the log of each database touched before replying. A prepared branch therefore survives a server shutdown, clean or abrupt: at the next opening, it is rebuilt with its rows held, and XA RECOVER lists it until a coordinator commits or rolls it back. A database that holds a prepared branch is opened at startup, even with lazy loading. Only the written rows regain their protection after a restart (reads do not, like the reference engine); in the PostgreSQL language, the branch's table locks (LOCK TABLE … IN [ACCESS] EXCLUSIVE MODE, TRUNCATE) are journaled with it and restored too.

Detached branch. A prepared branch survives the closing of its session (disconnection, KILL CONNECTION): it remains held by the server, row locks included, and XA RECOVER lists it. Any session (with no open transaction, autocommit at 1) then commits or rolls it back by its XID. As long as its session is open, a branch belongs to it alone (1397 for the others). An unprepared branch is rolled back when its session closes.

XA RECOVER returns one row per prepared branch: formatID, gtrid_length, bqual_length and data (gtrid followed by bqual, as raw bytes); with FORMAT = 'SQL', data is written as in an XA command ('a','b',7 or X'00ff',X'',2). After the XA PREPARE of the example above:

XA RECOVER;
XA RECOVER FORMAT = 'SQL';

+----------+--------------+--------------+-------------+
| formatID | gtrid_length | bqual_length | data        |
+----------+--------------+--------------+-------------+
|        1 |           11 |            0 | commande-42 |
+----------+--------------+--------------+-------------+

+----------+--------------+--------------+---------------+
| formatID | gtrid_length | bqual_length | data          |
+----------+--------------+--------------+---------------+
|        1 |           11 |            0 | 'commande-42' |
+----------+--------------+--------------+---------------+

Conflict. If the transaction of an ACTIVE branch is rolled back by a conflict (1213), the branch goes to ROLLBACK ONLY: any statement that touches a table, COMMIT, BEGIN, XA START, XA END and XA PREPARE return 1399 (… in the ROLLBACK ONLY state); XA COMMIT returns 1614 and closes the branch; XA ROLLBACK closes it.

Limits:

XA START … JOIN and XA END … SUSPEND are refused (1398), as on the reference engine;
Cluster edition: a transaction that writes the partitions of another node cannot be prepared (9041), nor can a transaction that writes a dedicated partition held locally (1235); a prepared branch lives on the primary: a promoted secondary does not know it.

14.6 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. To compare while treating two NULLs as equal, write <=> or IS [NOT] DISTINCT FROM; to test a condition itself, IS [NOT] TRUE, IS [NOT] FALSE, IS [NOT] UNKNOWN always return 1 or 0:

SELECT NULL = NULL AS a, NULL <=> NULL AS b, NULL IS NOT DISTINCT FROM NULL AS c,
       1 IS DISTINCT FROM NULL AS d, (NULL > 1) IS UNKNOWN AS e, (NULL > 1) IS NOT TRUE AS f;

+------+---+---+---+---+---+
| a    | b | c | d | e | f |
+------+---+---+---+---+---+
| NULL | 1 | 1 | 1 | 1 | 1 |
+------+---+---+---+---+---+

The UNKNOWN result is displayed as NULL. AND and OR follow the standard's truth table: NULL AND FALSE is 0 (false), NULL OR TRUE is 1 (true), the other combinations with NULL are NULL.

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.

SELECT 2 NOT IN (1, 3, NULL) AS a, 1 IN (1, NULL) AS b, 2 IN (1, NULL) AS c;

+------+---+------+
| a    | b | c    |
+------+---+------+
| NULL | 1 | NULL |
+------+---+------+

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#

-- ventes: three rows, remise is NULL, 5 and NULL
SELECT COUNT(*), COUNT(remise), SUM(remise), AVG(remise) FROM ventes;

+----------+---------------+-------------+-------------+
| COUNT(*) | COUNT(remise) | SUM(remise) | AVG(remise) |
+----------+---------------+-------------+-------------+
|        3 |             1 |        5.00 |    5.000000 |
+----------+---------------+-------------+-------------+

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.