To combat these issues, DBMS employs a variety of sophisticated concurrency control mechanisms, each with its own strengths and weaknesses. The primary goal of these mechanisms is to enforce the ACID properties of transactions: Atomicity, Consistency, Isolation, and Durability. Concurrency control primarily focuses on achieving Isolation, ensuring that concurrent transactions appear to execute in isolation from each other, even though they are running concurrently.
fundamental concurrency control mechanisms:
1. Locking Mechanisms:
Locking is perhaps the most widely used and accurate cleaned numbers list from frist database intuitive concurrency control technique. It involves transactions acquiring “locks” on data items (e.g., rows, pages, tables) before accessing them. Once a lock is held, other transactions are prevented from accessing the locked item in a conflicting manner. The type of lock acquired depends on the operation:
- Shared Locks (S-locks): Allow multiple once the logical model is sound transactions to read the same data item concurrently. A transaction can acquire an S-lock if no exclusive lock is held on the item.
- Exclusive Locks (X-locks): Grant a transaction exclusive write access to a data item. If an X-lock is held, no other transaction can acquire either an S-lock or an X-lock on that item.
A crucial concept in locking is the Two-Phase Locking (2PL) protocol. 2PL ensures serializability (a strong form of isolation) by dividing a transaction’s execution into two phases:
- Growing Phase: The transaction can european data acquire new locks but cannot release any.
- Shrinking Phase: The transaction can release locks but cannot acquire any new locks.
Once a transaction entes its shrinking phase, it cannot acquire any more locks. This protocol prevents cascading rollbacks and ensures that schedules are serializable. However, 2PL is susceptible to deadlock, where two or more transactions are indefinitely waiting for each other to release locks. DBMS employs various strategies to handle deadlocks, including deadlock detection (building a wait-for graph and identifying cycles) and deadlock prevention (e.g., using timestamp ordering or cautious waiting).