SQLP 3-2 Lock과 Transaction 동시성 제어

in Devlopment on SQLP


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과 호환이 안됨

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걸린 리소스를 액세스하려 할 경우 영원히 대기상태에 빠지는 것
    • 유일한 해결 방법은 둘 중 한 세션에 에러를 발생시키는 것
    • 테이블 접근 순서를 같게 처리하면 피할 수 있음

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;
...

2.1 Transaction

Transaction 특징

  1. 원자성 (Atomicity) : 업무상 최소단위
  2. 일관성 (Consistency) : 실행 전후 데이터베이스 상태가 모순되지 않아야 함
  3. 격리성 (Isolation) : 실행 중 다른 Transaction이 접근할 수 없음
  4. 영속성 (Durability) : 성공적으로 수행하면, 데이터베이스에 영속적으로 저장

낮은 Isolation Level에서의 현상

  1. Dirty Read
    • Commit하지 않은 데이터를 읽음. 이미 읽은 뒤 다시 Rollback 될 수 있음.
  2. Non-Repeatable Read
    • 한 Transaction 내의 같은 Query에 대해서 값이 바뀌는 현상
    • 처음 값을 읽은 뒤 다른 Transaction이 해당 값을 변경한 경우
  3. Phantom Read
    • 한 Transaction 내에 같은 Query에 대해서 처음에 없었던 값이 읽히는 현상
    • 처음 값을 읽은 뒤 다른 Transaction이 새로운 값을 입력한 경우

Transaction Isolation Level

  1. Read Uncommitted : 다른 Transaction이 처리 중인 값을 읽는 것을 허용 (이걸 허용하는 DB는 거의 없음)
  2. Read Committed : Dirty Read 방지
  3. Repeatable Read : Non-Repeatable Read 방지
  4. Serializable Read : Phantom Read 방지
    SET TRANSACTION ISOLATION LEVEL READ SERIALIZABLE;
    • Transaction Isolation Level을 높일수록 Lock에 의존적이다보니 동시성이 저하된다.
    • Oracle에서는 다중버전 동시성 제어(Multiversion Concurrency Control)을 사용한다.

동시성 제어 (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 ...;

      다중버전 동시성 제어 (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 수행

관련 내용 Slide


이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :)