Unit 5: Transactions and Recovery

[cheatsheet1999]

Lesson Introduction: ACID Properties

Topic: Principles of Transactions: ACID Properties

Atomicity:

• A transaction might commit after completing all its actions, or it could abort after excuting some actions
• Always executing all actions, or not excuting any actions at all
• DBMS logs all actions so that it can undo the actions of aborted transacations.

Consistency

• No violation of integrity constrains
• A transaction which executes alone against a consistent database leaves it in a consistent state.
• Transactions do not violate database integrity constrains.
• Transactions are correct programs.

Isolation (Transacation are isolated or protected)

• Serializability: If several transcations are executed concurrently, the results must be the same as if they were executed serially in some order.
• Incomplete results: An incomplete transcation cannot reveral its results to other transcations before its commitment.

Durability

• Once the transaction commits, the system must guarentee that the results of its operations will never be lost.
• Database recovery.

A transaction is seen by DBMS as a series, or list of actions. The action can read and write

Knowledge Check: ACID Properties

1. What is a sequence of reads and writes from/to a database called?

• Transaction

2. How does a database achieve concurrency?

• A database can process multiple transactions concurrently by interleaving them.

3. What are the four principles of database transactions?

• Atomicity, Consistency, Isolation, Durability

4. Which of the following describes the atomicity principle of database transactions?

• All of the components of a transaction run in their entirety, or none of them run at all.

Topic: Concurrency Control

We call such interleave - Schedule

Serial Schedule

• Schedule that does not interleave the actions of different transactions. T1 first, and then T2, or T2 first and then T1, NOT interleave each other.

Equivalent Schedule

• The effect of executing first schedule is the same as the effect the second schedule

Serializable Schedule

• A schedule that is equivalent to some serial execution of the transcations

Conflict Serializable Schedules

• Two schedules are conflict equivalent if

  1. involve the same actions of the same transcations.
  2. Every pair of conflicting actions is ordered the same way

• Schedule S is conflict serializable if S is conflict equivalent to some serial schedule

Dependency Graph

• if there's NO CYCLE in the graph, that schedule is conflict serializable, which is great 👍
• If there is a cycle, that is not conflict serializable, which might lead inconsistency in the database.

🔼 This is serialiazble

🔼 This is not conflict serializable

🔼 This is not serializable T1 => T2 (R(A), W(A)) T2 => T1 (R(B), W(B))

Example

Knowledge check: Transactions and Concurrency Control

1. What is the result of the following database transaction if the initial values of both A and B is 100?

BEGIN

A=A+100

B=A+100

END

• A=200, B=300
  Explanation : A = 100 + 100 = 200 B = 200 + 100 = 300

---

2. Consider the following two database transactions with initial values of A=500 and B=500:

T1: A=A+100, B=A-100

T2: A=A1.06, B=B1.06

Assuming T1 arrives first, what will be the final values of A and B if the two transactions are processed using a serial schedule?

• A=636, B=530
  In a serial schedule, T1 is processed in its entirety first and then T2 is processed.
  T1: A = 500 + 100 = 600 B = 600 - 100 = 500
  T2: A = 600 * 1.06 = 636 B = 500 * 1.06 = 530

---

3. Consider the following two database transactions with initial values of A=500 and B=500:

T1: A=A+100, B=A-100

T2: A=A1.06, B=B1.06

Assuming T1 arrives first, what will be the final values of A and B if the database management system interleaves transactions?

• A=636, B=568.16
  Starting with T1 and then interleaving after the first part to T1, these are the final values of A and B.

T1: A = 500 + 100 = 600 T2: A = 600 * 1.06 = 636 T1: B = 636 - 100 = 536 T2: B = 536 * 1.06 = 568.16