Review Lecture 30

1
ReviewLecture 30
2
Administrivia

• Office hours 130 215 today
• Final Exam May 16 8-11 a.m.
• Location 22 Warren
• Topics since Midterm 2
• Transactions, concurrency control, locking,
  recovery
• Logical design, ER Modeling, Functional
  Dependencies, Normalization, Data Mining
• Guest lectures
• Cumulative questions from semester

Review today
Review Tuesday
3
Concurrency

• Concurrent users introduce anomalies
• Dirty reads (WR) T2 reads a value A that T1
  wrote but didnt commit
• Unrepeatable Reads (RW) T1 reads a value A that
  is then written by T2
• Lost Updates (WW) T2 overwrites a write by T1
• Serializable schedules
• A schedule that is equivalent to some serial
  execution of the transactions.
• Definition Two operations conflict if
• They are by different transactions,
• they are on the same object,
• and at least one of them is a write.

R(B)
W(B)
W(A)
R(A)
T1
R(A)
R(B)
W(B)
W(A)
T2
4
Conflict Serializability Intuition

• A schedule S is conflict serializable if
• You are able to transform S into a serial
  schedule by swapping consecutive non-conflicting
  operations of different transactions.
• Example

R(B)
R(A)
W(A)
W(B)
T1
T2
R(A)
W(A)
W(B)
R(B)
R(A)
R(B)
T1
W(A)
W(B)
T2
R(A)
W(A)
R(B)
W(B)
5
Dependency Graph
Ti
Tj

• Dependency graph
• One node per Xact
• Edge from Ti to Tj if
• An operation Oi of Ti conflicts with an operation
  Oj of Tj and
• Oi appears earlier in the schedule than Oj.
• Theorem Schedule is conflict serializable if and
  only if its dependency graph is acyclic.

6
Another Example

• A schedule that is not conflict serializable
• The cycle in the graph reveals the problem.
• The output of T2 depends on T1s value of A, and
  the output of T1 depends on T2s value of B.

T1 R(A), W(A), R(B), W(B) T2

T1 R(A), W(A), R(B), W(B) T2
R(A), W(A), R(B), W(B)
T1 R(A), W(A), R(B), W(B) T2
R(A), W(A), R(B), W(B)
T1 R(A), W(A), R(B), W(B) T2
R(A), W(A), R(B), W(B)
T1 R(A), W(A), R(B), W(B) T2
R(A), W(A), R(B), W(B)
T1
T2
Dependency graph
7
Review Lock-Based Concurrency Control

• Two-phase Locking (2PL) Protocol
• Each Xact must obtain
• a S (shared) lock on object before reading, and
• an X (exclusive) lock on object before writing.
• If an Xact holds an X lock on an object, no other
  Xact can get a lock (S or X) on that object.
• System can obtain these locks automatically
• Two phases acquiring locks, and releasing them
• No lock is ever acquired after one has been
  released
• Growing phase followed by shrinking phase.

• Ensures acyclic dependency graphs
• Allows only conflict serializable schedules

8
Review Strict 2 Phase Locking

• Strict Two-phase Locking (Strict 2PL) Protocol
• Same as 2PL, except All locks held are released
  only when the transaction completes

• Advantage no other transaction reads anything
  you write until you commit.
• e.g a transaction will only read committed data.
• Disadvantage transactions end up waiting.

• Ensures acyclic dependency graphs
• Allows only conflict serializable schedules
• Allows only strict schedules
• No values written by an Xact T can be read or
  overwritten until T commits or aborts.

9
Deadlocks

• Deadlock Cycle of transactions waiting for locks
  to be released by each other.
• Two ways of dealing with deadlocks
• Deadlock prevention
• Wait-die new transactions arent allowed to wait
• Wound-wait old transactions dont have to wait
• Deadlock detection
• Create a waits-for graph
• There is an edge from Ti to Tj if Ti is waiting
  for Tj to release a lock
• Periodically check for cycles in the waits-for
  graph

10
Deadlock Detection (Continued)

• Example
• T1 S(A), S(D), S(B)
• T2 X(B) X(C)
• T3 S(D), S(C), X(A)
• T4 X(B)

S(B)
S(A) S(D)
X(B)
T1
T2
X(B)
X(C)
X(A)
S(D) S(C)
T4
T3
11
Lock Management
contains

• Multi-granularity locking
• Use database containment hierarchy to vary
  granularity of locks
• Full table insert lock table vs read 1 row lock
  record
• Locking in indexes
• dont want to lock a B-tree root for a whole
  transaction!
• actually do non-2PL latches in B-trees
• CC w/out locking
• optimistic concurrency control

12
Multiple Granularity Lock Protocol

• Each Xact starts from the root of the hierarchy.
• Special SIX lock used when reading many records,
  and updating a few.
• SIX lock conflicts are all S and IX conflicts
  (e.g. only compatible with IS locks).
• To get S or IS lock on a node, must hold IS or IX
  on parent node.
• To get X or IX or SIX on a node, must hold IX or
  SIX on parent node.
• Must release locks in bottom-up order.

13
Multi-Granularity Example

• Rules
• Each Xact starts from the root of the hierarchy.
• To get S or IS lock, must hold IS or IX on
  parent.
• To get X or IX or SIX, must hold IX or SIX on
  parent.
• Must release locks in bottom-up order.

• T1 wants to read change tuple 2
• T2 wants to read all of Page 1
• T1 gets IX lock on DBMS, Sailor, Page 1
• T1 gets X lock on Tuple 2 changes it
• T2 gets IS lock on DBMS, Sailor
• T2 tries to get S lock on Page 1, but S conflicts
  with IX lock. T2 blocks.
• What if T2 had started first?

T2IS
Database
T1IX
T1IX
T2IS
Sailor Table
T1IX
Page 1
Page 2
T2wait
Tuple 2
Tuple 4
Tuple 3
Tuple 1
T1X
14
Multi-Granularity Example

• Rules
• Each Xact starts from the root of the hierarchy.
• To get S or IS lock, must hold IS or IX on
  parent.
• To get X or IX or SIX, must hold IX or SIX on
  parent.
• Must release locks in bottom-up order.

• T1 wants to read change tuple 2
• T2 wants to read all of Page 1
• T2 gets IS lock on DBMS, Sailor
• T2 gets S lock on Page 1
• T1 gets IX lock on DBMS, Sailor
• T1 tries to get IX lock on Page 1, waits

T2IS
Database
T1IX
T1IX
T2IS
Sailor Table
T1waits
Page 1
Page 2
T2S
Tuple 2
Tuple 4
Tuple 3
Tuple 1
15
Locking in B Trees

• Higher levels of the tree only direct searches
  for leaf pages.
• For inserts
• a node must be X locked only if a split can
  propagate up to it from the modified leaf.
• Example
• insert 9
• vs insert 15

20
30
24
13
15
9
7
14
15
16
16

• We can exploit these observations to design
  efficient locking protocols that guarantee
  serializability even though they violate 2PL.

16
Simple Locking in B Trees

• Search Start at root and go down
• S lock node.
• Unlock its parent.
• Insert/Delete Start at root and go down,
• X lock node.
• If node is safe, release all locks on ancestors.
• Safe node Node such that changes will not
  propagate up beyond this node.
• Inserts Node is not full.
• Deletes Node is not half-empty.

17
Example
ROOT
T1 Search 38 T2 Insert 45 T3 Insert 25
A
20
T1S
T3X
T2X

• Search
• S lock node.
• Unlock its parent.
• Insert/Delete
• X lock node.
• If node is safe, release all locks on ancestors.

T1S
B
35
T2X
T3X
T3X
F
T1S
C
23
38
44
T2X
H
D
E
G
I
20
22
23
24
35
36
38
41
44
T1S
T2X
T3X
T3X
18
Optimistic CC (Kung-Robinson)

• Locking is a conservative approach in which
  conflicts are prevented. Disadvantages
• Lock management overhead.
• Deadlock detection/resolution.
• Lock contention for heavily used objects.
• If conflicts are rare, we might be able to gain
  concurrency by not locking, and instead checking
  for conflicts before Xacts commit.

19
Kung-Robinson Model
Buffer Pool
14
23
27

• Xacts have three phases
• READ Xacts read from the database, but make
  changes to private copies of objects.
• VALIDATE Check for conflicts.
• WRITE Make local copies of changes public.

Reads from
Writes back
Tj
W
R
V
Writes to
14
23
Tj private copies

• Validation, and Write phase are done inside a
  critical section!
• i.e., Nothing else goes on concurrently.

20
Validation Phase

• Tests conditions that are sufficient to ensure
  that no conflict occurred.
• If conflict did occur, restart transaction.
• Each Xact is assigned a timestamp at end of READ
  phase, just before validation begins.
• Also keep track of xact phase begin end times
• Compute
• ReadSet(Tj) Set of objects read by Xact Tj.
• WriteSet(Tj) Set of objects modified by Tj.

21
Validation Test 1 for Tj no overlap

• For all i and j such that Ti lt Tj, check that Ti
  completes write phase before Tj begins read phase.

Ti
Tj
R
V
W
R
V
W

• Implies a serial order for Ti and Tj Ti came
  first.

22
Validation Test 2 for Tj Overlapping read phase

• For all i and j such that Ti lt Tj, check that
• Ti completes before Tj begins its Write phase
• WriteSet(Ti) ReadSet(Tj) is empty.

Ti
R
V
W
Tj
R
V
W

• Ensures Tj does not read any object written by
  Ti.
• Implies a serial order Tj might write same set
  of objects, but writes are in a serial order
  Tis writes come first.

23
Validation Test 3 for Tj Overlapping write phase

• For all i and j such that Ti lt Tj, check that
• Ti completes Read phase before Tj does
• WriteSet(Ti) ReadSet(Tj) is empty
• WriteSet(Ti) WriteSet(Tj) is empty.

Ti
R
V
W
Tj
R
V
W

• Ensures Tj does not read or write any object
  written by Ti.
• Implies a serial order Tj reads and writes are
  to different objects than those written by Ti.

24
Optimistic CC Overhead

• Must record read/write activity in ReadSet and
  WriteSet per Xact.
• Must create and destroy these sets as needed.
• Must check for conflicts during validation, and
  must make validated writes global.
• Critical section can reduce concurrency.
• Scheme for making writes global can reduce
  clustering of objects.
• Optimistic CC restarts Xacts that fail
  validation.
• Work done so far is wasted requires clean-up.

25
Write-Ahead Logging (WAL)

• The Write-Ahead Logging Protocol
• Must force the log record for an update before
  the corresponding data page gets to disk.
• Must force all log records for a Xact before
  commit. (or, a transaction is not committed until
  all of its log records including its commit
  record are on the stable log.)
• 1 (with UNDO info) helps guarantee Atomicity.
• 2 (with REDO info) helps guarantee Durability.
• This allows us to implement Steal/No-Force buffer
  management policy

26
Buffer Management summary
No Steal
Steal
No Steal
Steal
No Force
Fastest
No Force
Force
Slowest
Force
Performance Implications
Logging/Recovery Implications
27
WAL the Log
RAM
DB
LSNs
pageLSNs
flushedLSN

• Each log record has a unique Log Sequence Number
  (LSN).
• LSNs always increasing.
• Each data page contains a pageLSN.
• The LSN of the most recent log record
  for an update to
  that page.
• System keeps track of flushedLSN.
• The max LSN flushed so far.
• WAL Before page i is written to DBlog must
  satisfy
• pageLSNi flushedLSN

Log records flushed to disk
flushedLSN
pageLSN
Log tail in RAM
28
Log Records

• prevLSN is the LSN of the previous log record
  written by this Xact (so records of an Xact form
  a linked list backwards in time)
• Possible log record types
• Update, Commit, Abort
• Checkpoint (for log maintenance)
• Compensation Log Records (CLRs)
• for UNDO actions
• End (end of commit or abort)

LogRecord fields
LSN prevLSN
XID
type
pageID
length
update records only
offset
before-image
after-image
29
Other Log-Related State

• Two in-memory tables
• Transaction Table
• One entry per currently active Xact.
• entry removed when Xact commits or aborts
• Contains XID, status (running/committing/aborting)
  , and lastLSN (most recent LSN written by Xact).
• Dirty Page Table
• One entry per dirty page currently in buffer
  pool.
• Contains recLSN -- the LSN of the log record
  which first caused the page to be dirty.

30
The Big Picture Whats Stored Where
LOG
RAM
LogRecords
Xact Table lastLSN status Dirty Page
Table recLSN flushedLSN
LSN prevLSN
Data pages each with a pageLSN
XID
type
pageID
length
Master record
offset
before-image
after-image
31
Example
BEGIN_CHKPT
To disk
END_CHKPT

• T1 update 2 (DEF)
• (assume written to disk)
• T2 update 3 (KLM)
• T2 update 1 (QRS)
• T1 update 2 (WXY)
• T2 commit
• T1 update 4 (RST)
• SYSTEM CRASH

11 null T1 update 2
ABC DEF
12 null T2 update 3
HIJ KLM
13 12 T2 update 1
GDE QRS
14 11 T1 update 2
DEF WXY
15 13 T2 commit and end
16 14 T1 update 4
OPQ RST
SYSTEM CRASH
Buffer Frame 1
Buffer Frame 2
Buffer Frame 3
32
Crash Recovery Big Picture
Oldest log rec. of Xact active at crash

• Start from a checkpoint (found via master
  record).
• Three phases. Need to do
• Analysis - Figure out which Xacts committed since
  checkpoint, which failed.
• REDO all actions.
• (repeat history)
• UNDO effects of failed Xacts.

Smallest recLSN in dirty page table after Analysis
Last chkpt
CRASH
A
R
U
33
End result goal of recovery

• T1 update 2 (DEF)
• T2 update 3 (KLM)
• T2 update 1 (QRS)
• T1 update 2 (WXY)
• T2 commit
• T1 update 4 (RST)
• SYSTEM CRASH

• T1 aborts
• Roll back updates if they made it to disk.

• T2 commits
• Re-apply updates if needed

34
Recovery The Analysis Phase

• Re-establish knowledge of state at checkpoint.
• via transaction table and dirty page table stored
  in the checkpoint
• Scan log forward from checkpoint.
• End record Remove Xact from Xact table.
• All Other records Add Xact to Xact table, set
  lastLSNLSN, change Xact status on commit.
• also, for Update records If page P not in Dirty
  Page Table, Add P to DPT, set its recLSNLSN.
• At end of Analysis
• transaction table says which xacts were active at
  time of crash.
• DPT says which dirty pages might not have made it
  to disk

35
Analysis
BEGIN_CHKPT
END_CHKPT
Xact Table
11 null T1 update 2
ABC DEF
12 null T2 update 3
HIJ KLM
T1 11 U
T1 14 U
13 12 T2 update 1
GDE QRS
T2 12 U
T2 13 U
T2 15 C
14 11 T1 update 2
DEF WXY
15 13 T2 commit and end
Buffer Frame 1
Buffer Frame 2
Buffer Frame 3
1. Create entries the Xact table with xacts
active at time of crash.
36
Analysis
BEGIN_CHKPT
END_CHKPT
Xact Table
11 null T1 update 2
ABC DEF
12 null T2 update 3
HIJ KLM
T1 14 U
13 12 T2 update 1
GDE QRS
14 11 T1 update 2
DEF WXY
15 13 T2 commit and end
Dirty Page Table
2 11
3 12
1 13
Buffer Frame 1
Buffer Frame 2
Buffer Frame 3
2. Create entries in the Dirty Page table with
pages that might not have made it to disk.
37
Phase 2 The REDO Phase

• We Repeat History to reconstruct state at crash
• Reapply all updates (even of aborted Xacts!),
  redo CLRs.
• Scan forward from log rec containing smallest
  recLSN in DPT. Q why start here?
• For each update log record or CLR with a given
  LSN, REDO the action unless
• Affected page is not in the Dirty Page Table, or
• Affected page is in D.P.T., but has recLSN gt LSN,
  or
• pageLSN (in DB) ³ LSN. (this last case requires
  I/O)
• To REDO an action
• Reapply logged action.
• Set pageLSN to LSN. No additional logging, no
  forcing!

38
Redo
BEGIN_CHKPT
END_CHKPT
Xact Table
11 null T1 update 2
ABC DEF
12 null T2 update 3
HIJ KLM
T1 14 U
13 12 T2 update 1
GDE QRS
14 11 T1 update 2
DEF WXY
15 13 T2 commit and end
Dirty Page Table
2 11
3 12
1 13
Buffer Frame 1
Buffer Frame 2
Buffer Frame 3
Step 1. Find lowest rec LSN in Dirty Page Table.
39
Redo
BEGIN_CHKPT
END_CHKPT
Xact Table
11 null T1 update 2
ABC DEF
12 null T2 update 3
HIJ KLM
T1 14 U
13 12 T2 update 1
GDE QRS
14 11 T1 update 2
DEF WXY
15 13 T2 commit and end
Dirty Page Table
2 11
3 12
1 13
Buffer Frame 1
Buffer Frame 2
Buffer Frame 3
Step 2. Scan forward and redo all redoable log
records.
40
Redo
BEGIN_CHKPT
END_CHKPT
Xact Table
11 null T1 update 2
ABC DEF
12 null T2 update 3
HIJ KLM
T1 14 U
13 12 T2 update 1
GDE QRS
14 11 T1 update 2
DEF WXY
15 13 T2 commit and end
Dirty Page Table
2 11
3 12
1 13
Buffer Frame 1
Buffer Frame 2
Buffer Frame 3
1. Reapply LSN 11 T1 update 2 (DEF)
2. Reapply LSN 12 T2 update 3 (KLM)
41
Redo
BEGIN_CHKPT
END_CHKPT
Xact Table
11 null T1 update 2
ABC DEF
12 null T2 update 3
HIJ KLM
T1 14 U
13 12 T2 update 1
GDE QRS
14 11 T1 update 2
DEF WXY
15 13 T2 commit and end
Dirty Page Table
2 11
3 12
1 13
Buffer Frame 1
Buffer Frame 2
Buffer Frame 3
3. Reapply LSN 13 T2 update 1 (QRS)
4. Reapply LSN 14 T1 update 2 (WXY)
42
Redo
BEGIN_CHKPT
END_CHKPT
Xact Table
11 null T1 update 2
ABC DEF
12 null T2 update 3
HIJ KLM
T1 14 U
13 12 T2 update 1
GDE QRS
14 11 T1 update 2
DEF WXY
15 13 T2 commit and end
Dirty Page Table
2 11
3 12
1 13
Buffer Frame 1
Buffer Frame 2
Buffer Frame 3
5. Reapply T2 commit (and well write dirty pages
to disk.)
43
Phase 3 The UNDO Phase

• We undo actions of all active but not committed
  xacts at the time of the crash.
• May even need to undo some of what we did in
  REDO phase!
• ToUndolastLSNs of all Xacts in the Trans Table
• a.k.a. losers
• Repeat
• Choose (and remove) largest LSN among ToUndo.
• If this LSN is a CLR and undonextLSNNULL
• Write an End record for this Xact.
• If this LSN is a CLR, and undonextLSN ! NULL
• Add undonextLSN to ToUndo
• Else this LSN is an update. Undo the update,
  write a CLR, add prevLSN to ToUndo.
• Until ToUndo is empty.

• CLRs will help us remember where we are in case
  of system crash during recovery.

44
Undo
BEGIN_CHKPT
END_CHKPT
Xact Table
11 null T1 update 2
ABC DEF
12 null T2 update 3
HIJ KLM
T1 14 U
13 12 T2 update 1
GDE QRS
14 11 T1 update 2
DEF WXY
15 13 T2 commit and end
ToUndo
16 undoNextLSNnull T1 CLR
2 DEF ABC
14
11
Buffer Frame 1
Buffer Frame 2
Buffer Frame 3
1. Add last LSN for all transactions in Xact table
2. Recursively Process each last LSN in To Undo
table.