CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is reviewing the database's error logs and notices a recurring warning message: 'Deadlock found when trying to get lock; try restarting transaction'. This indicates frequent contention between transactions. To address this, the administrator decides to implement a strategy to minimize deadlocks. Which of the following approaches is MOST effective in preventing deadlocks?
- AIncreasing the database server's available memory.
- BReducing the frequency of database backups.
- CDisabling foreign key constraints on critical tables.
- DEnsuring transactions acquire locks in a consistent, predefined order.
Show answer & explanationAnswer & explanation
Correct answer: D. Ensuring transactions acquire locks in a consistent, predefined order.
Deadlocks occur when transactions wait for each other to release locks in a circular fashion. By ensuring all transactions acquire locks on resources in the same, consistent order, the circular dependency is broken, effectively preventing deadlocks.
Why the other options are wrong
- A. Increasing memory helps with caching and overall performance but does not directly prevent deadlocks, which are a logical issue of lock acquisition order.
- B. Reducing backup frequency has no impact on deadlock prevention; backups are for data recovery.
- C. Disabling foreign key constraints would compromise referential integrity and could lead to data inconsistencies, while not directly preventing deadlocks caused by concurrent updates to shared resources.
Deadlock Prevention
Strategies employed to ensure that deadlocks never occur in a database system, typically by breaking one of the four necessary conditions for a deadlock.
- One key strategy is consistent lock ordering.
- Can also involve using shorter transactions or avoiding certain lock types.
- Different from deadlock detection and resolution, which handle deadlocks after they occur.
Memory trick: Preventing deadlocks means planning lock acquisition like a dance.