---

Serial schedules and serializable schedules

---

• Review: correctness principle
  • Recall:

    A transaction will transform a database from a consistent state to another consistent state if: there are not system failures when the transaction is executed in isolation

    ---

    ---

  • Therefore:

    A number of transactions will transform a database from a consistent state to another consistent state if: there are not system failures each transaction is executed in isolation

  ---

  ---

  ---

  ---

• Definition: schedule
  • Schedule:

    Schedule = a sequence of (important) steps taken by one or more transactions

    ---

  • In concurrency control, the important steps made by transactions are:

    read operations (Read(X)) write operations (Write(X))

    ---

    ---

  • Example:

    We have 2 DB elements: A = 25 B = 25 (A = B) We have 2 transactions: T1 T2 ================================= READ(A, t) READ(A, s) t = t + 100 s = 2×s WRITE(A, t) WRITE(A, s) READ(B, t) READ(B, s) t = t + 100 s = 2×s WRITE(B, t) WRITE(B, s) Example of a schedule: READ1(A, t) // Subscript indicate the transaction READ2(A, s) t = t + 100 s = 2×s WRITE1(A, t) WRITE2(A, s) READ1(B, t) t = t + 100 WRITE1(B, t) READ2(B, s) s = 2×s WRITE2(B, t)

    Note:

    When T1 or T2 are executed in isolation: T1: READ(A, t) t = t + 100 WRITE(A, t) READ(B, t) t = t + 100 WRITE(B, t) T2: READ(A, s) s = 2×s WRITE(A, s) READ(B, s) s = 2×s WRITE(B, t) each transaction will preserve the property: A = B

  ---

  ---

  ---

  ---

• Serial schedule
  • Serial schedule:

    Serial schedule = a schedule where the actions of the same transactions are group together A serial schedule looks like this: Action by transaction 1 .... Action by transaction 1 Action by transaction 2 .... Action by transaction 2 ... ... Action by transaction k .... Action by transaction k

    ---

  • Example: serial schedule

    READ1(A, t) // Subscript indicate the transaction t = t + 100 WRITE1(A, t) READ1(B, t) t = t + 100 WRITE1(B, t) READ2(A, s) s = 2×s WRITE2(A, s) READ2(B, s) s = 2×s WRITE2(B, t)

  ---

  ---

  ---

  ---

• Representation of a serial schedule
  • We can represent a serial schedule by:

    A list of the transaction IDs

    ---

  • Example:

    Serial schedule: READ1(A, t) // Subscript indicate the transaction t = t + 100 WRITE1(A, t) READ1(B, t) t = t + 100 WRITE1(B, t) READ2(A, s) s = 2×s WRITE2(A, s) READ2(B, s) s = 2×s WRITE2(B, t) Representation (abriviation): T1 T2

  ---

  ---

  ---

  ---

• Database consistency and serial schedules
  • Fact:

    A serial schedule executes each transaction in isolation

    ---

    ---

  • Therefore:

    Every serial schedule will preserve the consistency (correctness) of the database

    ---

  • Conclusion:

    We can use a serial schedule as the basis for the definition of correctness for: Concurrent execution of transactions

  ---

  ---

  ---

  ---

• Serializable schedules
  • Serializable schedule:

    A schedule S is Serializable schedule iff: There is a serial schedule S' such that: The effect of the execution of S and S' are identical for every DB state

    ---

    ---

  • Example: serializable schedule

    T1 T2 A B ======================================================= READ(A, t) c c t = t + 100 WRITE(A, t) --------------------> c+100 READ(A, s) s = 2×s WRITE(A, s) ----> 2c+200 READ(B, t) t = t + 100 WRITE(B, t) ------------------------------> c+100 READ(B, s) s = 2×s WRITE(B, s) -------------> 2c+200

    Observe that:

    The interleaved execution (schedule): READ(A, t) t = t + 100 WRITE(A, t) READ(A, s) s = 2×s WRITE(A, s) READ(B, t) t = t + 100 WRITE(B, t) READ(B, s) s = 2×s WRITE(B, s) is equivalent to the following serial schedule: T1 T2 A B ======================================================= READ(A, t) c c t = t + 100 WRITE(A, t) --------------------> c+100 READ(B, t) t = t + 100 WRITE(B, t) ------------------------------> c+100 READ(A, s) s = 2×s WRITE(A, s) ----> 2c+200 READ(B, s) s = 2×s WRITE(B, s) -------------> 2c+200

    The schedule is therefore serializable

  ---

  ---

  ---

  ---

• Non-serializable schedules
  • Consider the following schedule:

    T1 T2 ================================= READ(A, t) t = t + 100 WRITE(A, t) READ(A, s) s = 2×s WRITE(A, s) READ(B, s) s = 2×s WRITE(B, s) READ(B, t) t = t + 100 WRITE(B, t)

    ---

    ---

  • The execution will result in an inconsistent database state:

    T1 T2 A B ======================================================= READ(A, t) c c t = t + 100 WRITE(A, t) --------------------> c+100 READ(A, s) s = 2×s WRITE(A, s) ----> 2c+200 READ(B, s) s = 2×s WRITE(B, s) -------------> 2c READ(B, t) t = t + 100 WRITE(B, t) ------------------------------> 2c+100

    At the end of the execution, we have:

    A = 2c + 100 B = c + 100

    This result can never be obtained by a serial execution of T₁ and T₂ !!!!

    Therefore:

    The schedule is not serializable !!!