song-ps

LOCK 260110

13 min read

01. Lock 경합과 교착상태

Lock 경합(=블로킹)은 최소화해야 하지만, Lock 자체는 자연스러운 현상이다.
다만 Lock 점유 시간이 길어지면 동시성이 급격히 나빠지므로, 튜닝 대상인지 점검할 필요가 있다.

교착상태(Deadlock)가 발생하면 DBMS가 둘 중 한 세션에 에러를 발생시켜 교착을 해소한다.
SQL 튜닝을 통해 I/O를 줄여 수행 시간을 단축하면, 경합과 교착상태 발생 가능성도 함께 줄어든다.

정답: 04, 05

02. (나중에 변경할 목적으로) 데이터를 읽을 때: SELECT … FOR UPDATE

한 트랜잭션 내에서 “나중에 변경할 목적”으로 데이터를 읽을 때는 SELECT … FOR UPDATE를 사용해야 한다.

DECLARE l_sal NUMBER; BEGIN SELECT sal INTO l_sal FROM emp WHERE empno = 7900 FOR UPDATE; /* DO ANYTHING */ UPDATE emp SET sal = l_sal + 100 WHERE empno = 7900; COMMIT; END; /

정답: 188

03. Lock 레벨, Lock Escalation

DBMS는 Lock을 다양한 레벨(페이지/익스텐트/테이블/로우 등)로 관리한다.

  • 로우 레벨 Lock을 많이 사용할수록 동시성은 좋아지지만, Lock 개수가 많아져 Lock 관리 비용(메모리/오버헤드)이 커진다.

  • 높은 레벨 Lock(테이블 등) 은 하나의 Lock으로 많은 레코드를 잠글 수 있어 관리 비용은 줄지만, 동시성은 나빠진다.

Lock Escalation은 “Lock 관리 자원이 부족해질 때 로우 Lock이 테이블 Lock으로 확장되는 현상”인데,
오라클은 일반적으로 Lock Escalation을 제공하지 않는다.

04. DML Lock은 배타적이며, 컬럼이 달라도 경합한다

DML 수행 시 DBMS는 기본적으로 배타적(Exclusive) Lock을 사용한다.
배타적 Lock끼리는 호환되지 않으므로 서로 블로킹한다.

따라서 서로 다른 컬럼을 UPDATE하더라도 같은 행을 갱신하려 하면 Lock 경합을 피할 수 없다.
(행 단위 Lock을 공유하는 게 아니라, 갱신 대상 행에 대한 배타적 제어가 걸리기 때문)

정답: 0

05. Lock 호환성과 MVCC (Oracle vs SQL Server)

  • 배타적 Lock ↔ 배타적 Lock: 호환되지 않음(블로킹 발생)

  • 공유 Lock ↔ 공유 Lock: 호환됨(보통 블로킹 없음)

  • 공유 Lock ↔ 배타적 Lock: 호환되지 않음(블로킹)

SQL Server는 기본적으로 SELECT에서 공유 Lock을 사용할 수 있어,
갱신(배타적 Lock)과 읽기(공유 Lock)가 충돌하면 블로킹이 발생할 수 있다.

반면 오라클은 MVCC(다중 버전 동시성 제어) 모델을 사용하므로,
일반적인 SELECT는 DML과 서로 블로킹하지 않고 읽을 수 있다(Dirty Read는 허용하지 않음).

SQL Server에서 Dirty Read를 하려면 NOLOCK 힌트 또는 격리수준을 READ UNCOMMITTED로 바꿔야 한다.
오라클은 기본적으로 Dirty Read를 제공하지 않는다.

06. SQL Server 자동 커밋 주의

SQL Server는 기본 설정이 Auto Commit이므로,
INSERT/UPDATE/DELETE 테스트 시 트랜잭션 단위를 명확히 하려면 보통:

  • BEGIN TRANSACTION / COMMIT 을 명시적으로 사용하거나

  • 세션 설정을 조정한다.

(원문에 나온 “DML 전에 begin transaction을 매번…” 취지)

13. Oracle의 TM Lock(테이블 Lock)과 TX Lock(로우 Lock)

오라클에서 데이터를 변경(DML)하려면 로우 Lock(TX) 뿐 아니라 테이블 Lock(TM) 도 함께 사용된다.

  • TM Lock은 “이 테이블에서 어떤 작업을 수행 중인지 알리는 일종의 플래그” 성격이 있으며,

  • TM Lock이 걸렸다고 해서 “항상 데이터 진입이 막히는 것”은 아니다.
    (모드에 따라 호환/비호환이 다름)

예를 들어 LOCK TABLE … IN ROW EXCLUSIVE MODE 같은 명령으로 TM Lock 모드를 확인할 수 있다.

LOCK TABLE emp IN ROW EXCLUSIVE MODE;

반면 EXCLUSIVE 모드 TM Lock은 강력해서 다른 세션의 접근을 광범위하게 막는다.

LOCK TABLE emp IN EXCLUSIVE MODE;

정답: 14

14. TX Lock / TM Lock의 개수(상황 예시)

오라클에서:

  • TX Lock은 트랜잭션별로 1개

  • TM Lock은 DML 대상 테이블별로 1개

따라서 “트랜잭션 2개가 같은 테이블을 DML” 하는 상황이면,
TX Lock 2개 + TM Lock 2개가 잡히는 형태가 된다.

정답: 15

15. PK 제약과 교착상태

(원문 취지 복원)
PK/Unique 제약이 걸려 있는 경우, 특정 동시 DML 패턴에서 교착상태가 구조적으로 발생하지 않거나
혹은 다른 방식(에러/대기/실패)으로 처리되어 “교착”으로 이어지지 않는 경우가 있다.

트랜잭션/격리수준/이상현상 파트

16. Dirty Read, Non-Repeatable Read, Phantom Read

동시 트랜잭션 환경에서 동시성 제어를 제대로 하지 않으면 다음 이상 현상이 발생할 수 있다.

  • Dirty Read
    다른 트랜잭션이 변경했지만 아직 커밋하지 않은 데이터를 읽는 현상
    (나중에 롤백되면 읽은 내용이 ‘없던 일’이 되어 비일관성)

  • Non-Repeatable Read
    한 트랜잭션에서 같은 데이터를 두 번 읽었는데,
    중간에 다른 트랜잭션이 값을 수정/삭제해 두 번의 결과가 달라지는 현상

  • Phantom Read
    한 트랜잭션에서 어떤 조건으로 집합을 두 번 조회했는데,
    중간에 다른 트랜잭션이 새로운 행을 INSERT/DELETE 해서
    두 번째 조회에서 “없던 행이 생기거나(phantom) 있던 행이 사라지는” 현상

정답: 193

17. 트랜잭션의 ACID

  • Atomicity(원자성): 전부 수행되거나 전혀 수행되지 않아야 함

  • Consistency(일관성): 트랜잭션 전후로 DB는 모순 없는 상태여야 함

  • Isolation(격리성): 트랜잭션 중간 결과를 다른 트랜잭션이 함부로 볼 수 없어야 함

  • Durability(지속성): 커밋된 결과는 영구히 보존되어야 함

18~21. 격리수준별 허용/차단되는 이상현상

격리수준별로 허용되는 비일관성 현상은 아래처럼 정리된다(원문 표 복구):

Isolation LevelDirty ReadNon-Repeatable ReadPhantom Read
Read Uncommitted가능가능가능
Read Committed불가가능가능
Repeatable Read불가불가가능
Serializable불가불가불가

  • 대부분 DBMS의 기본 격리수준은 Read Committed 이다. (정답: 20)

  • Read Committed에서는 읽기 시점/락 정책 때문에 Non-Repeatable/Phantom이 가능할 수 있다.

  • Serializable은 가장 높은 격리수준이라 비일관성 현상이 나타나지 않는다. (정답: 19)

26~29. “UPDATE 대상 식별 시점” (Oracle MVCC vs 비-MVCC)

두 트랜잭션이 같은 행을 갱신하려 하면 후행 트랜잭션은 선행 트랜잭션의 Lock 해제를 기다리며 블로킹된다.
그리고 경신 대상을 언제/어떻게 식별하느냐는 DBMS마다 다르다.

Oracle(MVCC)

  • UPDATE 문이 시작된 시점(문장 시작 시점)을 기준으로 갱신 대상 집합을 식별하는 성격이 강하다.

  • 기다렸다가 갱신하려는 순간, 조건절 값이 바뀌어 “더 이상 대상이 아니게 됨”을 발견하면
    문장을 재시도하거나(재실행) 결과적으로 갱신이 실패할 수 있다.

비-MVCC 계열(SQL Server 예시로 언급)

  • UPDATE가 실제로 행에 도달해서 갱신하는 시점의 판단/락 정책에 따라,
    “기다렸다가 조건을 확인하고 정상 처리” 같은 흐름이 나타날 수 있다.

(네 텍스트의 예시는 C1 값이 1→2→4로 바뀌는 시나리오를 통해
Oracle에서는 “대상 식별 시점 차이 때문에 최종값이 4가 된다”는 결론으로 이어짐)

정답: 196

Lost Update 예시(3100/3200/4000 케이스)

두 트랜잭션이 같은 값을 기반으로 갱신하면 Lost Update(갱신 분실) 이 발생할 수 있는데,
DBMS의 갱신 대상 식별/락/재시도 정책에 따라:

  • 어떤 DBMS에서는 후행 UPDATE가 실패하거나,

  • 어떤 경우에는 후행 UPDATE가 성공해서 선행 결과를 덮어써 최종값이 달라질 수 있다.

(원문은 “결과가 3100인 경우/4000이 되는 경우” 등으로 비교하며,
Oracle은 문장 시작 시점 기준 + 재시도 성격 때문에 “선행 갱신이 살아남는” 방향의 설명을 하고 있음)

정답: 197

30. 일반 Lock 모델에서 일관성과 동시성의 트레이드오프

일반적인 Lock 기반 모델에서는 격리성을 높이면(락을 강하게/오래 잡으면) 일관성은 좋아지지만 동시성은 나빠진다.
MVCC 계열은 읽기-쓰기 충돌을 줄여 동시성을 개선하지만, 격리수준/모델에 따라 여전히 트레이드오프가 있다.

31. 비관적 동시성 제어 vs 낙관적 동시성 제어

  • 비관적(Pessimistic): 충돌 가능성이 높다고 보고 처음부터 Lock을 잡는다.

  • 낙관적(Optimistic): 충돌 가능성이 낮다고 보고 일단 Lock 없이 진행하되,
    나중에 업데이트/커밋 시점에 변경 여부를 반드시 검증한다.

“아무 동시성 제어도 하지 않으면” 중간 갱신이 덮여 Lost Update가 발생할 수 있다.

정답: 32

32. WAIT는 SELECT FOR UPDATE에서만

WAIT 또는 NOWAIT 같은 옵션은 기본적으로 SELECT … FOR UPDATE 문에서 사용한다.

정답: 198

Graph View

Backlinks