Amazon Redshift에 쿼리를 차단하는 테이블 잠금이 있습니다. 이를 찾아서 해결하려면 어떻게 해야 합니까?
간략한 설명
잠금은 동시에 테이블에 액세스할 수 있는 세션 수를 제어하는 보호 메커니즘입니다. 잠금은 해당 세션에서 수행할 수 있는 작업도 결정합니다. 대부분의 관계형 데이터베이스는 행 수준 잠금을 사용합니다. 하지만 Amazon Redshift는 테이블 수준 잠금을 사용합니다. 사용자 테이블 또는 DDL 문을 DML 쿼리에서 자주 수행하는 경우 잠금 충돌이 발생할 수 있습니다.
Amazon Redshift에는 세 가지 잠금 모드가 있습니다.
- AccessExclusiveLock: ALTER TABLE, DROP 또는 TRUNCATE와 같은 DDL 작업 중에 주로 획득됩니다. AccessExclusiveLock은 다른 모든 잠금 시도를 차단합니다.
- AccessShareLock: UNLOAD, SELECT, UPDATE 또는 DELETE 작업 중에 획득됩니다. AccessShareLock은 AccessExclusiveLock 시도만 차단합니다. AccessShareLock은 테이블에서 읽기 또는 쓰기를 시도하는 다른 세션을 차단하지 않습니다.
- ShareRowExclusiveLock: COPY, INSERT, UPDATE 또는 DELETE 작업 중에 획득됩니다. ShareRowExclusiveLock은 AccessExclusiveLock 및 기타 ShareRowExclusiveLock 시도를 차단하지만, AccessShareLock 시도는 차단하지 않습니다.
쿼리 또는 트랜잭션이 테이블에서 잠금을 획득하면 쿼리 또는 트랜잭션 기간에 잠금이 유지됩니다. 동일한 잠금을 획득하기 위해 대기 중인 다른 쿼리 또는 트랜잭션은 AccessShareLock을 제외하고 차단됩니다. 쿼리가 중지될 수 있는 이유에 대한 자세한 내용은 쿼리 중지를 참조하십시오.
잠금 문제를 해결하려면 잠금을 보유하고 있는 세션(PID)을 식별한 다음, 세션을 종료합니다. 세션이 종료되지 않으면 클러스터를 재부팅합니다.
해결 방법
쿼리를 실행하여 잠금을 보유하는 세션을 식별합니다.
select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,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
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 stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class d on a.relation=d.oid
where a.relation is not null;
출력은 다음과 유사합니다.
txn_owner | txn_db | xid | pid | txn_start | lock_mode | table_id | tablename | granted | blocking_pid | txn_duration |
----------+--------+---------+-------+----------------------------+---------------------+----------+-----------+---------+--------------+-----------------------------+
usr1 | db1 | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessExclusiveLock | 351959 | lineorder | t | | 0 days 0 hrs 0 mins 52 secs |
usr1 | db1 | 5559927 | 20450 | 2018-06-30 10:52:19.761199 | AccessShareLock | 351959 | lineorder | f | 19813 | 0 days 0 hrs 0 mins 30 secs |
usr1 | db1 | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessShareLock | 351959 | lineorder | t | | 0 days 0 hrs 0 mins 52 secs |
권한 부여된 열의 결과가 f(false)이면, 다른 세션의 트랜잭션이 잠금을 보유하고 있습니다. blocking_pid 열은 잠금을 보유하는 세션의 프로세스 ID를 표시합니다. 이 예제에서 PID 19813이 잠금을 보유합니다.
잠금을 해제하려면 잠금을 보유하는 트랜잭션이 완료될 때까지 기다립니다. 다음 명령을 실행하여 세션을 수동으로 종료할 수도 있습니다.
select pg_terminate_backend(PID);
PID를 종료하면 실행 중인 모든 트랜잭션이 롤백되고 세션의 모든 잠금이 해제됩니다. 그런 다음, 잠금을 획득하기 위해 대기 중인 다른 트랜잭션에 의해 잠금이 설정됩니다.
참고: PG_TERMINATE_BACKEND(PID)가 값 "1"을 반환하면 일반적으로 PID에 대한 성공적인 종료 요청을 의미합니다. 하지만 PID가 실제로 종료되었음을 보장하지는 않습니다. 내부 상태에 따라 PID를 결정할 수 없는 경우도 있습니다. 따라서 STV_SESSIONS 및 기타 관련 시스템 테이블을 확인하여 PID도 실제로 종료되었는지 확인하는 것이 가장 좋습니다.
PG_TERMINATE_BACKEND(PID)가 세션을 종료하지 않으면 클러스터를 재부팅하여 프로세스를 종료합니다. 재부팅은 Amazon Redshift 콘솔을 통해 클러스터를 재부팅하거나 REBOOT_CLUSTER를 사용하여 데이터베이스를 재부팅하여 수행할 수 있습니다.
관련 정보
LOCK (Amazon Redshift)
동시 쓰기 트랜잭션의 잠재적 교착 상황