DEA-C01 Question 187
Select 4You are designing a data processing pipeline using Amazon Redshift to handle large-scale analytics. During queries, you notice that certain tables are frequently locked, causing delays for downstream processes. Which of the following actions can you take to manage locks effectively and avoid access contention?
- A
Use the
COMMITcommand frequently to release locks as soon as a transaction is completed. - B
Enable Amazon Redshift's automatic table locking mechanism to prevent manual intervention.
- C
Use the
STV_LOCKSsystem view to identify and resolve locking issues. - D
Avoid long-running transactions that hold locks for extended periods.
- E
Manually kill sessions holding locks using the
PG_TERMINATE_BACKENDfunction when necessary.
Show answer and explanation
Correct answers: A, C, D, E
Explanation
Managing locks in Amazon Redshift requires a combination of proactive monitoring, transaction management, and targeted action when conflicts occur. Using the COMMIT command, avoiding long-running transactions, and monitoring locks via the STV_LOCKS system view are essential practices. Additionally, you can manually terminate sessions holding locks when necessary, but there is no explicit mechanism to enable automatic locking management in Redshift.
- A. Correct.
Frequent use of the
COMMITcommand ensures that locks are released promptly after a transaction is completed, reducing contention. - B. Incorrect.
Amazon Redshift does not have an automatic table locking mechanism that can be explicitly enabled. Locking is handled implicitly by the system during transactions.
- C. Correct.
The
STV_LOCKSsystem view provides detailed information about the current locks in the system, helping you identify and address locking conflicts. - D. Correct.
Long-running transactions can hold locks for extended periods, causing contention. Avoiding them can help reduce lock-related issues.
- E. Correct.
The
PG_TERMINATE_BACKENDfunction can be used to terminate sessions that are holding locks unnecessarily, but it should be used cautiously to avoid disrupting other processes.