Database Systems Spring 2024 Instructor Jianer Chen Overview
Explore the concepts of database systems with a focus on dealing with system failures. Topics include lock tables, DDL language, concurrency control, file logging, recovery management, and more. Gain insights into managing processes concurrently running in the system, secondary storage, and DBMS functionalities. Delve into various aspects of database administration and programming for efficient system operations.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
CSCE-608 Database Systems Spring 2024 Instructor: Jianer Chen Office: PETR 428 Phone: 845-4259 Email: chen@cse.tamu.edu Notes 34: Dealing with system failures
lock table DDL language DDL complier database administrator concurrency control file logging & recovery manager transaction manager index/file manager buffer manager query execution engine database programmer DML (query) language DML complier main memory buffers secondary storage (disks) DBMS
lock table DDL language DDL complier database administrator concurrency control file logging & recovery manager transaction manager index/file manager buffer manager query execution engine database programmer DML (query) language DML complier main memory buffers secondary storage (disks) DBMS
lock table DDL language DDL complier database administrator concurrency control file logging & recovery manager transaction manager index/file manager buffer manager query execution engine database programmer DML (query) language DML complier main memory buffers System concerns on processes concurrently running in the system, not on individual process. secondary storage (disks) DBMS
lock table DDL complier concurrency control file logging & recovery manager User-1 DDL User-2 transaction manager User-3 index/file manager buffer manager query execution engine DML User-n DML complier main memory buffers secondary storage (disks) DBMS
lock table DDL complier concurrency control file logging & recovery manager User-1 DDL User-2 transaction manager User-3 index/file manager buffer manager query execution engine DML User-n DML complier main memory buffers secondary storage (disks) DBMS
lock table DDL complier concurrency control file logging & recovery manager User-1 DDL User-2 transaction manager User-3 index/file manager buffer manager query execution engine DML User-n DML complier main memory buffers secondary storage (disks) DBMS
lock table DDL complier concurrency control file logging & recovery manager User-1 DDL User-2 transaction manager User-3 index/file manager buffer manager query execution engine DML User-n DML complier main memory buffers secondary storage (disks) DBMS
lock table DDL complier concurrency control file logging & recovery manager User-1 DDL User-2 transaction manager User-3 index/file manager buffer manager query execution engine DML User-n DML complier main memory buffers secondary storage (disks) DBMS
Coping with System Failures System may fail because of Program or data (logic) errors Disk crashes Computer room fires World crashes System failures (power off, code execution errors )
Coping with System Failures System may fail because of Program or data (logic) errors Disk crashes Computer room fires World crashes System failures (power off, code execution errors ) Users errors, out of system s control
Coping with System Failures System may fail because of Program or data (logic) errors Disk crashes Computer room fires World crashes System failures (power off, code execution errors ) } Data backup
Coping with System Failures System may fail because of Program or data (logic) errors Disk crashes Computer room fires World crashes System failures (power off, code execution errors ) We can do nothing with it Backup in Mars?
Coping with System Failures System may fail because of Program or data (logic) errors Disk crashes Computer room fires World crashes System failures (power off, code execution errors ) Recovery programs and transition scheduling may help
Integrity/Correctness of Data Data be accurate/correct at all times Data satisfy all pre-given (logic) predicates
Integrity/Correctness of Data Data be accurate/correct at all times Data satisfy all pre-given (logic) predicates Examples: A is key of relation R A B holds for all attributes B of R Domain(B) = {Red, Blue, Green} a is a valid index for attribute C of R
Integrity/Correctness of Data Data be accurate/correct at all times Data satisfy all pre-given (logic) predicates Examples: A is key of relation R A B holds for all attributes B of R Domain(B) = {Red, Blue, Green} a is a valid index for attribute C of R Data reflect the real world Reality DB
Integrity/Correctness of Data Data in consistent state: Data that satisfy all (logic/realistic) constraints Consistent Database: Database that contains data in consistent state
Integrity/Correctness of Data Observation: A database cannot always keep consistent!
Integrity/Correctness of Data Observation: A database cannot always keep consistent! Example: Logic constraint: a1 + a2+ . an = sum Now suppose that we wanted to deposit $100 in a2. We perform: a2 a2 + 100; sum sum + 100 a2 50 150 150 sum 1000 1000 1100 Inconsistent!
Tranactions What is a transaction? A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist.
Tranactions What is a transaction? A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist. Disk is non-volatile:
Tranactions What is a transaction? A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist. Disk is non-volatile: So the results of a transaction should reach disk
Tranactions What is a transaction? A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist.
Tranactions What is a transaction? A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist. System cannot do much with this: Errors can be introduced by transaction writers
Tranactions What is a transaction? A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist. System cannot do much with this: Errors can be introduced by transaction writers We adopt the following (big) assumption: All transactions we deal with are consistent. Consistent DB T Consistent DB
Tranactions What is a transaction? A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist.
Tranactions What is a transaction? What other things can affect transaction ACID? A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist.
Tranactions What is a transaction? What other things can affect transaction ACID? Accident system failures may destroy Atomicity and Durability, thus Consistency; A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist.
Tranactions What is a transaction? What other things can affect transaction ACID? Accident system failures may destroy Atomicity and Durability, thus Consistency; Concurrent executions of transactions (which is highly desired) may affect Isolation, thus Consistency. A sequence of actions that we want to be done as a single logic step What is a logic step (what do we want a transaction to be)? ACID: Atomicity: either all steps of a transaction happen, or none happen Consistency: a transaction transforms a consistent DB into a consistent DB Isolation: execution of a transaction is isolated from that of other transactions Durability: if a transaction commits, its effects persist.
We will be focused on: How to prevent/fix constraint violations due to system failures due to concurrent executed transactions due to both
We will be focused on: How to prevent/fix constraint violations due to system failures due to concurrent executed transactions due to both
We will be focused on: How to prevent/fix constraint violations due to system failures How do we recover from system failures? due to concurrent executed transactions due to both
System Failure Recovery What we want to recover? Undesired changes made by transactions How can a transaction make undesired changes? Partial changes made by an incomplete transaction whose execution is interrupted because of system failure.
Computational Model CPU C=30 B=10 A=10 A=10 memory Input(A): Read from disk to memory disk 36 36
Computational Model CPU C=30 B=10 A=10 A=30 A=30 memory Output(A): disk Value gets changed in memory Write back from memory to disk 37 37
Computational Model CPU C=30 B=10 A=30 memory When memory shuts off, the changes in disk remain. disk 38 38
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model CPU C=30 B=10 A=10 memory disk 39 39
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 CPU C=30 B=10 A=10 memory disk 40 40
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=10 A=10 memory disk 41 41
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=10 A=10 A=10 memory disk 42 42
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=10 A=10 A=30 memory disk 43 43
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=10 B=10 A=10 A=30 B=30 memory disk 44 44
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=10 A=10 A=30 A=30 B=30 memory disk 45 45
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=10 A=30 A=30 A=30 B=30 B=30 memory disk 46 46
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=30 A=30 memory disk 47 47
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=10 A=10 What if the system crashes incidentally? memory disk 48 48
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=30 A=30 A=30 If the crash occurs after this point, we are good. B=30 memory disk 49 49
input (x): disk Bx memory Bx output (x): memory Bx disk Bx read (x,t): \\ do input(x) if necessary t value of x in memory Write (x,t): \\ do input(x) if necessary Bx in memory value of t Computational Model T1: A A 3 B B 3 read (A,t); t t 3; write (A,t); read (B,t); t t 3; write (B,t); output (A); output (B); CPU C=30 B=10 A=10 If the crash occurs before this point, we are probably fine: just redo T1. A=30 B=30 memory disk 50 50
