REVIEW - THEORY QUESTIONS Flashcards
What is the purpose of the SAVEPOINT statement?
The SAVEPOINT statement allows you to set a point within a transaction to which you can later roll back, making it possible to undo part of a transaction without affecting the rest.
What are the ACID Properties?
Atomicity, Consistency, Isolation and Durability.
What is atomicity?
Ensures that all parts of a transaction are completed successfully, or none are. If any part fails, the entire transaction is rolled back.
What is consistency?
Guarantees that a transaction brings the database from one valid state to another, preserving database integrity.
What is isolation?
Ensures that transactions are executed independently without interference, preventing issues like dirty reads or lost updates.
What is durability?
Once a transaction is committed, its results are permanent and survive system failures.
What are the Isolation Levels in PL/SQL?
Read Uncommitted, Read Committed, Repeatable Read, Serializable.
What is Read Uncommitted?
Allows dirty reads, where transactions can see uncommitted changes from others.
What is Read Committed?
Prevents dirty reads by only allowing transactions to see committed changes.
What is Repeatable Read?
Ensures that if a transaction reads a record, it will see the same record throughout the transaction, but doesn’t prevent phantom reads.
What is Serializable?
Highest isolation level, ensuring transactions are executed as if they were serially executed, preventing dirty reads, non-repeatable reads, and phantom reads.
What is the difference between role and user privilege?
A role is a collection of privileges that can be assigned to users, simplifying the management of permissions. User privileges are individual permissions granted directly to a user.
How do you end a transaction?
A transaction is explicitly ended using the COMMIT statement (to save changes) or the ROLLBACK statement (to undo changes).
Why are roles so important for a database’s security?
Roles group multiple privileges, making it easier to manage user permissions. Assigning roles instead of individual privileges reduces administrative overhead and enhances security by ensuring users have only the necessary access.
What is the difference between object and system privileges?
Object Privileges: Specific to database objects, such as SELECT, INSERT, or DELETE on a table.
System Privileges: Broader permissions that affect the entire database, like CREATE TABLE or ALTER DATABASE.
What does consistency do in ACID?
This property ensures that a transaction moves the database from one consistent state to another, preserving data integrity by enforcing all constraints.
Why is isolation so important in ACID?
Isolation levels like Serializable ensure that transactions are executed without interference, preventing one transaction from overwriting the changes made by another.
What’s a scenario without atomicity?
In a banking application, if a transaction to transfer funds fails after debiting one account but before crediting another, the system could become inconsistent, with funds lost or duplicated.
How is durability implemented?
Durability is implemented through techniques like write-ahead logging, where changes are recorded in a log before being committed to the database. This ensures that, in case of a crash, the database can recover committed transactions by reapplying the changes from the log.
When does non-repeatable read occur?
When a transaction reads the same row twice and finds different data due to another transaction modifying the row in between.
What isolation level allows dirty reads?
Read Uncommitted
What is the difference between repeatable read and serializable?
Repeatable Read ensures consistent reads within a transaction but does not prevent phantom reads.
Serializable ensures no other transactions can affect the data being read, preventing all types of data anomalies but at the cost of performance due to higher locking.
How do the higher and lower levels of isolation impact concurrency and performance?
Lower isolation levels allow more concurrent transactions at the cost of potential data anomalies, while higher isolation levels, like Serializable, ensure data consistency but reduce concurrency due to increased locking and potential deadlocks.
What is the difference between implicit and explicit cursors?
An implicit cursor is automatically created for single SQL statements, while an explicit cursor must be manually declared and controlled.
