1.Which of the following isolation levels will lock all rows scanned to build a result data set?
· A. Uncommitted Read
· B. Cursor Stability
· C. Read Stability
· D. Repeatable Read
|
2. 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
|
3. 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.
|
4.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
|
5.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
|
6.Which of the following DB2 UDB isolation levels will only lock rows during read processing if another transaction tries to drop the table the rows are being read from?
· A. Repeatable Read
· B. Read Stability
· C. Cursor Stability
· D. Uncommitted Read
|
7.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
|
8.To which of the following resources can a lock NOT be applied?
· A. Table spaces
· B. Buffer pools
· C. Tables
· D. Rows
|
9.Which of the following causes a lock that is being held by an application using the Cursor Stability isolation level to be released?
· A. The cursor is moved to another row
· B. The row the cursor is on is deleted by the application
· C. The row the cursor is on is deleted by another application
· D. The row the cursor is on needs to be updated by another application
|
10.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
|
11.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
|
12.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
|
13.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
|
14.A table contains a list of all seats available at a football stadium. A seat consists of a section number, a seat number, and whether or not the seat has been assigned. A ticket agent working at the box office generates a list of all unassigned seats. When the agent refreshes the list, it should only change if another agent assigns one or more unassigned seats. Which of the following is the best isolation level to use for this application?
· A. Repeatable Read
· B. Read Stability
· C. Cursor Stability
· D. Uncommitted Read
|
15.Which of the following resources can be explicitly locked?
A. Row
B. Page
C. Table
D. Column
|
16.Application A issues the following SQL statements within in a single
transaction using the Uncommitted Read isolation level:
SELECT * FROM employee WHERE deptno='A00';
UPDATE employee SET salary = salary * 1.1 WHERE deptno='A00';
As long as the transaction has not been committed, which of the following is true for all other applications
not running under the Uncommitted Read isolation level?
A. They can read and make changes to data for any employee in department A00.
B. They can read but not make changes to data for any employee in department A00.
C. They must wait until the transaction is committed before accessing data of any employee in department A00.
D. They must wait until application A disconnects before accessing data of any employee in department A00.
|
17. Which of the following best describes how locks are used in DB2?
A. To maintain control of updated rows for commit processing
B. To ensure only committed changes are altered by another application
C. To allow two applications to update the same row of data simultaneously
D. To prevent multiple applications from accessing the same data simultaneously
|
18.If an application issues the same query more than once in the same Unit of Work, which isolation level will not permit this application to see additional rows inserted by other applications?
A. Read Stability (RS)
B. Repeatable Read (RR)
C. Uncommitted Read (UR)
D. Cursor Stability (CS)
|
19.If DB2 detects a deadlock between application A and application B (each waiting for a lock held by the other one), what action will DB2 take?
A. Rollback the transaction in both applications.
B. Rollback the transaction in one of the two applications.
C. Decrease the lock timeout value for both applications, thereby causing a lock timeout situation.
D. Send a message to let the administrator know of the situation and decide which application or applications should be terminated.
|
20.How does DB2 protect the integrity of indexes when data is updated?
A. Locks are acquired on the data.
B. Locks are acquired on index keys.
C. Locks are acquired on index pages.
D. Locks are acquired on index pointers.
|
21.Application A currently holds an exclusive lock on a single row and application B tries to access that row. If lock timeout is set to -1 and both applications are using isolation level RS, which of the following will occur?
A. Applications A and B will cause a deadlock situation.
B. Application B will read the previous version of the row.
C. Application B will read the row 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.
|
22.Which of the following resources can be referenced in the LOCK statement?
A. Row
B. Table
C. Column
D. Table space
|
23.If application A issues the following SQL statement, which of the following statements about concurrency is true?
SELECT deptno, deptname, mgrno FROM dept WHERE admrdept = 'A00' FOR READ ONLY WITH RS
A. Rows accessed by application A can be seen by other applications.
B. Rows accessed by application A cannot be seen by other applications.
C. Application A can see uncommitted changes made by other applications.
D. Results produced by re-execution of the statement by application A will not be affected by other applications.
|
24.Which of the following best describes the lock protection provided by DB2 for the current row of a cursor?
A. The cursor is only protected from updates and deletes by concurrent applications.
B. The row is only protected from positioned updates and deletes that reference another cursor of the same application.
C. The cursor is protected from positioned updates and deletes that reference another cursor of a different application.
D. The row is protected from updates and deletes by the current application and from positioned updates and deletes that reference another cursor of the same application.
|
25.Which of the following best describes the lock protection provided by DB2 for the current row of a cursor?
A. The cursor is only protected from updates and deletes by concurrent applications.
B. The row is only protected from positioned updates and deletes that reference another cursor of the same application.
C. The cursor is protected from positioned updates and deletes that reference another cursor of a different application.
D. The row is protected from updates and deletes by the current application and from positioned updates and deletes that reference another cursor of the same application
|
26.Which of the following is TRUE for the DB2 isolation level Cursor Stability (CS)?
A. An application process acquires at least a share lock on the current row of every cursor.
B. Any row that is read during a unit of work cannot be changed by other application processes until the unit of work is complete.
C. Any row changed by another application process can be read, even if the change has not been committed by that application process.
D. An application process that issues the same query more than once in a unit of work will not see additional rows caused by other application processes appending new information to the database.
|
27.Application A is designed to execute the following SQL statements within a single Unit of Work (UOW).
UPDATE employee SET salary = salary * 1.1 WHERE empno='000010'
UPDATE department SET deptname = 'NEW dept' WHERE deptno='A00'
Application B is designed to execute the following SQL statements within a single Unit of Work (UOW).
UPDATE department SET deptname = 'OLD DEPT' WHERE deptno='A00'
UPDATE employee SET salary = salary * 0.5 WHERE empno='000010'
Application A and application B execute their first SQL statement at the same time. When application A and application B try to execute their second SQL statement, a deadlock occurs. What will happen?
A. The database manager will rollback the transaction in both applications.
B. The database manager will rollback the transaction in one of the two applications.
C. Application B will successfully update the EMPLOYEE and DEPARTMENT tables; Application A will be placed in a lock wait state.
D. Application A will successfully update the EMPLOYEE and DEPARTMENT tables; Application B will terminate when the lock timeout value is reached.
|
28.Application A holds an Update lock on a single row and application B is trying to read that row. If both applications are using isolation level UR, which of the following will occur?
A. Application B will read the row.
B. Applications A and B will cause a deadlock situation.
C. Application B will wait until application A releases the Update lock.
D. Application A will be terminated so that application B can read the row.
|
29.To which of the following resources can a lock be applied?
A. Row
B. Alias
C. Bitmap
D. Column
Solutions ::::::::::::::::::::
1.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.)
|
2.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.
|
3.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.
|
4.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.
|
5.The correct answer 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.
|
6.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.
|
7.The correct answer is A. Any time 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.
|
8.The correct answer is B. Locks can only be acquired for table spaces, tables, and rows.
|
9.The correct answer is A. If a row level lock is held by a application using the Cursor Stability isolation level, that lock remains in effect until either the cursor is moved to a new row (at which time the lock for the old row is released - if possible, and a new lock for the current row is acquired) or the transaction holding the lock is terminated.
|
10.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.
|
11.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.
|
12.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.
|
13.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.
|
14.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.
|
15.Answer: C
|
16.Answer: C
|
17.Answer: B
|
18.Answer: B
|
19.Answer: B
|
20.Answer: A
|
21.Answer: D
|
22.Answer: B
|
23.Answer: A
|
24.Answer: A
|
25.Answer: A
|
26.Answer: A
|
27.Answer: B
|
28.Answer: A
|
29.Answer: A
|
|
No comments:
Post a Comment