Podcast Beta
Questions and Answers
The 2-phase locking technique ensures serialization by dividing operations into expansion and contraction phases.
True
In the expansion phase of 2PL, a transaction can perform both locking and unlocking operations.
False
If all lock operations appear before the first unlock operation, the transaction meets the 2PL protocol requirements.
True
Compliance with the 2PL protocol guarantees serialization of all transactions, and vice versa.
Signup and view all the answers
The 2PL protocol is primarily known for its ability to guarantee concurrency.
Signup and view all the answers
In the Read Uncommitted isolation level, other transactions can read data that is still being processed by the current transaction.
Signup and view all the answers
Read Committed isolation level allows other transactions to read only confirmed data from completed transactions.
Signup and view all the answers
Read Committed isolation level allows reading data that is still being processed by the current transaction.
Signup and view all the answers
There are five transaction isolation levels defined in the ANSI/ISO SQL standard.
Signup and view all the answers
The highest isolation level in SQL92 standard allows dirty reads.
Signup and view all the answers
Study Notes
Transaction Properties
- A transaction is a set of several operations that convert a consistent state of the database to another consistent state.
- A transaction is a unit of performing a series of read and write operations in a database, which should be performed at the same time.
ACID Properties of the Transaction
- Atomicity: All data should be successfully entered or failed in one transaction.
- Consistency: The transaction result should always be the same expected value.
- Isolation: Another transaction should not intervene and process the data until transaction commit or rollback is issued.
- Durability: The persistence of a successful transaction should be guaranteed.
Isolation Levels
- Repeatable read: When a query is executed more than twice in a transaction, the record in the first query does not disappear or the value is not changed.
- Serializable read: When a query is executed more than twice in a transaction, the record in the first query does not disappear or the value is not changed, and no new record appears either.
Definition of Deadlock
- Multiple processes or transactions are waiting for the allocation of a specific resource infinitely in a multi-processing environment or a multi-transaction database system.
- Transactions in a deadlock can never end execution and cannot start another task because system resources are locked.
- Deadlock occurs when all transactions are waiting indefinitely without making any progress at all.
Causes of Deadlock
- T1 waits for T2 to unlock data x.
- T2 is locking data x.
- T2 waits for T1 to unlock data y.
- T1 is locking data y.
Concurrency Control
- A function that enables the successful execution of several transactions at the same time in a database system that supports a multi-user environment.
- Purposes of concurrency control:
- To create a serializable schedule or ensure the possibility of transaction serialization.
- To guarantee the maximum sharing level, minimum response time, and maximum system activity.
- To ensure data integrity and consistency.
Problems that Occur when Concurrency is not Controlled
- Lost update: Occurs when transactions update the same data simultaneously.
- Dirty read: Other transactions refer to the intermediate execution result of a transaction.
- Inconsistency: A database remains inconsistent when two transactions are executed at the same time.
- Cascading rollback: When a specific transaction cancels processing if data is shared by multiple transactions, the part processed by other transactions cannot be cancelled.
- Unrepeatable read: When a transaction executes the same query twice, the query results differ because other transactions have modified or deleted the value in the middle.
Multiversion Concurrency Control (MVCC)
- MVCC is a concurrency control method that allows multiple transactions to execute simultaneously without interfering with each other.
- The complexity of MVCC is hidden from the user and developer, and the functionality is provided by the database vendor.
Optimistic Concurrency Control (OCC)
- OCC is a concurrency control method that assumes multiple transactions can frequently complete without interfering with each other.
- Phases of OCC:
- Begin: Record a timestamp marking the transaction's beginning.
- Modify: Read database values, and tentatively write changes.
- Validate: Check whether other transactions have modified data that this transaction has used.
- Commit/Rollback: If there is no conflict, make all changes take effect; if there is a conflict, resolve it, typically by aborting the transaction.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Learn about the definition and purposes of concurrency control in database systems. Understand how concurrency control allows multiple transactions to be executed simultaneously in a multi-user environment.
