Question 1 Flashcards
What is a transaction?
A sequence of one or more SQL statements that are executed as a single, logical unit of work. It ensures the consistency and integrity of a database.
Name the 5 transaction properties.
- Atomicity
- Consistency
- Isolation
- Durability
- Serializability
What is transaction Atomicity?
Ensures a transaction is treated as a single, logical unit of work. Either all of its operations are completed, or none are. If any part of the transaction fails, the entire transaction is rolled back.
What is transaction Consistency?
A transaction should bring the database from one consistent state to another. It means that the integrity constraints and business rules must be satisfied before and after the transaction.
What is transaction Isolation?
A database transaction property in which a data item used by one transaction is unavailable to other transactions until the first one ends.
What is transaction Durability?
The transaction property that ensures that once transaction changes are done and committed, they cannot be undone or lost, even in the event of a system failure.
What is transaction Serializability?
A property in which the selected order of concurrent transaction operations creates the same final database state that would have been produced if the transactions had been executed serially.
What is the Transaction log?
The transaction log records all operations that modify the database.
What is the function of the Transaction Log?
This log is essential for recovery, allowing the system to undo uncommitted changes (via ROLLBACK) in case of system failures, program crashes, or network issues.
Some systems, like Oracle, also use the log to recover and bring the database to a consistent state by rolling back uncommitted transactions and applying committed ones that haven’t been saved to disk.
This ensures data integrity and consistency in transactional databases.
Define Locking Methods.
Involves placing locks on database objects (e.g., rows, tables) to prevent concurrent transactions from accessing or modifying the same data simultaneously.
Locking is controlled by a lock manager.
Name the 5 Lock Granularity levels.
- Database-level: Restricts access to the owner of the lock and allows only one user at a time to access the database.
- Table-level: Allows only one transaction at a time to access a table.
- Page-level: An entire disk page or section of a disk is locked.
- Row-level: Allows concurrent transactions to access different rows of the same table, even if the rows are on the same page.
- Field-level: Allows concurrent transactions to access the same rows as long as they require the use of different attributes within that row.
Name 3 lock types.
- Binary lock: Has only two states, locked (1) and unlocked (0). If a data item is locked, no other transaction can use that data item.
- Exclusive lock: Issued when one or more transactions must update (WRITE) a data item. Therefore, an exclusive lock is issued only when a transaction must WRITE (update) a data item and no locks (not shared or exclusive) are currently held on that data item by any other transaction.
- Shared lock: Issued when a transaction must read data from the database and no exclusive locks are held on the data to be read.
What is a Deadlock?
Occurs when two transactions wait indefinitely for each other to unlock data.
Name 3 Deadlock control techniques.
- Avoidance: The transaction must obtain all the locks needed before it can be executed.
- Prevention: Transaction requesting a new lock is aborted when there is a possibility that a deadlock can occur.
- Detection: DBMS tests the database periodically for deadlocks, if found, the victim transaction is aborted.
What is Two-phase Locking?
A set of rules that manage how transactions acquire and release locks to ensure serializability, though it doesn’t prevent deadlocks.
