SQL Transactions Practice Guide

Understanding the Problem

We need to understand and work with SQL transactions in three scenarios:

• Transaction rollbacks and savepoints
• Connection interrupts during transactions
• Constraint violations in transactions

Devising a Plan

1. Analyze transaction flow in each scenario
2. Track balance changes and rollback effects
3. Understand transaction isolation
4. Implement constraint violation handling

Part 1: Understanding Rollbacks

Initial Table State

| id | first_name | last_name | balance |
|----|------------|-----------|----------|
| 1  | Amy        | Pond      | 2500    |
| 2  | Rose       | Tyler     | 600     |
| 3  | Martha     | Jones     | 3000    |
| 4  | Donna      | Noble     | 200     |
| 5  | River      | Song      | 1200    |

Transaction Analysis

Let's analyze the transactions in 01-transactions-rollbacks.sql step by step:

1. Amy to Rose: 200
   • Amy: 2500 - 200 = 2300
   • Rose: 600 + 200 = 800
2. Martha to Rose: 200
   • Martha: 3000 - 200 = 2800
   • Rose: 800 + 200 = 1000
3. Donna to Rose attempt (rolled back):
   • Rollback to martha_to_rose erases this
4. River to Rose: 200
   • River: 1200 - 200 = 1000
   • Rose: 1000 + 200 = 1200

Final Expected Balances

| id | first_name | last_name | balance |
|----|------------|-----------|----------|
| 1  | Amy        | Pond      | 2300    |
| 2  | Rose       | Tyler     | 1200    |
| 3  | Martha     | Jones     | 2800    |
| 4  | Donna      | Noble     | 200     |
| 5  | River      | Song      | 1000    |

Part 2: Connection Interrupts

Understanding Connection Behavior

When a connection is interrupted during a transaction:

• Changes are only visible within the transaction
• Other connections see the original state
• Transaction is automatically rolled back on connection loss

Testing Connection Interrupts

-- In first terminal:
.read 02-transactions-connection-interrupt.sql
SELECT * FROM accounts;  -- Shows partial transaction state

-- In second terminal:
SELECT * FROM accounts;  -- Shows original state

-- After closing first terminal:
SELECT * FROM accounts;  -- Shows original state (auto-rollback)

Part 3: Constraint Violations

Original File (No Rollback)

BEGIN TRANSACTION;
  UPDATE accounts
  SET balance = balance + 1000
  WHERE (first_name = 'Amy' AND last_name = 'Pond');

  UPDATE accounts
  SET balance = balance - 1000
  WHERE (first_name = 'Rose' AND last_name = 'Tyler');
COMMIT;

Modified File (With Rollback)

BEGIN TRANSACTION;
  UPDATE accounts
  SET balance = balance + 1000
  WHERE (first_name = 'Amy' AND last_name = 'Pond');

  UPDATE OR ROLLBACK accounts
  SET balance = balance - 1000
  WHERE (first_name = 'Rose' AND last_name = 'Tyler');
COMMIT;

Key Concepts

SAVEPOINT

SAVEPOINTs are like checkpoints in your transaction that you can return to:

• Create with: SAVEPOINT point_name;
• Return to with: ROLLBACK TO point_name;
• Useful for complex transactions with multiple steps

ROLLBACK

ROLLBACK can be used in two ways:

• Full rollback: Undoes entire transaction
• Partial rollback: Returns to specified SAVEPOINT

OR ROLLBACK Clause

Adding OR ROLLBACK to updates:

• Triggers full transaction rollback on constraint violation
• Prevents partial updates when constraints fail
• Maintains data integrity

Common Mistakes to Avoid

• Forgetting to commit transactions
• Not handling constraint violations
• Missing SAVEPOINT before risky operations
• Assuming automatic rollback on all errors

Best Practices

• Always use transactions for related updates
• Create SAVEPOINTs before risky operations
• Use OR ROLLBACK for constraint-sensitive updates
• Test transaction behavior with connection interrupts
• Verify final state matches expectations