Unit 5: Transactions and Recovery
cheatsheet1999 opened this issue · 0 comments
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
- What is a sequence of reads and writes from/to a database called?
- Transaction
- How does a database achieve concurrency?
- A database can process multiple transactions concurrently by interleaving them.
- What are the four principles of database transactions?
- Atomicity, Consistency, Isolation, Durability
- 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
- involve the same actions of the same transcations.
- 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))
Knowledge check: Transactions and Concurrency Control
- 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
- 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
- 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