Motivation

• Crash recovery

  • Example: Transfer $1M from Susan to Jane

    S1: UPDATE Account SET balance = balance - 1000000 WHERE owner = 'Susan'

    S2: UPDATE Account SET balance = balance + 1000000 WHERE owner = 'Jane'

    System crashes after S1 but before S2. What now?

• Concurrency: multiple attempts to change one thing at the same time

Transaction

• A sequence of SQL statements that are executed as "one unit"

• Two key commands related to transaction

  • After a sequence of SQL commands, user can issue either COMMIT or ROLLBACk
  • COMMIT
    • "I'm done. Commit everything what I have done!"
    • All changes made by the transaction must be stored permanently
  • ROLLBACK
    • "I changed mind. Ignore what I just did!"
    • Undo all changes made by the transaction

  Creating a Transaction

  • All SQL commands until COMMIT/ROLLBACK become one transaction

  ACID Property of Transaction

  • DBMS guarantees ACID property on all transactions
  • Atomicity: "All or nothing"
    • Either ALL OR NONE of the operations in a transaction is executed
    • If system crashes in the middle of a transaction, all changes are "undone"
  • Consistency
    • If the database was in a "consistent"" state before transaction, it is still in a consistent state after the transaction
  • Isolation
    • Even if multiple transaction run concurrently, the final result is the same as each transaction runs in isolation in a sequential order
  • Durability
    • All changes made by "committed" transaction will remain even after system crash

Autocommit Mode

• Sometimes, it is too inconvenient to declare transactions explicitly
• Autocommit mode
  • When ON: Every SQL statement automatically becomes one transaction
  • When OFF: Transaction implicitly begins when any data in DB is read or written
    • All subsequent read/write is considered to be part of the same transaction
    • A transaction finishes when COMMIT or a ROLLBACK statement is executed

SQL Isolation Levels

• By default, RDBMS guarantees ACID for transactions
• Some applications may not need ACID and may want to allow minor "bad scenarios" to gain more "concurrency:
• By specifying "SQL Isolation Level". app developer can specify what type of "bad scenarios" can be allowed for their apps
  • Dirty read, non-repeatable read, and phantom

Dirty Read: Read from uncommitted changes

• May be OK sometimes, return the state since the last commit but not the current value

Non-repeatable Read: When Ti reads the same row multiple times, Ti may get different values

• READ UNCOMMITTED or READ COMMITTED isolation levels allow NON-REPEATABLE READ

Phantom: When new tuples are inserted, statements may or may not see (part of) them

• Preventing phantom can be very costly
• Exclusive lock on the entire table or a range of tuple