Why transactions are needed in SQL - See It in Action

Understanding Why Transactions Are Needed in Databases

📖 Scenario: You are managing a small online bookstore database. Multiple users can buy books at the same time. You want to make sure that the stock count updates correctly even if many purchases happen simultaneously.

🎯 Goal: Build a simple transaction example that shows how to safely update the stock of a book when a purchase happens, preventing errors like selling more books than available.

📋 What You'll Learn

Create a table called books with columns id, title, and stock

Insert one book with id = 1, title = 'Learn SQL', and stock = 10

Start a transaction to buy 3 copies of the book with id = 1

Update the stock by subtracting 3 only if enough stock is available

Commit the transaction to save changes

💡 Why This Matters

🌍 Real World

Online stores and inventory systems use transactions to keep stock counts accurate and avoid selling items that are out of stock.

💼 Career

Understanding transactions is essential for database administrators and developers to ensure data integrity and consistency in multi-user environments.

1

Create the books table and insert initial data

Write SQL code to create a table called books with columns id (integer primary key), title (text), and stock (integer). Then insert one row with id = 1, title = 'Learn SQL', and stock = 10.

SQL

-- Your code here to create the table and insert the book

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the book.

2

Start a transaction to buy books

Write SQL code to start a transaction using BEGIN TRANSACTION;.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  stock INTEGER
);

INSERT INTO books (id, title, stock) VALUES (1, 'Learn SQL', 10);

-- Start the transaction here
-- Your code here

Need a hint?

Use BEGIN TRANSACTION; to start the transaction.

3

Update stock only if enough books are available

Write an UPDATE statement to reduce the stock by 3 for the book with id = 1, but only if the current stock is at least 3. Use a WHERE clause to check this condition.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  stock INTEGER
);

INSERT INTO books (id, title, stock) VALUES (1, 'Learn SQL', 10);

BEGIN TRANSACTION;

-- Update stock if enough copies are available
-- Your code here

Need a hint?

Use UPDATE with a WHERE clause to check stock before subtracting.

4

Commit the transaction to save changes

Write SQL code to commit the transaction using COMMIT;.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  stock INTEGER
);

INSERT INTO books (id, title, stock) VALUES (1, 'Learn SQL', 10);

BEGIN TRANSACTION;

UPDATE books SET stock = stock - 3 WHERE id = 1 AND stock >= 3;

-- Commit the transaction
-- Your code here

Need a hint?

Use COMMIT; to save the changes made in the transaction.