DBS-C01 Gap Lock Practice Question
A financial services company is using Amazon Aurora MySQL as its primary database. The database has a table 'transactions' that receives high inserts during business hours. The table is partitioned by date. Recently, the application team noticed an increase in lock wait timeouts. The database specialist reviewed the InnoDB status and found that there are frequent gap locks on the 'transaction_date' column. The isolation level is REPEATABLE READ. What should the specialist do to reduce lock waits while maintaining data consistency?
⚠ Common exam trap
Candidates often think that adding an index will reduce locking, but in REPEATABLE READ, non-unique indexes still cause gap locks. The correct solution is to change the isolation level to READ COMMITTED.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Change the transaction isolation level to READ COMMITTED.
In REPEATABLE READ isolation level, InnoDB uses gap locks on non-unique indexes to prevent phantom reads, which can cause lock wait timeouts. Changing to READ COMMITTED eliminates gap locks for non-unique indexes because it only uses row-level locks (no gap locks). This reduces lock contention. Option A is incorrect: adding an index on transaction_date does not eliminate gap locks if the index is non-unique; gap locks still occur. Option B is incorrect: increasing innodb_lock_wait_timeout only increases the time a transaction waits for a lock, it does not prevent the lock from happening. Option C is incorrect: modifying the partition key does not affect the locking mechanism at the row level.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Add a secondary index on transaction_date.
Why it's wrong here
If not unique, gap locks can still occur in REPEATABLE READ.
- ✗
Increase the innodb_lock_wait_timeout parameter.
Why it's wrong here
This only allows longer waits, does not reduce lock contention.
- ✗
Modify the partitioning key to use a hash-based partition.
Why it's wrong here
Partitioning does not affect locking behavior.
- ✓
Change the transaction isolation level to READ COMMITTED.
Why this is correct
READ COMMITTED avoids gap locks for locking reads.
Go deeper
Related to this question
About these practice questions
One of 1,663 original DBS-C01 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DBS-C01 practice question is part of Courseiva's free Amazon Web Services certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DBS-C01 exam.