[SQLp]과목 3 - 2 Lock과 트랜잭션 동시성 제어

 

SQLp - 국가공인 SQL 전문가

INDEX

• 과목 1 - 데이터 모델링의 이해

  • 데이터 모델링의 이해

  • 데이터 모델과 성능

  ​

• 과목 2 - SQL 기본 및 활용

  • SQL 기본

  • SQL 활용

  • SQL 최적화 기본 원리

  ​

• 과목 3 - SQL 고급 활용 튜닝

  • 아키텍처 기반 튜닝 원리

  • Lock 과 트랜잭션 동시성 제어

  • 옵티마이저 원리

  • 인덱스와 조인

  • 고급 SQL 튜닝

---

 

3과목은 튜닝에 대한 이해와 실습 병행이 필요할 것 같아 주제별로 정리를 할 계획입니다.

 

 

과목 3 - SQL 고급 활용 및 튜닝

2 . Lock 과 트랜잭션 동시성 제어

이 단원을 통해 DBMS가 동시성을 제어하기 위해 내부적으로 어떻게 동작하는 지 알 수 있습니다.

• Lock

1. Lock 기본

   다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위한 직렬화 (serialization)를 가능하게 하는 매커니즘이 Lock 이다.
   

   • Lock의 기본 개념

     • 공유 Lock 과 배타적 Lock

       가장 기본이 되는 모드로써 공유 Lock 과 배타적 Lock 이 있다. 공유 Lock은 데이터를 읽고자 할 때 사용된다. 이는 공유 Lock 끼리만 호환되며 두개 이상의 공유 Lock을 동시에 설정할 수 있다. 배타적 Lock은 데이터를 변경하고자 할 때 사용되며, 트랜잭션이 완료될 때 까지 유지 된다. 즉, 배타적 Lock이 완료되기 전에 다른 트랜잭션은 이 자원에 접근할 수 없다.

     • 블로킹과 교착상태

       블로킹은 Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰선 상태이다. 블로킹 상태를 해소하는 방법은 커밋 혹은 롤백으로 인한 자원 반환이다. 이러한 블로킹을 최소화 하기 위한 방안으로 LOCK_TIMEOUT을 설정할 수 있다. 교착상태(Deadlock)는 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 하는 상황이다. 이를 방지하기 위해서는 테이블 접근 순서를 같게 처리하면 피할 수 있다.

     • Lock에 의한 성능 저하를 최소화하는 방안

       1) 트랜잭션을 가능한 짧게 정의

       2) 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계

       3) 블로킹 현상에 대비 (SQL Server 에서는 LOCK_TIMEOUT 설정, Oracle 에서는 nowait/wait 옵션 지정한 select for update 를 먼저 수행)

       4) 트랜잭션 격리성 수준의 불필요한 상향조정 지양

      

2. SQL Server Lock

   SQL Server의 공유 Lock은 다음 레코드가 읽히면 곧바로 해제된다. 이는 기본 트랜잭션 격리성 수준(Read Committed)에서만 해당된다. 만약 격리성 수준을 변경하지 않고 해당 기능을 사용하려면 테이블 힌트를 통해 holdlock을 지정하면 된다.

   ​x
   begin tran
   select 포인트, 방문횟수, 최근방문일시
   from 고객 with (holdlock)
   where 고객번호 =:cust_num
   ​
   -- 새로운 적립 포인트 계산
   ​
   update 고객 set 적립포인트 =: 적립포인트 where 고객번호 =:cust_num
   ​
   commit
   

    

   • 갱신 Lock

     잠재적인 교착 상태를 방지하기 위해 SQL Server는 갱신 Lock을 사용하기 위한 updlock 힌트가 있다. 갱신 Lock은 트랜잭션이 서로 배타적 Lock을 설정하기 위해 다른 트랜잭션의 공유 Lock이 해제되기만을 기다리는 교착상태를 방지하기 위해 사용된다.

     한 자원에 대한 갱신 Lock은 한 트랜잭션만 설정할 수 있다. 갱신 Lock 끼리는 호환되지 않지만 공유 Lock과는 호환된다.

      

   • 의도 Lock

     특정 로우에 락을 설정하면 상위 레벨 개체 (페이지, 익스텐트, 테이블)에 내부적으로 의도 Lock이 설정된다. 선행 트랜잭션이 어떤 작업을 수행 중인지 알리는 용도로 사용된다. 즉, 상위 레벨 개체에서 구조를 변경하거나 잠금을 수행할 때 하위 레벨 개체들이 어떤 작업을 수행하는지 (Lock이 설정되어 있는지) 일일이 검사하는 것은 비효율적이다. 해당 개체에 어떤 모드의 의도 Lock이 설정되어 있는지만 확인하고 작업을 결정할 수 있다.

      

   • 스키마 Lock

     테이블 스키마에 의존적인 작업을 수행할 때 사용된다.

     • Sch-S : SQL을 컴파일하면서 오브젝트 스키마를 참조할 때 발생하며, 읽는 스키마 정보가 수정되거나 삭제되지 못하도록 한다.

     • Sch-M : 테이블 구조를 면경하는 DDL 문을 수행할 때 발생하며, 수정중인 스키마 정보를 다른 세션이 참조하지 못하도록 한다.

   • Bulk Update Lock

     테이블 락의 일종으로 테이블에 데이터를 Bulk Copy 할 때 발생한다. 병렬 데이터 로딩을 허용하지만 일반적인 트랜잭션 작업은 허용하지 않는다.

      

   • Lock레벨과 Escalation

     • 로우레벨
     • 페이지 레벨 : 변경하려는 로우가 담긴 데이터 페이지나 인덱스 페이지에 Lock 을 설정하는 것을 말한다. 같은 페이지에 속한 로우는 변경 작업과 무관하더라도 모두 잠긴다.
     • 익스텐트 레벨
     • 테이블 레벨 : 테이블 전체와 관련 인덱스가 모두 잠긴다.
     • 데이터베이스 레벨 : 이는 보통 데이터베이스를 복구하거나 스키마를 변경할 때 일어난다.

     이 밖에도 인텍스 키에 로우레벨 Lock 을 거는 경우도 있다.

     Lock Escalation 이란 Lock 리소스가 정해진 임계치를 넘으면 락의 레벨이 점차 확장되는 것을 말한다. Locking 레벨이 낮을수록 동시성은 좋지만 관리해야할 Lock 개수가 증가하고, Locking 레벨이 높을수록 적은 양의 리소스를 사용하지만 동시성은 나빠진다.

      

3. Oracle Lock

   Oracle 은 아양한 락을 사용한다. 이 중에서 DML Lock은 다중 사용자에 의해 동시 액세스 되는 사용자 데이터 무결성을 보호해 준다. DML Lock 에는 로우 Lock과 테이블 Lock이 있다.

   • 로우Lock

     Oracle 의 로우 Lock은 항상 배타적이다. select ... for update 의 경우를 제외한다면 일반 조회 쿼리에서는 어떤 Lock도 설정되지 않는다. 즉 다른 DBMS 와는 달리 읽기 작업에서 공유 Lock을 사용하지 않는다. 공유 Lock을 사용하지 않고도 일관성을 유지할 수 있는 것은 Undo 테이터를 이용한 다중버전 동시성 제어 메커니즘을 사용하기 때문이다. 또한 레코드의 속성으로서 로우 Lock을 구현했기 때문에 아무리 많은 레코드를 갱신하더라도 Lock Escalation 은 발생하지 않는다.

      

   • 테이블 Lock

     한 트랜잭션이 로우 Lock을 얻는 순간, 해당 테이블에 대한 테이블 Lock 도 동시에 얻는다. 이로써 갱신중인 테이블에 호환되지 않는 DDL 오퍼레이션을 방지한다. 즉 테이블 구조를 변경하지 못 하도록 막는다. 테이블 Lock 종류로는 5가지가 있다.

     • Row Share (RS)
     • Row Exaclusive (RX)
     • Share(S)
     • Share Row Exclusive (SRX)
     • Exclusive (X)

     대표적으로, select ... for update 문을 수행할 때 RS 모드 테이블 Lock 을 얻고 insert, update, delete 문을 수행할 때 RX 모드 테이블 lock을 얻는다. DML 로우 Lock을 처음 얻는 순간 묵시적으로 테이블 Lock을 얻지만 Lock Table 명령어를 이용하여 명시적으로 테이블 Lock을 얻을 수도 있다.

     xxxxxxxxxx
     lock table emp in row share mode;
     --                모드 명
     

     RX는 RX간에 호환성이 있기 때문에 테이블 Lock 이 걸려 있어도 레코드를 추가하거나 갱신하는 것이 가능하다. Oracle 에서 테이블 Lock은 해당 트랜잭션이 어떤 작업을 수행 중인지를 알리는 푯말(Flag) 역할을 한다. 즉 어떤 테이블 Lock이 설정되어 있는지 확인하고, 테이블로의 진입 여부를 결정할 수 있다.

      

 

• 트랜잭션

1. 트랜잭션 특징 : ACID

   트랜잭션은 업무 처리를 위한 논리적인 작업 단위이다. 즉 데이터를 일관성있게 처리하기 위한 논리적으로 하나로 연결되어야 하는 단위로써 그 안을 구성하는 연산은 여러 개 일 수 있다.

   • 원자성
   • 일관성
   • 격리성
   • 영속성

2. 트랜잭션 격리성

   트랜잭션의 격리성은 일관성과 마찬가지로 Lock을 강하게 유지할수록 강화되고 Lock을 최소화할수록 약화된다.

   • 낮은 단계의 격리성 수준에서 발생할 수 있는 현상들

     1) Dirty Read

     다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것을 말한다.

     2) Non-Repeatable Read

     한 트랜잭션 내에서 같은 쿼리를 두 번 이상 수행했을 때, 그 사이에 값이 수정되어서 두 쿼리의 결과가 다르게 나타나는 현상이다.

     3) Phantom Read

     한 트랜잭션에서 같은 쿼리를 두 번 이상 수행했을 때 첫 번째 쿼리에서 없던 레코드가 나타나는 현상을 말한다.

      

   • 트랜잭션 격리성 수준

     1) Read Uncommitted

     트랜잭션에서 처리 중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다.

     2) Read Committed

     확정 된 (커밋된) 데이터만 읽기를 허용하여 Dirty Read를 방지한다.

     3) Repeatable Read

     첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지한다.

     4) Serializable Read

     첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌거나 새로운 레코드가 나오는 것을 방지한다.

     대부분의 DBMS는 Read Committed를 기본 트랜잭션 격리성 수준으로 채택하고 있으며, 그러므로 Non-Repeatable Read, Phantom Read 현상에 주의해야 한다. 다중 트랜잭션 환경에서 DBMS가 제공하는 기능을 이용하여 동시성을 제어하기 위해서는 Set Transaction 명령어를 수행하면 된다.

     xxxxxxxxxx
     set transaction isolation level read serializable;
     

     트랜잭션 격리성 수준은 Repeatable Read 나 Serializable Read로 올리고 Locking 메커니즘을 통해 해결할 수 있다. 그러나 대량의 데이터를 처리할 때 이러한 방법은 동시성에 문제가 된다. 이를 해결하기 위해 다중버전 동시성 제어를 채택하는데, 변경 이전의 데이터를 읽음으로써 공유 Lock을 설정하지 않아 간섭현상을 일으키지 않고 변경이 아직 확정되지 않은 값을 읽으려는 것도 아니기 때문이다.
     

• 동시성 제어

1. 비관적 동시성 & 낙관적 동시성

   다수의 사용자가 사용하는 DBMS 는 다중 트랜잭션의 상호 간섭 작용에서 데이터베이스를 보호해야 한다. 먼저, 동시성을 제어하기 위한 방법으로는 Lock 기능이 있다. 내부적으로 자원이 하나씩 액세스 되도록 트랜잭션을 직렬화 하는 것이다. 또한 set transaction 명령어를 통해 트랜잭션 격리성 수준을 조정하는 방법도 있다. 그러나 동시성과 일관성은 트레이드 오프 관계에 있다. 즉, 동시성을 높이기 위해 Lock의 사용을 최소화하면, 일관성을 유지하기 어려우며 일관성을 높인다면 동시성이 저하된다. 따라서 동시성 제어의 목표는 한번에 처리되는 트랜잭션 수는 최대화 하면서 데이터 무결성을 유지하는데에 있다.

   • 비관적 동시성 제어

     • 사용자들이 같은 데이터를 동시에 수정할 것 이라고 가정

     • for update

     • 시스템 동시성을 떨어뜨릴 수 있음 (그래서 'nowait, wait'와 함께 사용)

     • SQL Server 에서는 'holdlock, updlock' 힌트 사용

        

   • 낙관적 동시성 제어

     • 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정

     • 수정 시점에 값이 변경되었는지 검사 필수

        

   
   

    

2. 다중버전 동시성 제어

   • 일반적인 Locking 메커니즘의 문제점

     일반적인 Locking 메커니즘으로는 동시성 제어의 목표(동시실행 트랜잭션 수 최대화, 데이터 무결성 유지)를 원할히 수행하기에 어려움이 있다. 일반적으로 읽기 작업과 쓰기 작업이 서로 방해를 일으켜 동시성에 문제를 일으키기 때문이다. 비일관성 읽기 문제가 발생하는 것을 해결하기 위해 트랜잭션 격리성 수준을 상향 조정한다면 일관성은 높아지지만 동시성을 저하시킨다.

   • 다중버전 동시성 제어 - MVCC

     동시성과 일관성을 동시에 높이기 위해 데이터를 변경할 때마다 Undo 영역에 변경 사항을 저장한다. 데이터를 읽다가 변경된 값을 발견한다면 Undo 영역의 정보를 이용하여 시작 시점의 일관성 있는 버전(CR Copy)을 생성하고 이를 읽는다. 이 방법은 동시성 측면에서 매우 유리하며 데이터의 기준 시점이 고정되기 때문에 일관성 측면에서도 유리하다.

     그러나, 이 방법은 Undo 블록 I/O, CR Copy 생성, CR 블록 캐싱 같은 부가적인 작업으로 오버헤드가 생긴다는 단점이 있다.

     MVCC를 이용한 읽기 일관성에는 문장수준 읽기 일관성과 트랜잭션 수준 읽기 일관성이 있다.

   • 문장수준 읽기 일관성

     일관성 기준 시점이 쿼리의 시작지점이다. 이를 통해 다른 트랜잭션에 의한 데이터의 변경이 발생해도 단일 SQL 문 내에서 일관성 있게 값을 읽을 수 있다.

   • 트랜잭션 수준 읽기 일관성

     일관성 기준 시점이 트랜잭션의 시작 지점이다. 트랜잭션 수준으로 완벽한 읽기 일관성을 보장하기 위해서는 격리성 수준을 Serializable Read 로 올려야 한다.

   • Snapshot too old

     Undo 데이터를 활용함으로써 Snapshot too old 에러가 발생한다. 이는 Undo 정보가 다른 트랜잭션에 의해 재사용되어 필요한 CR Copy를 생성할 수 없을 때 발생한다. 또한 블록 클린아웃에 실패했을 때에도 발생하는 에러이다.

     Snapshot too old 에러를 줄이는 방법

     1) Undo 영역 크기 증가

     2) 불필요한 커밋 줄이기

     3) fetch across commit 형태의 프로그램 작성 회피

     4) 시간 조정 (시간이 오래 걸리는 쿼리 간)

     5) 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행 유도

     6) Nested Loop 형태 회피

     7) 소트 연산 발생을 통해 간격 주기

     8) 대량 업데이트 후에 곧 바로 Full Scan 하도록 쿼리 수행

    

 

• 정리