Amazon Redshift에서 잠금을 감지하고 해제하려면 어떻게 해야 합니까?

간략한 설명

사용자 테이블이나 데이터 조작 언어(DML) 쿼리에서 데이터 정의 언어(DDL) 문을 자주 수행하면 잠금 충돌이 발생할 수 있습니다.

Amazon Redshift에는 다음과 같은 세 가지 잠금 모드가 있습니다.

• AccessExclusiveLock은 다른 모든 잠금 시도를 차단하며 주로 ALTER TABLE, DROP 또는 TRUNCATE와 같은 DDL 작업 중에 발생합니다.
• AccessShareLock은 AccessExclusiveLock 시도만 차단하며 UNLOAD, SELECT, UPDATE 또는 DELETE 작업 중에 발생합니다. AccessShareLock은 테이블에서 읽거나 쓰려고 하는 다른 세션을 차단하지 않습니다.
• ShareRowExclusiveLock은 AccessExclusiveLock 및 기타 ShareRowExclusiveLock 시도는 차단하지만 AccessShareLock 시도는 차단하지 않습니다. ShareRowExclusiveLock은 COPY, INSERT, UPDATE 또는 DELETE 작업 중에 발생합니다.

이 문제를 해결하려면 문제 테이블 잠금을 식별하고 문제 쿼리(필요한 경우)를 식별한 다음 문제 테이블 잠금을 해제하십시오.

해결 방법

참고: AWS Command Line Interface(AWS CLI) 명령을 실행할 때 오류가 발생하면 AWS CLI의 오류 해결을 참조하십시오. 또한 최신 AWS CLI 버전을 사용하고 있는지 확인하십시오.

잠금 감지

잠금을 보유한 프로세스를 파악하려면 다음 쿼리를 실행합니다.

SELECT
  a.txn_start,
  _datediff_(s,a.txn_start,_getdate_())/86400||' days '||_datediff_(s,a.txn_start,_getdate_())%86400/3600||' hrs '||_datediff_(s,a.txn_start,_getdate_())%3600/60||' mins '||_datediff_(s,a.txn_start,_getdate_())%60||' secs' AS txn_duration,
  a.txn_owner,
  a.txn_db,
  a.pid,
  a.xid,
  a.lock_mode,
  a.relation AS table_id,
  nvl(_trim_(c."table"),d.relname) AS tablename,
  a.granted,
  b.pid AS blocking_pid
FROM SVV_TRANSACTIONS a
LEFT JOIN (SELECT pid, relation, granted FROM PG_LOCKS GROUP BY 1,2,3) b
  ON a.relation = b.relation AND a.granted='f' AND b.granted='t'
LEFT JOIN (SELECT * FROM SVV_TABLE_INFO) c
  ON a.relation = c.table_id
LEFT JOIN PG_CLASS d
  ON a.relation = d.oid
WHERE
  a.relation IS NOT NULL
  AND txn_db = '<database name>'
  AND tablename = '<table name>';

참고: Amazon Redshift 프로비저닝된 클러스터와 Amazon Redshift Serverless 모두에서 위 쿼리를 실행할 수 있습니다.

출력 예시:

         txn_start         |        txn_duration         | txn_owner | txn_db |    pid     |   xid   |      lock_mode      | table_id | tablename | granted | blocking_pid
---------------------------+-----------------------------+-----------+--------+------------+---------+---------------------+----------+-----------+---------+--------------
 2025-02-07 15:22:54.62833 | 0 days 0 hrs 3 mins 46 secs | admin     | dev    | 1073905801 | 3950326 | AccessExclusiveLock |  1410058 | abctbl    | t       |             
 2025-02-07 15:22:57.67816 | 0 days 0 hrs 3 mins 43 secs | admin     | dev    | 1073963119 | 3950380 | AccessShareLock     |  1410058 | abctbl    | f       |   1073905801

granted 열의 결과가 f(false)인 경우 다른 트랜잭션이 잠금을 보유하고 있으므로 트랜잭션이 잠금 대기 중입니다. blocking_pid 열은 잠금을 보유하고 있는 세션의 프로세스 ID(PID)를 보여줍니다.

문제 쿼리 감지

특정 테이블의 잠금 문제가 지속적으로 발생하는 경우 SYS_QUERY_HISTORY를 사용하여 문제의 원인이 되는 쿼리를 확인하십시오.

SELECT * FROM SYS_QUERY_HISTORY WHERE transaction_id = transaction ID;

잠금 해제

잠금을 해제하려면 다음 단계를 완료하십시오.

1. 잠금을 보유한 트랜잭션이 해제될 때까지 기다리십시오.
2. PG_TERMINATE_BACKEND 함수를 실행하여 잠금을 수동으로 해제합니다.
   참고: PG_TERMINATE_BACKEND(PID) 쿼리는 명령이 프로세스 중지를 성공적으로 요청하면 값 1을 반환합니다. SYS_SESSION_HISTORY를 확인하여 프로세스가 중지되었는지 확인하는 것이 좋습니다.
3. Redshift 프로비저닝된 클러스터의 경우 PG_TERMINATE_BACKEND로 프로세스를 성공적으로 중지되지 않으면 REBOOT_CLUSTER 함수를 실행합니다.
   참고: REBOOT_CLUSTER는 연결을 닫지 않고 클러스터를 재부팅합니다.
4. Redshift 프로비저닝된 클러스터의 경우, REBOOT_CLUSTER로 프로세스를 성공적으로 중지되지 않으면 Amazon Redshift 콘솔에서 클러스터를 재부팅하거나 reboot-cluster AWS CLI 명령을 실행합니다.
   참고: reboot-cluster 명령은 모든 현재 연결을 닫습니다.