[SQL 튜닝] Lock과 트랜잭션 동시성 제어

오라클 Lock 

- 공유 리소스와 사용자 데이터를 보호할 목적으로 DML Lock, DDL Lock, 래치, 버퍼 Lock, 라이브러리 캐시 Lock/Pin 등 다양한 종류의 Lock을 사용

- 래치 : SGA에 공유된 각종 자료구조를 보호하기 위해 사용

- 버퍼 LOCK : 버퍼 블록에 대한 액세스를 직렬화 하기 위해 사용

- 라이브러리 캐시 Lock와 Pin은 라이브러리 캐시에 공유된 SQL 커서와 PL/SQL프로그램을 보호하기 위해 사용

- 애플리케이션 개발에서 가장 중요한 것은 DML LOCK

- 다중 트랜잭션이 동시에 액세스하는 사용자 데이터의 무결성 보호

 

DML 로우 Lock

- 두개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지

- DML 로우 Lock에는 배타적 모드를 사용하므로 UPDATE 또는 DELETE를 진행 중인(아직 커밋하지 않은) 로우를 다른 트랜잭션이 UPDATE하거나 DELETE 할 수 없다.

- Insert에 대한 로우 Lock경합은 Unique인덱스가 있을 때만 발생

- MVCC모델을 사용하는 오라클은 Select문에 로우 lock 경합은 발생하지 않음

- 오라클에서는 DML과 select는 서로 진행을 방해하지 않음

- DML로우 Lock에 의한 성능저하를 방지하려면, 온라인 트랜잭션을 처리하는 주간에 Lock를 필요이상ㅇ로 오래 유지하지 않도록 커밋시점을 조절

 

DML 테이블 Lock

- 로우 lock를 설정하기 전에 테이블 lock를 먼저 설정 -> 현재 트랜잭션이 갱신중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해서임

- 선행 트랜잭션과 호환되지 않는 모드로 테이블 Lock을 설정하려는 후행 트랜잭션은 대기하거나 작업을 포기

- 테이블 lock를 설정한 트랜잭션이 해당 테이블에서 현재 어떤 작업을 수행 중인지를 알리는 푯말

- 푯말에 기록된 lock모드와 후행 트랜잭션이 현재 하려는 작업에 따라 진행여부가 결정

 

Lock를 푸는 열쇠, 커밋

- 블로킹 : 선행 트랜잭션이 설정한 lock때문에 후행트랜잭션이 작업을 진행하지 못하는 상태

- 교착상태 : 두 트랜잭션이 각각 특정 리소스에 Lock를 설정한 상태에서 맞은편 트랜잭션이 Lock를 설정한 리소스에 또 락을 설정하려고 하는 상태 -> 오라클은 이때 교착상태를 발생시킨 문장을 롤백

- 해당 메세지를 받은 트랜잭션은 커밋 또는 롤백을 결정해야 함

- 오라클은 데이터를 읽을 때 lock을 사용하지 않으므로 다른 DBMS에 비해 상대적으로 lock경합이 적게 발생, 읽는 트랜잭션의 진행을 막는 부담감이 없으므로 필요한 만큼 트랜잭션을 길게 가져갈 수 있음(물론 불필요하게 길필요는 없음)

 

트랜잭션 동시성 제어

1. 비관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정할 것으로 가정하여 한 사용자가 데이터를 읽는 시점에 lock를 걸고 조회 또는 갱신이 완료될때까지 이를 유지

2. 낙관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정하여, 데이터를 읽을 때 lock를 걸지 않음

 

데이터 품질과 동시성 향상을 위해서는?

• 다중 트랜잭션이 존재하는 데이터베이스 환경에서 공유 자원에 대한 엑세스 직렬화는 필수
• 데이터를 변경할 목적으로 읽는 다면 Lock는 필수
• For Update 사용을 두려워하지 말것
• 트랜잭션의 원자성을 보장하는 범위 내에서 가급적 빨리 커밋 진행
• 낙관적 동시성 제어를 시도했다가 다른 트랜잭션에 의해 데이터가 변경된 사실이 발견되면 롤백하고 다시 시도할 때 비관적 동시성 제어를 사용하는 방식으로 낙관적/비관적 동시성 제어를 동시에 사용 가능
• 동시성 향상에는 SQL튜닝이 기본, Lock에 대한 고민은 트랜잭션 내 모든 SQL을 완벽히 튜닝하고 나서 진행

 

채번 방식에 따른 INSERT 성능 비교

- Insert가 가장 중요하고 튜닝요소가 많음

- PK중복 방지를 위한 채번이 선행되어야함.

1) 채번 테이블

• 각 테이블 식별자의 단일칼럼 일련번호 또는 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식
• 채번 레코드를 읽어서 1을 더한 값을 변경하고 그 값을 새로운 레코드를 입력하는 데 사용

장점

1. 범용성이 좋음
2. 중복 레코드 발생에 대비한 예외처리에 신경쓰지 ㅇ나하도 되므로 채번 함수만 잘 정의하면 됨
3. Insert과정에 결번 방지 가능
4. PK가 복합카럼일 때도 사용가능

단점

• 다른 채번방식에 비해 성능이 안좋음 -> 채번 레코드를 변경하기 위한 로우 lock경합 때문

따라서 동시 Insert가 많은 테이블에는 이 방식을 적용하기 어려움

 

2) 시퀸스 오브젝트

• 장점 : 성능이 굉장히 좋음 -> 중복 레코드 발생에 대비한 예외처리에 신경쓸 필요 없음
• 하지만 시퀸스 오브젝트도 결국 ㅌ이블이므로 값을 읽고 변경하는 과정에 lock메커니즘이 작동
• 단점 :
  1. 기본적으로 PK가 단일컬럼일 때만 사용가능, 복합 칼럼일때도 사용가능하지만 최소성 요건 위배(각 레코드를 유일하게 식별하는 최소 칼럼으로 PK 구성)
  2. 신규데이터를 입력하는 과정에 결번이 생길 수 있음 -> 시퀀스 채번 이후 트랜잭션을 롤백하는 경우와, CATHE옵션을 설정한 시퀀스가 캐시에서 밀려나는 경우

 

3) MAX + 1조회

- 대상 테이블의 최종 일련번호를 조회하고, 거기에 1을 더해서 insert

장점

1. 시퀀스 또는 별도의 채번 테이블을 관리하는 부담이 없음
2. 동시 트랜잭션에 의한 충돌이 많지 않으면 성능이 매우 빠름
3. PK가 복합칼럼인 경우, 즉 구분 속성별 순번을 채번할 때도 사용가능 -> 값의 수가 많을수록 성능이 좋아짐

단점

1. 레코드 중복에 대비한 세밀한 예외처리 필요
2. 다중 트랜잭션에 의한 동시 채번이 시퀀스보다 성능이 많이 나빠질 수 있음

 

LOCK 경합 요소를 고려한 채번 방식 선택 기준

1. 채번 테이블이나 시퀀스 오브젝트 관리 부담을 고려한다면 가급적 MAX+1방식을 사용
2. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 단일칼럼 일련번호라면, 시퀀스 방식이 가장 좋음
3. 다중 트랜잭션에 의한 동시 채번이 많고 PK구분 속성에 값 종류 개수가 많으면, 중복에 의한 로우 Lock경합 및 재실행 가능성이 낮다. 그렇다면 시퀀스보다 MAX+1 방식이 구조적으로 좋다.
4. 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성에 값 종류 개수가 적으면, MAX + 1방식은 성능에 문제가 생길 수 있음., 그럴때는 순환 옵션을 가진 시퀀스 오브젝트 활용 고려