commit transaction ONLY when ALL queries succeed

I have a simple query

START TRANSACTION;

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

INSERT INTO Table_2 VALUES(5); 

ROLLBACK;

Basically, its 2 parts: 1st creates a table. 2nd inserts into table Table_2.

Table_2 does not exist hence causing an issue. BUT it still creates the table Persons and does NOT rollback.

The same can be done with simply 2 insert queries (1st the correct one and the 2nd a falsy one).

How do I properly create a transaction that will rollback everything if even 1 query fails?

Solution

Your approach is correct but it fails because MariaDB does not support transactional DDL statements. If your transaction would've consistent only of DML statements (INSERT, DELETE, UPDATE, SELECT etc.) it would've behaved as you expected.

This list contains all the statements that behave as if you did a COMMIT right before them (i.e. they perform an implicit commit). The most notable of these are:

CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
CREATE INDEX
DROP INDEX

A recent improvement in MariaDB 10.6 is the atomic DDL support. This means that even if these statements are not transactional (you can't roll them back) they are atomic: if you start a DDL statement and the server suddenly stops (e.g. due to a power outage), you'll be guaranteed to not corrupt your databse.