트랜잭션과 SELECT FOR UPDATE
두 세션 실습으로 본 DML의 행 잠금과 COMMIT, ROLLBACK, 조회한 행을 미리 잠그는 SELECT FOR UPDATE와 쓸 수 없는 경우, 잠금 대기 때 DBA의 조치 순서
#핵심 요약
한 세션이 UPDATE하고 COMMIT하지 않으면, 다른 세션이 같은 행을 바꾸려 할 때 잠금(lock)에 걸려 기다림
다른 컬럼을 바꿔도 걸림
잠금은 COMMIT(또는 ROLLBACK)하면 풀림
실습에서는 "SELECT만 빼고 모두 잠금을 쓴다"고 설명했음INSERT 후 COMMIT하지 않은 테이블은 다른 세션이 DROP할 수 없었음
그냥 SELECT는 잠금에 걸리지 않지만 SELECT ... FOR UPDATE는 조회한 행을 잠금
Oracle은 autocommit이 꺼져 있고 PostgreSQL(psql)은 기본으로 켜져 있음
#개념과 동작 원리
설명: Oracle이 UPDATE 때 컬럼 하나가 아니라 행 전체를 잠그는 이유는, 필드 단위로 잠그면 잠금 관리 메타데이터가 폭발적으로 늘고 관리가 복잡해져 고장이 잦아지고 성능이 떨어지기 때문임
행 잠금은 같은 행을 두 세션이 동시에 변경하지 못하게 함
FOR UPDATE를 쓸 수 없는 경우:
| 경우 | 설명 |
|---|---|
GROUP BY, DISTINCT |
원래 행을 특정할 수 없음 |
UNION, INTERSECT, MINUS |
결과 집합이 여럿 |
집계 함수(SUM, COUNT 등) |
행이 아니라 계산값을 돌려줌 |
| 키 보존이 안 된 조인 뷰 | 잠글 대상이 모호 |
일반 조회의 읽기 일관성과 행 변경의 잠금은 별개임
두 세션 잠금 대기를 V$SESSION의 BLOCKING_SESSION으로 확인하는 실습은 동시성 제어와 잠금에 있음
집합 연산자와 FOR UPDATE를 함께 쓸 수 없다는 공식 문서 제약은 집합 연산에 정리했음
#주요 용어와 구성 요소
| 구문 | 의미 |
|---|---|
| TX 행 잠금 | 변경하거나 FOR UPDATE로 선택한 행에 유지되며 COMMIT, ROLLBACK 때 해제됨 |
| TM 테이블 잠금 | DML과 충돌하는 DDL을 막는 용도이며 행 잠금과 함께 존재할 수 있음 |
#필수 명령어 또는 SQL
| 항목 | 실행 조건 |
|---|---|
| 작업 대상 | A, B 세션의 emp 동일 행, 테이블 DML, DDL과 FOR UPDATE 잠금 |
| 필요 권한 | 대상 DML, SELECT 권한과 DROP TABLE 권한을 구분하며 READ 권한만으로 FOR UPDATE를 수행할 수 없음 |
| 바꿀 입력값 | <EMPNO>, <ENAME>, <SAL> 및 실제 잠글 조건 |
| 결과 해석 | 잠금 대기 기록은 본문 참조 추가 대기 옵션의 실행 출력은 기재 없음 |
#같은 행, 다른 컬럼
UPDATE emp SET sal = 9500 WHERE ename = 'KING';UPDATE emp SET deptno = 30 WHERE ename = 'KING';결과 해석: B 세션은 A 세션이 COMMIT이나 ROLLBACK할 때까지 기다림
COMMIT;결과 해석: 개별 실행 출력은 기재 없음
#INSERT 중인 테이블 DROP
INSERT INTO emp (empno, ename, sal) VALUES (<EMPNO>, '<ENAME>', <SAL>);DROP TABLE emp;결과 해석: A의 INSERT가 테이블에 잠금을 걸어 B는 DROP할 수 없음
실패하지 않으면 테이블이 지워지는 문장이므로 실습 테이블에서만 함
#SELECT FOR UPDATE
SELECT ename, sal, job
FROM emp
WHERE job = 'SALESMAN'
FOR UPDATE;UPDATE emp SET comm = 5700 WHERE ename = 'MARTIN';결과 해석: MARTIN은 SALESMAN이라 A가 잠근 행이므로 B는 기다림
#잠금 대기 때 DBA의 조치
- 누가 잠금을 먼저 잡은 홀더 세션인지 알아냄
- 홀더 세션의 터미널 이름을 알아내 연락함
- 연락이 안 되면 홀더 세션을 종료함
실습에서는 SQL Developer의 모니터링 화면으로 확인했음
세션 종료(ALTER SYSTEM KILL SESSION)는 그 세션의 작업을 ROLLBACK시키는 운영 영향이 큰 작업임
홀더 세션을 찾는 딕셔너리 조회는 동시성 제어와 잠금을 봄
#구문과 옵션 설명
| 구문 | 의미 |
|---|---|
| FOR UPDATE OF 열 | 조인에서 잠글 테이블을 지정하며 특정 열만 잠그는 기능은 아님 |
| NOWAIT / WAIT n | 행 잠금이 있으면 즉시 반환하거나 지정 초 동안 기다림 |
| SKIP LOCKED | 다른 트랜잭션이 잠근 행을 건너뛰며 대기열 소비에 쓰는 방식임 |
#사용 조건과 주의사항
대기 옵션을 생략하면 행 잠금을 얻을 때까지 기다림
클라이언트 자동 커밋 설정을 확인하고, 업무 단위의 변경 확정, 취소와 세션 강제 종료를 구분함