3과목 SQL 고급 활용 및 Tuning
2장 Lock과 Transaction 동시성 제어
2.1 Lock
Shared Lock vs. Exclusive Lock
- Shared Lock (공유 Lock)
- 데이터를 읽을 때 사용
- 다른 Shared Lock과는 호환되지만, Exclusive Lock과는 호환이 안됨
- 즉, Shared Lock이 걸려있는 상태에서 Exclusive Lock은 Blocking 됨
- Exclusive Lock (베타적 Lock)
- 데이터를 변경할 때 사용
- 모든 종류의 Lock과 호환이 안됨
- 데이터를 읽을 때 사용
- 다른 Shared Lock과는 호환되지만, Exclusive Lock과는 호환이 안됨
- 즉, Shared Lock이 걸려있는 상태에서 Exclusive Lock은 Blocking 됨
- 데이터를 변경할 때 사용
- 모든 종류의 Lock과 호환이 안됨
Blocking과 Deadlock
- Blocking
- Lock 경합이 발생하여 특정 세션이 작업을 진행하지 못하고 멈춘 상태
- Blocking 상태를 해소하는 방법은 Commit or Rollback 뿐
- Lock에 의한 성능 최소화 방안
- Transaction의 원자성을 훼손하지 않는 선에서 최대한 짧게 정의
- 같은 데이터를 갱신하는 Transaction이 동시에 수행되지 않도록 설계
- Transaction Isolation Level을 지나치게 상향 조정하지 않음
- Transaction을 잘 설계하여 대기 현상을 피하도록 프로그래밍
- 주간의 대용량 갱신작업이 불가피하다면 timeout을 이용
SELECT * FROM T WHERE ... FOR UPDATE NOWAIT -- 대기없이 Exception
SELECT * FROM T WHERE ... FOR UPDATE WAIT 3 -- 3초 대기 후 Exception
- Deadlock (교착상태)
- 두 세션이 각각 Lock을 건 상태에서 서로의 Lock걸린 리소스를 액세스하려 할 경우 영원히 대기상태에 빠지는 것
- 유일한 해결 방법은 둘 중 한 세션에 에러를 발생시키는 것
- 테이블 접근 순서를 같게 처리하면 피할 수 있음
- Lock 경합이 발생하여 특정 세션이 작업을 진행하지 못하고 멈춘 상태
- Blocking 상태를 해소하는 방법은 Commit or Rollback 뿐
- Transaction의 원자성을 훼손하지 않는 선에서 최대한 짧게 정의
- 같은 데이터를 갱신하는 Transaction이 동시에 수행되지 않도록 설계
- Transaction Isolation Level을 지나치게 상향 조정하지 않음
- Transaction을 잘 설계하여 대기 현상을 피하도록 프로그래밍
- 주간의 대용량 갱신작업이 불가피하다면 timeout을 이용
SELECT * FROM T WHERE ... FOR UPDATE NOWAIT -- 대기없이 Exception
SELECT * FROM T WHERE ... FOR UPDATE WAIT 3 -- 3초 대기 후 Exception
- 두 세션이 각각 Lock을 건 상태에서 서로의 Lock걸린 리소스를 액세스하려 할 경우 영원히 대기상태에 빠지는 것
- 유일한 해결 방법은 둘 중 한 세션에 에러를 발생시키는 것
- 테이블 접근 순서를 같게 처리하면 피할 수 있음
Oracle Lock
- Oracle에서는 어떠한 읽기 작업도 Lock에 영향을 받지 않는다.
- Undo 데이터를 활용한 다중버전 동시성 제어 메커니즘을 활용한다.
- 단, SELECT ... FOR UPDATE 제외
- Row Lock
- 항상 Exclusive Lock
- INSERT, UPDATE, DELETE, SELECT ... FOR UPDATE를 수행한 Transaction에 의해 설정되며 해당 Transaction이 Commit 이나 Rollback할때까지 다른 Transaction은 해당 Row를 변경할 수 없다.
- Table Lock
- Row Lock을 얻는 순간, 해당 Table에 대한 Table Lock도 얻는다.
- 현재 Transaction이 갱신 중인 Table에 대해 호환되지 않는 DDL 수행을 방지한다.
- 5종류의 Lock이 있음
- RS (Row Share) : X제외 모두 허용
- RX (Row Exclusive) : RS, RX 허용
- S (Share) : RS, S 허용
- SRX (Share Row Exclusive) : RS 허용
- X (Exclusive) : 모두 불허용
- 명시적으로
Lock Table 명령어를 사용 할 수도 있음
LOCK TABLE emp IN ROW SHARE MODE;
LOCK TABLE emp IN ROW EXCLUSIVE MODE;
...
- Undo 데이터를 활용한 다중버전 동시성 제어 메커니즘을 활용한다.
- 단, SELECT ... FOR UPDATE 제외
- 항상 Exclusive Lock
- INSERT, UPDATE, DELETE, SELECT ... FOR UPDATE를 수행한 Transaction에 의해 설정되며 해당 Transaction이 Commit 이나 Rollback할때까지 다른 Transaction은 해당 Row를 변경할 수 없다.
- Row Lock을 얻는 순간, 해당 Table에 대한 Table Lock도 얻는다.
- 현재 Transaction이 갱신 중인 Table에 대해 호환되지 않는 DDL 수행을 방지한다.
- 5종류의 Lock이 있음
- RS (Row Share) : X제외 모두 허용
- RX (Row Exclusive) : RS, RX 허용
- S (Share) : RS, S 허용
- SRX (Share Row Exclusive) : RS 허용
- X (Exclusive) : 모두 불허용
- 명시적으로
Lock Table명령어를 사용 할 수도 있음
LOCK TABLE emp IN ROW SHARE MODE;
LOCK TABLE emp IN ROW EXCLUSIVE MODE;
...
2.1 Transaction
Transaction 특징
- 원자성 (Atomicity) : 업무상 최소단위
- 일관성 (Consistency) : 실행 전후 데이터베이스 상태가 모순되지 않아야 함
- 격리성 (Isolation) : 실행 중 다른 Transaction이 접근할 수 없음
- 영속성 (Durability) : 성공적으로 수행하면, 데이터베이스에 영속적으로 저장
낮은 Isolation Level에서의 현상
- Dirty Read
- Commit하지 않은 데이터를 읽음. 이미 읽은 뒤 다시 Rollback 될 수 있음.
- Non-Repeatable Read
- 한 Transaction 내의 같은 Query에 대해서 값이 바뀌는 현상
- 처음 값을 읽은 뒤 다른 Transaction이 해당 값을 변경한 경우
- Phantom Read
- 한 Transaction 내에 같은 Query에 대해서 처음에 없었던 값이 읽히는 현상
- 처음 값을 읽은 뒤 다른 Transaction이 새로운 값을 입력한 경우
- Commit하지 않은 데이터를 읽음. 이미 읽은 뒤 다시 Rollback 될 수 있음.
- 한 Transaction 내의 같은 Query에 대해서 값이 바뀌는 현상
- 처음 값을 읽은 뒤 다른 Transaction이 해당 값을 변경한 경우
- 한 Transaction 내에 같은 Query에 대해서 처음에 없었던 값이 읽히는 현상
- 처음 값을 읽은 뒤 다른 Transaction이 새로운 값을 입력한 경우
Transaction Isolation Level
- Read Uncommitted : 다른 Transaction이 처리 중인 값을 읽는 것을 허용 (이걸 허용하는 DB는 거의 없음)
- Read Committed : Dirty Read 방지
- Repeatable Read : Non-Repeatable Read 방지
- Serializable Read : Phantom Read 방지
SET TRANSACTION ISOLATION LEVEL READ SERIALIZABLE;
- Transaction Isolation Level을 높일수록 Lock에 의존적이다보니 동시성이 저하된다.
- Oracle에서는 다중버전 동시성 제어(Multiversion Concurrency Control)을 사용한다.
SET TRANSACTION ISOLATION LEVEL READ SERIALIZABLE;
동시성 제어 (Concurrency Control)
- 비관적 동시성 제어 (Pessimistic Concurrency Control)
- 같은 데이터를 동시에 수정할 것이라고 가정
- 먼저 Lock을 걸고 Transaction이 완료될때까지 유지
SELECT ... FOR UPDATE;
UPDATE ...
COMMIT;
- 낙관적 동시성 제어 (Optimistic Concurrency Control)
- 같은 데이터를 동시에 수정하지 않을 것이라고 가정
- 데이터 읽을 때 Lock을 걸지 않지만, 수정할때 변경되었는지 확인
SELECT ... INTO :a, :b, :c, :d ... ;
UPDATE ... WHERE col1 = :a AND col2 = :b ...;
- 같은 데이터를 동시에 수정할 것이라고 가정
- 먼저 Lock을 걸고 Transaction이 완료될때까지 유지
SELECT ... FOR UPDATE;
UPDATE ...
COMMIT;
- 같은 데이터를 동시에 수정하지 않을 것이라고 가정
- 데이터 읽을 때 Lock을 걸지 않지만, 수정할때 변경되었는지 확인
SELECT ... INTO :a, :b, :c, :d ... ;
UPDATE ... WHERE col1 = :a AND col2 = :b ...;
다중버전 동시성 제어 (Multiversion Concurrency Control)
- 데이터를 변경할 때마다 Undo 영역에 저장
- Query (또는 Transaction) 시작 시점 이후 변경된 값에 대해서는 Undo 영역에 저장된 정보를 이용해서 일관성 있는 버전(CR Copy)를 생성하여 읽음
- 문장수준 읽기 일관성 (Statement-Level Read Consistency)
- 단일 SQL 내에서의 일관성 유지
- Query 시작시점 이후 변경값에 대해서는 CR Copy 값을 읽음
- 트랜잭션 수준 읽기 인관성 (Transaction-Level Read Consistency)
- Transaction 시작시점 이후 변경값에 대해서는 CR Copy 값을 읽음
- Snapshot too old
- Undo 영역의 정보가 다른 Transaction에 의해 재사용됨으로 CR Copy를 생성할 수 없을 경우 발생
- 줄일 수 있는 방법
- Undo 영역 크기 증가
- Commit을 자주하지 않음
- fetch across commit 형태의 프로그램 작성을 피함
- Transation이 몰리는 시간대에 오래 걸리는 Query 수행을 피함
- 큰 Table을 일정 범위로 나우어 일고 단계적으로 실행하도록 코딩 (단 일관성 문제는 없어야 함)
- 오랜 시간에 걸쳐 같은 Block을 여러 번 방문하는 NL JOIN 또는 Index를 경유한 Table 액세스를 체크하고 회피할수 있는 방법 찾음 (JOIN 방법 변경, Full Table Scan)
- ORDER BY를 강제로 삽입하여 Sort연산을 강제로 발생
- 대량 UPDATE후 바로 해당 Table 또는 Index를 Full Scan하도록 Query 수행
- 단일 SQL 내에서의 일관성 유지
- Query 시작시점 이후 변경값에 대해서는 CR Copy 값을 읽음
- Transaction 시작시점 이후 변경값에 대해서는 CR Copy 값을 읽음
- Undo 영역의 정보가 다른 Transaction에 의해 재사용됨으로 CR Copy를 생성할 수 없을 경우 발생
- 줄일 수 있는 방법
- Undo 영역 크기 증가
- Commit을 자주하지 않음
- fetch across commit 형태의 프로그램 작성을 피함
- Transation이 몰리는 시간대에 오래 걸리는 Query 수행을 피함
- 큰 Table을 일정 범위로 나우어 일고 단계적으로 실행하도록 코딩 (단 일관성 문제는 없어야 함)
- 오랜 시간에 걸쳐 같은 Block을 여러 번 방문하는 NL JOIN 또는 Index를 경유한 Table 액세스를 체크하고 회피할수 있는 방법 찾음 (JOIN 방법 변경, Full Table Scan)
- ORDER BY를 강제로 삽입하여 Sort연산을 강제로 발생
- 대량 UPDATE후 바로 해당 Table 또는 Index를 Full Scan하도록 Query 수행
댓글 없음:
댓글 쓰기