SQL & Databases Transactions, Concurrency, and Administration Syllabus

Transactions, Concurrency, and Administration syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Transactions, Concurrency, and Administration in SQL & Databases, not a summary of it.

1. 1

   Transactions and ACID

   3 topics

   • ACID Properties
   • Transaction Lifecycle
   • Autocommit Behavior
2. 2

   Concurrency Control

   5 topics

   • Isolation Levels
   • Concurrency Phenomena
   • Locking
   • MVCC and Snapshot Isolation
   • Optimistic vs Pessimistic Locking
3. 3

   Security and Access Control

   5 topics

   • Authentication and Roles
   • Privileges and GRANT/REVOKE
   • Row-Level Security
   • SQL Injection Prevention
   • Encryption at Rest and in Transit
4. 4

   Backup, Recovery, and Reliability

   4 topics

   • Backup Strategies
   • Point-in-Time Recovery
   • Write-Ahead Logging
   • Replication and High Availability
5. 5

   Monitoring and Maintenance

   4 topics

   • Key Performance Metrics
   • VACUUM and Statistics Updates
   • Log Analysis and Slow Query Logs
   • Capacity Planning

Transactions, Concurrency, and Administration flashcards for SQL & Databases

19 of 51 cards from the Transactions, Concurrency, and Administration deck — real questions with worked answers.

1. What does the ACID acronym stand for in transaction processing?

   Atomicity, Consistency, Isolation, and Durability — the four guarantees that ensure reliable database transactions.

2. Define the Atomicity property of ACID.

   A transaction is all-or-nothing: either every operation commits, or if any part fails the entire transaction is rolled back, leaving no partial changes.

3. Define the Consistency property of ACID.

   A transaction moves the database from one valid state to another, preserving all defined rules, constraints, triggers, and referential integrity.

4. Define the Isolation property of ACID.

   Concurrently executing transactions do not interfere; each behaves as if it ran serially, so intermediate states are not visible to others.

5. Define the Durability property of ACID.

   Once a transaction commits, its changes are permanent and survive crashes or power loss, typically guaranteed by writing to a durable log on disk.

6. What SQL statements mark the beginning and successful end of an explicit transaction?

   BEGIN (or START TRANSACTION) starts it, and COMMIT makes its changes permanent; ROLLBACK undoes them.

7. What are the main states in a transaction's lifecycle?

   Active, Partially Committed (after final statement), Committed, Failed, and Aborted (rolled back), with an optional Terminated end state.

8. What is a SAVEPOINT and how is it used in a transaction?

   A named marker within a transaction that allows a partial ROLLBACK TO SAVEPOINT, undoing work after it without aborting the whole transaction.

9. What is autocommit mode in SQL?

   A mode where each individual statement is treated as its own transaction and committed immediately, unless an explicit transaction block is opened.

10. In autocommit mode, how do you group multiple statements into one atomic unit?

    Explicitly issue BEGIN/START TRANSACTION to suspend autocommit, then COMMIT or ROLLBACK to end the multi-statement transaction.

11. List the four standard SQL isolation levels from weakest to strongest.

    Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

12. What concurrency anomaly does the Read Uncommitted isolation level permit?

    Dirty reads (plus non-repeatable reads and phantoms) — it allows reading uncommitted changes from other transactions.

13. What is a dirty read?

    Reading a row that another transaction has modified but not yet committed; if that transaction rolls back, the read value never truly existed.

14. What is a non-repeatable read?

    When a transaction reads the same row twice and gets different values because another transaction committed an UPDATE in between.

15. What is a phantom read?

    When a transaction re-executes a range query and sees new rows that another committed transaction inserted, changing the result set.

16. What is a lost update anomaly?

    When two transactions read the same value, both compute updates from it, and one write overwrites the other, silently losing a change.

17. Which anomalies does Read Committed prevent and which does it still allow?

    It prevents dirty reads but still allows non-repeatable reads and phantom reads.

18. Which anomaly does Repeatable Read prevent that Read Committed does not?

    Non-repeatable reads; a transaction sees a stable snapshot of rows it has read, though phantoms may still occur in the SQL standard.

19. What guarantee does the Serializable isolation level provide?

    The strongest level: transactions appear to execute one at a time in some serial order, preventing dirty reads, non-repeatable reads, and phantoms.

See more Transactions, Concurrency, and Administration flashcards →

Planning Transactions, Concurrency, and Administration for SQL & Databases

Transactions, Concurrency, and Administration (SQL & Databases) FAQ