CompTIA DataSys+ (DS0-001)Database Management and MaintenanceHard
A database administrator is reviewing the database's error logs and notices a recurring warning about 'deadlocks detected and rolled back'. Users are complaining about occasional transaction failures and slow response times. The application uses a high concurrency model with many short-lived transactions updating the same set of critical tables. Which of the following strategies would be MOST effective in reducing the occurrence of these deadlocks?
- AIncreasing the `max_connections` parameter.
- BRunning `VACUUM FULL` more frequently.
- CDecreasing the `lock_timeout` setting.
- DImplementing consistent lock ordering.
Show answer & explanationAnswer & explanation
Correct answer: D. Implementing consistent lock ordering.
Deadlocks occur when transactions acquire locks on resources in different orders, leading to a circular dependency. Implementing consistent lock ordering ensures all transactions attempt to acquire locks on shared resources in the same predefined sequence, thereby preventing the circular wait condition that causes deadlocks.
Why the other options are wrong
- A. Increasing `max_connections` would allow more concurrent users, potentially *increasing* the likelihood of deadlocks.
- B. `VACUUM FULL` reclaims space and rewrites tables, which is for fragmentation/bloat, not for preventing deadlocks.
- C. Decreasing `lock_timeout` would make transactions fail faster when encountering a lock, but it wouldn't prevent the deadlock from forming, only detect and resolve it quicker.
Consistent Lock Ordering
A deadlock prevention strategy where all concurrent transactions acquire locks on shared resources (e.g., tables, rows) in a predefined, consistent sequence, thereby eliminating the possibility of a circular wait condition.
- Prevents deadlocks by eliminating circular dependencies.
- Requires careful design and adherence in application logic.
- Applies to resources that multiple transactions might access.
Memory trick: Always take the same path, so no one gets stuck in a loop.