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

3분 분량

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';

특정 테이블의 잠금을 감지하려면 database name을 데이터베이스 이름으로 바꾸고 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;

잠금 해제

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

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

주제: Analytics
태그: Amazon Redshift
언어: 한국어