MariaDB Tutorial / Start InnoDB Transactions with COMMIT and ROLLBACK

A transaction groups related data changes into one unit of confirmation. In InnoDB, begin with START TRANSACTION and finish with COMMIT when the result is correct or ROLLBACK when it is not; the statements must share a connection and remain inside the uncommitted transaction boundary.

Define the changes that must succeed together

This exercise changes two balance values to demonstrate confirmation and cancellation without leaving only one side changed. It is a small transaction exercise rather than a complete payment implementation; real financial workflows additionally need a ledger, idempotency, authorization and audit design. Check Target Rows Before UPDATE and DELETE rolls back concrete updates/deletes and Compare Isolation Levels, Locks, and Deadlocks with Two Connections observes concurrent conflicts. The caller-controlled routines in Use Stored Procedures, Functions, and Triggers use the same transaction boundaries.

Check the MariaDB documentation for the syntax and conditions used here: start transaction.

Prepare an isolated InnoDB balance table

Connect as course_user to shopcourse and verify the server and database. Check that accounts_35_lab does not exist; otherwise inspect and preserve its contents before deciding whether it is disposable. Run the full setup before beginning either transaction experiment.

USE shopcourse;
CREATE TABLE accounts_35_lab (
 account_id INT PRIMARY KEY,balance DECIMAL(12,2) NOT NULL CHECK(balance>=0)
) ENGINE=InnoDB;
INSERT INTO accounts_35_lab VALUES(1,10000),(2,5000);

SELECT @@autocommit,@@in_transaction;
SHOW CREATE TABLE accounts_35_lab;

Confirm InnoDB and initial balances of 10000.00 for ID 1 and 5000.00 for ID 2. Run the sequence in one connection. A tool that changes connections or automatically commits individual statements changes the procedure’s meaning.

Inspect both updates before rolling back

START TRANSACTION;
SELECT account_id,balance FROM accounts_35_lab
WHERE account_id IN (1,2) ORDER BY account_id FOR UPDATE;
UPDATE accounts_35_lab SET balance=balance-3000
WHERE account_id=1 AND balance>=3000;
SELECT ROW_COUNT() AS debit_rows;

Continue only when debit_rows is 1. If it is zero or an error occurs, issue ROLLBACK; in this connection and do not execute the following credit UPDATE.

UPDATE accounts_35_lab SET balance=balance+3000 WHERE account_id=2;
SELECT ROW_COUNT() AS credit_rows;

credit_rows must also be 1. Otherwise issue ROLLBACK; and stop. Only after both checks succeed, read the balances below.

SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

Starting from the untouched fixture, debit_rows and credit_rows must each be 1, with balances 7000.00 and 8000.00. If a step affects the wrong number of rows, stop and ROLLBACK rather than continue the next update. The displayed SQL is a supervised exercise; a program must implement explicit error and row-count branches.

ROLLBACK;
SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

The balances return to 10000.00 and 5000.00. Distinguish your own visible uncommitted writes from confirmed changes. If they do not return, inspect connection identity and any automatic or intermediate COMMIT.

Repeat the checked changes and confirm with COMMIT

Repeat the debit, credit and verification blocks in that order, checking each affected-row count. This time, choose the following COMMIT instead of ROLLBACK only when both counts and balances match.

COMMIT;
SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

The committed balances are 7000.00 and 8000.00. An ordinary subsequent ROLLBACK no longer restores the original values. Repeat the exercise only through a deliberate fixture reset, and do not equate adding the opposite amount in real data with complete recovery.

Account for errors, locks and implicit commits

An error in one statement does not universally roll back the whole transaction. Handle each error type and the driver’s behavior explicitly. Keep locks short-lived and avoid waiting for user input while holding them.

DDL such as CREATE, ALTER, DROP and TRUNCATE has implicit-commit or other special boundaries. Nontransactional engines do not provide the same cancellation behavior as InnoDB, and an external API call or file transfer cannot be canceled merely by issuing a database ROLLBACK.

Finish open work before resetting the exercise

If execution stopped midway, issue ROLLBACK in the relevant connection and inspect @@in_transaction. Only when this table contains no needed data should it be dropped for a fresh run. Keep structural cleanup outside the rollback experiment.

ROLLBACK;
DROP TABLE shopcourse.accounts_35_lab;