Oracle DBA/SQL

083 락(LOCK) 이해하기

Nuez 2023. 7. 7. 14:49
반응형

LOCK

 

: 세션(유저)이 특정 데이터를 수정하게 되면 COMMIT하기 전까지 수정한 행(row)에 락을 검.

특정 세션이 변경한 데이터를 다른 세션이 변경하지 못하도록 막는 기능 

 

-> 만약 lock이 없다면 데이터의 일관성이 깨짐

예시) 위에서 아래로 시간 순서상 진행된다고 이해해보기

c##scott c##scott
A session B session
UPDATE emp
SET sal = 9000
WHERE ename = 'KING';		  
  UPDATE emp
SET sal = 0
WHERE ename = 'KING';

COMMIT;
SELECT ename, sal
FROM emp;		  

세션 A에서 마지막 쿼리문 수행할 때 0으로 나온다면? 데이터의 일관성이 깨져버림. 

∴ 해당 행에다 락(lock)을 걸어, 다른 행들이 수정하지 못하게 막음. 

실습

 

명령 프롬프트 창 2개 켜고 실습 

 

1. 둘다 접속 

sqlplus c##scott/tiger

2. user 확인 

show user;

3. Session A창에 KING의 월급 업데이트

UPDATE emp
SET sal = 9000
WHERE ename = 'KING';

4. Session B창에 KING의 월급 업데이트 시도

아무 변동 없이 커서만 깜빡이고 있음. ( KING의 행에 락이 걸려있기 때문)

5. Session A창 커밋이나 롤백하기

세션 A에서 커밋되는 순간 세션 B의 락이 풀리면서 업데이트 진행됨.

6. Session A에서 한번 더 업데이트 

( B롤백해놓고 시작하기)

7. Session B에서 한번 더 업데이트 시도 

다른 컬럼을 대상으로 업데이트 함에도 락이 걸려서 아무 반응 없이 대기 중 

왜냐하면 락은  행단위로 걸리기 때문!! 

 

* 업데이트 하는 컬럼이 달라도, 행이 같으면 커밋이나 롤백 전까지 락 걸려서 대기해야 함. 

 

Lock holder와 waiter 찾는 쿼리문

select decode(status,'INACTIVE',username || ' ' || sid || ',' || serial#,'lock') as Holder,
       decode(status,'ACTIVE',  username || ' ' || sid || ',' || serial#,'lock') as waiter, sid, serial#, status
    from( select level as le, NVL(s.username,'(oracle)') AS username,
    s.osuser,   
    s.sid,
    s.serial#,
    s.lockwait,
    s.module,
    s.machine,
    s.status,
    s.program,
    to_char(s.logon_TIME, 'DD-MON-YYYY HH24:MI:SS') as logon_time
       from v$session s
      where level>1
                or EXISTS( select 1
    from v$session
    where blocking_session = s.sid)
      CONNECT by PRIOR s.sid = s.blocking_session
  START WITH s.blocking_session is null);

LOCK HOLDER, WAITER에 대한 정보 확인 가능 (SID, SERIAL#)

락 홀더의 세션을 KILL하시오.

ALTER SYSTEM KILL SESSION '857,63293';

kill되면서 락때문에 대기하고 있던 세션B(오른쪽 이미지)의 update 수행됨.

동시에 세션A(락 홀더)가 조회를 해보면 세션이 종료됐다는 알림과 함께 롤백됨. 

테이블의 데이터를 update하면 lock이 행 단위로 걸림.

db에 데이터 수정 작업을 했으면 꼭 commit을 해서 마무리 해야함.

안 그러면 다른 세션들이 lock에 걸리게 됨.

문제 479. 아래와 같이 c##scott세션을 2개 열고 update를 수행하시오.

c##scott c##scott
A session B session
UPDATE emp
SET sal = 9000
WHERE job = 'SALESMAN';		  
  UPDATE emp
SET sal = 7000
WHERE ename = 'MARTIN';OMMIT;

오른쪽 B세션 대기중

B 세션 대기중

이유: A 세션에서 업데이트한 4개의 행에 lock이 걸려있는데 MARTIN도 그 중 하나이기 때문.

(MARTIN이 SALESMAN이기도 함.)

문제 480. 이번에는 holder를 죽이지 말고, waiter를 kill 하시오. 

ALTER SYSTEM KILL SESSION '981,41115';

WAITER가 KILL되면서 세션 종료 메세지 출력

문제 481. emp 테이블 전체에 LOCK을 겁니다. 

LOCK TABLE emp IN EXCLUSIVE MODE;

session A에서 테이블 전체에 대해 잠금을 걸었기 때문에, session B에서는 emp 테이블에 대한 어떤 update를 시도해도 실행X

 

session A에서 롤백하는 순간 락이 풀리면서, session B 업데이트 수행

 

문제 482. c##scott 유저로 3개의 세션을 열고, 다음과 같이 업데이트하시오. 

c##scott c##scott c##scott
A session B session C session
update emp
set sal = 8700
where ename = 'ALLEN';		    
  update emp
set sal = 0
where ename = 'ALLEN';		  
    update emp
set deptno = 70
where ename = 'ALLEN';

A는 업데이트 중 

QUEUE : B,C 순으로 대기 중 

A COMMIT

B deQueue : 큐에서 빠져나가면서 업데이트 (선입선출이라 먼저 업데이트하면서 큐에서 나감)

C inQueue : 아직 큐에서 대기 중