Transaction Management and Concurrency Control Flashcards
What does a logical unit of work that must be entirely completed or aborted consist of?
SELECT statement
Series of related UPDATE statements
Series of INSERT statements
Combination of SELECT, UPDATE, and INSERT statements
What does a consistent database state?
All data integrity constraints are satisfied
Must begin with the database in a known consistent state to ensure consistency
How are most transactions formed?
They are formed by two or more database requests
What are database requests?
They are the equivalent of a single SQL statement in an application program or transaction
Why aren’t all transactions updated in the database?
Because SQL code represents a transaction because it accesses the database
How can improper or incomplete transactions can have devastating effect on database integrity?
Users can define enforceable constraints based on business rules
Other integrity rules are automatically enforced by the DBMS
What is atomicity?
All operations of a transaction must be completed; if not the transaction is aborted
What is consistency?
Permanence of database’s consistent state
What is isolation?
Data used during transaction cannot be used by second transaction until the first is completed
What is durability?
Ensures that once transactions are committed they cannot be undone or lost
What is serializability?
Ensures that the schedule for the concurrent execution of several transactions should yield consistent results
What SQL statements provide transaction support?
COMMIT
ROLLBACK
When should the transaction sequence continue until one of four events occur?
COMMIT statement is reached
ROLLBACK statement is reached
End of program is reached
Program is abnormally terminated
What does a transaction log do?
Keeps track of all transactions that update the database
What does the DBMS use the information stored in a log for?
Recovery requirement triggered by a ROLLBACK statement
Program’s abnormal termination
System failure
What is the objective of coordination of the simultaneous transactions execution in a multiuser database system?
Ensures serializability of transactions in a multiuser database environment
Why is concurrency control important?
It is important because the simultaneous execution of transactions over a shared database can create several data integrity and consistency problems
What are the three main problems of concurrency control?
lost updates
uncommitted data
inconsistent retrievals
What is a lost update?
Occurs in two concurrent transactions when:
Same data element is updated
One of the updates is lost
What is uncommitted data?
Occurs when:
Two transactions are executed concurrently
First transaction is rolled back after the second transaction has already accessed uncommitted data
What is inconsistent retrivals?
Occurs when:
A transaction accesses data before and after one or more other transactions finish working with such data
What is the locking method?
Locking methods facilitate isolation of data items used in concurrently executing transactions
What is a lock?
Guarantees exclusive use of a data item to a current transaction
What is pessimistic locking?
Use of locks based on the assumption that conflict between transactions is likely
What is a lock manager?
Responsible for assigning and policing the locks used by the transactions
