Chapter 6 - Database Administration Flashcards
What are the Database Administration-Related Terms?
Data administration - a function that applies to an entire org concerning data privacy and security.
Database administration - More technical function that is specific to a particular database
Database administrator - Person in charge of a database and facilitates the development of use of it
Figure 6.2
The database processing environment
What is Concurrency Control?
It ensures that one user’s work does not inappropriately influence another user’s work. The difficulty is balancing strict and lenient levels
How do users work in a Database?
Users submit work in the form of transactions, also known as logical units of work (LUWs)
What is an Atomic Transaction?
One where a series of actions are taken on a database such that all of them are performed successfully or none of them are performed at all. Nothing is partially processed
What are the Typical Problems that may occur when many users process work in a database At the Same Time?
Lost Update (Concurrent Problem) - If one user’s update overwrites another’s
Inconsistent Read - One user reads data that have been processed by only a portion of another user’s transaction
Figure 6.5
Example of the Lost Update Problem
What is Resource Locking?
It prevents concurrent processing problems by disallowing sharing by locking data that are retrieved for update
What are the Four (4) Types of Resource Locks?
Explicit Locks: Lock resources by placing ‘commands’ explicitly
Implicit Locks: A DBMS places locks because it needs to control resources
Exclusive Locks: Lock resources from any type of access
Shared Locks: Locks an item from being changed but not from being read
What does Serializable mean in regards to Transactions?
Concurrent transactions are processed in ‘an arbitrary serial fashion’ so that the database is maintained in a logically consistent state.
What is Two-Phased Locking?
A method to achieve serializable transactions. It lets locks be obtained and released as they are needed.
What are the Two Phases of Two-Phased Locking?
Growing Phase - When the transaction continues to request additional locks
Shrinking Phase - When the transaction begins to release the locks
What is a Deadlock?
AKA Deadly Embrace; When two transactions indefinitely wait on each other to release locks on resources.
What is the Difference between Optimistic and Pessimistic Locking?
Optimistic locking assumes that no conflict will occur thus no errors will occur.
Pessimistic locking assumes that conflict will occur, thus locks are issued.
What is the SQL Transaction Control Language (TLC)? (Note: SQL syntax varies between DBMS products)
SQL BEGIN TRANSACTION statement
SQL COMMIT TRANSACTION statement
SQL ROLLBACK TRANSACTION statement
What does it mean when a Transaction is ACID?
Atomic
Consistent
Isolation
Durable
What is an Atomic Transaction?
It is a transaction in which all of the database actions occur, or none of them do
What is a Consistent Transaction?
A consistent transaction means that no other transactions are permitted on the records until the current transaction finishes.
What is Isolation as it regards to ACID Transactions?
Different transactions may be operating on the same data which can result in continuously changing data content. It is controlled by setting the “isolation levels” to prevent data read problems.
What is a Durable Transaction?
A durable transaction is one in which all committed changes are permanent.
What are the Three (3) Types of Data Read Problems?
Dirty Read (DR) - Reads changed data that is uncommitted. If it’s rolled back, the transaction has incorrect data.
Nonrepeatable Read (NR) - Rereads changed data but finds committed changes in between
Phantom Read (PR) - Rereads data and finds new rows inserted
Which Data Read Problems are possible on each Isolation Level?
Read Uncommitted - All 3 Possible
Read Committed - NR and PR possible; DR not possible
Repeatable Read - PR possible; DR and PR not possible
Serializable - None possible
What is a Cursor?
A cursor is a pointer into a set of rows that is the result set from a SQL SELECT statement.
What are the Cursor Types?
Either: Forward only (one way) or scrollable (both ways)
Static - Takes a snapshot and processes it
Dynamic - A fully featured cursor
Keyset - Combines some features of static and dynamic