SQL Lock과 트랜잭션 동시성 제어
제 1절 Lock
1. Lock 기본
가. Lock이란?
같은 자원을 액세스하려는 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하면서 트랜잭션의 순차적 진행을 보장할 수 있는 직렬화 장치.
나. 공유 Lock과 배타적 Lock
- 공유 Lock
- 배타적 Lock
다. 블로킹과 교착상태
- 블로킹
- 교착상태
2. Oracle Lock
Oracle은 다양한 종류의 Lock을 사용한다. 그중 가장 중요한 Lock은 DML Lock이다.
DML Lock은 다중 사용자에 의해 동시에 액세스되는 사용자 데이터의 무결성을 보호해 준다. DML Lock에는 로우 Lock, 테이블 Lock이 있다.
가. 로우 Lock
- Oracle에서 로우 Lock은 항상 배타적이다. 따라서, insert, update, delete, select for update문을 수행한 트랜잭션에 의해 설정된다.
- 일반 select문에 대해서는 어떠한 Lock도 설정되지 않는다. 다른 DBMS에 비해 읽기 작업에 대해 공유 Lock을 설정하지 않기 때문에 읽기와 갱신작업은 서로 방해받지 않는다.
나. 테이블 Lock
- 한 트랜잭션이 로우 Lock을 얻으면, 동시에 테이블 Lock도 얻는다.
- 테이블 Lock의 종류:
- Row Share(RS)
- Row Exclusive(RX)
- Share(S)
- Share Row Exclusive(SRX)
- Exclusive(X)
- 대표적으로 select for update문을 수행할때는 RS모드 테이블 Lock을 얻는다.
- insert, update, delete 문을 수행할 때 RX모드 테이블 Lock을 얻는다.
- DML 로우 Lock을 얻는 순간 묵시적으로 테이블 Lock을 얻지만 명시적으로 다음 명령어를 사용할때 Lock을 얻을 수 있다.
- lock table emp in row share mod;
- lock table emp in row exlcusive mode;
- lock table emp in share mode;
- lock table emp in share row exclusive mode;
- lock table emp in exclusive mode;
| NULL | RS | RX | S | SRX | X | |
|---|---|---|---|---|---|---|
| NULL | O | O | O | O | O | O |
| RS | O | O | O | O | O | |
| RX | O | O | O | |||
| RX | O | O | O | |||
| SRX | O | O | ||||
| X | O |
제 2절 트랜잭션
트랜잭션은 업무 처리를 위한 논린적인 작업 단위이다.
여러 개의 갱신 연산이 하나의 작업처럼 전부 처리되거나 아예 하나도 처리되지 않도록(All or Nothing)동시 실행을 구현해야 한다.
1. 트랜잭션의 특징
- 원자성(Atomicity) : 트랜잭션은 더 이상 분해가 되지 않는 최소 단위이다.
- 일관성(Consistency) : 트랜잭션 실행의 결과로 데이터베이스 상태가 모순되지 않아야한다.
- 격리성(Isolation) : 실행중인 트랜젹션의 중간결과를 다른 트랜잭션이 접근할 수 없다.
- 영속성(Durability) : 실행을 성공적으로 완료하면 그 결과는 데이터베이스에 영속적으로 저장된다.
2. 트랜잭션 격리성
가. 낮은 단계의 격리성 수준에서 발생할 수 있는 현상들
* Dirty Read
- 다른 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 상태에서 데이터를 읽는 것을 말한다.
- 예를 들어 변경 후 아직 커밋되지 않은 값을 읽었는데 최종적으로 롤백이 된다면, 그 값을 읽은 트랜잭션은 비일관된 상태가 되게 된다.
* Non-Repeatable Read
- 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하여 두 쿼리 결과가 다르게 나타나는 현상을 말한다.
| TX1 | TX2 | |
| SELECT 잔고 into :balance FROM 계좌 WHERE 계좌번호 = 123; | t1 | |
| t2 | UPDATE 계좌 SET 잔고 = 잔고 - 50000 WHERE 계좌번호 123; | |
| t3 | COMMIT; | |
| UPDATE 계좌 SET 잔고 = 잔고 - 10000 WHERE 계좌번호 =123 AND 잔고 >=10000; | t4 | |
| IF sql%rowcount = 0 THEN alert(‘잔고가 부족합니다’); END IF; | t5 | |
| COMMIT | t6 |
* Phantom Read
- 한 트랜잭션 내에서 같은 쿼리를 두 번 수행하였는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두번째 쿼리에서 나타나는 현상이다.
| TX1 | TX2 | |
| INSERT INTO 지역별고객 SELECT 지역, COUNT(*) FROM 고객 GROUP BY 지역; | t1 | |
| t2 | INSERT INTO 고객(고객번호, 이름, 지역, 연령대, …) VALUES (:a, :b, :c, :d, …); | |
| t3 | COMMIT; | |
| INSERT INTO 지역별고객 SELECT 지역, COUNT(*) FROM 고객 GROUP BY 연령대; | t4 | |
| COMMIT | t5 |
나. 트랜잭션 격리성 수준
ANSI ISO SQL에서 정의한 4가지 트랜잭션 경리성 수준(Transaction Isolation Level)은 다음과 같다.
* Read Uncommitted
- 트랜잭션에서 처리 중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다.
* Read Committed
- 트랜잭션이 커밋되어 확정된 데이터만 다른 트랜잭션이 읽도록 허용하여 Dirty Read를 방지한다.
- 커밋된 데이터만 읽더라도 Non-Reaptable Read와 Phantom Read현상을 막지는 못한다.
* Repeatable Read
- 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지한다.
- Phantom Read를 막지는 못한다.
* Serializable Read
- 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않고 새로운 레코드도 나오지 않는다.
제 3절 동시성 제어
동시에 작동하는 다중 트랜잭션의 상호 간섭 작용에서 데이터베이슬 보호하는것.
동시성 제어기법에는 비관적 동시성 제어와 낙관적 동시성 제어 2가지가 있다.
1. 비관적 동시성 제어 vs. 낙관적 동시성 제어
가. 비관적 동시성 제어
- 비관적 동시성제어는 사용자들이 같은 데이터에 대해서 동시에 수정할 것이라고 가정한다. 따라서 데이터를 읽는 시점에 Lock을 걸고 트랜잭션이 완료될 때까지 이를 유지한다.
나. 낙관적 동시성 제어
- 사용자들이 같은 데이터를 동시에 수정하지 않을것이라고 가정한다. 따라서 데이터를 읽을때는 Lock을 설정하지 않는다.
2. 다중버전 동시성 제어
가. 일반적인 Locking 메커니즘의 문제점
- 동시성 제어의 목표는, 동시에 실행되는 트랜잭션 수를 최대화하면서도 입력, 수정, 삭제, 검색 시 데이터 무결성이 유지되도록 하는데에 있다.
나. 다중버전 동시성 제어(Multiversion Concurrency Control)
- 데이터를 변경할 때마다 그 변경사항을 Undo영역에 저장한다.
- 데이터를 쿼리 시작 시점 이후에 변경된 값을 발견하면, Undo 영역에 저장된 정보를 이용해 쿼리 시작 시점의 일관성 있는 버전을 생성하고 그것을 읽는다.
- 다중버전 동시성 제어에는 Undo 블록 I/O, CR 블록 캐싱 등의 부가적인 오버헤드가 발생한다.
다. 문장수준 읽기 일관성
- 다른 트랜잭션에 의해 데이터가 추가, 변경, 삭제 되더라도 단일 SQL문 내에서 일관성 있게 값을 읽는것을 말한다.
- 즉 해당 쿼리 이후 시점의 쿼리에 의해 변경된 데이터 블록을 만날때 Rollback(=Undo) 세그먼트에 저장된 정보를 이용하여 값을 읽는다.
라. 트랜잭션 수준 읽기 일관성
- 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는것을 말한다.
- 격리성 수준을 상향 조정한다.