Kekkei - Advancing Financial Science For Everyone

Introduction to Transactions

Imagine you're transferring $500 from your savings account to your checking account. Two things must happen:

Subtract $500 from savings
Add $500 to checking

What if the database crashes after step 1 but before step 2? You'd lose $500! This is why we need transactions - they ensure that related operations either all succeed together or all fail together.

A sequence of one or more SQL operations treated as a single unit of work. Either all operations complete successfully (commit), or none of them do (rollback).

Transactions guarantee data integrity by following the ACID properties (Atomicity, Consistency, Isolation, Durability). In this lesson, we'll focus on the practical commands: BEGIN, COMMIT, and ROLLBACK.

Transaction Commands

Command	Purpose	Example Use Case
BEGIN	Start a new transaction	BEGIN; (or BEGIN TRANSACTION;)
COMMIT	Save all changes permanently	COMMIT;
ROLLBACK	Undo all changes since BEGIN	ROLLBACK;
SAVEPOINT	Create a checkpoint within transaction	SAVEPOINT sp1;
ROLLBACK TO	Undo to a specific savepoint	ROLLBACK TO sp1;

Why Transactions Matter

Let's see what happens without transactions. Consider this banking scenario:

accounts

account_id	account_holder	balance
1	Alice	1000.00
2	Bob	500.00
3	Charlie	2500.00

Without transactions (DANGEROUS):

sql

-- Transfer $300 from Alice to Bob
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
-- CRASH HERE! Bob never gets the money!
UPDATE accounts SET balance = balance + 300 WHERE account_id = 2;

If the system crashes between the two UPDATEs, Alice loses $300 but Bob doesn't receive it. The money vanishes!

With transactions (SAFE):

sql

-- Transfer $300 from Alice to Bob (safely)
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 300 WHERE account_id = 2;
COMMIT;

Now, if the system crashes before COMMIT, both UPDATEs are automatically rolled back. The money transfer is all-or-nothing.

Rule: Always use transactions for operations that must happen together. If one fails, they all should fail.

BEGIN - Starting a Transaction

BEGIN (or BEGIN TRANSACTION) starts a new transaction. All subsequent SQL statements are part of this transaction until you COMMIT or ROLLBACK.

sql

-- Start a transaction
BEGIN;

-- These changes are not yet permanent
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
INSERT INTO transactions (account_id, amount, type)
VALUES (1, -100, 'withdrawal');

After BEGIN, changes exist in a "pending" state. Other database users can't see these changes yet (isolation). You can still query and modify data, but nothing is permanent until you COMMIT.

Different databases, different syntax:

PostgreSQL, MySQL, SQL Server: BEGIN; or BEGIN TRANSACTION;
Oracle, DB2: Transactions start automatically (implicit BEGIN)

COMMIT - Making Changes Permanent

COMMIT saves all changes made since BEGIN. Once committed, changes are permanent and visible to all users.

sql

-- Complete money transfer
BEGIN;

-- Deduct from Alice
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;

-- Add to Bob
UPDATE accounts SET balance = balance + 300 WHERE account_id = 2;

-- Make it permanent
COMMIT;

After COMMIT:

sql

SELECT account_id, account_holder, balance
FROM accounts
WHERE account_id IN (1, 2);

Alice now has $700 (down from$ 1000), Bob has $800 (up from$ 500). The transfer is complete and irreversible.

COMMIT ends the current transaction. If you want to make more changes, you need to start a new transaction with BEGIN.

ROLLBACK - Undoing Changes

ROLLBACK undoes all changes made since BEGIN, returning the database to its state before the transaction started.

sql

-- Oops, let's undo this!
BEGIN;

-- Accidentally delete all accounts!
DELETE FROM accounts WHERE balance > 0;

-- Wait, that was a mistake!
ROLLBACK;

After ROLLBACK, accounts table is unchanged:

sql

SELECT * FROM accounts;

All three accounts are still there! The DELETE was completely undone.

When to use ROLLBACK:

You made a mistake and want to undo it
An error occurred during the transaction
Business logic determines the operation should be cancelled
Testing - you want to run queries without saving changes

Transaction Flow Examples

Example 1: Successful order placement

products

product_id	product_name	stock
101	Laptop	10
102	Mouse	50

orders

order_id	product_id	quantity	order_date
1	102	2	2024-02-01

sql

-- Customer orders 3 laptops
BEGIN;

-- Check if we have enough stock
SELECT stock FROM products WHERE product_id = 101;
-- Returns 10, so we have enough

-- Create the order
INSERT INTO orders (order_id, product_id, quantity, order_date)
VALUES (2, 101, 3, '2024-02-05');

-- Reduce inventory
UPDATE products SET stock = stock - 3 WHERE product_id = 101;

-- Everything looks good!
COMMIT;

After COMMIT:

sql

SELECT * FROM products WHERE product_id = 101;

sql

SELECT * FROM orders WHERE order_id = 2;

Example 2: Failed order (insufficient stock)

sql

-- Customer tries to order 20 laptops
BEGIN;

-- Check stock
SELECT stock FROM products WHERE product_id = 101;
-- Returns 7 (from previous example)

-- Not enough stock! Cancel the transaction
ROLLBACK;

No order is created, stock remains at 7. The transaction is cancelled cleanly.

SAVEPOINT - Creating Checkpoints

SAVEPOINT creates a named checkpoint within a transaction. You can rollback to a savepoint without undoing the entire transaction.

sql

BEGIN;

-- Step 1: Deduct from Alice
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
SAVEPOINT after_deduction;

-- Step 2: Try to add to Bob
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
SAVEPOINT after_addition;

-- Step 3: Record transaction log
INSERT INTO transaction_log (from_account, to_account, amount)
VALUES (1, 2, 500);

-- Oops, error in the log entry! Undo just the INSERT
ROLLBACK TO after_addition;

-- Fix the log entry
INSERT INTO transaction_log (from_account, to_account, amount, timestamp)
VALUES (1, 2, 500, NOW());

-- Everything's good now
COMMIT;

SAVEPOINT benefits:

Partial rollbacks within a transaction
Test intermediate steps
Handle complex multi-step operations
Retry failed steps without starting over

Best practice: Use descriptive savepoint names like after_deduction, before_inventory_update, validated_input to make your code readable.

Auto-Commit Mode

Many databases run in auto-commit mode by default. Every SQL statement is automatically wrapped in BEGIN...COMMIT.

sql

-- Auto-commit ON (default in many databases)
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
-- This is automatically committed immediately!

Disabling auto-commit:

sql

-- MySQL
SET autocommit = 0;

-- SQL Server
SET IMPLICIT_TRANSACTIONS ON;

-- PostgreSQL (psql command-line)
\set AUTOCOMMIT off

Once auto-commit is disabled, you must explicitly use BEGIN, COMMIT, and ROLLBACK.

Be careful! If you disable auto-commit and forget to COMMIT, your changes will be lost when you disconnect. Always remember to COMMIT your work!

Transactions and Isolation Levels

When multiple users access the database simultaneously, isolation levels control how transactions interact:

Isolation Levels

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

Reading data that another transaction has modified but not yet committed. If that transaction rolls back, you've read invalid data.

Reading the same row twice in a transaction and getting different values because another transaction modified and committed it between your reads.

Re-running the same query and getting different rows because another transaction inserted or deleted rows.

Setting isolation level:

sql

-- Set isolation level for this transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Your queries here
COMMIT;

-- Or set for the entire session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Most databases default to READ COMMITTED, which prevents dirty reads but allows non-repeatable and phantom reads. This balances data integrity with performance.

Higher isolation levels (like SERIALIZABLE) provide stronger guarantees but reduce concurrency and performance. Choose based on your application's needs.

Transaction Best Practices

1. Keep transactions short:

sql

-- BAD: Long-running transaction locks resources
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- User goes to lunch, transaction still open!
-- 2 hours later...
COMMIT;

-- GOOD: Quick transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;  -- Done in milliseconds

2. Don't include user input within transactions:

sql

-- BAD: Waiting for user input
BEGIN;
UPDATE inventory SET quantity = 100 WHERE product_id = 5;
-- Prompt user: "Are you sure? (y/n)"
-- Transaction held open while waiting...
COMMIT;

-- GOOD: Get user confirmation BEFORE starting transaction
-- User confirms: YES
BEGIN;
UPDATE inventory SET quantity = 100 WHERE product_id = 5;
COMMIT;

3. Always handle errors:

sql

-- Pseudocode example
BEGIN;

TRY {
  UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

  IF (balance < 0) {
    ROLLBACK;
    THROW "Insufficient funds";
  }

  UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
  COMMIT;

} CATCH (error) {
  ROLLBACK;
  LOG error;
}

4. Use transactions for data consistency:

Transactions are essential when:

Transferring money between accounts
Creating an order and updating inventory
Deleting a parent record and its related child records
Multi-step registration processes
Any operation where partial completion would corrupt data

Golden Rule: If two or more SQL statements must execute as a unit (all succeed or all fail), use a transaction.

Common Mistakes to Avoid

Don't make these mistakes:

Forgetting to COMMIT: ❌
- You make changes, close the connection, changes are lost
- Always COMMIT when you want to save changes
Mixing transactional and non-transactional tables: ❌
- MySQL: InnoDB supports transactions, MyISAM doesn't
- If you mix table types, some changes can't be rolled back
Leaving transactions open: ❌
- Open transactions lock resources
- Other users can't access locked data
- Always COMMIT or ROLLBACK promptly
Not handling errors: ❌
- Errors during a transaction require explicit ROLLBACK
- Some systems auto-rollback, others don't
- Always handle errors explicitly
Using transactions for read-only queries: Usually unnecessary
- SELECT queries don't need transactions (unless you need consistent reads)
- BEGIN; SELECT * FROM users; COMMIT; wastes resources
Nesting transactions incorrectly: ❌
- Most databases don't support true nested transactions
- Use SAVEPOINTs for partial rollbacks instead
Over-using SERIALIZABLE isolation: ❌
- Highest isolation level, but slowest
- Can cause deadlocks and timeouts
- Only use when absolutely necessary
Assuming all SQL statements support transactions: ❌
- DDL statements (CREATE, DROP, ALTER) often auto-commit
- Can't rollback a DROP TABLE in most databases

Practical Examples

Example 1: Bank transfer with validation

sql

-- Transfer $200 from account 1 to account 2
BEGIN;

-- Check if source account has sufficient funds
SELECT balance FROM accounts WHERE account_id = 1;
-- Returns 1000.00, so we're good

-- Deduct from source
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;

-- Add to destination
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;

-- Log the transaction
INSERT INTO transaction_history (from_account, to_account, amount, timestamp)
VALUES (1, 2, 200, NOW());

-- Verify final balances are correct
SELECT account_id, balance FROM accounts WHERE account_id IN (1, 2);

COMMIT;

Example 2: E-commerce order processing

products

product_id	name	stock	price
201	Headphones	25	79.99
202	Webcam	15	129.99

sql

-- Customer buys 2 headphones and 1 webcam
BEGIN;

-- Create the order
INSERT INTO orders (customer_id, order_date, total)
VALUES (42, '2024-02-08', 289.97);

-- Get the order_id we just created
-- (In real code, you'd use LAST_INSERT_ID() or RETURNING)
SET @order_id = 1001;

-- Add order items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES
  (@order_id, 201, 2, 79.99),
  (@order_id, 202, 1, 129.99);

-- Update inventory
UPDATE products SET stock = stock - 2 WHERE product_id = 201;
UPDATE products SET stock = stock - 1 WHERE product_id = 202;

-- Verify we didn't go negative
SELECT product_id, stock FROM products WHERE product_id IN (201, 202);

COMMIT;

If any step fails (e.g., insufficient stock), the entire order is rolled back. No partial orders!

Example 3: Batch updates with rollback on error

sql

-- Apply 10% discount to all Electronics
BEGIN;

-- Save current state
CREATE TEMP TABLE price_backup AS
SELECT product_id, price FROM products WHERE category = 'Electronics';

-- Apply discount
UPDATE products
SET price = price * 0.9
WHERE category = 'Electronics';

-- Verify no price went below $5 (business rule)
SELECT COUNT(*) AS below_minimum
FROM products
WHERE category = 'Electronics' AND price < 5.00;

-- If any prices are too low, rollback
-- Otherwise, commit
COMMIT;

If below_minimum were greater than 0, you'd ROLLBACK instead.

Key Takeaways

What you learned:

✅ Transactions ensure related operations succeed or fail together ✅ BEGIN starts a transaction ✅ COMMIT makes changes permanent and visible to all users ✅ ROLLBACK undoes all changes since BEGIN ✅ SAVEPOINT creates checkpoints for partial rollbacks ✅ Transactions follow ACID properties (Atomicity, Consistency, Isolation, Durability) ✅ Isolation levels control how concurrent transactions interact ✅ Keep transactions short to avoid locking resources ✅ Always handle errors and rollback when needed ✅ Use transactions for any multi-step operation requiring data integrity

Real-world use cases:

Bank transfers
Order processing
Inventory management
User registration with profile creation
Deleting records with dependencies
Batch updates requiring validation

Coming up: You'll learn more about ACID properties in detail, understanding how databases guarantee reliability even in the face of crashes, power failures, and concurrent access!

Practice Exercise: Try these scenarios:

Write a transaction to transfer $500 from account 3 to account 1
Create a transaction that adds a new product and updates category counts
Practice using SAVEPOINT to undo only part of a transaction
Experiment with ROLLBACK - make changes, then undo them
Try different isolation levels and observe their behavior

Introduction to TransactionsFocusStart Focus Mode

Why Transactions MatterFocusStart Focus Mode

BEGIN - Starting a TransactionFocusStart Focus Mode

COMMIT - Making Changes PermanentFocusStart Focus Mode

ROLLBACK - Undoing ChangesFocusStart Focus Mode

Transaction Flow ExamplesFocusStart Focus Mode

SAVEPOINT - Creating CheckpointsFocusStart Focus Mode

Auto-Commit ModeFocusStart Focus Mode

Transactions and Isolation LevelsFocusStart Focus Mode

Transaction Best PracticesFocusStart Focus Mode

Common Mistakes to AvoidFocusStart Focus Mode

Practical ExamplesFocusStart Focus Mode

Key TakeawaysFocusStart Focus Mode