💡 Ask Tutor

Chapter 19: Understanding Transactions (ACID Properties)

Imagine you’re developing an e-commerce application. A user places an order. This single “order placement” action might involve several steps in your database:

  1. Decrease stock_quantity for the ordered product(s) in the products table.
  2. Insert a new record into the orders table.
  3. Insert multiple records into the order_items table (one for each product in the order).
  4. Update the user’s last_activity in the users table.

What if step 1 and 2 succeed, but step 3 (inserting order items) fails due to a network error or a database crash? You’d have an order created, but no items associated with it, and stock might be incorrectly reduced. This leaves your database in an inconsistent and incorrect state.

This is where database transactions become absolutely essential. A transaction groups multiple SQL statements into a single, logical unit of work.

What is a Transaction?

A transaction is a sequence of SQL operations that are performed as a single logical unit. Either all of the operations in the transaction complete successfully (COMMIT), or none of them do (ROLLBACK). This guarantees the integrity of your data.

The ACID Properties

Database transactions are designed to adhere to a set of properties known as ACID:

  1. Atomicity: (All or Nothing)
    • This is the core principle. A transaction is treated as a single, indivisible unit. If any part of the transaction fails, the entire transaction is aborted (ROLLBACK), and the database is returned to its state before the transaction began. If all parts succeed, the transaction is committed (COMMIT), and changes become permanent.
    • Analogy: Transferring money between two bank accounts. Either the money is debited from one and credited to the other, or neither happens. You don’t want money to disappear from one account without appearing in the other.
  2. Consistency: (Valid State)
    • A transaction brings the database from one valid state to another valid state. It ensures that any data written to the database must be valid according to all defined rules, including constraints (e.g., PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK).
    • If a transaction attempts to violate a constraint, it is automatically rolled back.
  3. Isolation: (Independent Execution)
    • Concurrent transactions operate independently of each other. The changes made by one transaction are not visible to other transactions until the first transaction is committed. This prevents “dirty reads,” “non-repeatable reads,” and “phantom reads” in multi-user environments.
    • Analogy: Multiple people using ATMs simultaneously. Each person sees their account balance and transactions without interference from others’ ongoing transactions.
  4. Durability: (Permanent Changes)
    • Once a transaction has been committed, its changes are permanent and survive subsequent system failures (e.g., power outages, crashes). The committed data is written to persistent storage (disk) and will not be lost.

SQL Syntax & Examples (MySQL/MariaDB)

By default, MySQL runs in autocommit mode, meaning every SQL statement is treated as its own transaction and is automatically committed. To use transactions, you need to disable autocommit for a block of statements or explicitly start a transaction.

1. Starting a Transaction

Syntax:

SQL
START TRANSACTION;
-- OR (for older MySQL/MariaDB versions, or if you prefer)
BEGIN;

2. Committing a Transaction

Syntax:

SQL
COMMIT;

This makes all changes since START TRANSACTION permanent.

3. Rolling Back a Transaction

Syntax:

SQL
ROLLBACK;

This undoes all changes since START TRANSACTION, restoring the database to its state before the transaction began.

Example Scenario: Placing an Order

Let’s simulate an order placement process. We’ll decrease stock and create order records within a transaction.

Assume product_id = 1 (‘Laptop Pro’) has stock_quantity = 50.

SQL
USE scriptbuzz_db;

-- 1. Start the transaction
START TRANSACTION;

-- 2. Decrease stock quantity for Laptop Pro
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1;

-- 3. Insert a new order for user 1 (john_doe)
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 1200.00, 'processing');

-- Get the last inserted order_id (important for order_items)
-- This is a MySQL-specific function
SET @last_order_id = LAST_INSERT_ID();

-- 4. Insert the order item for Laptop Pro
INSERT INTO order_items (order_id, product_id, quantity, price_per_item)
VALUES (@last_order_id, 1, 1, 1200.00);

-- Now, let's verify the changes (they are not yet permanent!)
-- In a separate session, you might NOT see these changes until committed.
SELECT stock_quantity FROM products WHERE product_id = 1;
SELECT * FROM orders WHERE order_id = @last_order_id;
SELECT * FROM order_items WHERE order_id = @last_order_id;

-- DECISION POINT:
-- If everything went well (no errors), commit the transaction:
-- COMMIT;

-- If something went wrong (e.g., not enough stock, or an insert failed), roll back:
-- ROLLBACK;

Testing the Transaction:

  1. Run the START TRANSACTION and all INSERT/UPDATE statements.
  2. Verify (in the same session): The SELECT statements after the inserts/updates will show the changes.
    • Laptop Pro stock will be 49.
    • A new order and order item will exist.
  3. Open another MySQL client session (or another tab in Workbench) and run:
SQL
USE scriptbuzz_db;
SELECT stock_quantity FROM products WHERE product_id = 1;
SELECT * FROM orders ORDER BY order_id DESC LIMIT 1;
  • You should NOT see the updated stock or the new order in this second session, demonstrating Isolation.
  1. Go back to your first session and run ROLLBACK;
  2. Go back to your second session and re-run the SELECT statements.
    • Laptop Pro stock should be back to 50.
    • The new order and order item will be gone. This demonstrates Atomicity.
  3. Alternatively, run COMMIT; in the first session.
  4. Go back to your second session and re-run the SELECT statements.
    • Now you should see the updated stock and new order, demonstrating Durability.

4. SAVEPOINT (Advanced Transaction Control)

SAVEPOINT allows you to set a named point within a transaction to which you can ROLLBACK without rolling back the entire transaction.

SQL
START TRANSACTION;

-- First set of operations
INSERT INTO users (username, email, password) VALUES ('temp_user1', 'temp1@example.com', 'pass');
SAVEPOINT sp1; -- Set a savepoint

-- Second set of operations (maybe problematic)
INSERT INTO users (username, email, password) VALUES ('temp_user2', 'temp2@example.com', 'pass');
-- Oh no, let's say we made a mistake here, or the next step failed
-- Maybe: UPDATE non_existent_table SET col = 1; -- This would cause an error

ROLLBACK TO SAVEPOINT sp1; -- Undoes only changes made AFTER sp1

-- Now continue with other operations
INSERT INTO users (username, email, password) VALUES ('temp_user3', 'temp3@example.com', 'pass');

COMMIT; -- Commits changes up to sp1, plus temp_user3

SAVEPOINT is useful for complex transactions where you might want to retry parts or handle specific errors without discarding all work.

Common Pitfalls and Best Practices

  • Long-Running Transactions: Avoid holding transactions open for too long, especially in a high-concurrency environment. Long transactions can:
    • Consume more resources.
    • Increase lock contention, slowing down other operations.
    • Increase the risk of deadlocks (two transactions blocking each other).
  • Missing COMMIT or ROLLBACK: Always explicitly COMMIT or ROLLBACK your transactions. If your application crashes or the connection drops before a COMMIT, the database might automatically ROLLBACK depending on its configuration, but it’s not guaranteed.
  • Error Handling: In application code, ensure that any database error encountered during a transaction leads to a ROLLBACK. A COMMIT should only happen if all operations succeed.
  • Transaction Scope: Design your transactions to be as small and focused as possible while still ensuring atomicity for the logical unit of work.
  • Transaction Isolation Levels: Databases offer different isolation levels (e.g., READ COMMITTED, REPEATABLE READ, SERIALIZABLE). These levels determine how much one transaction “sees” the uncommitted changes of other transactions. REPEATABLE READ is the default for InnoDB in MySQL. Understanding these is crucial for advanced concurrency control.

Notes:

  • A transaction is a sequence of SQL operations treated as a single, indivisible unit.
  • ACID Properties (Atomicity, Consistency, Isolation, Durability) ensure reliable transaction processing.
  • START TRANSACTION; (or BEGIN;) initiates a transaction.
  • COMMIT; makes all changes permanent.
  • ROLLBACK; undoes all changes since the transaction started.
  • Transactions are essential for maintaining data integrity, especially when multiple database operations depend on each other.
  • Avoid long-running transactions to minimize resource consumption and lock contention.

Small Exercise

Using your scriptbuzz_db:

  1. Start a transaction.
  2. Update the price of ‘Laptop Pro’ (product_id = 1) to 1350.00.
  3. Attempt to insert a new user with a username that you know already exists (if you have a UNIQUE constraint on username). Observe the error.
  4. Rollback the transaction.
  5. Verify that the price of ‘Laptop Pro’ is still its original value and the duplicate user was not inserted.
  6. Now, start a new transaction.
  7. Increase the stock_quantity of ‘Wireless Mouse’ (product_id = 2) by 50.
  8. Insert a new product called ‘Smart Watch’ with a price of 299.99 and stock_quantity of 100.
  9. Commit the transaction.
  10. Verify that both the stock quantity is updated and the new product exists.

You’ve now tackled transactions, a fundamental concept for building robust and reliable database applications. This knowledge is crucial for handling complex logic and ensuring data integrity! Next, we’ll look at database views.

📘 Continue to the Next Chapter

Simplifying Queries with Views

Learn how SQL Views can simplify complex queries by creating virtual tables, and discover best practices for managing and using views effectively.

➡️

🚀 Explore Popular Learning Tracks