CH6 Flashcards
Database Administration Functions
– Concurrency control
– Security
– Backup
– Recovery
Concurrency Control
ensures that one user’s actions do not impact another user’s actions
Concurrent Transaction
When two transactions are being processed against a database at the same time
Interdependency
Changes required by one user may impact others
Atomic Transactions
database operation typically involves several transactions.
transactions are atomic and are sometimes called logical units of work (LUW).
Lost Update Problem
• If two or more users are attempting to update the same piece of data at the same time, it is possible that one update may overwrite the other update
Concurrency Issues
- Dirty reads
* Phantom reads
Dirty reads
The transaction reads a changed record that has not been committed to the database
Phantom reads
The transaction re-reads a data set and finds that a new record has been added
Implicit locks
issued automatically by the DBMS based on an activity.
Explicit locks
issued by users requesting exclusive rights to the data.
Serializable Transactions
When two or more transactions are processed concurrently, the results in the database should be logically consistent with the results that would have been achieved had the transactions been processed in an arbitrary serial fashion
Two-Phased Locking
- One way to achieve serializable transactions is by using two-phased locking.
- Two-phased locking lets locks be obtained and released as they are needed
Growing phase
when the transaction continues to request additional locks
Shrinking phase
when the transaction begins to release the locks
Deadlock
On occasions, two transactions may indefinitely wait on each another to release resources
Optimistic Locking
Read transaction is processed, updates are issued
Pessimistic Locking
Locks are issued, the transaction is processed and then the locks are freed
Consistent Transactions
Consistent transactions are often referred to by the ACID. – Atomic – Consistent – Isolated – Durable
ACID: Atomic
- An atomic transaction is one in which all of the database actions occur or none of them do.
- A transaction consists of a series of steps. Each step must be successful for the transaction to be saved.
- This ensures that the transaction completes everything it intended to do before saving the changes.
ACID: Consistent
- No other transactions are permitted on the records until the current transaction finishes.
- This ensures that the transaction integrity has statement level consistency among all records.
ACID: Isolation
- Within multiuser environments, different transactions may be operating on the same data.
- As such, the sequencing of uncommitted updates, rollbacks, and commits continuously change the data content.
- The 1992 ANSI SQL standard defines four isolation levels that specify which of the concurrency control problems are allowed to occur
ACID: Durable
• A durable transaction is one in which all committed changes are permanent
Cursors
- A cursor is a pointer into a set of rows that are the result set from an SQL SELECT statement.
- Cursors are usually defined using SELECT statements.
Cursor Types
• Forward only or scrollable
– Static cursor
– Keyset cursor
– Dynamic cursor
Notes On Cursor Types
- Other DBMS products may define a different set of cursors.
- In this case, the forward only cursor is considered a separate cursor type, and only a scrollable cursor may be static, keyset, or dynamic.
Database Security
• Database Security strives to ensure that
– Only authenticated users
– Perform authorized activities
Processing Rights and Responsibilities
- Processing rights define who is permitted to do what and when.
- The individuals performing these activities have full responsibility for the implications of their actions.
- Individuals are identified by a username and a password.
Granting Permissions
- Database users are known as an individual and as a member of one or more roles.
- Granting access and processing rights/privileges may be granted to an individual and/or a role.
- Users possess the compilation of rights granted to the individual and all the roles for which they are members.
Database Security Guidelines
- Run the DBMS behind a firewall.
- Apply the latest operating system and DBMS service packs and patches.
- Limit DBMS functionality to needed features. • Protect the computer that runs the DBMS.
- Manage accounts and passwords.
Database Backup and Recovery
• Common causes of database failures – Hardware failures – Programming bugs – Human errors/mistakes – Malicious actions • As these issues are impossible to completely avoid, recovery procedures are essential.
Reprocessing
all activities since the backup was performed are redone.
• This is a brute-force technique.
• This procedure is costly in the effort involved in re-entering the data.
• This procedure is risky in that human error is likely and in that paper record-keeping may not be accurate.
Log File
The log file is then used for recovery via rollback or rollforward.
– To undo a transaction the log must contain a copy of every database record before it was changed.
• Such records are called before-images.
• A transaction is undone by applying before-images of all its changes to the database.
– To redo a transaction the log must contain a copy of every database record (or page) after it was changed.
• These records are called after-images.
• A transaction is redone by applying after-images of all its changes to the database.
Rollback
– Log files save activities in sequence order.
– It is possible to undo activities in reverse order that they were originally executed.
– This is performed to correct/undo erroneous or malicious transaction(s) after a database is recovered from a full backup.
