잠금(Lock) 범위를 줄이기 위한 인덱스 최적화 방법

DBMS에서 잠금(Lock) 범위를 줄이기 위한 인덱스 최적화 방법

DBMS는 데이터의 일관성과 무결성을 유지하기 위해 잠금(Lock) 메커니즘을 사용합니다. 하지만 잠금 범위가 넓어지면 동시성이 떨어지고 성능 문제가 발생할 수 있습니다. 특히 Primary Key와 적절한 인덱스가 없는 경우, 테이블 전체 잠금(Table Lock)이 발생할 가능성이 높습니다. 이 글에서는 잠금의 종류와 이를 줄이기 위한 인덱스 최적화 방법을 자세히 설명합니다.

---

1. 잠금의 종류

잠금은 데이터를 처리하는 트랜잭션 간 충돌을 방지하는 메커니즘입니다. DBMS에서 주로 사용되는 잠금 종류를 표로 정리했습니다.

잠금 종류	설명	장점	단점	사용 사례
Table Lock	테이블 전체를 잠금. 모든 행에 대해 읽기 또는 쓰기 작업을 독점.	낮은 관리 오버헤드	동시성 감소, 다른 트랜잭션의 작업 대기 가능	대량 업데이트/삭제 작업 (DELETE ALL)
Row Lock	특정 행만 잠금. 트랜잭션이 특정 데이터 행에만 영향을 미침.	높은 동시성	관리 오버헤드 증가	특정 행 조회/수정 (e.g., Primary Key 검색)
Page Lock	데이터 페이지(보통 8KB~16KB) 단위로 잠금. 해당 페이지 내 여러 행이 영향을 받음.	행 잠금보다 오버헤드 낮음	페이지 내 다른 행도 잠길 가능성	범위 검색, 인덱스 스캔 작업

---

2. Primary Key가 없으면 발생할 문제

(1) Table Lock 증가

• Primary Key는 테이블의 기본 검색 기준이 되며, 특정 행을 빠르게 찾을 수 있도록 돕습니다.
• Primary Key가 없는 경우, DBMS는 특정 데이터를 찾기 위해 테이블 전체를 스캔(Full Table Scan)해야 하며, 이 과정에서 테이블 잠금(Table Lock)이 발생할 가능성이 높습니다.

-- Primary Key 없는 쿼리: 테이블 전체 스캔 발생
SELECT * FROM orders WHERE customer_name = 'John Doe';

(2) Row Lock을 활용하지 못함

• Primary Key 또는 Unique Key가 없는 경우, DBMS는 특정 행에 대해서만 잠금을 걸 수 없고, 더 광범위한 잠금을 사용해야 합니다.

(3) 인덱스 활용 불가

• Primary Key는 기본적으로 클러스터드 인덱스를 생성하므로 데이터 검색 속도와 잠금 범위를 줄이는 데 유리합니다. 이를 대체할 다른 인덱스가 없다면, DBMS는 전체 테이블 스캔과 잠금을 유발합니다.

---

3. 잠금 범위와 인덱스의 관계

(1) 효율적인 인덱스 사용으로 Row Lock 유도

• 적절한 인덱스를 사용하면 DBMS는 특정 행에 대해 정확히 잠금을 걸 수 있어 동시성을 높일 수 있습니다.

-- Primary Key를 사용한 쿼리
SELECT * FROM orders WHERE order_id = 123;

• Primary Key 인덱스를 통해 DBMS는 특정 행(Row)에 대해 잠금을 걸며, 다른 트랜잭션이 동일 테이블의 다른 행을 자유롭게 작업할 수 있습니다.

---

(2) 인덱스 없는 경우 Table Lock 발생

• 인덱스가 없는 조건에서는 DBMS가 데이터를 검색하기 위해 테이블 전체를 탐색해야 하므로 Table Lock이 발생할 가능성이 큽니다.

-- 인덱스 없는 테이블 검색
SELECT * FROM orders WHERE customer_name LIKE '%John%';

• 테이블 전체를 스캔하므로 검색 작업이 완료될 때까지 다른 트랜잭션이 대기해야 할 수 있습니다.

---

(3) Page Lock과 범위 검색

• 인덱스가 있더라도 범위 검색(예: BETWEEN, LIKE)의 경우, 한 페이지에 포함된 여러 행이 잠길 수 있습니다.

-- 범위 검색으로 Page Lock 발생
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

• DBMS는 검색 범위를 좁히기 위해 인덱스를 사용하지만, 해당 범위에 포함된 페이지 단위로 잠금을 설정할 수 있습니다.

---

4. 잠금 최적화를 위한 전략

(1) Primary Key와 Unique Key 설계

• 테이블의 주요 검색 기준이 되는 필드에 Primary Key를 설정하세요.
• 각 행을 고유하게 식별할 수 있는 Unique Key를 추가해 검색 범위를 제한합니다.

-- Primary Key와 Unique Key 설정
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    UNIQUE (customer_id, order_date)
);

---

(2) 효율적인 인덱스 설계

• 복합 인덱스(Composite Index)를 사용해 자주 사용하는 다중 조건 검색을 최적화합니다.

  -- 고객 ID와 주문 날짜를 기준으로 복합 인덱스 생성
  CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

• 인덱스 크기를 작게 유지하고, 필요 없는 필드는 포함하지 않도록 주의하세요.

---

(3) 쿼리 최적화

• 검색 조건을 명확히 설정하여 불필요한 데이터 잠금을 줄입니다.

  -- 검색 범위를 제한하는 효율적인 쿼리
  SELECT customer_id, order_date FROM orders WHERE customer_id = 123;

• 필요한 데이터만 선택하여 처리하도록 쿼리를 설계합니다.

---

(4) 트랜잭션 길이 최소화

• 트랜잭션 길이가 길수록 잠금 유지 시간이 증가하므로, 짧은 트랜잭션을 유지하세요.
• 대량 작업은 배치(batch)로 나누어 실행합니다.

-- 1000개 단위로 삭제 (MySQL)
BEGIN TRANSACTION;
DELETE FROM orders WHERE order_date < '2020-01-01' LIMIT 1000;
COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

-- 1000개만 삭제 (Sql Server / MSSQL)
DELETE TOP (1000) FROM orders WHERE order_date < '2020-01-01';

COMMIT TRANSACTION;

---

(5) 테이블 파티셔닝

• 대규모 테이블은 파티셔닝(Partitioning)을 사용해 논리적으로 분리합니다.
• 파티션별로 작업을 수행하면 잠금 충돌 가능성을 줄일 수 있습니다.

-- 주문 날짜를 기준으로 테이블 파티셔닝
CREATE TABLE orders_2024 PARTITION BY RANGE (order_date) (
    PARTITION p2024_q1 VALUES LESS THAN ('2024-04-01'),
    PARTITION p2024_q2 VALUES LESS THAN ('2024-07-01')
);

---

5. 결론

• Primary Key는 잠금 최적화의 핵심입니다. 기본 키가 없으면 테이블 전체를 잠그는 비효율적인 작업이 발생할 수 있습니다.
• 적절한 인덱스 설계와 쿼리 최적화를 통해 Row Lock을 유도하고 잠금 범위를 최소화하세요.
• 트랜잭션 관리와 테이블 구조 설계(파티셔닝 등)를 함께 고려하면 동시성과 성능을 극대화할 수 있습니다.

잠금 문제로 어려움을 겪고 있다면, Primary Key와 인덱스 최적화부터 시작하세요. 이것만으로도 성능 향상을 크게 기대할 수 있습니다! 😊