Managing Multiuser Databases Questions and Answers

Answer: A

Correct answer: Nonrepeatable read

A non-repeatable read occurs when a transaction reads a row, another transaction modifies or deletes it and commits, and the first transaction reads it again and sees something different. The remedy is the repeatable-read isolation level, which holds read locks to the end of the transaction.

Answer: C

Correct answer: Exclusive lock

An exclusive lock excludes everyone else entirely: no other transaction may read the item or change it while the lock is held. It is what a writing transaction needs. A shared lock is the weaker form, permitting concurrent readers but no writer, which is why several may be held on the same item at once.

Answer: B

Correct answer: False

A record of a page after it has changed is an after image; a before image is the copy taken beforehand. Both are kept, and each has its own use in recovery: after images are applied to roll a transaction forward, before images to roll it back.

Answer: B

Correct answer: False

What is described is a dirty read - seeing data from a transaction that is only part way through. The lost update problem is different: two transactions each read the same value, each compute a new one from it, and each write back, so the second silently destroys the first. Resource locking exists to prevent it.

Answer: A

Correct answer: the lock is obtained only after the transaction has processed.

Optimistic locking assumes conflict will be rare: the transaction reads, does its work without holding locks, and only at the end takes a lock long enough to check that nothing it relied on has changed. If something has, the transaction is rolled back and retried. Locks are therefore held for a very short time, which is where the concurrency gain comes from.

Answer: B

Correct answer: Phantom read

A phantom read is the appearance of new rows that were not there on the earlier read, inserted and committed by another transaction in the interval. It is distinct from a nonrepeatable read, where rows already seen have changed or gone. Only the serializable isolation level prevents phantoms.

Answer: A

Correct answer: True

Lock granularity is the size of the item locked, from a single row up to the whole database. Fine granularity allows more transactions to proceed at once but requires far more locks to be held and checked; coarse granularity is cheap to administer but blocks transactions that would not really have conflicted.

Answer: A

Correct answer: True

Transactions are concurrent when their execution overlaps in time, whether or not they touch the same data. The whole apparatus of locking and isolation levels exists to make concurrent transactions behave as though they had run one after another, which is what serializability means.

Answer: C

Correct answer: Dirty read

A dirty read is reading data that another transaction has changed but not yet committed. If that transaction subsequently rolls back, the value read never existed in the database at all, and any decision taken on it is unfounded. Only the read uncommitted isolation level permits it.

Answer: D

Correct answer: Both the first and third answers above are correct.

The two statements are the same responsibility described at two levels. The DBA facilitates the development and use of the database, and that facilitation covers the development, operation and maintenance of the database and its applications. Creating and populating tables is one task within that, not a statement of the overall responsibility.