Transcript RDBMS Issues 2: Database Recovery & Concurrency

And Franchise Colleges
HSQ - DATABASES & SQL
06 (a) Supplement RDBMS Issues 2
By MANSHA NAWAZ
Section 06 (a)
RDBMS 2
1
RDBMS Issues 2: Database Recovery & Concurrency
Database Recovery
This lecture is concerned with advanced server based RDBMS needs.
Recovery is Needed
•
When:
– Hardware errors occur
– Software errors occur
– External circumstances occur (e.g., flood, fire, etc.)
•
Redundancy
– is key to recovery, but this redundancy is hidden from the user and is different to
data redundancy as seen previously
Section 06 (a)
RDBMS 2
2
Transaction
A transaction is a unit of work,
– that is either done completely
– or not done at all
Transaction
– is the unit of recovery and
– the unit of concurrency
• (see later on concurrency)
Section 06 (a)
RDBMS 2
3
Transaction Process
Assume an SQL statement is atomic, then we are concerned with situations
requiring more than one SQL statements (e.g., crediting a bank account and
debiting another)
BEGIN TRANSACTION
Do lots of things …..
ABORT TRANSACTION (ROLLBACK)
END TRANSACTION (COMMIT)
Section 06 (a)
RDBMS 2
4
Transaction Process
Q: How do we know how to rollback – what were the previous operations
since the Begin Transaction statement?
A: keep a LOG
– A System File
• keeps details of transactions begun
• operations performed (before and after states)
• transactions aborted
• transactions committed
• and any other information of importance to recovery (see later)
Section 06 (a)
RDBMS 2
5
Transaction Process
•
Log enables an undo, as it keeps details of new and old values of
attributes (i.e., update Attribute A from 3 to 5)
– Old values are restored on an undo
•
Failure occurs during undo, then undo – undoing many times = undo
once
•
Failure between operation & writing log – Write Ahead Log protocol
Section 06 (a)
RDBMS 2
6
Possible Recovery Situations
•
Particular types of software and hardware situations where recovery may
be needed:
–
–
–
–
Planned situations by programmer (in software)
Unplanned by programmer (in software)
Systems failures (e.g., CPU)
Media Failures (e.g., disk head crash)
Section 06 (a)
RDBMS 2
7
Possible Recovery Situations
•
Planned by programmer
– force an abort of a transaction by writing the code explicitly
IF Balance < 0 THEN
Message (“Can’t transfer – aborting transaction’);
Abort;
END;
Section 06 (a)
RDBMS 2
8
Possible Recovery Situations
•
Unplanned by programmer
– Temp:= count / 0
– Divide by zero causes a software fault and results in an abort of the current
transaction.
Section 06 (a)
RDBMS 2
9
Possible Recovery Situations
•
System failures (e.g., CPU failure)
– When a COMMIT is performed, the changes are considered to be permanent by
the user
– However, internally the changes may not have been transmitted from primary
storage (buffers) to secondary storage (disk) at the time of systems failure, and
CPU failure affects the buffers (but not the disk).
Section 06 (a)
RDBMS 2
10
Possible Recovery Situations
Q: How can we find out which transactions are affected?
A: LOG?
– insufficient on its own
• whilst a transaction commit may have been logged on the log, via write ahead
protocol, either the actual update may not have taken place
• and/or the page associated with the last operation(s) of the transaction may
not have been written to disk.
Section 06 (a)
RDBMS 2
11
Checkpoints
•
System controlled points
• (snapshot)
– flush all data out of the buffers to guarantee changes
– issue a checkpoint record to the log
• what transactions are active
• address of the most recent log record for each transaction
– on abort, the most recent checkpoint ensures that we know which transactions are
of interest to us
Section 06 (a)
RDBMS 2
12
Using Checkpoints
Tc
Tf
T1
T2
T3
T4
T5
Checkpoint -------------LOG---------->
Section 06 (a)
RDBMS 2
13
Using Checkpoints
Process for determining Undo and Redo list of transactions at Tf
•Redo – completely undone, then done again
– using log details of past and present states
•Redo several times = redo once
•At Checkpoint Tc: T2 & T3 are active, so put on Undo List
– Redo list is initially empty
•Work through log from checkpoint to Tf
•Find BEGIN transaction
– put on Undo list
•Find END transaction
– change from Undo List to Redo List
Section 06 (a)
RDBMS 2
14
Using Checkpoints
•
For our transactions diagram:
– Undo {initial} = {T2, T3}
– Redo {initial} = {}
– Undo {Tf} = {T3, T5}
– Redo {Tf} = {T2, T4}
Section 06 (a)
RDBMS 2
15
Media Failures:
•
These affect the database
•
periodic dumping at appropriate times (e.g., end of the week)
•
page updating/incremental dumping (e.g., end of day)
•
cold start - use archived dump information and the log if intact
Section 06 (a)
RDBMS 2
16
Summary: Database Recovery
•
What database recovery is about
– Transaction
– Log & Write Ahead Log Protocol
•
Types of failures:
– Software failures, planned and unplanned
– Systems failures (e.g., CPU) where only recent transactions may be affected
• Checkpoints
– Media Failures, where database may be affected
Section 06 (a)
RDBMS 2
17
Database Concurrency
This is about ensuring that all concurrent transactions leave the database in
the same state as if the transactions were executed one after the other
– Multi-user problem ONLY
– Look at the major types of problems and some common solutions
– Note: work in terms of reads and writes
• 1 SQL statement may involve both reads and writes
• e.g., UPDATE
Section 06 (a)
RDBMS 2
18
The Lost Update Problem
Transaction 1
Read R
Write R = R + 1
Time Period
1
2
3
4
5
6
Transaction 2
Read R
Write R = R + 1
With R = 5, R’ = 6 rather than 7
i.e., lost one of the updates, as it was written over!
Section 06 (a)
RDBMS 2
19
Uncommitted Dependency Problem
Transaction 1
Read R
Time Period
Transaction 2
1
2
3
4
5
6
Write R = some_value
Abort
First transaction has a read value of R which no longer
exists
Section 06 (a)
RDBMS 2
20
Inconsistent Analysis Problem
Transaction 1
Read R
Read P
Time Period
1
2
3
4
5
6
7
Transaction 2
Read R
Write R = R + 10
Read P
Write P = P + 10
Commit
First transaction has an inconsistent picture of R and P
Section 06 (a)
RDBMS 2
21
Solutions
•
Locking (most common):
– Lock an object so that other transactions cannot interfere with it
– Unlock it when finished
– Lock + Unlock = 2 Phase Locking (Protocol)
– Most common lock types
• Exclusive / write / X Lock
• Shared / read / S Lock
X Lock
S Lock
Section 06 (a)
X Lock
N
N
RDBMS 2
S Lock
N
Y
22
Solutions
•
Transaction can promote its S lock into an X lock, as long as there are no
other S locks on it
•
N --- Transaction goes into a wait state
•
Locking can occur when a statement is used, or at the beginning of a
transaction (greedy!)
•
Unlocking occurs typically at COMMIT or ROLLBACK
Section 06 (a)
RDBMS 2
23
Lost Update Problem
Transaction 1
Read R
Write R = R + 1
•
•
Time Period
1
2
3
4
5
Transaction 2
Read R
Write R = R + 1
Solved the Lost Update, but introduced Deadlock or Deadly Embrace
Solutions:
– Never allow transactions together that could result in deadlock
– Choose a victim and abort, with error message
Section 06 (a)
RDBMS 2
24
Uncommitted Dependency Problem
Transaction 1
Read R
Section 06 (a)
Time Period
Transaction 2
1
2
3
4
5
6
Write R = some_value
RDBMS 2
Abort
25
Inconsistent Analysis Problem
Transaction 1
Read R
Read P
Section 06 (a)
Time Period
1
2
3
4
5
6
7
8
9
10
11
RDBMS 2
Transaction 2
Read R
Write R = R + 10?
Write R = R + 10
Read P
Write P = P + 10
Commit
26
Granularity of Locking
•
What to lock ….
– Whole Database
– Whole Table
– Whole Record
– Whole Attribute
•
Trade-off between simplicity of method vs. concurrency allowed
Section 06 (a)
RDBMS 2
27
Summary: Database Concurrency
•
Three common concurrency problems
•
Standard Method of dealing with them
– 2 Phase Locking
•
Show how they overcome concurrency problems
•
Deadlock or Deadly Embrace
•
Granularity of Locking
Section 06 (a)
RDBMS 2
28
End of Lecture
Section 06 (a)
RDBMS 2
29