CompTIA DataSys+ (DS0-001)Database Management and MaintenanceHard
A database administrator is conducting a performance tuning exercise. They observe that a particular stored procedure, which performs multiple `INSERT` and `UPDATE` operations within a single transaction, is experiencing significant contention, leading to blocking and long transaction times. The `EXPLAIN` plan shows that the procedure is waiting on row-level locks. Which of the following approaches would be MOST effective in reducing this contention?
- AIncreasing the database server's CPU cores.
- BAdding more RAM to the database server.
- CImplementing a read replica for reporting queries.
- DRefactoring the stored procedure to commit smaller batches of operations.
Show answer & explanationAnswer & explanation
Correct answer: D. Refactoring the stored procedure to commit smaller batches of operations.
Committing smaller batches of operations reduces the duration for which locks are held, thereby decreasing contention and blocking. This directly addresses the observed issue of long-held row-level locks.
Why the other options are wrong
- A. Increasing CPU cores helps with CPU-bound workloads, but not directly with contention caused by long-held locks during write operations.
- B. Adding more RAM primarily helps with caching data and reducing I/O, not with contention caused by transaction-level locking for write operations.
- C. Implementing a read replica helps offload read queries, but does not alleviate contention for write operations on the primary database, which is where the stored procedure is executing.
Transaction Atomicity
One of the ACID properties, ensuring that a transaction is treated as a single, indivisible unit of work. Either all of its operations succeed, or none of them do.
- Crucial for data consistency.
- Determines the scope of locks held during writes.
- Long-running transactions can lead to contention and blocking.
Memory trick: Contention is like traffic, reduce it with smaller trips.