[Oracle] 데이터의 LOCK 확인해보기

 

 

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

 

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

 

 

 

 

---

LOCK

  LOCK은 하나의 데이터를 여러 곳에서 동시에 조작하려 할 때 발생할 수 있는 혼란을 최소화하기 위한 요소로, 조작 중인 데이터를 다른 세션들이 조작할 수 없도록 접근을 보류시키는 것을 의미한다. 데이터에 LOCK이 걸린 경우  COMMIT 이나  ROLLBACK 을 실행해야만 해당 데이터의 LOCK이 풀린다.

 

   HANG  : 특정 세션에서 데이터 조작이 완료될 때까지 다른 세션에서 해당 데이터 조작을 기다리는 현상

 

 

  ■ LOCK의 종류

  SQL문으로 조작하는 대상 데이터가 테이블의 특정 행 데이터일 경우 해당 행만 LOCK이 발생한다는 의미로 행 레벨 록 (row level lock)이라고 정의한다.  WHERE 절을 지정하지 않은  UPDATE ,  DELETE 문일 경우에는 테이블의 모든 행 데이터에 영향을 주는 명령어 이므로 이 경우에는 테이블에 저장되어 있는 전체 행이 LOCK상태가 된다. 다른 세션에서는 해당 테이블에 이미 저장되어 있는 행에  UPDATE ,  DELETE 명령을 수행하기 위해서 대기해야 하지만 테이블 전체 행이 LOCK 상태여도  INSERT 문은 수행은 가능하다. 하지만 변경되는 행의 수와는 관계없이  DML (데이터 조작어)을 사용하여 데이터가 변경되는 테이블은 테이블 단위 잠금이라는 의미로 테이블 레벨 록(table level lock)이 걸린다. 즉 데이터를 변경 중인 세션 외 다른 세션에서  DDL (데이터 정의어)을 통한 테이블의 구조를 변경할 수는 없다.

 

→    DML (데이터 조작어) 관련 SQL문을 어떤 방식으로 작성하느냐에 따라 테이블의 일부 데이터만 LOCK이 될 수 있고, 테이블 전체 데이터가 LOCK이 될 수 있다. 

 

---

CREATE TABLE DEPT_TCL
	AS SELECT *
    	     FROM DEPT;

0. 과정을 진행하기 전에 먼저  DEPT 테이블을 복사한  DEPT_TCL 이라는 테이블을 임시로 만들어 놓아야 한다.

 

 

 

 

SQL developer 로그인화면

 

SQLPlus 로그인 화면

1. SQL developer와 CMD(SQLPlus로  scott 계정 접속)로 오라클 데이터베이스의  scott 계정에 접속해 2개의 세션을 만든다.

 

 

 

 

-- 1) SQL developer에서 DEPT_TCL 테이블 조회
SELECT * FROM DEPT_TCL;


-- 2) SQLPlus에서 DEPT_TCL 테이블 조회
SELECT * FROM DEPT_TCL;

2. 양쪽 세션(SQL developer와 SQLPlus)에서  DEPT_TCL 테이블을 조회해본다.

 

 

SQL developer 실행결과

SQLPlus 실행결과

아직 아무것도 안 했기 때문에 두 세션에서 테이블을 조회해도 같은 결과가 나온다.

 

 

 

 

-- 1) SQL developer에서 UPDATE문 실행
UPDATE DEPT_TCL 
   SET LOC = 'BUNDANG'
 WHERE DEPTNO = 30;


-- 2) 결과확인
SELECT * FROM DEPT_TCL;


-- 3) SQLPlus에서 해당 테이블 확인
SELECT * FROM DEPT_TCL;

3.SQL developer에서  UPDATE 문을 실행하고 양쪽 세션에서 테이블을 확인해본다.

 

 

SQL developer 실행결과

SQLPlus 실행결과

SQL developer에서는  UPDATE 문이 실행된 상태로  DEPT_TCL 테이블이 조회되지만 SQLPlus에서는 아직  UPDATE 문이 적용되지 않은 상태(변경이 일어나기 전 상태)로 출력되는 것을 확인할 수 있다. 이는 SQL developer에서 실행한  UPDATE 문의 수행결과가 아직  COMMIT (데이터베이스에 반영)되지 않았기 때문이다. 

 

 

 

 

-- SQLPlus에서 UPDATE문 실행
UPDATE DEPT_TCL
   SET DNAME = 'DATABASE'
 WHERE DEPTNO = 30;

4. 이상태로 SQLPlus에서도  UPDATE 문을 실행해본다.

 

 

SQLPlus 실행결과

 UPDATE 문을 실행했는데도 화면이 멈추고 아무 동작도 일어나지 않는다.

이는 SQL developer에서  DEPT_TCL 테이블의  DEPTNO (부서 번호)  30 인 행 데이터를 먼저 조작하고 있기 때문이다. 다른 세션에서 데이터의 조작이 완료되지 않았기 때문에  COMMIT 이나  ROLLBACK 을 수행하기 전까지 해당 행 데이터를 조작하려는 다른 세션은 위의 결과와 같이 작업을 대기하게 된다. 이렇게 특정 세션에서 데이터 조작이 완료될 때까지 다른 세션에서 해당 데이터 조작을 기다리는 현상을  HANG 이라고 한다.

 

 

 

 

-- SQL developer에서 COMMIT 실행
COMMIT;

5. SQL developer에서  COMMIT 명령어를 실행하는 동시에 SQLPlus에서 어떤 일이 일어나는지 확인한다.

 

 

SQLPlus 실행결과

SQL developer에서  COMMIT 명령어를 실행하는 순간 SQLPlus의  UPDATE 문이 실행된 것을 확인할 수 있다. 한 쪽 세션(SQL developer)에 의해 LOCK상태였던  DEPTNO   30 행 데이터가  COMMIT 명령어로 트랜잭션이 완료되어 LOCK이 풀렸고, 이와 동시에 해당 데이터 작업을 기다리고 있었던 다른 쪽 세션(SQLPlus)의  UPDATE 문이 실행된 것이다.

 

 

 

 

-- 1) SQL developer 에서 DEPT_TCL 테이블 조회
SELECT * FROM DEPT_TCL;


-- 2) SQLPlus 에서 DEPT_TCL 테이블 조회
SELECT * FROM DEPT_TCL;

6. 마지막으로 양쪽 세션에서  DEPT_ TCL 테이블을 조회해본다. 

 

 

SQL developer 실행결과

SQLPlus 실행결과

SQL developer 세션의 LOCK이 풀린 후 SQLPlus세션의  UPDATE 문이 실행되었지만 아직  COMMIT 을 하지 않은 상태이기 때문에 SQLPlus의 세션에서만  DEPTNO   30 행의  DNAME 이  DATABASE 로 출력된 것을 확인할 수 있다.

 

 

 

 

-- 1) SQLPlus에서 COMMIT 실행
COMMIT;


-- 2) SQL developer에서 DEPT_TCL 테이블 조회
SELECT * FROM DEPT_TCL;

7. SQLPlus에서  COMMIT 을 실행한 뒤 다른 세션(SQL developer)에서 테이블을 조회해본다.

 

 

SQL developer 실행결과

SQLPlus 세션에서 실행한  UPDATE 문의 결과가 데이터베이스에 반영되어 다른 세션(SQL developer)이 테이블을 조회해도 똑같은 결과가 나온다.