[SQL]LOCK이란?

 

 

 

 

 

 

 

 

 

 

- 정의 : 데이터의 일관성을 보장하기 위한 방법 (= 잠금 / 데이터를 동시에 사용하게 해주는 기능)
- 유형

• Shared Lock(= 공유 Lock / Read Lock) : 다른 사용자가 같이 읽는 것을 허용하지만 변경하는 것은 허용하지 않음

  • Select

• Exclusive Lock(= 배타적 Lock / Write Lock) : 다른 사용자가 읽는 것, 변경하는 것 모두를 허용하지 않음

  • Insert / Update / Delete

- Lock 단위

• RID : 하나의 행

• KEY : 인덱스가 있을 때 행

• PAGE : 8KB 데이터 페이지 또는 인덱스 페이지

• EXTENT : 인접한 8개의 데이터 페이지 또는 인덱스

• TABLE : 데이터와 인덱스가 포함된 전체 테이블

• DB : 데이터베이스

- Lock 종류

• SHARED(S)

  • 트랜잭션이 데이터를 읽기 동안에만 잠금

  • 공유잠금(S) 를 사용시, 여러 트랜잭션이 동시에 하나의 개체를 읽을 수 있다. 즉, 공유 잠금끼리는 서로 충돌되지 않는다

  • 공유잠금(S) 를 사용시, 다른 트랜잭션이 데이터를 변경할 수 없다.

  • 다음 행을 읽을 때, 현재 행의 공유 잠금을 해제한다.

    • 예외 사항

      • 트랜잭션 고립수준 : REPEATABLE 레벨 이상 설정 시

      • LOCK HINT 써서, 해당 공유잠금을 트랜잭션 끝까지 유지

      • SELECT * FROM EMP(HOLDLOCK)

• EXCLUSIVE(X)

  • 트랜잭션이 데이터를 변경할 때 단독 잠금

  • 단독잠금이 걸린 데이터는 다른 트랜잭션이 공유 잠금, 단독 잠금 또한 걸 수 없다.

  • 오직 하나의 트랜잭션만이 데이터에 대한 단독 잠금을 걸 수 있다.

    • 예외 사항

      • 트랜잭션 격리 수준사용해서 단독 잠금 걸린 데이터를 다른 트랜잭션이 읽게 할 수 있다.

• UPDATE(U)

  • 트랜잭션이 변경할 데이터를 찾으면 거는 락

  • 나중에 데이터를 변경할 때 까지 데이터가 변경되지 않았음을 확신하기 위해 건다.

  • 한 번에 한 트랜잭션에만 업데이트 잠금을 얻을 수 있기 때문에 교착상태(deadLock)이 방지된다.

  • U 잠금이 걸린 데이터에는 다른 트랜잭션이 U잠금이나 X잠금을 걸 수 없지만, S잠금은 걸 수는 있다.

• INTENT(I)

  • 트랜잭션이 대상 데이터에 잠금을 걸 수 있을지 없을지를 신속히 판단할 수 있게 도와주는 잠금

  • ex ) 의도적 공유 잠금 (IS) 를 테이블 수준에서 설정 = 해당 트랜잭션이 해당 테이블의 페이지 또는 행에 대해 공유 잠금을 설정

  • 직접 테이블의 모든 행, 페이지 잠금을 확인할 필요가 없기 때문에 성능이 향상된다.

  • 종류

    • IX (= 의도적 배타저 잠금) : 잠금을 걸려는 트랜잭션이 각 리소스 계층 (테이블, 페이지, 행) 에 대해 X 잠금 설정하여 계층의 아래쪽 일부 리소스 수정

    • SIX (= 공유 및 의도적 배타적 잠금) : 잠금을 걸려는 트랜잭션이 각 리소스 계층에 대해 IX 잠금을 설정하여 계층의 아래쪽에 있는 모든 리소스에 대해서는 읽기 작업 / 일부 리소스는 수정 작업 / 최상위 수준 리소스에서는 동시 IS 잠금 허용

• SCHEMA (Sch)

  • Sch-M (Schema Manipulation)

    • DDL(스키마 변경) 문 실행 시, Schema 자체에 대해서 건다.

    • 모든 잠금에 대해 배타적, 어떤 작업도 허용하지 않음.

  • Sch-S (Schema Stability)

    • 쿼리문 컴파일 시 발생한다

    • S or X와 호환된다

    • 다른 트랜잭션 잠금을 차단하지 않 cl는다.

    • DDL 작업은 수행할 수 없다.

-  Lock 옵션 (WITH ~)

• 테이블 힌트

  • 쿼리문 작성 시 FROM 절에 오는 테이블 이름 다음에 WITH 구문으로 옵션 작성

  • 사용시 사용자가 원하는 형태로 강제 판단할 수 있음.

• 테이블 힌트 종류
  • NOLOCK ( = READUNCOMMITED) 
    • 배타적잠금 걸린 대상에 대해 공유잠금(S)를 설정할 수 없어 차단 발생 시,
    • 공유 잠금이 걸린 대상에 배타적잠금을 설정하는 경우 
      => 차단 현상이 발생하는 문제 발생 
    • 해결책 : SELECT 쿼리를 할 때 공유잠금을 설정하지 않고 데이터를 읽음 / 커밋되지 않은 결과를 미리 읽어오는 것
    • 예제 

 

• • ROWLOCK
    • 기본적인 행 단위 잠금을 사용하지만, 내부적 기준 따라 더 상위 단위(페이지, 테이블) 대상에 대해서 잠금 설정하려면 페이지 , 테이블 잠금을 자동적으로 승격하여 사용 
    • 해결책 : 사용자가 이를 통제하여 행단위잠금을 강제하기 위해서 사용하는 옵션
  • READPAST
    • 다른 트랜잭션이 잠근 행을 읽지 않음
  • XLOCK
    • 단독 잠금을 걸고 트랜잭션이 끝날 때 까지 유지
  • UPLOCK
    • SELECT 하는 대상에 대해 곧바로 배타적 잠금 설정 예정일 경우사용
    • SELECT 하는 대상에 다른 세션에서 배타적 잠금을 설정하지 못하도록 통제하기 위해서 사용 
    • 예시

1) SPID 51 : SELECT * FROM 테이블 WITH(UPDLOCK)
2) SPID 52 : UPDATE 테이블 SET...
3) SPID 51 : UPDATE 테이블 SET...


->  52번 업데이트 작업이 51번에서 설정된 UPLOCK 으로 인해 차단, 3번 UPDATE 작업은 계속하여 진행

 

 

- Lock 레벨

• 아래 레벨 외에 인덱스(key)에 로우 레벨 lock 거는 경우 있음

• locking level이 낮을 수록 동시성은 좋지만, 관리해야할 Lock 개수가 증가하기 때문에 더 많은 리소스 소비
• locking level이 높을 수록 적은 양의 lock 리소스를 사용하지만 하나의 lock 으로 수많은 레코드를 한꺼번에 locking 하기 때문에 동시성은 나빠짐
• Lock Level

로우 레벨	변경하려는 로우(실제로는 RID)에만 Lock을 설정하는 것
페이지 레벨	변경하려는 로우가 담긴 데이터 페이지(또는 인덱스 페이지)에 Lock을 설정하는 것 같은 페이지에 속한 로우는 진행 중인 변경 작업과 무관하더라도 모두 잠긴것과 같은 효과가 나타남.
익스텐트 레벨	익스텐트 전체가 잠김. SQL Server의 경우, 하나의 익스텐트가 여덟 개 페이지로 구성되므로 8개 페이지에 속한 모든 로우가 잠긴 것과 같은 효과가 나타남.
테이블 레벨	테이블 전체 그리고 관련 인덱스까지 모두 잠김.
데이터베이스 레벨	데이터베이스 전체가 잠긴다. 보통 데이터베이스를 복구하거나 스키마를 변경할 때 일어 남.

 

 

- Blocking 

• Lock들의 경합(Race condition) 이 발생하여 특정 세션이 작업을 진행하지 못하고 멈춰선 상태
  ->  먼저 Lock 을 설정한 트랜잭션을 기다려야하기 때문에, 이런 현상 반복시 빠른 서비스를 제공할 수 없다.
• 공유 Lock - 배타적 Lock / 배타적 Lock - 배타적 Lock 끼리 블로킹 발생할 수 있음
• 해결 방법 : Transaction commit 또는 Rollback
  1. SQL 문장에 가장 빠르게 실행되도록 리펙토링 하는 것
  2. 트랜잭션을 가능한 짧게 정의하면 경합 줄일 수 있음
  3. 동일한 데이터를 동시에 변경하는 작업을 하지 않도록 설계 
     / 트랜잭션이 활발한 주간에는 대용량 갱신 작업 수행하면 안됨
  4. 대용량 작업이 불가피할 경우
     작업 단위를 쪼개거나  / lock_timeout 설정하여 해당 lock 최대 시간 설정
     

set lock_timeout 3000

 

- DeadLock

• 트랜잭션간의 교착상태 
• 두 개의 트랜잭션 간에 각각의 트랜잭션이 가지고 있는 리소스 Lock 획득시 , 발생
• 해결책 : 한쪽 트랜잭션 처리를 강제 종료 (비용이 적은 트랜잭션의 처리 강제 종료)

1. 첫번째 deadlock 예시

• 1번 트랜잭션에서 2번 리소스의 잠금 획득 
• 2번 트랜잭션에서 1번 리소스 잠금 획득
  
  -> 이 때, 동시에 상대방 데이터를 액세스하려고 할 때, 기존의 Lock 이 해제될 때 까지 기다리는 상황

 

2. 두번째 deadlock 예시

• 1번 트랜잭션이 공유 Lock 설정하고 -> Sleep 
• 2번 트랜잭션이 배타적 Lock 설정하려고 할 때, 무기한 기다리게 되는 교착상태에 빠짐