Deck 15: Transaction Management

Question 1

Any number of users can hold a shared lock on the same part of the database,but only one user can hold an exclusive lock on that part of the database.

Answer 1

True

Question 2

In the case of an uncommitted dependency problem,transaction A may rely on data that no longer exists if transaction B is aborted before it commits.

Answer 2

True

Question 3

The Atomic property of a transaction means that the entire transaction must be completed or the database is restored to the state it was in before the transaction started.

Answer 3

True

Question 4

The optimistic concurrency control approach may delay a transaction longer than the pessimistic approach would have,because with the optimistic approach the conflicting transactions have to be rolled back and restarted,rather than waiting for a lock to be released.

Question 5

The keywords START TRANSACTION and COMMIT to denote the beginning and end of a transaction.

Question 6

To restore a database using the immediate update approach,both undo and redo operations may be necessary,whereas with the deferred update approach only redo operations are used.

Question 7

The "D" in the ACID properties of transactions means that the changes occurring during a transaction must be permanent.No failure will erase any changes after a transaction terminates.

Question 8

A lost update problem occurs when two or more transactions are trying to write to the same part of the database.

Question 9

Because a transaction is a user-defined set of database operations,a DBMS cannot limit a transaction to a specific number of database reads or writes.

Question 10

The "I" in the ACID properties of transactions means that the changes occurring during a transaction must be Isolated from other users until the transaction completes.

Question 11

An example of concurrency transparency would be that multiple students could register for the same course offering at the same time,without interference from each other.

Question 12

Hot spots occur only on rows,fields or tables that many users want to change simultaneously,but never on parts of the database users do not normally see,such as pages or indexes.

Question 13

One of the advantages of concurrency transparency is that relative to the results produced,a transaction takes place as if the transaction was executing alone.

Question 14

In designing a hierarchical form,the best choice for the transaction boundary would be to treat the main form as one transaction and each subform line as separate transactions.

Question 15

A lock on a larger data element increases both system overhead and waiting by transactions.

Question 16

If the SQL transaction boundary statements START TRANSACTION and COMMIT are not used,by default all of the SQL statements that comprise the entire logical transaction are considered a single transaction.

Question 17

When transactions T1 and T2 are in a deadlock,one of the transactions will eventually execute to completion and let the other one continue execution.

Question 18

The isolation level specifies the degree to which a transaction is separated from the actions of other transactions.A transaction designer can balance concurrency control overhead with potential interference problems by specifying the appropriate isolation level.

Question 19

Checkpoints can involve considerable disruption to transaction processing because no new transactions can begin and existing transactions cannot begin new operations during a checkpoint.

Question 20

When an update to the database takes place,the time of the update,the values of the data before and after the update,and the type of lock used are recorded in the transaction log.

Question 21

Which of the following is not considered to be a failure affecting DBMS transactions?

A) An abnormal termination of a transaction
B) A data entry error resulting in wrong data in the database
C) An operating system failure
D) A disk crash

Question 22

Julia has a shared lock on the savings account and Bill is trying to perform an action requesting a shared lock on the same data:

A) Bill's transaction is granted permission to perform the desired action
B) Bill's transaction must wait until Julia's lock is released
C) Bill's transaction must wait until Julia has read the savings account value
D) None of the above

Question 23

Typically,the following is not true under most operating systems control:

A) Data is placed in a buffer before being written to a disk
B) A buffer is written to a disk only when it is full
C) Data is directly written to disk when the "write" operation is requested by an application program
D) A buffer may contain many records

Question 24

Which of the following will cause a deadlock?

A) Transaction T1 requests an exclusive lock on data A at the same time as transaction T2
B) Simultaneously, transaction T2 places an exclusive lock on data B, then requests one on data A
B) Transaction T1 places an exclusive lock on data A, then transaction T2 requests a shared lock on data A
C) Transaction T1 places an exclusive lock on data A, then requests one on data
D) Transaction T1 and T2 modify data A without any lock

Question 25

Which of the following is an example of lost update?

A) The system assigns the last aisle seat on a flight to John whereas Peter receives a window seat, although both indicated preference for an aisle seat
B) John and Peter receive each a window seat as they both requested
C) John is initially assigned the last window seat, but this seat is later on assigned to Peter
D) None of the above

Question 26

SQL:2003 provides for a SAVEPOINT statement which establishes an intermediate point in a transaction that can be used to perform a partial rollback of the transaction.

Question 27

Which one of the following is the least likely to be viewed as a transaction?

A) Retrieving a customer's latest purchase
B) Transferring $1000 from a customer's savings account to the customer's checking account
C) Reserving a seat on a plane
D) Reserving a hotel room

Question 28

Which of the following statements may appear in the code for a transaction?

A) ROLLBACK
B) COMMIT
C) BEGIN TRANSACTION
D) All of the above

Question 29

Julia has a shared lock on the savings account and Bill is trying to perform an action requesting an exclusive lock on the same data:

A) Bill's transaction is granted permission to perform the desired action and Julia's transaction must now wait
B) Bill's transaction must wait until Julia's lock is released
C) Bill's transaction must wait until Julia has read the savings account value
D) None of the above

Question 30

Which of the following is not a recovery tool?

A) Time-out of a deadlocked transaction
B) Force writing
C) Transaction log
D) Checkpoint table

Question 31

In the two-phase locking protocol:

A) A transaction only acquires a lock after the previously acquired lock has been released
B) A transaction acquires locks as needed. After it starts releasing locks, it does not acquire new locks
C) A transaction acquires all needed locks at once, then releases them after completion
D) The transaction locks the full database, then releases the locks

Question 32

Locking Granularity: the size of the database item locked.Locking granularity is a trade-off between waiting time (amount of concurrency permitted)and overhead (number of locks held).

Question 33

Three enabling technologies which support workflow are distributed object management,recovery management,and customized transaction management.

Question 34

The ACID properties are insured by the following services of the DBMS:

A) Concurrency transparency
B) Recovery transparency
C) Data dictionary
D) Concurrency transparency and Recovery transparency

Question 35

Julia has an exclusive lock on the savings account and Bill is trying to perform an action requesting a shared lock on the same data:

A) Bill's transaction is granted permission to perform the desired action
B) Bill's transaction must wait until Julia's lock is released
C) Bill's transaction must wait until Julia has read the savings account value
D) None of the above

Question 36

In the optimistic approach:

A) Only shared locks are used
B) Deadlocks are always detected before they occur
C) Only exclusive locks are used
D) Locks are not used

Question 37

Julia has a lock on the savings account and Bill is trying to perform a conflicting action on the data:

A) Bill's transaction is granted permission to perform the desired action
B) Bill's transaction must wait until Julia's lock is released
C) Bill's transaction must wait until Julia has read the savings account value
D) None of the above

Question 38

Which of the following problems is not caused by insufficient concurrency control?

A) Lost update
B) Hot spot
C) Non repeatable read
D) Uncommitted dependency

Question 39

A hot spot is common data that multiple users try to change simultaneously.

Question 40

The serializable isolation level is not as strict as either the read committed or the repeatable read.

Question 41

Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee)under the immediate update mode.The transaction terminates abnormally after 10 rows have been updated in the database (and the normal operations have taken place on the log).What should the recovery procedure perform to make sure the transaction completes as desired?

A) Redo
B) Redo followed by execution of the transaction again
C) Undo
D) Undo followed by execution of the transaction again

Question 42

Typically,the most serious concurrency problem to avoid in transaction design is:

A) Incorrect summary
B) Uncommitted dependencies
C) Lost update
D) Deferred update

Question 43

A ________is common data that multiple users try to change.Without adequate concurrency control,users may interfere with each other on ______s.

Question 44

Which one of the following isolation levels does not prevent the scholar's lost update problem?

A) Read committed
B) Read uncommitted
C) Repeatable read
D) Serializable

Question 45

Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee)under the immediate update mode and completes before a checkpoint.The system crashes after the checkpoint.What should the recovery procedure perform to make sure the transaction completes as desired?

A) Nothing
B) Redo followed by execution of the transaction again
C) Undo
D) Undo followed by execution of the transaction again

Question 46

Depending on the DBMS,checkpoints:

A) Are performed when all transactions have ceased
B) Are performed concurrently with transactions
C) Either of the above
D) None of the above

Question 47

With database locking,a(n)_______________ lock must be obtained before reading a database item,and a(n)___________ lock must be obtained before writing to a database item.

Question 48

A(n)____________________ is also known as a dirty read because it is caused by one transaction reading dirty data.

Question 49

A measure of the amount of work performed by a DBMS is the number of transactions processed per time unit.This measure is known as _____________________.

Question 50

The transaction property of being ________ means that transactions do not interfere with each other except in allowable ways.A transaction should never overwrite changes made by another transaction.In addition,a transaction may be restricted from interfering in other ways such as not viewing the temporary changes made by other transactions.

Question 51

A(n)____________________ is an inconsistent retrieval problem which occurs when a transaction reads several values,but another transaction updates some of the values while the first transaction is still executing.

Question 52

Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee)under the immediate update mode and completes after a checkpoint but before a system failure.The system crashes after the checkpoint.What should the recovery procedure perform to make sure the transaction completes as desired?

A) Nothing
B) Redo forward from checkpoint
C) Undo
D) Undo followed by execution of the transaction again

Question 53

Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee)under the deferred update mode and completes after a checkpoint.The system crashes after the checkpoint.What should the recovery procedure perform to make sure the transaction completes as desired?

A) Redo from first log record
B) Redo from first log record followed by execution of the transaction again
C) Undo
D) Undo followed by execution of the transaction again

Question 54

Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee)under the deferred update mode.The transaction terminates abnormally after the new values of 10 rows have been written to the log.What should the recovery procedure perform to make sure the transaction completes as desired?

A) Execute the transaction again
B) Redo followed by execution of the transaction again
C) Undo
D) Undo followed by execution of the transaction again

Question 55

Name three types of inconsistent retrievals: __________________________________

Question 56

Common data that multiple users try to change simultaneously is called a(n)_____________.

Question 57

A(n)______________________ is a collection of operations that must be processed reliably as a single unit of work.

Question 58

At checkpoint time:

A) Log buffers are force written
B) Undo operations take place
C) Redo operations take place
D) Disk backup takes place

Question 59

Dividing an application program into several transactions usually:

A) Improves constraint checking
B) Reduces the possibility of deadlocks
C) Increases potential waiting by other transactions
D) All of the above

Question 60

The objective of ___________________ is to maximize transaction throughput while preventing interference among multiple users.

Question 61

When transaction T1 has a lock on a resource that transaction T2 needs,and T2 has a lock on a resource that T1 needs,this situation is known as a(n)_______________.

Question 62

When the recovery manager performs a(n)__________ operation on the log,the database reverts to a previous state by substituting the old value for the value that is stored in the database.

Question 63

With the _____________________ approach,database updates are written to disk when they occur,but only after the corresponding log updates have been written.

Question 64

With the _____________________ approach,database updates are written to disk only after a transaction commits.

Question 65

The degree to which a transaction is separated from the actions of other transactions is called the ___________________.

Question 66

One of the tools used by the recovery manager is a(n)__________________,which is a table containing the history of database changes.

Question 67

___________________ refers to the size of the database item being locked,and results in a trade-off between system overhead and transaction waiting.

Question 68

A type of lock usually used on a large database item,such as a table,to alleviate blocking when locking coarse items is called a(n)_________________.

Question 69

A collection of related tasks structured to accomplish a business purpose,which can be either human-oriented or computer-oriented,is known as a(n)________________.

Question 70

The recovery manager uses ___________________ to control when data is written to nonvolatile storage.

Question 71

In order to prevent lost update problems,the _______________________ requires transactions to hold on to their exclusive locks until the transaction commits.

Question 72

The decision made by a transaction designer as to how a collection of SQL statements is divided into one or more transactions is known as the _______________________.

Question 73

For each transaction,the ____________________ statement can be used to specify whether deferrable constraints are deferred or immediately enforced.

Question 74

When the recovery manager performs a(n)__________ operation on the log,a new state is established by substituting the new value for the value that is stored in the database.

Question 75

Some transactions have tentative actions that can be canceled by user actions or other events.In order to allow a partial rollback of a transaction (to save parts of the transaction not affected by those user actions or events),SQL:2003 provides the _______ statement.