Transaction Management and Concurrency Control

Question 1

What does a logical unit of work that must be entirely completed or aborted consist of?

Answer 1

SELECT statement

Series of related UPDATE statements

Series of INSERT statements

Combination of SELECT, UPDATE, and INSERT statements

Question 2

What does a consistent database state?

Answer 2

All data integrity constraints are satisfied

Must begin with the database in a known consistent state to ensure consistency

Question 3

How are most transactions formed?

Answer 3

They are formed by two or more database requests

Question 4

What are database requests?

Answer 4

They are the equivalent of a single SQL statement in an application program or transaction

Question 5

Why aren’t all transactions updated in the database?

Answer 5

Because SQL code represents a transaction because it accesses the database

Question 6

How can improper or incomplete transactions can have devastating effect on database integrity?

Answer 6

Users can define enforceable constraints based on business rules

Other integrity rules are automatically enforced by the DBMS

Question 7

What is atomicity?

Answer 7

All operations of a transaction must be completed; if not the transaction is aborted

Question 8

What is consistency?

Answer 8

Permanence of database’s consistent state

Question 9

What is isolation?

Answer 9

Data used during transaction cannot be used by second transaction until the first is completed

Question 10

What is durability?

Answer 10

Ensures that once transactions are committed they cannot be undone or lost

Question 11

What is serializability?

Answer 11

Ensures that the schedule for the concurrent execution of several transactions should yield consistent results

Question 12

What SQL statements provide transaction support?

Answer 12

COMMIT
ROLLBACK

Question 13

When should the transaction sequence continue until one of four events occur?

Answer 13

COMMIT statement is reached
ROLLBACK statement is reached
End of program is reached
Program is abnormally terminated

Question 14

What does a transaction log do?

Answer 14

Keeps track of all transactions that update the database

Question 15

What does the DBMS use the information stored in a log for?

Answer 15

Recovery requirement triggered by a ROLLBACK statement
Program’s abnormal termination
System failure

Question 16

What is the objective of coordination of the simultaneous transactions execution in a multiuser database system?

Answer 16

Ensures serializability of transactions in a multiuser database environment

Question 17

Why is concurrency control important?

Answer 17

It is important because the simultaneous execution of transactions over a shared database can create several data integrity and consistency problems

Question 18

What are the three main problems of concurrency control?

Answer 18

lost updates
uncommitted data
inconsistent retrievals

Question 19

What is a lost update?

Answer 19

Occurs in two concurrent transactions when:

Same data element is updated

One of the updates is lost

Question 20

What is uncommitted data?

Answer 20

Occurs when:

Two transactions are executed concurrently

First transaction is rolled back after the second transaction has already accessed uncommitted data

Question 21

What is inconsistent retrivals?

Answer 21

Occurs when:

A transaction accesses data before and after one or more other transactions finish working with such data

Question 22

What is the locking method?

Answer 22

Locking methods facilitate isolation of data items used in concurrently executing transactions

Question 23

What is a lock?

Answer 23

Guarantees exclusive use of a data item to a current transaction

Question 24

What is pessimistic locking?

Answer 24

Use of locks based on the assumption that conflict between transactions is likely

Question 25

What is a lock manager?

Answer 25

Responsible for assigning and policing the locks used by the transactions