In relational databases, locks are essential mechanisms for managing concurrent access to data. They prevent data corruption and ensure data consistency when multiple transactions try to read or modify the same data simultaneously.
Without locks, concurrent transactions could lead to several problems. For example,
- Dirty Reads, a transaction may read data that has been modified by another transaction but not yet committed;
- Lost updates, one transaction’s updates may be overwritten by another transaction;
- Non-Repeatable Reads, A transaction reads the same data multiple times, and due to updates by other transactions, the results of each read may be different;
- Phantom Reads: A transaction executes the same query multiple times, and due to insertions or deletions by other transactions, the result set of each query may be different.
Here’s a detailed breakdown of locks in relational databases.
Types of Locks
Relational databases use various types of locks with different levels of restriction:
Shared Lock
Allows multiple read operations simultaneously. Prevents write operations until the lock is released.
Example: SELECT statements in many databases.
Exclusive Lock
Allows a single transaction to modify data. Prevents other operations (read or write) until the lock is released.
Example: UPDATE, DELETE.
Update Lock
Prevents deadlocks when a transaction might upgrade a shared lock to an exclusive lock.
Intent Lock
Indicate the type of lock a transaction intends to acquire. Intent Shared (IS): Intends to acquire a shared lock on a lower granularity level. Intent Exclusive (IX): Intends to acquire an exclusive lock on a lower granularity level.
Lock Granularity
Locks can be applied at different levels of granularity.
Row-Level Lock
Locks a specific row in a table. Provide the highest concurrency, but if many rows are locked, it may lead to lock management overhead.
Example: Updating a specific record (UPDATE ... WHERE id = 1).
Page-Level Lock
Locks a data page, a block of rows. Provide a compromise between concurrency and overhead.
(a page is a fixed-size storage unit)
Table-Level Lock
Locks an entire table. Provide the lowest concurrency but minimal overhead.
Example: Prevents any modifications to the table during an operation like ALTER TABLE.
Lock Duration
Transaction Locks: Held until the transaction is committed or rolled back.
Session Locks: Held for the duration of a session.
Temporary Locks: Released immediately after the operation completes.
Deadlocks Prevention and Handling
A deadlock occurs when two or more transactions are waiting for each other to release locks. Databases employ deadlock detection and resolution mechanisms to handle such situations.
Prevent Deadlocks
Avoid Mutual Exclusion
Use resources that allow shared access (e.g., shared locks for read-only operations).
Ā Eliminate Hold and Wait
Require transactions to request all resources they need at the beginning. If any resource is unavailable, the transaction must wait without holding any resources.
Allow Preemption
If a transaction requests a resource that is held by another, the system can preempt (forcefully release) the resource from the holding transaction. The preempted transaction is rolled back and restarted.
Break Circular Wait
Impose a global ordering on resources and require transactions to request resources in that order. For example, if resources are ordered as R1, R2, R3, a transaction must request R1 before R2, and R2 before R3.
Handle Deadlocks
If deadlocks cannot be prevented, the database system must detect and resolve them. Here’s how deadlocks are typically handled:
Deadlock Detection
The database system periodically checks for deadlocks by analyzing the wait-for graph, which represents transactions and their dependencies on resources. If a cycle is detected in the graph, a deadlock exists.
Deadlock Resolution
Once a deadlock is detected, the system must resolve it by choosing a victim transaction to abort. The victim is typically selected based on criteria such as:
- Transaction Age: Abort the newest or oldest transaction.
- Transaction Progress: Abort the transaction that has done the least work.
- Priority: Abort the transaction with the lowest priority.
The aborted transaction is rolled back, releasing its locks and allowing other transactions to proceed.
Conclusion
Locks are crucial for ensuring data consistency and integrity in relational databases. Understanding the different types of locks, lock granularity, locking protocols, and isolation levels is essential for database developers and administrators to design and manage concurrent applications effectively.
Please do not hesitate to contact me if you have any questions at William . chen @ mainri.ca
(remove all space from the email account š)
