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

Complete the following learning activities: (3.25–3.5 hours total)

  1. Read Fundamentals of Database Management Systems Chapter 11: Database Control Issues: Security, Backup and Recovery, Concurrency (60 minutes)
  2. 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
  3. Attend the class meeting (60 minutes)
  4. Optional: Watch Crazy Concurrency (3 minutes)
  5. Take the quiz (10 minutes)
  6. Continue work on the project website (60–75 minutes)
    • Complete the implementation of additional pages
    • Create a consistent theme for the site (using CSS)

Due

As a reminder, the following is due this lesson:

Resources

« Indexing and B+ trees

Transactions and Concurrency »