동시성 제어와 잠금
두 세션이 같은 행을 고치려 할 때 생기는 행 잠금 대기를 재현하고, V$SESSION의 BLOCKING_SESSION과 EVENT로 누가 누구를 막는지 확인한 실습
#핵심 요약
한 세션이 행을 고치고 커밋하지 않으면 그 행에 잠금이 걸림
다른 세션이 같은 행을 고치려 하면 명령이 끝나지 않고 기다림
기다리는 세션은 V$SESSION.EVENT가 enq: TX - row lock contention이고, BLOCKING_SESSION에 막고 있는 세션의 SID가 나옴
실습에서는 세션을 살아 있는 논리적 개체로 보여 주는 대표 상황으로 이 잠금 대기를 들었음
#개념과 동작 원리
- 세션 A:
UPDATE로 행을 고치고 커밋하지 않음
이 순간 A가 그 행을 잠금 - 세션 B: 같은 행을
UPDATE하면 프롬프트가 돌아오지 않고 기다림 - 세션 A가 커밋하거나 롤백했을 때 B가 진행되는 단계는 수업 자료에 기록이 없음
- 관리자는 막고 있는 세션을 확인해 필요하면 강제로 끊음
TX 행 잠금과 TM 테이블 잠금은 목적이 다르며 동일 테이블에 함께 존재할 수 있음
잠금 경합 분석은 성능 분야(잠금과 공간 경합, P164)에서 다룸
Lock은 데이터 무결성을 지키려고 Oracle이 둔 안전장치임
Oracle의 Lock은 escalation되지 않아 행 수준 Lock이 테이블 수준 Lock으로 자동으로 올라가지 않음
Lock 대기가 자주 나면 UPDATE가 오래 걸리는 것이 악성 SQL 때문이 아니라 커밋 누락 때문일 수 있으므로, DBA는 개발자에게 프로그램의 COMMIT 누락을 점검하도록 권함
#비관적 동시성 제어와 낙관적 동시성 제어
동시성 제어는 여러 사용자가 같은 데이터를 동시에 다룰 때 정합성을 지키면서 많은 작업을 동시에 처리하게 하는 장치임
정합성만 좇아 잠금을 과하게 쓰면 대기가 길어져 서비스가 느려지므로, 핵심은 정합성과 성능의 균형임
갱신 손실(Lost Update) 예: 두 직원이 동시에 고객 포인트 100점을 조회하고, 한 명은 50점을 더해 150점, 다른 한 명은 30점을 더해 130점으로 저장함
나중에 저장한 130점이 먼저 저장한 150점을 덮어써 첫 번째 적립이 사라짐
| 구분 | 비관적 동시성 제어 | 낙관적 동시성 제어 |
|---|---|---|
| 가정 | 동시에 수정할 가능성이 높음 | 동시에 수정할 가능성이 낮음 |
| 핵심 행동 | 읽는 순간 잠금을 걸어 다른 세션의 접근을 막음 | 잠금 없이 읽고, 수정할 때 읽은 뒤 값이 바뀌었는지 검증 |
| 장점 | 데이터 정확성 | 동시 처리량 |
| 어울리는 곳 | 금융, 재고처럼 오류가 치명적인 업무 | 웹 조회처럼 충돌이 드문 업무 |
| 예 | SELECT ... FOR UPDATE |
변경 일시 컬럼으로 읽은 시점과 같은지 확인하는 절차 추가 |
여러 인스턴스의 Holder, Waiter를 함께 추적하는 조회는 세션과 블로킹 진단에서 다룸
#주요 용어와 구성 요소
| 구문 | 의미 |
|---|---|
| TX | 충돌하는 행 변경을 직렬화하며 트랜잭션이 커밋, 롤백할 때까지 유지됨 |
| TM | DML과 충돌하는 테이블 DDL을 막는 잠금이며 서로 다른 행의 DML을 모두 막는다는 뜻은 아님 |
| 교착 상태 | 세션들이 서로의 자원을 기다리는 순환 대기임 |
#필수 명령어 또는 SQL
| 항목 | 실행 조건 |
|---|---|
| 작업 대상 | emp 동일 행의 A, B 변경과 C의 블로킹 조회, 홀더 종료 |
| 필요 권한 | A, B는 emp UPDATE 권한, C의 조회는 관리자 조회 권한, 세션 종료는 ALTER SYSTEM 권한 |
| 바꿀 입력값 | <USERNAME>, <SID>, <SERIAL#>과 연결 대상 컨테이너 |
| 결과 해석 | 홀더 종료 이후 B의 실행 결과와 최종 급여는 기재 없음 |
#잠금 대기 재현
UPDATE emp SET sal = sal + 100 WHERE empno = 7369;
-- 커밋하지 않음UPDATE emp SET sal = sal + 200 WHERE empno = 7369;
-- 프롬프트가 돌아오지 않고 대기결과 해석: 개별 실행 출력은 기재 없음
#누가 누구를 막는지 확인
SELECT sid, username, blocking_session, event
FROM v$session
WHERE username = '<USERNAME>';결과 해석:
실습 기록(예):
| SID | BLOCKING_SESSION | EVENT | 해석 |
|---|---|---|---|
| 142 | 비어 있음 | SQL*Net message from client | 세션 A 커밋하지 않고 다음 입력을 기다리는 중 |
| 168 | 142 | enq: TX - row lock contention | 세션 B 142가 잡은 행 잠금을 기다리는 중 |
실습에서는 SQLcl에서 SET SQLFORMAT ANSICONSOLE 뒤 LOCKS <사용자> 명령으로도 확인했음
결과에 잠금을 잡은 세션은 TRANSACTION ROW-LEVEL, DML LOCK, 모드 X로, 기다리는 세션은 같은 항목에 WAIT 표시로 나왔음
#막고 있는 세션 끊기
ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;결과 해석:
실습에서는 막고 있던 세션을 KILL SESSION으로 끊었음
끊긴 세션의 커밋하지 않은 변경은 롤백된다고 보이나 설명은 기재 없음
운영 환경에서 세션을 끊기 전에는 그 세션의 작업과 소유자를 먼저 확인해야 함
#구문과 옵션 설명
Oracle은 교착 상태를 감지하면 관련 문장 하나를 롤백하므로 전체 트랜잭션이 자동 취소됐다고 볼 수 없음
KILL SESSION은 진행 중인 트랜잭션을 롤백하고 세션 잠금을 해제하도록 처리함
SID와 SERIAL#을 함께 사용하고 RAC 원격 인스턴스 대상이면 인스턴스 식별도 필요함
#사용 조건과 주의사항
실습 ADM 관리 이론에는 동시성과 잠금 절이 없고, SQL 튜닝 쪽 "트랜잭션 동시성 제어" 실습에서는 분류 판단이 필요해 쓰지 않았음(이관 대기 기록)
V$SESSION 조회 권한이 필요함
비관적 잠금은 충돌하는 변경을 제한하며 일반 일관성 조회까지 모두 차단하는 뜻은 아님
세션 종료 뒤 정리와 롤백이 끝나는 시각, 업무 재시도 성공 여부는 각각 확인해야 함