Chapter 9
The transaction boundaries are the essential information that the DBMS needs from the application programs to enforce different locking strategies.
TRUE
Transaction level consistency means that all rows impacted by any actions in a transaction are protected from change during the entire transaction.
TRUE
Two-phased locking has a growing phase and a shrinking phase.
TRUE
Two-phased locking is a scheme for achieving serializability of transactions.
TRUE
With optimistic locking, the assumption is made that no conflict will occur.
TRUE
With pessimistic locking, the assumption is made that a conflict will occur.
TRUE
Briefly define the four isolation levels and the problems they prevent.
The four isolation levels are read uncommitted, read committed, repeatable read and serializable. The three problems that can occur because of concurrent processing are dirty reads, nonrepeatable reads, and phantom reads. The read uncommitted isolation level does not prevent any of these problems from occurring. The read committed isolation level prevents dirty reads. The repeatable read isolation level prevents dirty reads and nonrepeatable reads. The serializable isolation level prevents all three problems from occurring.
Requiring all application programs to lock resources in the same order is one way of preventing a deadlock condition.
TRUE
Resource locking is one remedy to the lost update problem.
TRUE
Rollforward is a method of database recovery that restores the database save and all valid transactions since the save was reapplied.
TRUE
The DBA has to find a balance between the conflicting goals of maximizing availability of the database to users and protecting the database.
TRUE
The DBA should periodically analyze run-time statistics of database performance to help manage the DBMS.
TRUE
The database is most vulnerable to failure after a change to its structure.
TRUE
The goal of database security is to ensure that only authorized users can perform authorized activities at authorized times.
TRUE
The security provided by the DBMS often has to be augmented by additional security features within the application program.
TRUE
The size of a lock is referred to as the lock granularity.
TRUE
Measures that are taken to prevent one user's work from inappropriately influencing another user's work are called ________. A) concurrency control B) checkpoint C) database recovery D) database logging
A
Recovering a database via reprocessing involves ________. A) restoring the database from the save and reprocessing all the transactions since the save B) restoring the database from the save and reapplying all the changes made by transactions since the save C) undoing the changes made by erroneous or partially processed transactions and restarting the valid transactions that were in process at the time of the failure D) recreating the database by reentering all of the data from the beginning, and then reprocessing all of the transactions
A
The situation that occurs when one user's changes to the database are lost by a second user's changes to the database is known as the ________. A) lost update problem B) deadly embrace problem C) inconsistent read problem D) inconsistent write problem
A
Which of the following is not true of data repositories? A) They are usually created after the database has been implemented and optimized for performance. B) They may be virtual. C) They may contain metadata about database applications. D) They may contain metadata about users.
A
Which type of lock prevents all types of access to the locked resource? A) Exclusive lock B) Shared lock C) Explicit lock D) Implicit lock
A
Explain the concept of an atomic transaction.
An atomic transaction is a transaction in which either all steps must be completed successfully, or none of the steps can be completed at all. A transaction becomes atomic when the complete set of steps within the transaction is a single logical unit of work.
Recovering a database via rollforward involves ________. A) restoring the database from the save and reprocessing all the transactions since the save B) restoring the database from the save and reapplying all the changes made by transactions since the save C) undoing the changes made by erroneous or partially processed transactions and restarting the valid transactions that were in process at the time of the failure D) re-creating the database by re-entering all of the data from the beginning, and then reprocessing all of the transactions
B
The situation that occurs when two users are each waiting for a resource that the other person has locked is known as a(n) ________. A) lost update problem B) deadlock C) inconsistent read problem D) checkpoint
B
The task of diagnosing errors due to changes in the database structure is eased by ________. A) formal policies for requesting changes B) database structure change documentation C) rollback analysis D) configuration control
B
When a transaction reads a row that has been changed, but the change has not been committed, this is known as a ________. A) Clean read B) Dirty read C) Nonrepeatable read D) Phantom read
B
Which of the following is not a database administration responsibility of a DBA? A) Managing the database structure B) Writing the applications C) Managing the DBMS D) Maintaining the data repository
B
Which of the following is not true about locks? A) Locks with large granularity are easier for the DBMS to administer. B) Locks with small granularity cause more conflicts. C) Locks may have a database-level granularity. D) Locks may have a table-level granularity.
B
Which of the following is not true about two-phased locking? A) Cannot obtain a new lock once a lock has been released B) Uses only shared locks C) Has a growing phase D) Has a shrinking phase
B
Which of the following is not true of DBMS security features? A) Users may be assigned to one or more roles. B) A role may be assigned to only one user. C) Both users and roles can have many permissions. D) Objects have many permissions.
B
Which of the following would a DBA typically NOT do in managing the DBMS? A) Analyze system performance statistics B) Install new versions of the operating system, as needed C) Evaluate new DBMS product features D) Tune DBMS product options to accommodate other software in use
B
Which type of lock still allows other transactions to have read-only access to the locked resource? A) Exclusive lock B) Shared lock C) Explicit lock D) Implicit lock
B
Ensuring that all rows impacted by the actions of a transaction are protected from changes until the entire transaction is completed is called ________. A) statement level consistency B) optimistic locking C) transaction level consistency D) durable transactions
C
Once processing rights have been defined, they may be implemented at any of these levels except ________. A) network B) operating system C) data D) application
C
Recovering a database via rollback involves ________. A) restoring the database from the save and reprocessing all the transactions since the save B) restoring the database from the save and reapplying all the changes made by transactions since the save C) undoing the changes made by erroneous or partially processed transactions and restarting the valid transactions that were in process at the time of the failure D) re-creating the database by re-entering all of the data from the beginning and, then reprocessing all of the transactions
C
Requiring all application programs to lock resources in the same order is a technique for preventing what problem? A) Concurrent update B) Lost update C) Deadlock D) Exclusive locks
C
When a transaction rereads data that has been changed and finds changes due to committed transactions, this is known as a ________. A) Clean read B) Dirty read C) Nonrepeatable read D) Phantom read
C
Which of the following cannot be enforced in the DBMS or application programs? A) Processing rights B) Security C) Processing responsibilities D) Cursors E) Transaction isolation
C
Which of the following is allowed by "Repeatable Read Isolation"? A) Nonrepeatable reads B) Dirty reads C) Phantom reads D) Both A and B are correct
C
Which of the following is true about making changes to the database structure? A) The DBA need not get input from users on the issue because it is a technical decision. B) Formal policies and procedures for requesting a change are not used because they are too limiting. C) Documentation of when the change was made, how it was made, and why it was made must be created. D) Changes do not produce unexpected results because the DBA will have investigated the change thoroughly before implementing it.
C
Which of the following is true of forward only cursors? A) Current values for each row are retrieved when the application accesses a row. B) All changes of any type from any source are visible. C) Changes made by the transaction are visible only if they occur on rows ahead of the cursor. D) It requires the greatest overhead of any cursor type.
C
Which type of data repository is composed of metadata that is created automatically as the system components are created? A) Passive B) Dynamic C) Active D) Automatic
C
Explain the importance of documenting changes in database structure.
Changes in database structure may introduce errors that do not become apparent for long periods of time. Documentation provides a record of changes to assist the investigation into the cause of database errors. Further, documentation of database changes can be used to determine the structure of the database at some point in the past. This is necessary for proper interpretation of any historical records that may need to be retrieved. Finally, documentation can assist in recovering the database from a previous copy in case of database failure. Not only do transactions have to be restored to the recovered database, but also any structural changes since the backup was created will have to be reapplied.
Explain the concept of serializable transactions.
Concurrent transactions are two or more transactions that are processed against the database at the same time. It is desirable for concurrent transactions to be serializable; that is, the results of the concurrent transactions should be logically consistent with the results that would be obtained if the transactions were not processed concurrently but rather in an arbitrary serial order.
A lock placed automatically by the DBMS is called a(n) ________ lock. A) exclusive B) explicit C) shared D) implicit
D
A series of actions to be taken on the database such that either all actions are completed successfully, or none of them can be completed, is known as a ________. A) checkpoint B) log C) lock D) transaction
D
Locks that are placed assuming that a conflict will not occur are called ________. A) dynamic B) shared C) pessimistic D) optimistic
D
Locks that are placed assuming that a conflict will occur are called ________. A) explicit locks B) implicit locks C) optimistic locks D) pessimistic locks
D
One remedy for the inconsistencies caused by concurrent processing is ________. A) concurrency B) checkpointing C) rollback D) resource locking
D
When two transactions are being processed against the database at the same time ________. A) they are called concurrent transactions B) they are usually interleaved C) they always result in a lost update problem D) Both A and B are correct
D
Which of the following would not be contained in a transaction log? A) Before images B) Type of operation C) Time of the action D) Permissions
D
A database save is used to mark the end of a transaction.
FALSE
A dynamic cursor saves primary key values when the cursor is opened and retrieves the values for each row as the application program accesses it.
FALSE
A passive data repository is preferred over an active repository because it requires less human intervention.
FALSE
A standby data repository is one which requires a person to generate the metadata and place it in the repository.
FALSE
A transaction is a group of alternative database actions, from which the database can choose to perform only one of them.
FALSE
An exclusive lock locks the item from change but not from read access.
FALSE
Changes in the database structure usually involve only one application.
FALSE
Concurrency control measures are taken to ensure that one user's work has absolutely no influence on another user's work.
FALSE
Copies of each database record or page after it was changed by a transaction that are saved for use in database recovery are called ghost images.
FALSE
Database administration is more important but less difficult in multiuser database systems than in single-user database systems.
FALSE
Explicit locks are locks that are placed automatically by the DBMS.
FALSE
In two-phase locking, all locks are released during the contracting phase.
FALSE
Repeatable Read isolation is the most restrictive level of isolation.
FALSE
Reprocessing is normally the most convenient method for recovery after a system failure.
FALSE
Resource locking must be carefully planned because most DBMS products cannot detect a deadlock condition.
FALSE
Resources are locked for a shorter amount of time with pessimistic locking because the transaction is pre-processed.
FALSE
Rollforward and reprocessing are two different names for the same technique.
FALSE
The DBA is responsible for managing changes to the database structure, but is rarely involved in the original design of the structure.
FALSE
To support rollforward and rollback recovery, transactions must be written to a script before they are applied to the database.
FALSE
Distinguish between the four cursor types.
Forward only cursors are the simplest type of cursor, and only allow the application program to move forward through the record set. The three other types of cursors are all scrollable cursors, meaning that the application program can move forward and backward through the record set. Static cursors take a snapshot of the relation at the point in time when the cursor was opened. Keyset cursors save a copy of the primary key value for each record in the cursor. This key value is then used to retrieve the values of each record as the application program accesses that record. Dynamic cursors are fully functional cursors and can see any changes made to the records by any committed transactions.
Which are more commonly used: implicit or explicit locks? Why?
Implicit locks are more commonly used than explicit locks. Concurrency control involves many complex factors that influence the performance of the system. The impact of some of these factors can only be determined through trial and error. Changing explicit locks to tune system performance can require making changes throughout the program code to obtain and release locks at various places in the transactions. Implicit locks are much easier to change since a locking strategy can be specified in a system parameter or lock declaration area and then the DBMS will place the locks implicitly wherever they are needed to implement that strategy.
Explain the process of using optimistic locking.
Optimistic locking makes the assumption that no conflicts will occur. Data is read, the transaction is processed, updates are issued, and then a check is made to see if a conflict occurred. If a conflict does occur, the transaction is repeated until it completes without a conflict. Thus, the lock is not secured until the transaction is completed. This reduces the amount of time that the lock is held.
Distinguish between statement-level consistency and transaction-level consistency.
Statement-level consistency requires that a statement apply to a set of rows that is consistent from the beginning of the statement until the end of the statement. Transaction-level consistency indicates that the set of rows will remain consistent throughout the processing of all statements within the transaction.
A checkpoint is a point of synchronization between the database and the transaction log.
TRUE
A dirty read happens when one transaction reads a changed record that has not been committed to the database.
TRUE
A durable transaction is one for which all committed changes are permanent.
TRUE
A static cursor processes a snapshot of the relation that was taken when the cursor was opened.
TRUE
A transaction is a series of actions to be taken on the database such that either all of them are performed successfully or none of them is performed at all.
TRUE
A transaction is sometimes called atomic because it is performed as a unit.
TRUE
According to ANSI SQL, the serializable isolation level will not allow phantom reads, dirty reads, and nonrepeatable reads.
TRUE
All commercial DBMS products use some version of "username and password" as part of their security features.
TRUE
An active data repository is one in which the metadata is automatically created as the system components are created.
TRUE
An exclusive lock locks the item from access of any type.
TRUE
Both rollforward and rollback require the use of a log of transaction results.
TRUE
Copies of each database record or page before being changed by a transaction that are saved for use in database recovery are called before images.
TRUE
Database administration tasks have to be performed for single-user, personal databases.
TRUE
In general, optimistic locking is the preferred technique for Internet databases.
TRUE
In general, the overall responsibility of the DBA is to facilitate the development and use of the database system.
TRUE
In regard to database security, neither the DBMS nor the database applications can enforce processing responsibilities.
TRUE
In two-phase locking, all locks are obtained during the growing phase.
TRUE
Locks placed automatically by the DBMS are called implicit locks.
TRUE
Locks placed by a command issued to the DBMS from the application program are called explicit locks.
TRUE
Locks with large granularity are easy for the DBMS to administer but frequently cause conflicts.
TRUE
Nonrepeatable reads occur when a transaction rereads data it has previously read and finds modifications or deletions caused by a committed transaction.
TRUE
One important reason for documenting changes to the database structure is for diagnosing errors.
TRUE
Processing responsibilities should be documented and encoded into manual procedures.
TRUE
Processing rights may be implemented at the DBMS level.
TRUE
Briefly define the three problems that can occur because of concurrent processing that are addressed by resource locking.
The three problems that can occur because of concurrent processing are dirty reads, nonrepeatable reads, and phantom reads. A dirty read occurs when a transaction reads a changed record that has not been committed to the database. If the change is cancelled before it is committed, then the read will contain incorrect data. A nonrepeatable read occurs when a transaction rereads data and finds modifications or deletions caused by another transaction. A phantom read occurs when a transaction rereads data and finds new rows that were inserted by another transaction since the prior read.
Briefly explain the strategy of two-phased locking.
Two-phased locking is a strategy to ensure the serializability of transactions. A transaction is allowed to obtain locks as necessary, but once the first lock is released no other locks may be obtained. This produces the two-phased effect—the growing phase in which locks are obtained and the shrinking phase as locks are released.
