Mysql - Lock 기본

1. Shared Lock(S) vs Exclusive Lock(X)

 

1.1 Shared Lock(S)

다음과 같이 동작을 합니다.

1. Trasaction 1에서 (S) Lock을 얻은 상태에서 Transaction 2에서 (S) Lock을 요청하는 경우 허용됩니다.
2. Trasaction 1에서 (S) Lock을 얻은 상태에서 Transaction 2에서 (X) Lock을 요청하는 경우 거부됩니다.

 

1.2 Exclusive Lock(X)

다음과 같이 동작합니다.

1. Trasaction 1에서 (S) Lock을 얻은 상태에서 Transaction 2에서 (S) Lock을 요청하는 경우 거부됩니다.
2. Trasaction 1에서 (S) Lock을 얻은 상태에서 Transaction 2에서 (X) Lock을 요청하는 경우 거부됩니다.

 

 

2. Table Lock & Row Lock

Table Lock, Row Lock 은 모두들 들어본적이 있을 것입니다.

 

2.1 Table Lock

Table Lock은 유저가 접근하는 Table에 대하여 Lock을 걸어 해당 Lock을 놓기 전까지 다른 유저의 접근을 막도록 하여 일관성을 유지하는 방법입니다.

 

Table Lock에는 READ, WRITE Lock이 존재합니다. 각각에 대해 조금 더 자세히 알아보겠습니다.

 

> create table user(
    id int auto_increment primary key,
    name varchar(100)
);

실제로 테스트를 진행하며 특성을 알아보기 위해서, User table을 다음과 같이 생성합니다.

 

READ LOCK(Shared Lock)

READ LOCK은 말그대로 읽기 작업을 위해 해당 테이블을 잠그는 것을 말하며, 일종의 Shared Lock입니다.

 

> lock table user READ;

위의 명령어로 획득할 수 있습니다.

 

READ LOCK은 다음과 같은 특성이 있습니다.

1. LOCK을 얻은 세션에서 읽기만 가능 하며 쓰기(insert, update, delete) 작업을 수행할 수 없습니다.
2. 다른 세션에서 WRITE 작업 수행 시 READ LOCK이 모두 제거 될 때 까지 대기하게 됩니다.
3. 첫번째 세션에서 lock을 realease하는 순간, 두번째 세션의 대기중인 write작업이 성공하게 됩니다.
4. show processlist; 명령어로 현재 대기중인 세션의 상태를 확인할 수 있습니다.
5. 여러 세션에서 동시에 READ LOCK을 얻을 수 있습니다.

 

 

WRITE LOCK(Exclusive Lock)

WRITE LOCK은 쓰기작업을 위해 해당 테이블의 잠금을 획득하는 것을 말하며, Exclusive Lock의 일종입니다.

 

> lock table user WRITE;

위의 명령어로 획득이 가능합니다.

 

쓰기 작업을 위해 잠금을 획득하는 것이므로, READ LOCK과는 다른 특성이 있습니다.

1. WRITE 작업이 당연히 가능합니다.

2. 다른 세션에서, READ, WRITE 잠금 요청 시 모두 대기 하게 됩니다.READ와 마찬가지로 WRITE lock을 release하는 순간 모든 대기중인 요청이 수행됩니다.
   

   

 

 

2.2 ROW LOCK(Record Lock)

ROW LOCK는, ROW 단위의 잠금을 말합니다.

해당 테이블에 대한 모든 작업을 막는 Table Lock과 달리 ROW는 특정 ROW만을 잠그기 때문에, 다른 ROW의 접근에 대해서는 허용합니다.

마찬가지로, ROW단위 잠금도 Shared LOCK, Exclusive LOCK이 존재합니다.

User table에는 위와 같은 row가 존재합니다.

 

 

SHARE LOCK(for share)

첫번째 세션에서 트랜잭션을 시작하고, select * from user where id = 19 for share; 명령어로 id가 19인 row에 share 잠금을 획득합니다.

 

두번째 세션에서 같은 row(id=19)에 대해서, share 잠금을 획득 요청을하면, 성공적으로 획득이 됩니다.

 

두번째 세션에서, 같은 row(id=19)에 write 잠금 요청을 하는 경우에는, 요청이 대기하게 됩니다.

 

id가 20인 행에 대해서, 잠금 요청을 하면 모두 성공적으로 얻게 됩니다.

 

 

EXCLUSIVE LOCK (for update)

첫 세션에서 for update를 이용해 id=19인 row에 대해 EXCLUSIVE LOCK을 얻고, 다른 세션에서 id=19 인 row에 대해서, for share, for update로 row Lock 획득을 요청하면 모든 종류의 요청이 대기하게 됩니다.

 

id가 20인 행에 대해서, 잠금 요청을 하면 모두 성공적으로 얻게 됩니다.