Database Transaction Management Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

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?

Locked

Unlocked

Shared (correct)

Exclusive

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?

The transaction reads uncommitted changes made by other transactions. Signup and view all the answers

What happens when a deadlock occurs?

Selected transactions are rolled back automatically. Signup and view all the answers

What is an exclusive lock used for?

To reserve access only for one transaction. Signup and view all the answers

What characterizes the serial lock assignment in deadlock avoidance?

It can lead to reduced action response time. Signup and view all the answers

In the example provided, what happens to Mary's salary after the treasurer rolls back the transaction?

Her salary remains unchanged at ₱15,000. Signup and view all the answers

What occurs during a non-repeatable read?

A transaction reads the same row at different times and gets different results. Signup and view all the answers

Which isolation level allows transactions to ignore locks placed by other transactions?

READ UNCOMMITTED Signup and view all the answers

What is an example of a phantom read?

Reading a set of employees and getting additional rows due to new records. Signup and view all the answers

During which transaction does Mary see her salary change from ₱15,000 to ₱20,000?

Transaction 1 when reading her salary again. Signup and view all the answers

Which of the following best describes READ UNCOMMITTED isolation level?

It allows reading of uncommitted changes from other transactions. Signup and view all the answers

What type of read occurs when a transaction reads all employees earning ₱1000 and later finds one more employee inserted with the same salary?

Phantom read Signup and view all the answers

In the context of concurrent transactions, what consequence can non-repeatable reads lead to?

Data inconsistency or unexpected results. Signup and view all the answers

Why is READ UNCOMMITTED considered the least restrictive isolation level?

It ignores any locks and may read uncommitted changes. Signup and view all the answers

What is the primary role of the READ COMMITTED isolation level?

It prevents dirty reads by restricting read access to uncommitted data. Signup and view all the answers

Which statement is true about transactions in the READ COMMITTED isolation level?

Other transactions can modify data between executions of individual statements. Signup and view all the answers

What happens when a transaction attempts to access data being modified by another uncommitted transaction?

The transaction can only see committed changes made by the first transaction. Signup and view all the answers

What is a consequence of using the READ COMMITTED isolation level?

It may allow non-repeatable reads or phantom reads to occur. Signup and view all the answers

During a transaction using READ COMMITTED, what must happen before the data becomes visible to other transactions?

The transaction must be committed. Signup and view all the answers

What must occur for a transaction to be considered complete under the atomicity requirement?

All operations must be executed successfully. Signup and view all the answers

In case of a system failure, what is the purpose of the transaction log?

To recover the database to a consistent state. Signup and view all the answers

Which statement represents the isolation requirement in database transactions?

Only one transaction can access a set of data at a time. Signup and view all the answers

What condition must be met for the consistency requirement in a database?

Only valid data following all rules and constraints is written to the database. Signup and view all the answers

What should happen if a user tries to enter an invalid value, such as 'Person'?

The database will disallow the entry to maintain consistency. Signup and view all the answers

Which SQL command marks the beginning of a transaction?

BEGIN TRANSACTION Signup and view all the answers

What does the durability requirement ensure after a transaction is completed?

Updates must persist despite failures. Signup and view all the answers

If a transaction lacks atomicity, what implication does it have?

Part of the transaction may be saved while others fail. Signup and view all the answers

What isolation level is set for Transaction 1?

REPEATABLE READ Signup and view all the answers

What operation does Transaction 2 perform on the Stocks table?

It inserts a new stock item. Signup and view all the answers

In Transaction 1, what is the initial action taken before the commit?

Update quantity of Samsung S10 Signup and view all the answers

What delay is introduced in Transaction 1 before the commit?

00:00:01 Signup and view all the answers

What does the SELECT statement in Transaction 1 retrieve?

All records from the Stocks table Signup and view all the answers

What is the main purpose of the WAITFOR DELAY command in the transactions?

To allow for data consistency during updates Signup and view all the answers

Which operation is executed last in Transaction 2?

COMMIT Signup and view all the answers

How is the quantity of Samsung S10 modified in Transaction 1?

By reducing it by 1 Signup and view all the answers

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.

Description

Test your knowledge on lock types, deadlocks, and transaction isolation levels with this engaging quiz. Explore the nuances of binary, shared, and exclusive locks, as well as the various isolation levels that dictate transaction behavior in databases.