[SQLP] 2장. Lock과 트랜잭션 동시성 제어 - Lock

Lock

1. Lock 기본

가. Lock이란?

• 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하려면 트랜잭션의 순차적 진행을 보장할 수 있는 직렬화(serialization) 장치

• DBMS마다 Lock을 구현하는 방식과 세부적인 기능이 많이 다르다.

나. 공유 Lock과 배타적 Lock

1) 공유 Lock

• 데이터를 읽고자 할 때 사용

• 한 리소스에 두 개 이상의 Lock을 동시에 설정할 수 있음 (다른 공유 Lock 호환 가능)

• 다른 사용자가 동시에 읽을 수는 있어도 변경은 불가능

2) 배타적 Lock

• 데이터를 변경하고자 할 떄 사용

• 배타적 Lock 해제될 때 까지 다른 Lock이 읽기, 쓰기 불가능

다. 블로킹과 교착상태

1) 블로킹

• Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태

• 블로킹을 해소하는 방법 : 커밋, 롤백

• 성능 저하를 최소화 하는 방법

  • 트랜잭션을 가능한 짧게 정의하기

  • 트랜잭션이 활발한 주간에 대용량 갱신 작업을 수행하지 말것

  • LOCK_TIMEOUT을 설정하여 Lock의 대기 시간을 지정 (에: for update wait 3 : 3초 대기)

  • SQL이 빠른 시간 안에 끝나도록 작성

2) 교착 상태

• 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 마주보며 진행하는 상황

• 마스터 테이블 다음에 상세 테이블을 갱신하기로 규칙을 정하기

2. SQL Server Lock

가. Lock 종류

1) 공유 Lock

• 다음 레코드 Read시 곧바로 현재 Lock 해제

• 트랜잭션 내에서 공유 Lock이 유지되도록 하려면 아래와 같이 테이블 힌트로 holdlock을 지정

2) 배타적 Lock

공유 Lock과 동일

3) 갱신 Lock

• updlock 힌트를 사용하여 동시에 배타적 Lock을 거는 것을 방지할 수 있다.

4) 의도 Lock

• 특정 Row에 Lock을 설정할 때 어떤 작업을 수행중인지 검사해야 한다. 이때 어떤 모드의 의도 Lock이 설정돼 있는지 보고 작업을 결정할 수 있다.

5) 스키마 Lock

테이블 스키마에 의존적인 작업을 수행할 때 사용된다.

• Sch-S(Schema Stability) : SQL을 컴파일하면서 오브젝트 스키마를 참조할 때 발생하며, 읽는 스키마 정보가 수정되거나 삭제되지 못하도록 함

• Sch-M(Schema Modification) : 테이블 구조를 변경하는 DDL 문을 수행할 때 발생하며, 수정 중인 스키마 정보를 다른 세션이 참조하지 못하도록 함

나. Lock 레벨과 Escalation

Lock Escalation

관리할 Lock 리소스가 정해진 임계치를 넘으면서 로우 레벨 락이 페이지, 익스텐트, 테이블 레벨 락으로 점점 확장되는 것

다. Lock 호환성

한 리소스에 두 개 이상의 Lock을 동시에 설정할 수 있음을 뜻한다.

3. Oracle Lock

DML Lock, DDL Lock, 래치(Latch), 버퍼 Lock, 라이브러리 캐시 Lock/Pin 등 
다음은 DML Lock을 알아보자.

가. 로우 Lock

• 로우 Lock은 항상 배타적임

• Insert, Update, Delete문 등 트랜젝션에 설정

• Select문은 Lock 설정되지 않음

  • Read 하려는 데이터가 다른 Write 트랜젝션 중이어도 기다리지 않는다.

  • Write 하려는 데이터가 다른 Read 트랜젝션 중이어도 기다리지 않는다.

  • Write 하려는 데이터가 다른 Write 트랜젝션 중이면 기다린다.

나. 테이블 Lock

• 한 트랜잭션이 로우 Lock을 얻는 순간, 해당 테이블에 대한 테이블 Lock도 동시에 얻음

• 테이블 Lock의 의미는, Lock을 획득한 선행 트랜잭션이 해당 테이블에서 현재 어떤 작업을 수행중인지를 알리는 일종의 푯말(Flag)임 (후행 트랜잭션은 어떤 테이블 Lock이 설정돼 있는지만 보고도 그 테이블로의 진입 여부를 결정할 수 있다.)