3-7-1. Lock

1. Lock 기본

가. Lock이란?

 - '직렬화(serialization)'을 가능하게 하기 위해 모든 DBMS가 공통적으로 사용하는 메커니즘

 

나. 공유 Lock과 배타적 Lock

 1) 공유 Lock

   - 공유(Shared) Lock은 데이터를 읽을 때 사용한다. 다른 공유 Lock과는 호환(하나의 리소스에 두 개 이상의 Lock을 동시에 설정 가능)되나 배타적 Lock과는 호환되지 않음.

   - 자신이 읽고 있는 리소스를 다른 사용자가 동시에 읽을 수는 있어도 변경은 불가능하고, 다른 사용자가 변경중인 리소스를 동시에 읽을 수는 없다.

 

 2) 배타적 Lock

   - 배타적(Exclusive) Lock은 데이터를 변경할 때 사용, 트랜잭션이 완료될 때까지 유지된다. 어느 Lock과도 호환 안됨.

 

다. 블로킹, 교착상태

 1) 블로킹

   - Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태를 의미. 주로 배타적 Lock이 껴있을 때 발생한다.

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

 

아래는 Lock 경합에 의한 성능 저하를 최소화하는 방안이다.

1. 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션을 가능한 짧게 정의한다.
2. 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계한다.
3. 대용량 갱신 작업이 불가피하다면, 사용자가 무한정 기다리지 않도록 적절한 프로그래밍 기법을 도입해야 한다. 예를 들면 아래와 같다.
   
   set lock_timeout 2000 --> Lock에 의한 대기 시간이 최대 2초를 넘지 않도록 설정한 것, SQL Server
   
   # 오라클에서는, update/delete 문장 수행 전에 nowait나 wait 옵션을 지정한 select ~~~ for update문을 수행하여 Lock이 설정되었는지 체크할 수 있다.
   select * from t where no = 1 for update nowait --> 대기 없이 Exception을 던짐, Oracle
   select * from t where no = 1 for update wait 3 --> 3초 대기 후 Exception을 던짐, Oracle
4. 트랜잭션 격리성 수준을 불필요하게 상향 조정하지 않는다.
5. 결과가 가장 빨리 나오는 SQL을 작성하는 것이 Lock 튜닝의 기본이자 가장 효과가 확실하다.

 2) 교착상태(DeadLock)

 - 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 마주 보며 진행하는 상황을 말한다. 즉, 둘 중 하나가 뒤로 물러서지 않으면 영영 풀릴 수 없게 된다.

 - 일반적으로 DeadLock이 발생하면 DBMS가 둘 중 한 세션에 에러를 발생시켜 문제를 해결한다.

 - 그 전에 Lock 튜닝으로 최적화를 하거나, SQL Server라면 갱신(Update) Lock을 사용하여 교착상태 발생 가능성을 줄인다.

 

2. SQL Server Lock

가. Lock 종류

 1) 공유 Lock, 배타적 Lock

   - SQL Server의 공유 Lock은 트랜잭션이나 쿼리 수행이 완료될 때까지 유지되는 것이 아니라, 다음 레코드를 읽으면 곧바로 해제된다. 단 기본 트랜잭션 격리성 수준(Read Committed)에서만 그렇다.

   - 격리성 수준을 변경하지 않고도 트랜잭션 내에서 공유 Lock을 유지하려면 다음과 같이 테이블 힌트로 holdlock을 지정하면 된다.

 

begin tran

 

select 적립포인트, 방문횟수, 구매실적

from 고객 with (holdlock)

where  고객번호 =: cust_num

 

-- 새로운 적립포인트 계산

update 고객 set 적립포인트 =: 적립포인트 where 고객번호 =: cust_num

 

commit

   - 나중에 변경할 목적으로 레코드를 읽을 때는 반드시 위와 같은 패턴으로 트랜잭션을 처리해야 한다. 예를 들어, 고객 데이터를 읽고 적립포인트를 변경하기 전 다른 트랜잭션이 해당 고객 데이터를 변경했다면 적립포인트가 비일관된 상태에 놓일 수 있기 때문이다.

 

2) 갱신 Lock

   - 위의 프로그램을 두 트랜잭션이 동시에 수행했다고 가정하자. 이러면 update를 수행할 때 배타적 Lock을 설정하려고 할 것이고, 서로 상대 트랜잭션에 의한 공유 Lock이 해제되기만을 기다리는 교착 상태에 빠지게 된다.

   - 이런 잠재적인 교착상태를 방지하기 위해 SQL Server는 갱신(Update) Lock을 두게 되고, 이 기능을 사용하려면 다음과 같이 updlock 힌트를 지정하면 된다.

 

begin tran

 

select 적립포인트, 방문횟수, 구매실적

from 고객 with (updlock)

where 고객번호 =: cust_num

 

 -- 새로운 적립포인트 계산

update 고객 set 적립포인트 =: 적립포인트 where 고객번호 =: cust_num

 

commit

   - 위 코드에 의해 첫 번째 트랜잭션이 고객 데이터를 읽을 때 '갱신 Lock'을 설정하면, 두 번째 트랜잭션은 해당 데이터가 해제될 때 까지 기다려야 한다.

 

4) 의도 Lock

   - 특정 row에 Lock을 설정하면 그와 동시에 상위 레벨 개체에 내부적으로 의도(Intent) Lock이 설정된다. Lock이 설정하려는 개체의 하위 레벨에서 선행 트랜잭션이 어떤 작업을 수행중인지를 알리는 용도로 사용되며, 일종의 푯말(Flag)이라 할 수 있다.

   - 테이블을 잠그려고 할 때, 해당 테이블의 어떤 모드의 의도 Lock이 설정되어 있는지만 보고도 작업을 진행할지 아니면 기다릴지 결정할 수 있다.

 

5) 스키마 Lock: 테이블 스키마에 의존적인 작업을 수행할 때 사용한다.

• Sch-S(Schema Stability): SQL을 컴파일하면서 오브젝트 스키마를 참조할 때 발생하며, 읽는 스키마 정보를 수정하거나 삭제하지 못하도록 함
• Sch-M(Schema Modification): 테이블 구조를 변경하는 DDL 문을 수행할 때 발생하며, 수정중인 스키마 정보를 다른 세션이 참조하지 못하도록 함

 

6) Bulk Update Lock: 테이블에 데이터를 Bulk Copy(대량 복사)할 때 발생. 병렬 데이터 로딩(Bulk Insert / bcp 작업 동시수행)만 허용된다.

 

나. Lock Level과 Escalation

Lock 레벨	설명
로우 레벨	변경하려는 로우(RID)에만 Lock을 설정
페이지 레벨	변경하려는 로우가 담긴 데이터 페이지에 Lock을 설정
익스텐트 레벨	익스텐트 전체가 잠긴다. SQL Server는 하나의 익스텐트가 여덟 개 페이지로 구성되므로, 8개의 페이지가 모두 잠긴 것과 같은 효과를 나타낸다.
테이블 레벨	테이블 전체와 관련 인덱스까지 모두 잠긴다.
데이터베이스 레벨	데이터베이스 전체가 잠긴다. 데이터베이스 복구 작업 및 스키마 변경 시 사용된다.

   - Lock Escalation: 관리할 Lock 리소스가 정해진 임계치를 넘으면서 로우 레벨 락이 페이지, 익스텐트, 테이블 레벨 락으로 점점 확장되는 것을 말한다. Lock 레벨이 낮을수록(예: 로우 레벨) 동시성은 좋아지나 관리해야 할 Lock 수가 증가하고, Locking 레벨이 높을수록(예: 데이터베이스 레벨) 적은 양의 Lock 리소스를 사용하나, 동시성이 나빠진다.

 

다. Lock 호환성

 - '호환된다'라는 말은, 한 리소스에 두 개 이상의 Lock을 동시에 설정할 수 있음을 의미한다.

 

아래 표는, SQL Server Lock 호환성을 표로 나타낸 것이다.

	IS	S	U	IX	SIX	X
Intent Shared(IS)	O	O	O	O	O
Shared(S)	O	O	O
Update(U)	O	O
Intent Exclusive(IX)	O			O
Shared with Intent Exclusive(SIX)	O
Exclusive(X)

스키마 Lock의 호환성은 아래와 같다.

• Sch-S: Sch-M을 제외한 모든 Lock과 호환된다.
• Sch-M: 어떤 Lock과도 호환되지 않는다.

 

3. Oracle Lock

가. 로우 Lock

   - Oracle에서의 로우 Lock은 항상 배타적으로, insert-update-delete문이나 select-for update문을 수행할 때 설정된다.

   - 이 트랜잭션이 커밋 or 롤백할 때까지 다른 트랜잭션은 해당 로우를 건드릴 수 없다.

   - 일반 select 문에는 어떠한 Lock도 설정되지 않는다(단순 읽기라서). 읽으려는 데이터를 다른 트랜잭션이 갱신중이라도 기다리지 않고, 갱신하려는 데이터를 다른 트랜잭션이 읽는 중이라도 기다리지 않는다. 즉, 갱신하려는 데이터를 다른 트랜잭션이 갱신중이라면 기다린다.

 

나. 테이블 Lock: 한 트랜잭션이 로우 Lock을 얻을 때 해당 테이블의 테이블 Lock도 동시에 얻는다. 이렇게 하는 것은, 현재 트랜잭션이 갱신중인 테이블의 구조를 변경(DDL)하지 못하게 막기 위함이다.

 

테이블 Lock 종류는 아래 5가지가 있다.

• Row Share(RS): select ~ for update
• Row Exclusive(RX): insert, update, delete
• Share(X),
• Share Row Exclusive(SRX),
• Exclusive(X)

DMl 로우 Lock을 처음 얻는 순간 묵시적으로 테이블 Lock을 얻지만, 아래처럼 Lock Table 명령어를 이용해 명시적으로 테이블 Lock을 얻을 수도 있다.

 

lock table emp in row share mode; --> RS

lock table emp in row exclusive mode; --> RX

...

테이블 Lock끼리의 호환성은 아래와 같다.

	Null	RS	RX	S	SRX	X
Null	O	O	O	O	O	O
RS	O	O	O	O	O
RX	O	O	O
S	O	O		O
SRX	O	O
X	O

SQL Server에서는 테이블 Lock이 '모두 잠그는' 기능이나, Oracle Server에서는 그렇지 않다.

Oracle Server에서 테이블 Lock은, Lock을 획득한 선행 트랜잭션이 해당 테이블에서 현재 어떤 작업을 수행중인지 알리는 일종의 푯말이다.