DB2 Universal Database V8.1 Certification Exam 700 Study Guide

The correct answer is D. The Repeatable Read isolation level will lock all rows scanned in response to a query. (The Read Stability isolation level will only lock the rows returned in the result data set; the Cursor Stability isolation level will only lock the row in the result data set that the cursor is currently pointing to; and the Uncommitted Read isolation level will not lock any rows during normal read processing.)

The correct answers are A and E. Locks can only be acquired for tablespaces, tables, and rows.

The correct answer is D. Usually, locks are not acquired during processing when the Uncommitted Read isolation level is used. However, rows that are retrieved by a transaction using the Uncommitted Read isolation level will be locked if another transaction attempts to drop or alter the table from which the rows were retrieved.

The correct answer is A. To ensure that the data being accessed by a transaction running under the Repeatable Read isolation level is not adversely affected by other transactions, each row referenced by the isolating transaction is locked. As a result, if the same SELECT SQL statement is issued two or more times within the same transaction, the result data set produced will always be the same.

The correct answer is B. When the Repeatable Read and Read Stability isolation levels are used, all locks acquired are released when the owning transaction terminates by performing a commit or a rollback operation. (When the Cursor Stability isolation level is used, a lock is held on the row the cursor is pointing to, and when the cursor is moved to a new row, the lock for the old row is released and a lock for the new row is acquired; when the Uncommitted Read isolation level is used, locks are usually not acquired.)

The correct answer is C. The LOCK TABLE statement allows a transaction to 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.

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.

The correct answer is D. If a table is marked as read-only, there is no need to use a restrictive isolation level when accessing it; since no transaction can modify its data, queries against the table should always return the same result data set each time they are run. Therefore, if the least restrictive isolation level (which is Uncommitted Read) is used, more applications can access the table at the same time (concurrently).

The correct answer is B. When the Repeatable Read isolation level is used, every row that is referenced in any manner by the isolated transaction is "locked" for the duration of that transaction. Thus, if a transaction scans 10,000 rows in order to retrieve 100, locks are acquired and held on all 10,000 rows scanned ”not just on the 100 rows retrieved. (If the Read Stability isolation level is used, only the rows returned to the result data set are locked; if the Cursor Stability isolation level is used, only the last row accessed is locked; and if the Uncommitted Read isolation level is used, no rows are locked.)

The correct answer is C. If the Repeatable Read isolation level is used, other agents will be unable to assign seats as long as the transaction that generated the list remains active; therefore, the list will not change when it is refreshed. If the Read Stability isolation level is used, other agents will be able to unassign currently assigned seats (and these unassigned seats will show up when the list is refreshed), but they will not be able to assign any seat that appears in the list as long as the transaction that generated the list remains active. If the Uncommitted Read isolation level is used, other agents will be able to unassign currently assigned seats, as well as assign unassigned seats; however, uncommitted seat unassignments/assignments will show up when the list is refreshed, and the agent may make an inappropriate change based on this data. Therefore, the best isolation level to use for this particular application is the Cursor Stability isolation level.