[ORACLE] - 여러 Transaction들로 LOCK 충돌 시나리오 확인 (Consistency, Isolation 확인)

시나리오

시간	Transaction 1	Transaction 2	Transaction 3	비고
09:00	SELECT last_name, salary FROM employees WHERE employee_id=200;			HARD PARSING (DISK IO)
09:01	UPDATE employees SET salary = 8000 WHERE employee_id=200;			LOCK(TM/TX) 걸림
09:02		SELECT last_name, salary FROM employees WHERE employee_id=200;		SOFT PARSING (MEM IO) UNDO 데이터에서 가져옴
09:03		UPDATE employees SET salary = 8000 WHERE employee_id=200;		LOCK(TM/TX) 걸림 TX LOCK 충돌 WAIT ENQUEUE에 들어감
09:04			SELECT last_name, salary FROM employees WHERE employee_id=200;	SOFT PARSING (MEM IO) UNDO 데이터에서 가져옴
09:05			UPDATE employees SET salary = 8000 WHERE employee_id=200;	LOCK(TM/TX) 걸림 TX LOCK 충돌 WAIT ENQUEUE에 들어감
09:06	ROLLBACK			T2가 LOCK 잡음
09:07		ROLLBACK		T3가 LOCK 잡음
09:08			ROLLBACK	모든 트랜잭션 종료

 

위의 시나리오에서 
UNDO 데이터에서 가져와 모두 4400의 데이터를 보여주는 것을 확인하는 것으로
ACID의 CONSISTENCY 특성을 확인할 수 있다.

또한 LOCK으로 각자의 트랜잭션에서 수행하는 것을 외부에서 관여할 수 없는 것을 확인함으로
ACID의 ISOLATION 특성을 확인할 수 있다.

 

실행

1. 기존 트랜잭션과 세션, 락이 없는지 확인
   • sys 유저로 실행

   select * from v$transaction;
   select * from v$session where username='AWSUSER';
   

2. cmd로 awsuser 접근→ 3개의 개별 창으로 → 각 창이 T1, T2, T3
   • 창 하나 sqlplus 접속 할 때마다 세션 정보 확인하여 SID, SERIAL 번호 확인

   sqlplus awsuser/oracle
   

   T1 : SID 494, SERIAL 19932
   T2 : SID 618, SERIAL 34558
   T3 : SID 252 SERIAL 22701
3. 위의 시나리오 중 T1의 SELECT 실행
   • 
4. 기존 LOCK 없는 거 확인

   • select * from v$lock where sid in (494,618,252) and type <> 'AE';

5. T1의 Update 실행
   • LOCK(TM, TX) 걸렸는 지 확인
   • TM : table level lock
     • table drop, alter 등 막는다.
   • TX : row level lock
     • row 변경 막는다.

   • 

     
6. T2 SELECT 진행
   • 8000이 아닌 4400이 보인다.
     • UNDO data에서 가져오기 때문에
       

     • 

7. T2 UPDATE 진행
   • 깜빡 거리며 WAIT
   • LOCK 충돌
   • ENQUEUE에 들어감
   • LOCK 확인해보면 T2도 LOCK 생성한 것을 확인할 수 있다.

   • 

     
8. 위와 동일하게 T3도 SELECT, UPDATE 진행
   • T2와 마찬가지로 4400 출력
   • WAIT
   • ENQUEUE에 들어감
   • LOCK 생성 확인

   

   

9. ENQUEUE에 들어가 있는 T2, T3의 UPDATE 절 확인

   • 

10. T1에서 ROLLBACK
    • T1 트랜잭션이 종료된 후 T2로 LOCK이 넘어온다.
    • T2에서 바로 업데이트
    • enqueue 확인해보면 T3 남아있다.

    • 

      

11. T2에서 ROLLBACK
    • T2 트랜잭션 종료된 후 T3으로 LOCK이 넘어온다.
    • T3에서 바로 업데이트
12. 남아있는 트랜잭션과 lock, queue확인
    • T3가 마저 lock을 잡았기에 queue는 비어있다.

      select * from v$enqueue_lock where sid in (494,618,252) and type <> 'AE';​

    • LOCK은 T3가 생성한 LOCK 확인가능하다.

      select * from v$lock where sid in (494,618,252) and type <> 'AE';​

      

     
13. T3도 ROLLBACK 진행
14. 트랜잭션, 락, 큐 모두 비어있는 것 확인