Managing Transactions

Transactions keep related changes together. If one statement fails, the whole group rolls back, preserving a consistent state. The University schema benefits from transactions when registering students, assigning instructors, or seeding the database.

Learning Goals

• Use BEGIN, COMMIT, and ROLLBACK to wrap multi-statement changes
• Understand ACID guarantees in the context of the University database
• Apply savepoints and error handling to partial operations
• Choose isolation levels appropriate for academic workflows

Transaction Basics

Enroll a student in two courses atomically

BEGIN TRANSACTION;

INSERT INTO enrollment (enroll_id, student_id, course_id, semester, grade)
VALUES (1001, 5, 1, 'Fall 2025', NULL);

INSERT INTO enrollment (enroll_id, student_id, course_id, semester, grade)
VALUES (1002, 5, 2, 'Fall 2025', NULL);

COMMIT;

If the second INSERT fails (for example, due to a duplicate enroll_id), switch the final COMMIT to ROLLBACK to undo both inserts.

Rolling back on failure

BEGIN TRANSACTION;

INSERT INTO enrollment (enroll_id, student_id, course_id, semester, grade)
VALUES (1003, 5, 7, 'Fall 2025', NULL);

-- Suppose the course is full; detect the issue
SELECT capacity - COUNT(*) AS seats_remaining
FROM course_section cs
LEFT JOIN enrollment e ON e.course_id = cs.course_id AND e.semester = cs.term
WHERE cs.course_id = 7 AND cs.term = 'Fall 2025';

-- If seats_remaining < 1 then
ROLLBACK;
-- ELSE
--   COMMIT;

Savepoints

Savepoints allow partial rollbacks while keeping earlier work.

Mixing seed data with optional inserts

BEGIN TRANSACTION;

INSERT INTO department (dept_id, dept_name) VALUES (8, 'Aerospace Engineering');

SAVEPOINT after_department;

INSERT INTO course (course_id, course_code, course_title, credits, dept_id)
VALUES (14, 'AE201', 'Flight Mechanics', 3, 8);

-- If we detect a duplicate course code
ROLLBACK TO after_department;

-- Continue with other statements
INSERT INTO instructor (instructor_id, first_name, last_name, dept_id)
VALUES (21, 'Rita', 'Narayan', 8);

COMMIT;

Isolation Levels (Conceptual Overview)

Most teaching scenarios can run at the default READ COMMITTED level. For high-stakes operations—like batch posting of final grades—you may choose higher isolation:

• READ COMMITTED: Prevents reading uncommitted grade changes, usually sufficient.
• REPEATABLE READ: Ensures each instructor sees the same list of enrollments during grade entry.
• SERIALIZABLE: Strictest; transactions behave as if they run one after another.

Engines set isolation differently:

PostgreSQL example

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- grade publishing statements here
COMMIT;

MySQL example

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- critical updates
COMMIT;

SQLite uses BEGIN IMMEDIATE/BEGIN EXCLUSIVE to control locking because it lacks fine-grained isolation levels.

Best Practices in the University Schema

• Keep transactions short: Complete enrollment or grading steps quickly to avoid locking tables while advisors or faculty run reports.
• Validate first: Run SELECT statements to confirm prerequisites (e.g., seat availability) before starting the transaction.
• Log changes: Insert into an audit_log table within the transaction so you can trace who registered a course or changed a grade.
• Handle errors: Higher-level application code should catch exceptions and issue ROLLBACK.

Summary

• Group related statements with transactions so the University data remains consistent.
• Use savepoints when you can recover from a portion of the work failing.
• Pick an isolation level that balances accuracy with concurrency needs.
• Always match every BEGIN with either COMMIT or ROLLBACK.

Quiz

Show quiz

1. Which property ensures that both inserts succeed or fail together when enrolling a student in two courses?
   A) Consistency
   B) Atomicity
   C) Isolation
   D) Durability

2. Why would you use a savepoint when seeding new courses?
   A) To speed up inserts
   B) To roll back a subset of statements while keeping the rest
   C) To change isolation level mid-transaction
   D) To enable parallel writes

3. In PostgreSQL, how do you request a higher isolation level at the start of a transaction?
   A) SET isolation_level = 'REPEATABLE READ';
   B) BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   C) LOCK TABLE enrollment;
   D) BEGIN IMMEDIATE;

4. True or False: If a transaction finishes without ROLLBACK or COMMIT, the database automatically commits.
   A) True
   B) False

---

Answers

1. B
2. B
3. B
4. B