Schedules Schedules In practice transactions often run concurrently

Schedules

Schedules In practice, transactions often run concurrently with other transactions, so the correctness principle doesn’t apply directly. But just allowing multiple transactions to simultaneously modify the database will likely lead to an inconsistent state. We use the notion of schedules to determine what transactions can occur at the same time without leading to inconsistent state. A schedule is a sequence of actions taken by one or more transactions.

Abstract Transactions Transaction (+ 100) Transaction (* 2) READ(A, t) READ(A, s) t += 100 s *= 2 WRITE(A, t) WRITE(A, s) READ(B, t) READ(B, s) t += 100 s *= 2 WRITE(B, t) WRITE(B, s) READ(A, t) represents reading the element A from the database and storing it in the variable t. WRITE(A, t) representing modifying the database to make element A set to the value of variable t. Consistency Constraint - A is always equal to B

Transaction (+ 100) Transaction (* 2) A B 25 25 READ(A, t) t += 100 WRITE(A, t) 125 READ(B, t) t += 100 WRITE(B, t) 125 READ(A, s) s *= 2 WRITE(A, s) 250 READ(B, s) s *= 2 WRITE(B, s) 250 250

Transaction (+ 100) Transaction (* 2) A B 25 25 READ(A, s) s *= 2 WRITE(A, s) 50 READ(B, s) s *= 2 WRITE(B, s) 50 READ(A, t) t += 100 WRITE(A, t) 150 READ(B, t) t += 100 WRITE(B, t) 150 150

Serial Schedules The two previous schedules are called serial schedules. A schedule is serial if its actions consist of all of the actions of one transaction, then all of the actions of another transaction and so on. No mixing allowed. Serial schedules preserve consistency as each transaction occurs in complete isolation. However, other schedules exist. A schedule is called serializable if there exists a serial schedule that would achieve the same final database state. This means a serializable schedule passes the ACID test.

Transaction (+ 100) Transaction (* 2) A B 25 25 READ(A, s) s *= 2 WRITE(A, s) 50 READ(A, t) t += 100 WRITE(A, t) 150 READ(B, s) s *= 2 WRITE(B, s) 50 READ(B, t) t += 100 WRITE(B, t) 150 150

Serializable Schedules According to The Correctness Principle, serial schedules will preserve consistency as each transaction occurs one after the other. But there are other ways to preserve consistency. Serializable schedules are schedules that preserve consistency having that same external effects as if they were serial.

With 3 transactions, many possible serial schedules are there? 3 6 9 It is not known

With 3 transactions, many possible serializable schedules are there? 3 6 9 It is not known

Non-serializable schedule Not all schedules are serializable. Non-serializable schedules can produce states / effects on the database that would not have occurred if transactions weren't interweaved. This violates the Isolation principle in ACID. The following schedule is non-serializable and schedules like it must be avoided.

Transaction (+ 100) Transaction (* 2) A B 25 25 READ(A, s) s *= 2 WRITE(A, s) 50 READ(A, t) t += 100 WRITE(A, t) 150 READ(B, t) t += 100 WRITE(B, t) 125 READ(B, s) s *= 2 WRITE(B, s) 250 150 250

Sometimes the computations matter Depending on what steps take place in a transaction, some schedules may be serializable.

Transaction (+ 100) Transaction (+ 200) A B 25 25 READ(A, s) s += 200 WRITE(A, s) 225 READ(A, t) t += 100 WRITE(A, t) 325 READ(B, t) t += 100 WRITE(B, t) 125 READ(B, s) s += 200 WRITE(B, s) 325 325

Arithmetic Coincidence In this case, one transaction was adding 100 to A and B. The other transaction was adding 200 to A and B. ◦ Because 100 + 200 = 200 + 100, the order and interweaving of the transactions doesn't violate serializability. ◦ This is called an arithmetic coincidence. Unfortunately, it is unrealistic for the scheduler to take such information into account. Transactions can include complicated SQL and other programming languages and knowing what effect they would have on the database is very difficult (see Halting Problem). The scheduler can see the read and write requests, and thus know what a transaction might do. ◦ Assumption: Any database element that a transaction writes is given a value that depends on the database state in such a way that no arithmetic coincidences occur.