Podcast
Questions and Answers
What must a transaction do to avoid deadlock?
What must a transaction do to avoid deadlock?
- Obtain locks in any order.
- Release locks immediately after reading.
- Use only exclusive locks for all actions.
- Obtain all required locks before execution. (correct)
Which state is NOT one of the states of a binary lock?
Which state is NOT one of the states of a binary lock?
- Locked
- Unlocked
- Shared (correct)
- Exclusive
In a shared lock scenario, what is true about the data being read?
In a shared lock scenario, what is true about the data being read?
- Multiple transactions can read the data concurrently. (correct)
- The data must be in a committed state.
- The data is reserved exclusively for one transaction.
- No other transaction can read the data.
What is a consequence of a dirty read in a transaction?
What is a consequence of a dirty read in a transaction?
What happens when a deadlock occurs?
What happens when a deadlock occurs?
What is an exclusive lock used for?
What is an exclusive lock used for?
What characterizes the serial lock assignment in deadlock avoidance?
What characterizes the serial lock assignment in deadlock avoidance?
In the example provided, what happens to Mary's salary after the treasurer rolls back the transaction?
In the example provided, what happens to Mary's salary after the treasurer rolls back the transaction?
What occurs during a non-repeatable read?
What occurs during a non-repeatable read?
Which isolation level allows transactions to ignore locks placed by other transactions?
Which isolation level allows transactions to ignore locks placed by other transactions?
What is an example of a phantom read?
What is an example of a phantom read?
During which transaction does Mary see her salary change from ₱15,000 to ₱20,000?
During which transaction does Mary see her salary change from ₱15,000 to ₱20,000?
Which of the following best describes READ UNCOMMITTED isolation level?
Which of the following best describes READ UNCOMMITTED isolation level?
What type of read occurs when a transaction reads all employees earning ₱1000 and later finds one more employee inserted with the same salary?
What type of read occurs when a transaction reads all employees earning ₱1000 and later finds one more employee inserted with the same salary?
In the context of concurrent transactions, what consequence can non-repeatable reads lead to?
In the context of concurrent transactions, what consequence can non-repeatable reads lead to?
Why is READ UNCOMMITTED considered the least restrictive isolation level?
Why is READ UNCOMMITTED considered the least restrictive isolation level?
What is the primary role of the READ COMMITTED isolation level?
What is the primary role of the READ COMMITTED isolation level?
Which statement is true about transactions in the READ COMMITTED isolation level?
Which statement is true about transactions in the READ COMMITTED isolation level?
What happens when a transaction attempts to access data being modified by another uncommitted transaction?
What happens when a transaction attempts to access data being modified by another uncommitted transaction?
What is a consequence of using the READ COMMITTED isolation level?
What is a consequence of using the READ COMMITTED isolation level?
During a transaction using READ COMMITTED, what must happen before the data becomes visible to other transactions?
During a transaction using READ COMMITTED, what must happen before the data becomes visible to other transactions?
What must occur for a transaction to be considered complete under the atomicity requirement?
What must occur for a transaction to be considered complete under the atomicity requirement?
In case of a system failure, what is the purpose of the transaction log?
In case of a system failure, what is the purpose of the transaction log?
Which statement represents the isolation requirement in database transactions?
Which statement represents the isolation requirement in database transactions?
What condition must be met for the consistency requirement in a database?
What condition must be met for the consistency requirement in a database?
What should happen if a user tries to enter an invalid value, such as 'Person'?
What should happen if a user tries to enter an invalid value, such as 'Person'?
Which SQL command marks the beginning of a transaction?
Which SQL command marks the beginning of a transaction?
What does the durability requirement ensure after a transaction is completed?
What does the durability requirement ensure after a transaction is completed?
If a transaction lacks atomicity, what implication does it have?
If a transaction lacks atomicity, what implication does it have?
What isolation level is set for Transaction 1?
What isolation level is set for Transaction 1?
What operation does Transaction 2 perform on the Stocks table?
What operation does Transaction 2 perform on the Stocks table?
In Transaction 1, what is the initial action taken before the commit?
In Transaction 1, what is the initial action taken before the commit?
What delay is introduced in Transaction 1 before the commit?
What delay is introduced in Transaction 1 before the commit?
What does the SELECT statement in Transaction 1 retrieve?
What does the SELECT statement in Transaction 1 retrieve?
What is the main purpose of the WAITFOR DELAY command in the transactions?
What is the main purpose of the WAITFOR DELAY command in the transactions?
Which operation is executed last in Transaction 2?
Which operation is executed last in Transaction 2?
How is the quantity of Samsung S10 modified in Transaction 1?
How is the quantity of Samsung S10 modified in Transaction 1?
Flashcards are hidden until you start studying
Study Notes
Lock Types
- A binary lock has two states: locked (1) and unlocked (0).
- Shared/exclusive locks allow for three states: unlocked, shared (read), and exclusive (write).
- Exclusive locks allow only one transaction to access the data.
- Shared locks allow multiple transactions to read the data concurrently.
Deadlocks
- Occur when two transactions wait indefinitely for each other to unlock data.
- For example, Transaction 1 needs data item X and Transaction 2 needs data item Y, but they both hold the other's required data item.
Transaction Isolation Levels
- Determine the level of isolation between transactions.
- Read Uncommitted: Allows dirty reads (reading uncommitted data).
- Read Committed: Prevents dirty reads, allowing potential non-repeatable reads and phantom reads.
- Repeatable Read: Prevents dirty reads and non-repeatable reads, but may allow phantom reads.
- Serializable: The most restrictive level, preventing all problems.
Dirty Read
- A transaction reads data that has been modified but not yet committed.
- Can lead to inconsistent results as the data may be rolled back later.
Non-Repeatable Read
- A transaction reads a given row at time T1, and then reads the same row at time T2, yielding different results.
- This occurs because the row may have been updated or deleted by another committed transaction.
Phantom Read
- A transaction executes a query at time T1, and then runs the same query at time T2, yielding additional rows.
- This occurs because new rows may have been inserted by another committed transaction.
Transaction Properties
- Atomicity: Ensures that all operations within a transaction are completed successfully, or none are.
- Consistency: Guarantees that only valid data is written to the database, ensuring data integrity.
- Isolation: Ensures that transactions operate independently, preventing interference from other transactions.
- Durability: Ensures that once a transaction is committed, the changes are permanently stored and survive system failures.
SQL Transactional Commands
- BEGIN TRANSACTION: Marks the start of a transaction.
- COMMIT: Completes the transaction, making all changes permanent.
- ROLLBACK: Reverts all changes made within a transaction, undoing any operations.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
