www.cardiff.ac.uk/medic/irg-clinicalepidemiology
Transactions & recovery
Copyright By PowCoder代写 加微信 powcoder
Information modelling
& database systems
in the previous lecture we learn about database integrity
database integrity is paramount and DBMS often include the ability to handle transactions to maintain the integrity of data
in this lecture we introduce the concept of transaction
Transactions
a transaction is an action, or a series of actions, carried out by a single user or an application program, which reads or updates the contents of a database
a transaction is a logical unit of work that transforms the database from one consistent state to another consistent state
transactions are the units of:
consistency
ACID properties
a transaction must satisfy the ACID properties:
Consistency
Durability
ACID properties
transactions do not have parts
transactions cannot be executed partially
consistency
transactions take the database from one consistent state into another
note: midway through a transaction the database might not be consistent, but at the and it has to be!
ACID properties
the effects of a transaction are not visible to other transactions until it has completed
durability
once a transaction is completed, its updates survive, even if there is a subsequent system crash
transaction: “transfer £50 from account A to account B”
A = A – 50
B = B + 50
atomicity: should not take money from A without giving it to B
consistency: no money lost or gained overall
isolation: other queries should not see A or B change until completion
durability: the money does not go back to A
Transaction manager
transaction manager enforces the ACID properties
it schedules the operations of transactions
COMMIT and ROLLBACK are used to ensure atomicity
locks or timestamps are used to ensure consistency and isolation for concurrent transactions
a log is kept to ensure durability in the event of system failure
COMMIT and ROLLBACK
COMMIT signals the successful end of a transaction
any changes made by the transaction are saved
these changes become visible to other transactions
ROLLBACK signals the unsuccessful end of a transaction
any changes made by the transaction are undone
as if the transaction never occurred
in theory, transactions should be durable
… but in practice we cannot always prevent all types of failures, e.g.
system crash
power failure
disk crash
user mistake
natural disaster
Transaction log
transaction log records details of all transactions
any changes transactions make to the database
how to undo these changes
when transactions complete and how
the log is stored on disk, not in memory
if the system crashes, then the log is preserved
write ahead log rule
the entry in the log must be recorded before COMMIT
System failures
a system failure affects all running transactions
software crash
power failure
the physical media (disks) are not damaged
at various times a DBMS takes a checkpoint
all committed transactions are written to disk
a record is made (on disk) of the transactions that are currently running
System recovery
any transaction that was running at the time of failure needs to be undone and restarted
any transaction that committed since the last checkpoint needs to be redone
Types of transactions
needs no recovery
undo and restart
in progress
undo and restart
Transaction recovery
two lists of transaction
UNDO: all transaction that were in progress at the
last checkpoint
REDO: empty list
for each entry in the log (at the last checkpoint) do:
if a BEGIN TRANSACTION entry is found for transaction T, then add T to UNDO
if a COMMIT entry if found for T, then move T from UNDO to REDO
Transaction recovery
UNDO: T2, T3
active transactions: T2, T3
add them to UNDO
Transaction recovery
UNDO: T2, T3, T4
add it to UNDO
Transaction recovery
UNDO: T2, T3, T4, T5
add it to UNDO
Transaction recovery
UNDO: T3, T4, T5
T2 commits
move it to REDO
Transaction recovery
UNDO: T3, T5
REDO: T2, T4
T4 commits
move it to REDO
Forwards and backwards recovery
backwards recovery
we need to undo some transactions
working backwards through the log we undo any operation by a transaction on the UNDO list
this returns the database to a consistent state
forwards recovery
some transactions need to be redone
working forwards through the log we redo any operation by a transaction on the REDO list
this brings the database up to date
Media failures
system failures are not too severe
only information since the last checkpoint is affected
this can be recovered from the transaction log
media failures (e.g. disk crash) are more serious
the data stored to disk is damaged
the transaction log itself may be damaged
backups are needed to recover from media failure
the transaction log and entire contents of the database is written to secondary storage (e.g. tape)
time consuming and thus often requires down time
backup frequency
frequent enough to minimise information loss
not too frequent as to cause problems
daily backup (e.g. overnight) is common
Recovery from media failure
use the last backup to restore the database
use the transaction log to redo any changes made since the last backup
if the transaction log is damaged, we cannot do step 2
store the transaction log and the database on separate physical devices
this minimises the risk of losing both
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com