Home » Learn » MySQL Tutorial » MySQL Transactions

MySQL Transactions

A transaction is a group of SQL commands that are treated as one. Either all of them succeed, or none of them is saved. A classic example is moving money from one bank account to another.

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  balance DECIMAL(10,2)
) ENGINE = InnoDB;

INSERT INTO accounts VALUES (1, 'Robin', 1000), (2, 'Maria', 500);

Transactions work with the InnoDB storage engine, which is the default in MySQL. Read the interview question What are MySQL Storage Engines?

COMMIT: save the changes

START TRANSACTION;

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

COMMIT;
SELECT * FROM accounts;
Result
id name balance
1 Robin 800.00
2 Maria 700.00

ROLLBACK: cancel the changes

START TRANSACTION;

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

ROLLBACK;     -- something went wrong, undo

SELECT * FROM accounts;
Same as before: the change was cancelled
id name balance
1 Robin 800.00
2 Maria 700.00

ACID

LetterMeaning
AtomicityAll the steps happen, or none
ConsistencyThe data stays valid after the transaction
IsolationTransactions do not disturb each other
DurabilityAfter COMMIT the data is safe, even after a crash
Note: By default MySQL runs in autocommit mode: each single statement is saved at once. START TRANSACTION turns that off until you COMMIT or ROLLBACK. A DROP or ALTER command commits the transaction automatically.

Summary

  • START TRANSACTION, then your queries, then COMMIT to save or ROLLBACK to cancel.
  • Use transactions when many steps must succeed together.
  • Congratulations, you have finished the MySQL tutorial. Practise with the exercises.

Share this post:

Share on LinkedIn
Congratulations! Chapter Finished. Learn more about the similar topics:
Exercises & Assignments
No Content Found.
Interview Questions & Answers
No Content Found.