DEA-C01 Question 186
Select 3You are a data engineer managing an Amazon Redshift cluster for a data analytics team. Analysts frequently report issues with queries getting stuck or timing out due to locking conflicts. How can you resolve and prevent these locking issues in Amazon Redshift?
- A
Use the STV_LOCKS system table to identify and troubleshoot existing locks.
- B
Configure Amazon Redshift to automatically terminate all long-running queries to avoid locks.
- C
Use the 'SET LOCK_TIMEOUT' command to set a threshold for how long a query should wait for a lock before terminating.
- D
Encourage the use of the 'WITH NOLOCK' option in queries to bypass existing locks.
- E
Optimize table design and use distribution keys to minimize lock contention during query execution.
Show answer and explanation
Correct answers: A, C, E
Explanation
In Amazon Redshift, locking issues can occur if multiple queries or transactions try to access the same resource simultaneously. To address this, you can use the STV_LOCKS system table to identify active locks, implement the 'SET LOCK_TIMEOUT' command to prevent long waits for locks, and optimize table design to minimize contention. These steps help ensure smoother query execution and improve overall cluster performance.
- A. Correct.
The STV_LOCKS system table helps identify active locks, the transactions holding them, and those waiting, which is critical for resolving and troubleshooting locking issues.
- B. Incorrect.
Amazon Redshift does not have a built-in feature to automatically terminate all long-running queries. This option is incorrect.
- C. Correct.
The 'SET LOCK_TIMEOUT' command allows you to control how long a query waits for a lock, reducing the risk of queries getting stuck indefinitely.
- D. Incorrect.
Amazon Redshift does not support a 'WITH NOLOCK' option. This is specific to SQL Server and is not applicable here.
- E. Correct.
Optimizing table design, such as using proper distribution keys and sort keys, can reduce lock contention by ensuring queries are distributed effectively across nodes.