Shared lock과 Execlusive lock

Lock?
같은 데이터를 동시에 접근해서 조회 및 수정을 해야하는 DB환경에서 일관성과 무결성 유지를 위해서는 트랜잭션의 순차적 진행을 보장할 수 있는 직렬화(Serialization)장치가 필요하게 됩니다. 즉, 같은 데이터를 동시에 접근했을 때 한명만 사용할 수 있게 해야한다는 것입니다. 그때 다른 사용자는 해당 데이터에 lock이 걸린 상태로 대기해야 합니다.

Shared Lock?
데이터를 읽고자 할 때 사용됩니다. Read lock이라고도 불리며 어떤 트랜잭션에서 데이터를 읽고자 할 때 다른 Shared lock은 허용이 되지만 Execlusice lock은 불가능합니다. 리소스를 다른 사용자가 동시에 읽을 수 있게 하되 변경은 불가능하게 됩니다.
즉, 어떤 사용자가 읽고 있는 레코드 혹은 테이블을 동시에 다른 사용자가 읽을 수 있습니다.


Exclusive Lock?
데이터를 변경하고자 할 떄 사용됩니다. Write Lock이라고도 불리며 트랜잭션에서 데이터를 변경하고자 할 때 트랜잭션이 완료될 때까지 해당 테이블 혹은 레코드를 다른 트랜잭션에서 읽거나 쓰지 못합니다. Exclusive lock에 걸리면 Shared lock을 걸 수 없습니다. Exclusive lock에 걸린 테이블, 레코드 등의 자원에 대해 다른 트랜잭션이 Exclusive lock을 걸 수 없습니다.
즉, 어떤 사용자가 변경 하고 있는 레코드 혹은 테이블에 다른 사용자가 접근할 수 없습니다.(읽기, 변경 둘다 포함)

 

 

TEST

Oracle 12.2.0.1

Scott 유저에서 실행

 

#1

Session 1

$sqlplus scott/tiger

 

Session 2

select a.session_id,a.type,a.block,b.username from dba_locks a,v$session b where a.session_id=b.sid and  b.username='SCOTT'

 

SESSION_ID LOCK_TYPE                  MODE_HELD                 USERNAME  
---------- -------------------------- ------------------------- ------------
        64 AE                         Share                     SCOTT     

 

접속만 했을 때 Share Lock이 생기게 됩니다. Lock_TYPE의 AE는 Edition lock으로써 사용중인 에디션 삭제 방지를 위한 Lock으로 확인됩니다.

 

 

#2

Session 3

$sqlplus scott/tiger

 

Session 2

SESSION_ID LOCK_TYPE                  MODE_HELD                                USERNAME
---------- -------------------------- ---------------------------------------- ----------------------------------------------------
        27 AE                         Share                                    SCOTT
        64 AE                         Share                                    SCOTT

 

 

다른 세션으로 접속했을 시 Shared Lock으로 하나더 생성된 것을 확인할 수 있습니다.

 

#3

Session 1

SCOTT@ysbae>  update emp_bak set grade='A' where empno=7788;

1 row updated.

 

Session 2

SESSION_ID LOCK_TYPE                  MODE_HELD                                USERNAME
---------- -------------------------- ---------------------------------------- ----------------------------------------------------
        64 Transaction                Exclusive                                SCOTT
        27 AE                         Share                                    SCOTT
        64 AE                         Share                                    SCOTT
        64 DML                        Row-X (SX)                               SCOTT

4 rows selected.

 

DML 작업이 수행됐을 경우, DML Lock Type과 Transaction 타입의 lcok이 추가되었고 Exclusive 인것을 확인 할 수 있습니다. Row-X (SX)도 Row lock Exclusive lock이라는 뜻입니다.

 

#4

 

session 3

SCOTT@ysbae>  update emp_bak set grade='A' where empno=7788; 

 

Session 2

SESSION_ID LOCK_TYPE                  MODE_HELD                                USERNAME 
---------- -------------------------- ---------------------------------------- ---------
        27 Transaction                None                                     SCOTT
        64 Transaction                Exclusive                                SCOTT
        27 AE                         Share                                    SCOTT
        64 AE                         Share                                    SCOTT
        27 DML                        Row-X (SX)                               SCOTT
        64 DML                        Row-X (SX)                               SCOTT

 

다른 세션에서 같은 ROW를 update하는 쿼리가 실행됐을 때 Lock이 걸리며, 마찬가지로 Row-X (SX)이 발생합니다. 하지만 Transaction 타입에서는 None으로 확인됩니다. 

 

v$lock 를 확인하여 어떤 세션이 blocking하고 있는지 확인할 수 있습니다.

select a.sid,a.type,a.block,b.username  from v$lock a,v$session b where a.sid=b.sid and username='SCOTT';

       SID TY      BLOCK USERNAME
---------- -- ---------- --------
        27 TX          0 SCOTT
        64 TX          1 SCOTT
        27 AE          0 SCOTT
        64 AE          0 SCOTT
        72 AE          0 SCOTT
        27 TM          0 SCOTT
        64 TM          0 SCOTT

 

확인 결과 64번 Session에서 Block을 하고 있는것을 확인할 수 있으며 64번 Session은 Session1으로 확인할 수 있습니다.

 

Lock을 해결하는 방법은 Blocking 하고 있는 세션의 트랜잭션을 완료(Commit or Rollback)하거나 alter session kill 명령어로 강제로 세션을 종료하는 방법이 있습니다.