Transactions group SQL statements into atomic units. Either all succeed or all fail together.

Basic Transaction

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Transaction with ROLLBACK

START TRANSACTION;

UPDATE inventory SET stock = stock - 1 WHERE product_id = 123;

INSERT INTO orders (product_id, quantity) VALUES (123, 1);

-- If any error occurs

ROLLBACK;

-- Or if successful

COMMIT;

SAVEPOINT

START TRANSACTION;

INSERT INTO orders (customer_id) VALUES (1);

SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 100);

-- If item insert fails, rollback only to savepoint

ROLLBACK TO order_created;

COMMIT;

Auto-commit

-- Check auto-commit status

SELECT @@autocommit;

-- Disable auto-commit

SET autocommit = 0;

ACID Properties

Atomicity: All or nothing

Consistency: Valid state to valid state

Isolation: Concurrent transactions don't interfere

Durability: Committed changes persist

Generate Transaction Logic

AI2sql helps structure safe transactions.