If databases only supported a single user at a time, they wouldn’t be very useful. In fact, most databases support concurrent access by hundreds of users! A database transaction represents a single unit of work and is independent of other transactions that may be running concurrently. This lesson describes the data anomalies that arise without transactions, the ACID properties to prevent those anomalies, and SQL’s transaction isolation levels.

Learning Objectives

  • Describe the following data anomalies:
    • lost update
    • dirty read
    • nonrepeatable read
    • phantom read
  • Define the following terms:
    • Atomicity
    • Consistency
    • Isolation
    • Durability
  • Describe the following transaction isolation levels
    • read uncommitted
    • read committed
    • repeatable read
    • serializable
  • Describe techniques used by relational databases to provide the ACID properties

How to Complete this Lesson

  1. Read Fundamentals of Database Management Systems Chapter 11: Database Control Issues: Security, Backup and Recovery, Concurrency (60 minutes)
  2. Watch Intro to Transactions (7 minutes)
  3. Watch Examples and Problems (7 minutes)
    • Note: This video uses the term inconsistent read instead of nonrepeatable read
  4. Complete the handout on data anomalies (20–30 minutes)
  5. Watch Serializability (6 minutes)
  6. Read the introduction to Transaction Isolation (5 minutes)
    • Note: You do not need to read the subsections – just the beginning of the section in the documentation
  7. Watch ACID Properties of Transactions (8 minutes)
  8. Respond to one of the prompts in the discussion forum (30 minutes)
  9. Optional: Watch Crazy Concurrency (3 minutes)
  10. Start the programming portion of the B+ tree assignment (30 minutes)
    • Find a partner and accept the GitHub Classroom assignment
    • Implement the find operation

Due

As a reminder, the following is due this lesson:

« Indexing and B+ trees Transactions and Concurrency »