Transactions Basics

Introduction

A transaction groups SQL statements into one atomic unit—either all succeed or all roll back. That is how bank transfers and order checkout avoid half-updated state. This chapter covers ACID, BEGIN/COMMIT/ROLLBACK, autocommit, and SAVEPOINT using InnoDB tables in blog_dev.

Prerequisites

• Constraints and Keys
• InnoDB tables (default)—MyISAM does not support transactions

ACID Overview

autocommit Mode

Default on—each statement is its own transaction:

SELECT @@autocommit;

+---------------+
| @@autocommit  |
+---------------+
|             1 |
+---------------+

Single insert auto-commits on success.

Turn off for manual control (session scope):

SET autocommit = 0;

Remember to COMMIT or ROLLBACK, then restore:

SET autocommit = 1;

BEGIN and COMMIT

Transfer-style demo with account balances:

CREATE TABLE accounts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
 
INSERT INTO accounts (name, balance) VALUES
  ('Alice', 1000.00),
  ('Bob', 500.00);

Successful transfer:

START TRANSACTION;
 
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
 
COMMIT;

Verify:

SELECT name, balance FROM accounts;

Code explanation:

• START TRANSACTION same as BEGIN
• Other sessions see old balances until COMMIT (depending on isolation level)

ROLLBACK

START TRANSACTION;
 
UPDATE accounts SET balance = balance - 50 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 50 WHERE name = 'Bob';
 
ROLLBACK;

Balances unchanged from before transaction.

ROLLBACK on Error

Check constraint or business rule manually:

START TRANSACTION;
 
UPDATE accounts SET balance = balance - 2000 WHERE name = 'Alice';
 
-- Insufficient funds check in app; SQL demo:
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;
 
-- If balance would go negative, ROLLBACK instead of COMMIT
ROLLBACK;

Apps should validate before commit; FOR UPDATE locks row in transaction—locks chapter next.

SAVEPOINT

Partial rollback within transaction:

Code explanation:

• ROLLBACK TO sp2 undoes Alice overdraft attempt; keeps earlier updates in transaction
• RELEASE SAVEPOINT sp1 removes savepoint (optional)

DDL and Transactions

CREATE TABLE, ALTER cause implicit commit in MySQL—do not mix schema changes inside business transactions casually.

Order Checkout Preview

Ecommerce project pattern:

START TRANSACTION;
 
-- INSERT order header
-- INSERT order items
-- UPDATE inventory SET qty = qty - 1 WHERE sku = 'X' AND qty >= 1;
-- if affected rows = 0, ROLLBACK;
 
COMMIT;

Full schema in Project: E-Commerce Orders.

FAQ

COMMIT failed?

Connection lost before commit—transaction rolled back automatically.

autocommit 0 forgot COMMIT?

Session holds locks until commit—dangerous; always finish.

Transaction across multiple tables?

Yes—InnoDB participates in one transaction across tables same engine.

MyISAM transaction?

Not supported—use InnoDB.

JDBC transaction?

conn.setAutoCommit(false) … commit()—see JDBC transactions.

MongoDB transactions?

MongoDB multi-document transactions also require ACID semantics but need a replica set—MongoDB transactions. SQL skills transfer; syntax differs.

Nested transactions?

Savepoints simulate nesting; true nested commits not separate.