DEA-C01 Question 190
Select 2You are a data engineer managing an Amazon Redshift cluster for a data analytics team. The team reports that some queries are occasionally hanging and not completing. Upon investigation, you identify that table locks are being held by long-running transactions, preventing other queries from accessing the data. What steps can you take to ensure efficient management of locks and prevent such issues in the future?
- A
Enable automatic table vacuuming and analyze to manage table statistics and reduce lock contention.
- B
Manually kill long-running transactions using the
CANCELcommand to release locks. - C
Use a row-level locking mechanism instead of table-level locking in Amazon Redshift.
- D
Set the
max_execution_timeparameter in Amazon Redshift to automatically terminate long-running queries. - E
Educate the team to use explicit
COMMITorROLLBACKstatements to close transactions promptly.
Show answer and explanation
Correct answers: B, E
Explanation
To manage locks in Amazon Redshift, it is crucial to address long-running transactions that hold locks and block other queries. Manually killing these transactions releases the locks immediately. Additionally, educating your team to use explicit COMMIT or ROLLBACK statements ensures transactions are closed promptly, preventing unnecessary lock contention. Other options like vacuuming or row-level locking are either unrelated or not supported in Amazon Redshift.
- A. Incorrect.
Vacuuming and analyzing tables help improve query performance by managing table statistics and storage, but they do not directly resolve lock contention caused by long-running transactions.
- B. Correct.
Manually killing long-running transactions releases the locks held by them, which can help resolve immediate lock contention issues.
- C. Incorrect.
Amazon Redshift does not support row-level locking. It uses table-level locking, so this option is not applicable.
- D. Incorrect.
There is no
max_execution_timeparameter in Amazon Redshift for automatically terminating long-running queries. However, you can manually monitor and terminate such queries if needed. - E. Correct.
Encouraging the use of explicit
COMMITorROLLBACKstatements ensures that transactions do not remain open unnecessarily, thereby reducing the chances of locks being held for extended periods.