Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Midterm II Review - Database System Implementation | CS 4420, Exams of Computer Science

Georgia Institute of Technology - Main CampusComputer Science

Material Type: Exam; Class: Database Sys Implement; Subject: Computer Science; University: Georgia Institute of Technology-Main Campus; Term: Unknown 1989;

Typology: Exams

Pre 2010

Uploaded on 08/05/2009

koofers-user-7su-1
koofers-user-7su-1 🇺🇸

10 documents

1 / 24

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
1
DB20 Midterm 2 Review
CS 4420 Database System Implementation
Midterm 2 Review
Ling Liu
Associate Professor
College of Computing, Georgia Tech
2
DB20 Midterm 2 Review
Midterm 2 Coverage
Chapter 8: (read all sections)
Chapter 9:
zskim 9.8
Chapter 10:
zRead 10.1, 10.2, 10.3
zskim 10.4, 10.5, 10.6, 10.7
Chapter 11
zSkim 11.2
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18

Partial preview of the text

Download Midterm II Review - Database System Implementation | CS 4420 and more Exams Computer Science in PDF only on Docsity!

1

CS 4420 Database System Implementation

Midterm 2 Review

Ling Liu Associate Professor College of Computing, Georgia Tech

DB20 (^) Midterm 2 Review

Midterm 2 Coverage

„ Chapter 8: (read all sections)

„ Chapter 9:

z skim 9.

„ Chapter 10:

z Read 10.1, 10.2, 10. z skim 10.4, 10.5, 10.6, 10.

„ Chapter 11

z Skim 11.

3

Failure Modes

„ Erroneous data entry: caught by DBMS through

triggers, key, foreign key, value and tuple based

constraints …

„ Media failures:

z Local: caught by parity checks on sectors, main memory z Disk crash: handled by RAID

„ System Crashes

z Problem is that memory loss wipes out process state and data – what if the process had already made some modifications to the database?

DB20 (^) Midterm 2 Review

How can we prevent/fix violations?

„ Chapter 8: due to failures only

„ Chapter 9: due to data sharing only

„ Chapter 10: due to failures and sharing

Correctness (informally)

„ If we stop running transactions,

DB left consistent

„ Each transaction sees a consistent DB

7

Logging Schemes

„ Undo only

z Log entries contain only old values z A crash before transaction finishes, the log tells us how to restore old values for any DB elements changed on disk z Important Principle: WAL Æ log records for an item X must be on disk before any DB modification to X is flushed to disk ÆBefore commit record flushed to disk, all DB modifications must be flushed to disk.

„ Weakness:

z Undo log cannot bring backup DB copies to date

DB20 (^) Midterm 2 Review

Logging Schemes

„ Redo only

z Log entries contain only new values z Before writing modifications to disk, transaction must be committed and COMMIT record must be written to log z Before writing modifications to disk, flush all log records involving X (including commit) to disk.

„ Weakness:

z Redo logging need to keep all modified blocks in memory until commit.

9

Checkpointing

„ Problem: In principle recovery requires looking at entire log „ Simple solution: occasional checkpoint operation z Stop accepting new transactions z Wait for all current transactions commit or abort z Flush log to disk and all DB updates in memory buffer to disk z Enter checkpoint record in the log record and flush to disk. „ Advantage: z If recovery is necessary after this point, all transactions prior to a checkpoint have committed and need not be undone.

DB20 (^) Midterm 2 Review

Exercise 1: what to do at recovery?

Undo log (disk):

<T1,A,8> <T1,commit> Checkpoint<T2,B,8> <T2,commit>

<T3,C,8>

Crash ... ... ... ... ... ...

Redo log (disk):

<T1,A,16> <T1,commit> Checkpoint<T2,B,17> <T2,commit> <T3,C,21>

Crash ... ... ... ... ... ...

Undo cannot bring backup DB copies up to date

Redo need to keep all DB blocks in memory until commit

13

Exercise 2 Examine the schedule given below. There are three transactions, T1, T2, and T3. Initially, salary = 1 and tax = 2. The assignments happen within the local memory space of the transactions and the effects of these assignments are not reflected in the database until the WRITE operation.

--------------------------------------------------------------------^ T1^ T2^ T 01 startREAD tax (^23) start tax := tax + 1 45 READ salarysalary := salary + 1 67 WRITE taxcommit 89 startREAD tax 1011 READ salarytax := tax + salary 1213 WRITE taxcommit 1415 ------------------- checkpoint start --------------------------READ tax 16 tax := tax + salary 1817 WRITE salary------------------- checkpoint end -------------------------- 19 commit a) Show the undo/redo log file entries that would be generated by this execution. For each log entry, indicate what line above generates it.

<0, T3, start> <3, T1, start> <6, T3, tax, 2, 3> <7, T3, commit> <8, T2, start> <12, T2, tax, 3, 4> <13, T2, commit> <14, checkpoint start , T1 is still active> <17, T1, salary, 1, 2> <18, checkpoint end> <19, T1, commit>

DB20 (^) Midterm 2 Review

Exercise 2 (Cont)

„ b) Assume that the undo/redo recovery

algorithm with checkpoints is being used. The

database crashes immediately after statement

7. (Assume that all the log records up to this

point are on disk.)

z b.1) Which transactions would have to be undone?

z b.2) Which transactions would have to be redone?

Answer: T

Answer: T

15

Exercise 2 (Cont)

„ c) Again assume that the undo/redo recovery

algorithm with checkpoints is being used, but now

the databases crashes just after statement 18.

(Assume that all the log records up to this point are

on disk.)

z c.1) Which transactions would have to be undone?

z c.2) Which transactions would have to be redone?

Answer: T

Answer: None

DB20 (^) Midterm 2 Review

Concepts

Transaction: sequence of r i(x), wi(x) actions

Conflicting actions: r 1(A) w2(A) w1(A)

w2(A) r 1(A) w2(A)

Schedule: represents chronological order in which

actions are executed

Serial schedule: no interleaving of actions

or transactions

19

Exercise 3:

T1: Read(A) T2: Read(A)

A ← A+100 A ← A× 2

Write(A) Write(A)

Read(B) Read(B)

B ← B+100 B ← B× 2

Write(B) Write(B)

Constraint: A=B

DB20 (^) Midterm 2 Review

Question(a): is this schedule conflict serializable? Which serial schedule it is equivalent to?

T1 T Read(A); A ← A+ Write(A);

Read(A);A ← A×2;

Write(A); Read(B); B ← B+100; Write(B);

Read(B);B ← B×2;

Write(B);

Answer: Yes. It is equivalent to serial schedule of T proceeding T

T

T

A, B

21

A Serial Schedule

T1 T

Read(A); A ← A+ Write(A); Read(B); B ← B+100; Write(B);

Read(A);A ← A×2;

Write(A);

Read(B);B ← B×2;

Write(B);

T

T

A, B

DB20 (^) Midterm 2 Review

Question (b): is this schedule conflict serializable? Which serial schedule it is equivalent to?

T1 T Read(A); A ← A+ Write(A); Read(A);A ← A×2; Write(A); Read(B);B ← B×2; Write(B); Read(B); B ← B+100; Write(B);

Answer: Not serializable, and it is NOT equivalent to any serial schedule

T

T

B

A

25

Conflict-Serializable Schedule

(Three Rules 1, 2, 3)

Rule #3 Two phase locking (2PL) for transactions Ti = ……. li(A) ………... ui(A) ……...

no unlocks no locks

Rule #1: Well-formed transactions Ti: … li(A) … pi(A) … ui(A) ...

Rule #2 Legal scheduler

S = …….. li(A) ………... ui(A) ……...

no lj(A)

DB20 (^) Midterm 2 Review

Two-phase Locking (2PL) Protocol To guarantee serializability:

  • In a transaction, all lock operations (S_Lock or X_Lock) precede the first unlock operation.
  • No locks can be acquired after the first lock is released. We call a transaction satisfying two-phase locking protocol, if it obeys the above rules. Two-phase execution
    • Growing phase : lock acquisition only (no unlock)
    • Shrinking phase : lock release (no more lock)
    • Lock point divides the two phases.

Obtain lock Release lock

Lock point

Phase 1 Phase 2 BEGIN END

27

Deadlock Avoidance: Conservative 2 PL

Conservative 2 PL (static) : requires a transaction T to

  • pre-declare all the read set of items and write set of items; and lock all the items it accesses before T begins execution.
  • If any of the pre-declared items can not be locked, T does not lock any item at all. Instead, T waits and tries again until all the items are available for locking.
  • Conservative 2 PL is deadlock-free

Obtain lock Release lock

BEGIN END

Transaction period of duration data item use

No. of locks

Phase 1 Phase 2

DB20 (^) Midterm 2 Review

Strict 2 PL In addition to the Basic 2 PL , and Conservative 2 PL , there is another version of 2 PL: Strict 2 PL :

  • A transaction does not release any of its locks until after it terminates (by committing)
  • Strict 2 PL is not deadlock-free, unless combined with conservative 2 PL.
  • But it helps to overcome the update lost problem.
  • If all transactions in a schedule S follow the strict 2PL protocol, S is called a strict schedule. Hold locks until the end.

Obtain lock Release lock

BEGIN (^) period of END Transactionduration data item use

No. of locks

31

Exercise 4

„ Examine the schedule given below. There are four

transactions, T1, T2, T3, and T4.

--------------------------------------------------------------------^ T1^ T2^ T3^ T 1 READ tax 2 READ salary 3 WRITE tax 4 READ tax 5 WRITE tax 6 READ tax 7 WRITE salary 8 READ salary 9 WRITE tax 10 WRITE salary 11 READ salary 12 WRITE salary

DB20 (^) Midterm 2 Review

Questions/Answers

„ a) Draw the precedence graph for this schedule.

„ b) What is the equivalent serialization order for this

schedule? If no order is possible, then state 'none'.

„ c) Assume that transaction T4 did not run at all.

What is the precedence graph in this case?

„ d) What is the equivalent serialization order for this

second schedule? If no order is possible, then state

'none'.

33

Exercise 4 (d)

„ Examine the schedule given below. There are four

transactions, T1, T2, T3, and T4.

--------------------------------------------------------------------^ T1^ T2^ T3^ T 1 READ tax 2 READ salary 3 WRITE tax 4 READ tax 5 WRITE tax 6 READ tax 7 WRITE salary 8 READ salary 9 WRITE tax 10 WRITE salary 11 READ salary 12 WRITE salary

DB20 (^) Midterm 2 Review

Questions/Answers „ a) Draw the precedence graph for this schedule. Answer: T1 Æsalary T3 Æsalary T4 Ætax T2 Æ tax T

„ b) What is the equivalent serialization order for this schedule? If no order is possible, then state 'none'. Answer: None, the conflict graph has a cycle.

„ c) Assume that transaction T4 did not run at all. What is the precedence graph in this case? Answer: T2 Æ tax T1 Æsalary T3,

„ d) What is the equivalent serialization order for this second schedule? If no order is possible, then state 'none'. Answer: T2 T1 T

37

Key idea

„ Make validation atomic „ If T1, T2, T3, … is validation order, then resulting schedule will be conflict equivalent to S s = T 1 T 2 T3...

To implement validation, system keeps two sets: „ FIN = transactions that have finished phase 3 (and are all done) „ VAL = transactions that have successfully finished phase 2 (validation)

DB20 (^) Midterm 2 Review

Example of what validation must prevent:

RS(T 2 )={B} RS(T 3 )={A,B}

WS(T 2 )={B,D} WS(T 3 )={C}

time

T 2

start

T 2

validated

T 3

validated

T 3

start

∩ =^ φ

39

T 2

finish phase 3

Example of what validation must prevent:

RS(T 2 )={B} RS(T 3 )={A,B}

WS(T 2 )={B,D} WS(T 3 )={C}

time

T 2

start

T 2

validated

T 3

validated

T 3

start

∩ =^ φ

allow

T 3

start

DB20 (^) Midterm 2 Review

Another thing validation must prevent:

RS(T 2 )={A} RS(T 3 )={A,B}

WS(T 2 )={D,E} WS(T 3 )={C,D}

time

T 2

validated

T 3

validated finish T 2

BAD: w 3 (D) w 2 (D)