<Database> 공유락(Shared Lock) & 배타락(Exclusive Lock)

2022.02.12 - [공부(2021)/Database] - 1. 트랜잭션의 Isolation Level 과 동시성 제어

가. Lock Lock~

 a. 필요한 이유?

  -  지난 포스팅에 동시성제어에 대한 내용으로 Isolation level를 확인했었다. 아쉬운점은 이 Isolation level은 connection 단위로만 적용가능하다. 

  -  트랜잭션 내에서 Select한 데이터에 대해 다른 트랜잭션을 동시성 제어하고 싶은 경우에 필요한 것이 데이터베이스의 Lock이다.

 

 b.  Lock 종류

  -  데이터베이스의 Lock은 크게 두가지가 있다.

      1. Shared Lock (공유락) - 읽기 O , 쓰기 X 

         -  다른 트랜잭션에서의 Shared Lock은 허용하지만 Exclusive Lock은 허용하지 않음 

      2. Exclusive Lock (배타락) - 읽기 X, 쓰기 X

         -  다른 트랙잭션에서의 Shared Lock, Exclusvie Lock 은 허용하지 않는다.

 

나. Lock 만들기

 a. Shared Lock

  -  Lock을 테스트하기 위해서는 서로 다른 connection이 필요하기 때문에 두개를 띄워서 테스트 해야한다.

  -  Shared Lock은 select 절 뒤에 for share를 명시하여 lock을 생성할 수 있다.

  -  data의 lock 여부는 아래 쿼리를 통해 확인 할 수 있고 lock_mode의 S 표시가 Shared Lock 이라는 의미이다.

 

 b. Exclusive Lock

  -  Exclusive Lock은 select 절 뒤에 for update 를 명시하여 lock을 생성할 수 있다.

     => Exclusive Lock은 읽기 쓰기 모두 잠금하기 때문에 이후에 실행한 오른쪽 쿼리는 대기상태에 걸린 것을 볼 수 있다.

  -  data의 lock 여부는 아래 쿼리를 통해 확인 할 수 있고 lock_mode의 S 표시가 Shared Lock 이라는 의미이다.

 

다. 이것저것 테스트해보기 

 1. Shared Lock이 걸린 row 업데이트

  -  아래 보는 것 처럼 Shared Lock이 걸린 트랜잭션이 commit 될때까지 오른쪽 update는 대기상태가 된다.

      => update query은 Exclusive Lock으로 해당 row를 잠금한다.

  -  기준 시간동안 Lock을 획득하지 못하면 Timeout이 발생했다. 왼쪽을 바로 커밋하는 경우에는 정상적으로 업데이트 된다.

 

 2. 서로 다른 Shared Lock이 걸린 row 업데이트

  -  개별적으로 S Lock을 걸린 상대방 row를 업데이트를 해보았다.

       => 서로 Lock을 획득하기 위해 왼쪽은 오른쪽이 커밋되기를 반대로 오른쪽은 왼쪽이 커밋되기를 기다린다.   

  -  오른쪽이 Deadlock 감지하여 롤백시키면서 왼쪽이 Lock을 획득하는 것을 볼 수 있다.

 

 3. 서로 Shared Lock이 걸고 row 업데이트

  -  순서와 관계없이 먼저 Shared Lock을 걸었어도 다른쪽이 commit하기 전까지 update가 발생하는 경우 대기상태가 된다. 

 

 4. 같은 row에 Exclusive Lock 걸기

  -  먼저 Exclusive Lock을 설정한 트랜잭션이 끝나기 전까지 대기상태가 된다.

 

 5. Exclusive Lock이 걸린 row를 Select 하기

  -  유의할부분은 Exclusive Lock을 걸었다고 해서 Shared Lock이 아닌 읽기(Select)는 일반적으로 잠금하지 않는다. 

 

라. ETC

  a. PK가 아닌 data에 Lock을 걸면 ?

  - 테이블을 새롭게 만들어서 테스트 해보자. 먼저 index가 없는 company만 lock을 거는 경우 어떻게 되는지 살펴보자

  -  lock이 PK 기준으로 생성이 되었는데 중요한건 테이블 전체의 모든 row에 lock이 생성된 것을 볼 수 있다.

  -  이번엔 company에 index를 생성하고 테스트 해보면 해당 row에만 lock이 생성된 것을 볼 수 있다. 또한 PK에도 Lock이 생성되는 것을 볼 수 있다.