-
[DB] DB Lock & 2PL ProtocolDB/DB Basic 2024. 3. 31. 21:37반응형
DB Lock
: Machanisms used in database management systems to control concurrent access to shared resources such as database tables, rows, or even specific data items.
- Ensure data consistency and integrity by preventing conflicts between concurrent transactions
Types
1. Shared Lock (S-Lock)
- Allows multiple transactions to read a resource simultaneously
- Prevents write operations by other transactions while allowing concurrent read operations
2. Exclusive Lock (X-Lock)
- Grants exclusive access to a resource, allowing modification while preventing other transactions from accessing it
- Ensrues only one transaction can modify a resource at a time, preventing data inconsistency
3. Intent Lock
- Higher-level locks indicating the intention of a transaction to acquire a specific type of lock on a resource.
- Helps manage lock compatibility and avoid deadlocks
4. Schema Lock
- Prevents concurrent transactions from modifying the structure of the database
- Ensures schema consistency during concurrent operaions
5. Record Lock
- Locks placed on individual database records or rows
- Prevents simultaneous modification of the same record, ensuring data integrity
Two-Phase Locking (2PL) Protocol
: Concurrency control mechanism used in database systems to ensure serializability of transactions and prevent issues such as data inconsistency and deadlock
- Ensure that transactions acquire all necessary locks before modifying data and release them once finished, preventing inconsistent reads or writes and avoiding deadlocks
Execution Process
1. At the start of the transaction's execution, it acquires all necessary DB Locks
2. The transaction holds onto these locks until it completes its operations, at which point it releases all the acquired DB Locks.
Phase
1. Growing Phase (Lock Acquisition)
: Transactions acquire all necessary locks without releasing any
2. Shirinking Phase (Lock Release)
: Transactions release all locks they hold, allowing other transactions to access locked resources
Types
Aspect Conservative 2PL Strict 2PL Lock Acquisition Acquires all locks at the beginning Lock Release Releases all locks at the end Releases locks after commiting or aborting Deadlock Handling Deadlocks are resolved by aborting one of the transactions involved Deadlocks are resolved similarly, but may involve distributed coordination Atomicity and Durability Ensures atomicity and durability within a single database Extends atomicity and durability across distributed databases or nodes Consistency Across Databases N/A Ensures global consistency across distributed databases Performance May suffer from lock contention and deadlocks due to long-held locks Holding locks until commit time can reduce lock contention but may introduce delays 반응형'DB > DB Basic' 카테고리의 다른 글
[DB] Concurrency Control (2) Concurrency Problem and Isolation Level (0) 2024.02.27 [DB] Concurrency Control (1) Schedule, Serializability, Recoverability (0) 2024.02.25 [DB] Transaction (1) 2024.02.24 [DB] Stored Procedure in 3-tier architecture : pros and cons (0) 2024.01.22 [DB] 저장 프로시저 (Stored Procedure)의 개념과 예시 (0) 2023.06.12