Transactions

• The DBMS’s abstract view of a user program: a sequence of reads and writes
• SQL supports transactions behind the scenes
  • Each statement issued at the query interface is a transaction by itself
  • In programming interfaces like embedded SQL, a transaction begins the first time a SQL statement is executed and ends with the program or an explicit transaction-end

Ending

• COMMIT
  • Causes a transaction to complete and save impact
  • Can be explicit or implicit when a modification completes successfully
• ROLLBACK
  • Causes a transaction to end by aborting (nothing that changed is saved)
  • Acts like nothing changed
  • Can either be explicit or implicit when an error occurs

Schedule

• A list of operations ordered by time, performed by a set of transactions that are executed concurrently in the system
• Serial: Does not interleave the actions of different transactions
• We want the result to be equivalent to the serial schedule even when interleaving
  • Serializable schedule: A schedule that is equivalent to some serial execution of the transactions
  • Anomalies: Interleaved execution which leads to effects different from any serial execution
• Types of anomalies
  • Unrepeatable reads
    • When a tuple is deleted between two reads (that makes the two reads inconsistent with each other)
  • Dirty read
    • When data that was rolled back gets read
  • Phantom
    • When an unexpectedly new tuples appears between two reads

Isolation Level

• Choices about what interactions are allowed by transactions that execute at the same time
• How a database user can avoid anomalies
• Within a transaction we can say
  • SET TRANSACTION ISOLATION LEVEL [level]
  • Options
    • SERIALIZABLE
      • Highest level of isolation
      • A little bit concurrent but it is exactly the same as in isolation
      • The default
    • REPEATABLE READ
      • Prevents unrepeatable reads and dirty reads (but allows phantoms)
      • What you read, you will still be able to read but there might be more
    • READ COMITTED
      • Prevents dirty reads (but allows unrepeatable reads and phantoms)
      • Read everything that has made it to the database
    • READ UNCOMITTED
      • Allows all anomalies
      • You can see dirty data that never gets to the database
  • There is a balance between performance and consistency