AI Hints
Homeโ€บSQL Tutorialโ€บRules, Safety, and Speed
SQL ยท Lesson 75 of 126

Transactions

All or nothing.

โฑ 2 min readโ–ถ Video at 1:45:50๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

When a customer buys a book, two things must happen. The stock goes down, and a new order is created. What if the power cuts out halfway? The stock went down, but there's no order. A transaction makes a group of changes all or nothing.

Transactions
From the video: Transactions
  • Stock down, order created
  • Power cut halfway?
  • All, or nothing

BEGIN and ROLLBACK

Begin starts a transaction. Now let's make a terrible mistake. Delete every customer. Inside the transaction, the count is zero. But then, rollback cancels everything since begin. Run it. Zero inside the transaction, and all five hundred customers are back after the rollback.

query.sqlsql
BEGIN;
DELETE FROM customers;
SELECT COUNT(*) AS inside_transaction FROM customers;
ROLLBACK;

SELECT COUNT(*) AS after_rollback FROM customers;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ inside_transaction โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 0                  โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ after_rollback โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 500            โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

COMMIT or ROLLBACK

A transaction ends one of two ways. Commit saves every change, permanently. Rollback throws them all away. If anything goes wrong in the middle, the database rolls back automatically, so you never end up half done.

COMMIT

  • Saves all changes
  • Permanent
  • Everything worked

ROLLBACK

  • Undoes all changes
  • Back to BEGIN
  • Something went wrong

Summary

Begin, then your changes, then commit to save, or rollback to undo. Next, saving a query so you can reuse it.

Key takeaways
  • BEGIN
  • COMMIT saves
  • ROLLBACK undoes
Next up: Views and ALTER TABLE โ€” saved queries and new columns