Transactions Flashcards
What is a database transaction?
A unit of program execution that accesses and possibly updates various data items delimited by begin transaction and end transaction statements
What does the ACID acronym stand for in database transactions?
Atomicity Consistency Isolation Durability
What is atomicity in database transactions?
Either all operations of the transaction are reflected in the database or none are
What is consistency in database transactions?
The database remains in a consistent state before and after transaction execution
What is isolation in database transactions?
Each transaction appears to execute alone even when multiple transactions run concurrently
What is durability in database transactions?
Once a transaction completes successfully its changes persist even if system failures occur
What are the two basic operations transactions use to access data?
read(X) and write(X)
What are the five possible states of a transaction?
Active Partially committed Failed Aborted Committed
What happens in the partially committed state?
Final statement executed but transaction may still need to be aborted as data might still be in main memory
What is conflict serializability?
A schedule that can be transformed into a serial schedule by swapping non-conflicting operations
When do two operations conflict in a transaction schedule?
When they are from different transactions operate on same data item and at least one is a write operation
How can you determine if a schedule is conflict serializable?
Create a precedence graph - if it has no cycles the schedule is conflict serializable
What is a recoverable schedule?
One where transactions commit only after all transactions whose changes they read have committed
What is a cascadeless schedule?
One where transactions only read data written by committed transactions
What are the three main implementation techniques for transaction isolation?
Locking Timestamp ordering and Versioning
What is snapshot isolation?
A versioning technique where each transaction works with its own version of the database when it begins
What information does a precedence graph contain?
Vertices represent transactions edges represent conflicts between operations
What are the two ways to handle an aborted transaction?
- Restart it (if aborted due to hardware/software error) 2. Kill it
What is cascading rollback?
When failure of one transaction forces rollback of other dependent transactions
What is view serializability?
A schedule that is equivalent to a serial schedule in terms of initial reads writes and final writes
Why is concurrent execution of transactions preferred over serial execution?
Two reasons: 1) Improved throughput and resource utilization 2) Reduced waiting time for transactions
What is the fundamental problem with immediate external writes (like ATM cash dispense) in transactions?
They cannot be undone once executed so they should only occur after the transaction has fully committed
What is stored in a transaction log?
The old values of any data on which a transaction performs a write stored on disk for recovery purposes
What are the three conditions for two schedules to be view equivalent?
1) Same initial reads 2) Same reads following writes 3) Same final writes for each data item
For conflict operations what are the four possible combinations and which ones conflict?
read-read: no conflict read-write: conflict write-read: conflict write-write: conflict
What’s the main difference between view serializability and conflict serializability?
Every conflict serializable schedule is view serializable but not vice versa. View serializability is also NP-complete to check
What happens when a transaction enters the failed state?
The system determines it can’t proceed with normal execution and must be rolled back to enter the aborted state
What is a compensating transaction?
A transaction that reverses the effects of a committed transaction when external actions can’t be undone (like ATM cash dispense)
What must be true about the read and write timestamps in timestamp-based concurrency control?
Read timestamp holds the largest (most recent) timestamp of transactions that read the item; write timestamp holds the timestamp of the transaction that wrote the current value
In locking implementation what’s the difference between database-level and item-level locking?
Database-level locking locks the entire database for each transaction while item-level locking only locks the specific data items being accessed