DEA-C01 practice question: Troubleshooting and tuning pipelines (3 of 3)
Data Operations and Support A free question from our original DEA-C01 practice exam. Try it, then open the answer to see why each option is right or wrong.
A cooperative grocery group's nightly Amazon Redshift load starts on time, but its final step waits for more than an hour on a table lock. The team's monitoring shows that the queries are queued, not running. An analyst's session ran an UPDATE on the same table earlier that evening, and the session is still open with no statement running.
The team needs the nightly load to finish tonight.
Which action should the team take tonight to unblock the load?
Choose one answer.
AA. Add more WLM queue slots to the nightly load's queue, so that the waiting step can run alongside the blocked one.
BB. Query SVV_TRANSACTIONS to find the open transaction holding the lock, then end that idle session so the nightly load can run.
CC. Run VACUUM FULL on the table, which rewrites the rows and releases the lock held by the open session.
DD. Enable concurrency scaling on the cluster, so that the queued load runs on a separate cluster that does not share the table lock.
Show the answer and explanation
Answer: B
A. Wrong: Queue slots control how many queries run at once, but a lock still blocks the step that needs the table. More slots do not release the lock held by the open session.
B. Right: Correct. SVV_TRANSACTIONS shows which transactions currently hold locks, so the team can find the idle session. Ending that session releases the lock, and the load runs. Applications should commit or roll back promptly to avoid the same wait. Learn more: SVV_TRANSACTIONS (opens in a new tab)
C. Wrong: VACUUM FULL needs the same table lock and waits behind the open transaction. It does not release a lock that another session holds.
D. Wrong: Concurrency scaling adds capacity for queued queries, but the lock belongs to a transaction on the main cluster. The load would still wait until that transaction ends.
Explanation
A queued query on a locked table is waiting for a transaction, not for capacity. SVV_TRANSACTIONS shows the open transaction that holds the lock, and ending or finishing that session releases the table so the load can run. Queue slots, VACUUM FULL and concurrency scaling do not release a lock held by another session.
Want more on Troubleshooting and tuning pipelines? The 5 full-length practice sets have 13 more Troubleshooting and tuning pipelines questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →