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.
Atomicity
Transactions are atomic – they don’t have parts (conceptually)
So can’t be executed partially
Consistency
Transactions take the database from one consistent state into another
In the middle of a transaction the database might not be consistent
Isolation
The effects of a transaction are not visible to other transactions until it has completed
From outside the transaction has either happened or not
To me this actually sounds like a consequence of atomicity…
Durability
Once a transaction has completed, its changes are made permanent
Even if the system crashes, the effects of a transaction must remain in place
The Transaction Manager
The transaction manager enforces the ACID properties
It schedules the operations of transactions
COMMIT and ROLLBACK are used to ensure atomicity
Locks are used to ensure consistency and isolation for concurrent transactions (next lecture)
A log is kept to ensure durability in the event of system failure (this lecture)
COMMIT signals the successful end of a transaction
Any changes made by the transaction should be saved
These changes are now visible to other transactions
ROLLBACK signals the unsuccessful end of a transaction
Any changes made by the transaction should be undone
It is now as if the transaction never existed
Prevention is better than cure
Can’t protect against everything though
The transaction log records the details of all transactions
Any changes the transaction makes 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 it is preserved
Write ahead log rule
The entry in the log must be made before COMMIT processing can complete
A system failure means all running transactions are affected
Software crashes or Power failures
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
Any transaction that was running at the time of failure needs to be undone and restarted
Any transactions that committed since the last checkpoint need to be redone
Transaction Recovery
UNDO = all transactions running at the last checkpoint
REDO = empty
For each entry in the log, starting at the last checkpoint
If a BEGIN TRANSACTION entry is found for T
Add T to UNDO
If a COMMIT entry is found for T
Move T from UNDO to REDO
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
System failures are not too severe
Only information since the last checkpoint is affected
This can be recovered from the transaction log
Media failures (disk crashes etc) 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 (often tape)
Time consuming, and often requires down time
Backups frequency
Frequent enough that little information is lost
Not so frequent as to cause problems
Every day (night) is common
Backup storage
Restore the database from the last backup
Use the transaction log to redo any changes made since the last backup
If the transaction log is damaged you can’t do step 2
Store the log on a separate physical device to the database
The risk of losing both is then reduced
Lecture 15: Concurrency
A schedule is a sequence of the operations by a set of concurrent transactions that preserves the order of operations in each of the individual transactions
A serial schedule is a schedule where operations of each transaction are executed consecutively without any interleaved operations from other transactions (each transaction commits before the next one is allowed to begin)
Two schedules are equivalent if they always have the same effect.
A schedule is serialisable if it is equivalent to some serial schedule.
A schedule is conflict serialisable if transactions in the schedule have a conflict but the schedule is still serialisable
To determine if a schedule is conflict serialisable we use a precedence graph
Locking is a procedure used to control concurrent access to data (to ensure serialisability of concurrent transactions)
Two types of lock
Shared lock (S-lock or read-lock)
Exclusive lock (X-lock or write-lock)
Read lock allows several transactions simultaneously to read a resource (but no transactions can change it at the same time)
Write lock allows one transaction exclusive access to write to a resource. No other transaction can read this resource at the same time.
The lock manager in the DBMS assigns locks and records them in the data dictionary
Before reading from a resource a transaction must acquire a read-lock
Before writing to a resource a transaction must acquire a write-lock
Locks are released on commit/rollback
A transaction may not acquire a lock on any resource that is write-locked by another transaction
A transaction may not acquire a write-lock on a resource that is locked by another transaction
If the requested lock is not available, transaction waits
A transaction follows the two-phase locking protocol (2PL) if all locking operations precede the first unlock operation in the transaction
A deadlock is an impasse that may result when two or more transactions are waiting for locks to be released which are held by each
Given a schedule, we can detect deadlocks which will happen in this schedule using a wait-for graph (WFG).
An alternative is timestamping
Requires less overhead in terms of tracking locks or detecting deadlock
Determines the order of transactions before they are executed
The protocol means that transactions with higher times take precedence
Equivalent to running transactions in order of their final time values
Transactions don’t wait - no deadlock
Lecture 17: Security and Integrity
GRANT
ON
No comments:
Post a Comment