- 정의 : 데이터의 일관성을 보장하기 위한 방법 (= 잠금 / 데이터를 동시에 사용하게 해주는 기능)
- 유형
-
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 쿼리를 할 때 공유잠금을 설정하지 않고 데이터를 읽음 / 커밋되지 않은 결과를 미리 읽어오는 것
- 예제
- NOLOCK ( = READUNCOMMITED)
-
- ROWLOCK
- 기본적인 행 단위 잠금을 사용하지만, 내부적 기준 따라 더 상위 단위(페이지, 테이블) 대상에 대해서 잠금 설정하려면 페이지 , 테이블 잠금을 자동적으로 승격하여 사용
- 해결책 : 사용자가 이를 통제하여 행단위잠금을 강제하기 위해서 사용하는 옵션
- READPAST
- 다른 트랜잭션이 잠근 행을 읽지 않음
- XLOCK
- 단독 잠금을 걸고 트랜잭션이 끝날 때 까지 유지
- UPLOCK
- SELECT 하는 대상에 대해 곧바로 배타적 잠금 설정 예정일 경우사용
- SELECT 하는 대상에 다른 세션에서 배타적 잠금을 설정하지 못하도록 통제하기 위해서 사용
- 예시
- ROWLOCK
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
- SQL 문장에 가장 빠르게 실행되도록 리펙토링 하는 것
- 트랜잭션을 가능한 짧게 정의하면 경합 줄일 수 있음
- 동일한 데이터를 동시에 변경하는 작업을 하지 않도록 설계
/ 트랜잭션이 활발한 주간에는 대용량 갱신 작업 수행하면 안됨 - 대용량 작업이 불가피할 경우
작업 단위를 쪼개거나 / lock_timeout 설정하여 해당 lock 최대 시간 설정
set lock_timeout 3000
- DeadLock
- 트랜잭션간의 교착상태
- 두 개의 트랜잭션 간에 각각의 트랜잭션이 가지고 있는 리소스 Lock 획득시 , 발생
- 해결책 : 한쪽 트랜잭션 처리를 강제 종료 (비용이 적은 트랜잭션의 처리 강제 종료)
1. 첫번째 deadlock 예시
- 1번 트랜잭션에서 2번 리소스의 잠금 획득
- 2번 트랜잭션에서 1번 리소스 잠금 획득
-> 이 때, 동시에 상대방 데이터를 액세스하려고 할 때, 기존의 Lock 이 해제될 때 까지 기다리는 상황
2. 두번째 deadlock 예시
- 1번 트랜잭션이 공유 Lock 설정하고 -> Sleep
- 2번 트랜잭션이 배타적 Lock 설정하려고 할 때, 무기한 기다리게 되는 교착상태에 빠짐
'Database Study > SQL' 카테고리의 다른 글
[RDB]데이터모델링 (2) | 2021.08.19 |
---|---|
[SQL]Join VS Union (0) | 2020.05.26 |
[MySQL] CASE, COALESCE, IFNULL NULL 처리 (1) | 2020.03.11 |
[MySQL]프로그래머스_입양 시각 구하기(2) (UNION/변수선언) (2) | 2020.03.10 |
[Oracle DB]Join 종류 (0) | 2020.01.06 |