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