Practice Questions | DB2 9 Fundamentals: Certification Study Guide

1.	Application A holds an Exclusive lock on table TAB1 and needs to acquire an Exclusive lock on table TAB2. Application B holds an Exclusive lock on table TAB2 and needs to acquire an Exclusive lock on table TAB1. If lock timeout is set to -1 and both applications are using the Read Stability isolation level, which of the following will occur? A. Applications A and B will cause a deadlock situation B. Application B will read the copy of table TAB1 that was loaded into memory when Application A first read it C. Application B will read the data in table TAB1 and see uncommitted changes made by Application A D. Application B will be placed in a lock-wait state until Application A releases its lock
2.	Two applications have created a deadlock cycle in the locking subsystem. If lock timeout is set to 30 and both applications were started at the same time, what action will the deadlock detector take when it "wakes up" and discovers the deadlock? A. It will randomly pick an application and rollback its current transaction B. It will rollback the current transactions of both applications C. It will wait 30 seconds, then rollback the current transactions of both applications if the deadlock has not been resolved D. It will go back to sleep for 30 seconds, then if the deadlock still exists, it will randomly pick an application and rollback its current transaction
3.	Application A is running under the Repeatable Read isolation level and holds an Update lock on table TAB1. Application B wants to query table TAB1 and cannot wait for Application A to release its lock. Which isolation level should Application B run under to achieve this objective? A. Repeatable Read B. Read Stability C. Cursor Stability D. Uncommitted Read
4.	Application A holds a lock on a row in table TAB1. If lock timeout is set to 20, what will happen when Application B attempts to acquire a compatible lock on the same row? A. Application B will acquire the lock it needs B. Application A will be rolled back if it still holds its lock after 20 seconds have elapsed C. Application B will be rolled back if Application A still holds its lock after 20 seconds have elapsed D. Both applications will be rolled back if Application A still holds its lock after 20 seconds have elapsed
5.	To which of the following resources can a lock NOT be applied? A. Tablespaces B. Buffer pools C. Tables D. Rows
6.	Which of the following modes, when used with the LOCK TABLE statement, will cause the DB2 Database Manager to acquire a table-level lock that prevents other concurrent transactions from accessing data stored in the table while the owning transaction is active? A. SHARE MODE B. ISOLATED MODE C. EXCLUSIVE MODE D. RESTRICT MODE
7.	An application has acquired a Share lock on a row in a table and now wishes to update the row. Which of the following statements is true? A. The application must release the row-level Share lock it holds and acquire an Update lock on the row B. The application must release the row-level Share lock it holds and acquire an Update lock on the table C. The row-level Share lock will automatically be converted to a row-level Update lock D. The row-level Share lock will automatically be escalated to a table-level Update lock
8.	Application A wants to read a subset of rows from table TAB1 multiple times. Which of the following isolation levels should Application A use to prevent other users from making modifications and additions to table TAB1 that will affect the subset of rows read? A. Repeatable Read B. Read Stability C. Cursor Stability D. Uncommitted Read
9.	A transaction using the Read Stability isolation level scans the same table multiple times before it terminates. Which of the following can occur within this transaction's processing? A. Uncommitted changes made by other transactions can be seen from one scan to the next. B. Rows removed by other transactions that appeared in one scan will no longer appear in subsequent scans. C. Rows added by other transactions that did not appear in one scan can be seen in subsequent scans. D. Rows that have been updated can be changed by other transactions from one scan to the next.
10.	Application A issues the following SQL statements within a single transaction using the Uncommitted Read isolation level: SELECT * FROM department WHERE deptno = 'A00'; UPDATE department SET mgrno = '000100' WHERE deptno = 'A00'; As long as the transaction is not committed, which of the following statements is FALSE? A. Other applications not running under the Uncommitted Read isolation level are prohibited from reading the updated row B. Application A is allowed to read data stored in another table, even if an Exclusive lock is held on that table C. Other applications running under the Uncommitted Read isolation level are allowed to read the updated row D. Application A is not allowed to insert new rows into the DEPARTMENT table as long as the current transaction remains active

Answers

1.	The correct answer is A. If Application B did not already have an Exclusive lock on table TAB2, Application B would be placed in a lock-wait state until Application A released its locks. However, because Application B holds an Exclusive lock on table TAB2, when Application A tries to acquire an Exclusive lock on table TAB2 and Application B tries to acquire an Exclusive lock on table TAB1, a deadlock will occur - processing by both transactions will be suspended until their second lock request is granted. Because neither lock request can be granted until one of the owning transactions releases the lock it currently holds (by performing a commit or rollback operation), and because neither transaction can perform a commit or rollback operation because they both have been suspended (and are waiting on locks), a deadlock has occurred.
2.	The correct answer is A. When a deadlock cycle occurs, all transactions involved will wait indefinitely for a lock to be released unless some outside agent steps in and breaks the cycle. With DB2, this agent is a background process, known as the deadlock detector, and its sole responsibility is to locate and resolve any deadlocks found in the locking subsystem. Each database has its own deadlock detector, which is activated as part of the database initialization process. Once activated, the deadlock detector stays "asleep" most of the time but "wakes up" at preset intervals and examines the locking subsystem to determine whether a deadlock situation exists. If the deadlock detector discovers a deadlock cycle, it randomly selects one of the transactions involved to roll back and terminate; the transaction chosen (referred to as the victim process) is then sent an SQL error code, and every lock it had acquired is released. The remaining transaction(s) can then proceed, because the deadlock cycle has been broken.
3.	The correct answer is D. Typically, locks are not acquired during processing when the Uncommitted Read isolation level is used. Therefore, if Application B runs under this isolation level, it will be able to retrieve data from table TAB1 immediately - lock compatibility is not an issue that will cause Application B to wait for a lock.
4.	The correct answer is A. Anytime one transaction holds a lock on a data resource and another transaction attempts to acquire a lock on the same resource, the DB2 Database Manager will examine each lock's state and determine whether they are compatible. If the state of a lock placed on a data resource by one transaction is such that another lock can be placed on the same resource by another transaction before the first lock acquired is released, the locks are said to be compatible and the second lock will be acquired. However, if the locks are not compatible, the transaction requesting the incompatible lock must wait until the transaction holding the first lock is terminated before it can acquire the lock it needs. If the requested lock is not acquired before the time interval specified in the locktimeout configuration parameter has elapsed, the waiting transaction receives an error message and is rolled back.
5.	The correct answer is B. Locks can only be acquired for tablespaces, tables, and rows.
6.	The correct answer is C. The LOCK TABLE statement allows a transaction to explicitly acquire a table-level lock on a particular table in one of two modes: SHARE and EXCLUSIVE. If a table is locked using the SHARE mode, a table-level Share (S) lock is acquired on behalf of the transaction, and other concurrent transactions are allowed to read, but not change, the data stored in the locked table. If a table is locked using the EXCLUSIVE mode, a table-level Exclusive (X) lock is acquired, and other concurrent transactions can neither access nor modify data stored in the locked table.
7.	The correct answer is C. If a transaction holding a lock on a resource needs to acquire a more restrictive lock on the same resource, the DB2 Database Manager will attempt to change the state of the existing lock to the more restrictive state. The action of changing the state of an existing lock to a more restrictive state is known as lock conversion. Lock conversion occurs because a transaction can hold only one lock on a specific data resource at any given time. In most cases, lock conversion is performed on row-level locks, and the conversion process is fairly straightforward. For example, if an Update (U) lock is held and an Exclusive (X) lock is needed, the Update (U) lock will be converted to an Exclusive (X) lock.
8.	The correct answer is A. When the Repeatable Read isolation level is used, the effects of one transaction are completely isolated from the effects of other concurrent transactions; when this isolation level is used, every row that's referenced in any manner by the owning transaction is locked for the duration of that transaction. As a result, if the same SELECT SQL statement is issued multiple times within the same transaction, the result data sets produced are guaranteed to be the identical. Other transaction are prohibited from performing insert, update, or delete operations that would affect any row that has been accessed by the owning transaction as long as that transaction remains active.
9.	The correct answer is C. When the Read Stability isolation level is used by a transaction that executes a query, locks are acquired on all rows returned to the result data set produced, and other transactions cannot modify or delete the locked rows; however, they can add new rows to the table that meet the query's search criteria. If that happens, and the query is run again, these new rows will appear in the new result data set produced.
10.	The correct answer is D. When the Uncommitted Read isolation level is used, rows retrieved by a transaction are only locked if the transaction modifies data associated with one or more rows retrieved or if another transaction attempts to drop or alter the table the rows were retrieved from. As the name implies, transactions running under the uncommitted read isolation level can see changes made to rows by other transactions before those changes have been committed. On the other hand, transactions running under the Repeatable Read, Read Stability, or Cursor Stability isolation level are prohibited from seeing uncommitted data. Therefore, applications running under the Uncommitted Read isolation level can read the row Application A updated while applications running under a different isolation level cannot. Because no locks are needed in order for Application A to read data stored in other tables, it can do so - even if a restrictive lock is held on that table. However, there is nothing that prohibits Application A from performing an insert operation from within the open transaction.