잠금에 대한 이해
MVCC(다중 버전 동시성 제어)는 대부분의 시나리오에 적절한 동시성 설정을 제공합니다. 그러나 애플리케이션에 영향을 받는 행과 특정 잠금 수준을 정확하게 제어하는 특정 잠금이 필요한 경우 명시적 잠금 모드를 사용하면 이 세분화된 컨트롤을 사용할 수 있습니다.
Azure Database for PostgreSQL에는 명시적 잠금, 테이블 수준 잠금, 행 수준 잠금 및 페이지 수준 잠금의 세 가지 유형이 있습니다. 초기 트랜잭션은 잠금을 요청하고 수락되면 요청된 잠금이 기존 잠금이 됩니다. 다른 트랜잭션이 동일한 데이터에 대한 잠금을 제거하려고 하면 원래 트랜잭션과 충돌하지 않으면 잠금이 부여됩니다.
예를 들어 두 트랜잭션은 SELECT 문을 사용하여 동시에 동일한 데이터를 쿼리할 수 있습니다. 이러한 요청은 ACCESS SHARE 잠금을 사용하며 둘 다 허용됩니다. 또 다른 시나리오에서는 한 트랜잭션이 SELECT 문과 ACCESS SHARE 잠금을 사용하여 데이터를 쿼리하지만 동시에 다른 트랜잭션이 동일한 테이블을 삭제하려고 시도합니다. 테이블을 삭제하려면 ACCESS EXCLUSIVE 잠금이 필요하며 이 시나리오에서는 부여되지 않습니다.
테이블 수준 잠금
테이블 수준 잠금은 이름에 'ROW'가 포함되어 있어도 전체 테이블에 잠금을 설정합니다. 테이블 자체가 수정되는 경우 전체 테이블을 잠그는 것이 필요하거나 많은 행 수준 잠금을 수행하는 것보다 더 효율적일 수 있습니다.
Azure Database for PostgreSQL에는 8가지 유형의 테이블 수준 잠금이 있으며 이러한 유형의 잠금을 획득하는 SQL 명령은 다음과 같습니다.
| 잠금 모드 | 취득자 |
|---|---|
| ACCESS SHARE | SELECT 명령 |
| 행 공유 | SELECT FOR UPDATE 및 SELECT FOR SHARE 명령어 |
| ROW EXCLUSIVE | UPDATE, DELETE, INSERT 명령 |
| SHARE UPDATE EXCLUSIVE | ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY 명령, 일부 ALTER INDEX 및 ALTER TABLE 명령, VACUUM(FULL 아님) |
| 공유 | CREATE INDEX(CONCURRENTLY 아님) 명령 |
| SHARE ROW EXCLUSIVE | CREATE TRIGGER 명령 및 일부 ALTER TABLE 명령 |
| 단독 | REFRESH MATERIALIZED VIEW CONCURRENTLY 명령 |
| 독점 접근 | DROP TABLE, REINDEX, TRUNCATE, CLUSTER, REFRESH MATERIALIZED VIEW(CONCURRENTLY 아님) 명령, 대부분의 ALTER INDEX 및 ALTER TABLE 명령, VACUUM FULL |
기존 잠금의 각 유형은 획득 중인 다른 요청된 잠금을 차단합니다. 다음 표에서는 다른 잠금을 획득하지 못하도록 차단하는 잠금을 나열합니다.
| -- | 기존 접근 공유 | 기존 ROW SHARE | 기존 ROW EXCLUSIVE | 기존 SHARE UPDATE EXCLUSIVE | 기존 공유 | 기존 SHARE ROW EXCL | 기존 EXCLUSIVE | 기존 ACCESS EXCLUSIVE |
|---|---|---|---|---|---|---|---|---|
| 요청된 ACCESS SHARE | 차단됨 | |||||||
| 요청된 ROW SHARE | 차단됨 | 차단됨 | ||||||
| 요청된 ROW EXCLUSIVE | 차단됨 | 차단됨 | 차단됨 | 차단됨 | ||||
| 요청된 SHARE UPDATE EXCLUSIVE | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | |||
| 요청된 공유 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | |||
| 요청된 SHARE ROW EXCLUSIVE | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | ||
| 요청된 EXCLUSIVE | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | |
| 요청된 ACCESS EXCLUSIVE | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 |
행 수준 잠금
행 수준 잠금은 더 세분화되며 동일한 행에 액세스하는 다른 트랜잭션에만 영향을 미칩니다. 이 잠금 유형은 동시성을 향상하지만 많은 잠금을 획득 및 삭제하면 성능에 부정적인 영향을 줍니다. 행 수준 잠금은 PostgreSQL에서 자동으로 획득되며 수동으로 적용되지 않습니다.
Azure Database for PostgreSQL에는 4가지 유형의 행 수준 잠금이 있으며 차단해야 하는 다른 잠금 형식에 따라 잠금을 획득합니다.
| -- | 기존 FOR KEY SHARE | 기존 FOR SHARE | 기존 FOR NO KEY UPDATE | 기존 FOR UPDATE |
|---|---|---|---|---|
| 키 공유 요청됨 | 차단됨 | |||
| 공유 요청됨 | 차단됨 | 차단됨 | ||
| 요청된 FOR NO KEY UPDATE | 차단됨 | 차단됨 | 차단됨 | |
| UPDATE 요청됨 | 차단됨 | 차단됨 | 차단됨 | 차단됨 |
페이지 수준 잠금
페이지 수준 잠금은 일반적으로 여러 행으로 구성된 데이터 페이지에 영향을 줍니다. PostgreSQL 프로세스는 페이지 수준 잠금을 사용하지만 애플리케이션 개발자는 일반적으로 이러한 유형의 잠금이 필요하지 않습니다.
수동으로 잠금 적용 및 현재 잠금 보기
테이블 수준 잠금을 수동으로 적용하려면 LOCK 명령을 필요한 잠금 모드로 사용할 수 있습니다. LOCK 명령은 트랜잭션 내에 있어야 하며 트랜잭션이 완료되면 잠금이 해제됩니다. 다음은 그 예입니다.
BEGIN TRANSACTION;
LOCK TABLE humanresources.department IN ROW EXCLUSIVE MODE;
COMMIT;
현재 데이터베이스에 있는 잠금을 보려면 pg_locks 사용합니다. 예를 들어 모든 현재 잠금을 보려면 다음 명령을 사용합니다.
SELECT * FROM pg_locks;