MySQL Lock 이해하기

- 목차

 
 

소개.

MySQL 의 Lock 은 다른 사용자가 특정 데이터를 변경하는 행위를 제한합니다.
즉, 여러 사용자가 하나의 데이터를 조작하는 것을 막아 충돌을 방지합니다.
MySQL 의 관점에서 사용자란 Client 가 되는데요.
Client 는 하나의 Transaction 으로 표현될 수 있습니다.
즉, 어떤 Transaction 이 특정 데이터에 Lock 을 적용한다면 다른 Transaction 들은 해당 데이터를 변경할 수 없게 됩니다.
Lock 의 범위는 Row, Table, Database 단위로 적용됩니다.
만약 아래와 같은 쿼리가 실행된 상태라면

select * from member where name = 'Andy' for share;

다른 Transaction 은 "Andy" 라는 member Table 의 row 를 수정, 삭제할 수 없습니다.
 
이러한 방식으로 Table, Database 범위로 Lock 을 적용할 수 있습니다.
이러한 Lock 보통 MySQL-Dump 같은 Backup 을 진행할 때에 주로 사용됩니다.
 
이번 글에서 MySQL 의 Lock 에 대해서 알아보려고 합니다.
 

Lock 의 적용 범위.

Lock 의 적용 범위는 크게 세가지입니다.
- Database
- Table
- Row
 
각각의 케이스에 대한 사례를 들어보도록 하겠습니다.
 

Database Level Lock.

Database Level 의 대표적인 Lock 은 flush tables 쿼리입니다.
"flush tables" 은 DML 쿼리로 인해서 In-Memory 에 생성 또는 변경된 데이터들을 Disk 로 Flush 하는 기능이라고 하는데요.
다음 번에 flush 에 대한 내용을 한번 정리해보겠습니다.
 
아무튼 아래 쿼리를 통해서 Database Level 의 Lock 을 적용할 수 있구요.

FLUSH TABLES WITH READ LOCK;

 
 
위 쿼리가 실행된 상황에서는 DML, DDL 쿼리들은 실행되지 않습니다.
 
아래와 같은 쿼리들은 Database Lock 이 Unlock 되기 전까지 실행되지 않으며, 에러를 유발하게 됩니다.

mysql> insert into test_table(id) values(1);
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

 
아래의 UNLOCK TABLES; 쿼리가 실행된 이후에는 DML, DDL 쿼리가 정상 실행됩니다.

UNLOCK TABLES;

mysql> insert into test_table(id) values(1);
Query OK, 1 row affected (0.00 sec)

 

Table Level Lock.

Table Level Lock 은 보통 mysqldump 와 같이 Backup 을 수행할 때에 사용됩니다.
Table Level Lock 이 적용된 기간동안은 정상적인 테이블 사용이 어렵게 됩니다.
Table 를 조회하는 것은 가능하겠지만, DML 쿼리들은 실행되지 않습니다.
 

LOCK TABLES test_table READ;

아래 쿼리는 test_table 이라는 테이블에 Read Lock 을 적용한 케이스입니다.
이 경우에는 select 쿼리는 사용가능하지만, insert & upate & delete 는 수행되지 않습니다.

lock tables test_table read;

 
아래 쿼리들은 수행되지 않습니다.

insert into test_table() values(1);
update test_table set id = 2 where id = 1;
delete from test_table where id = 1;

 
그리고 아래 쿼리로 Table 에 적용된 Lock 을 해제합니다.

unlock tables;

 
여러개의 테이블에 Lock 을 적용하고 싶다면 아래와 같은 방식을 사용할 수도 있습니다.

lock tables test_table read, test_table2 read;

 
 

LOCK TABLES test_table WRITE;

write Lock 이 적용되는 경우에는 select 쿼리마저도 사용할 수 없다는 점도 같이 명심해주시면 좋겠습니다.

LOCK TABLES test_table WRITE;

mysql> select * from test_table;
// unlock 되기 전까지 Blocked 상태 유지됨.

 

Row Level Lock.

Row Level 의 Lock 은 MySQL 을 운영하는 과정에서 사용되는 가장 일반적인 Lock 입니다.

Shared Lock.

Shared Lock 은 "공유락" 이라고 불립니다.

Shared Lock 은 다른 Transaction 의 읽기를 허용합니다.

그래서 "lock table `test_table` read;", "flush table with read lock;" 와 결이 비슷합니다.

Shared Lock 은 오로지 데이터를 변경하는 DML 의 충돌을 방지합니다.

 

Shared Lock 의 한가지 예시를 들어보겠습니다.

customer 이라는 테이블을 하나 생성하였습니다.

이는 3개의 column 인 id, name, age 를 가집니다. 

id 은 primarty key index, name 은 unique index, age 는 index 가 적용되지 않은 칼럼입니다.

그리고 3개의 Row 를 생성합니다.

create table customers (
    id int primary key, 
    name varchar(64), 
    age int,
    unique (name)
);

insert into customers(id, name, age) 
values (1, 'Andy', 20),
(2, 'Bob', 21),
(3, 'Chris', 22);

 

Shared Lock 사례 1.

첫번째로 살펴볼 내용은 Shared Lock 에 의해서 다른 Transaction 의 DML 이 Waiting 되는 경우입니다.

아래 케이스처럼 Transaction 1 에서 Shared Lock 을 적용한 이후에 다른 Transction 들은 Lock 이 걸린 Row 를 수정할 수 없습니다.

Transaction 1 > start transaction;
Transaction 1 > select * from customers where id = 1 for share;

Transaction 2 > update customers set name = 'Alice' where id = 1;
Transaction 2 > ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

Transaction 1 에서 commit 또는 rollback 을 통해 Transaction 을 종료하지 않으면 Transaction 2 의 쿼리는 수행되지 않습니다.

 

Shared Lock 사례 2.

두번째로 살펴볼 내용은 Index 의 종류에 따라서 Shared Lock 의 범위가 달라집니다.

Primary Key Index 와 Unique Index 의 경우에는 Row 단위로 Lock 이 적용됩니다.

하지만 그외의 Non-Unique Index 또는 Index 가 적용되지 않은 Column 에 대해서는 Table Level Lock 이 발생합니다.

 

name 은 Unique Index 가 적용된 Column 이며, 아래 예시는  name 을 기준으로 Lock 을 적용하였습니다.

그리고 Lock 이 적용된 Row 의 데이터 수정을 불가합니다.

Transaction 1 > start transaction;
Transaction 1 > select * from customers where name = 'Andy' for share;

Transaction 2 > update customers set name = 'Alice' where id = 1;
Transaction 2 > ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

반면 Lock 이 적용되지 않은 Row 를 수정하는 것은 가능합니다.

Transaction 1 > start transaction;
Transaction 1 > select * from customers where name = 'Andy' for share;

Transaction 2 > update customers set name = 'Bob' where id = 2;
Transaction 2 > Query OK, 0 rows affected (0.00 sec)
Transaction 2 > Rows matched: 1  Changed: 0  Warnings: 0

 

 

Shared Lock 사례 3.

세번째 케이스는 Primary Key Index, Unique Index 가 적용되지 않은 Column 으로 Lock 을 적용하는 케이스입니다.

이 경우에는 Table Level Lock 이 적용됩니다.

age Column 은 어떠한 Index 도 적용되지 않는 Column 입니다.

 

age 가 30 인 Row 는 존재하지 않지만, Transaction 2 에서 시도하는 DML 은 수행되지 않습니다.

Transaction 1 > start transaction;
Transaction 1 > select * from customers where age = 30 for share;

Transaction 2 > update customers set name = 'Andy' where id = 1;
Transaction 2 > ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

Exclusive Lock.

 

Exclusive Lock 은 Shared Lock 에서 허용한 Read Query 조차도 허용하지 않습니다.
Exclusive Lock 또한 Row Level Lock 그리고 Table Level Lock 이 존재합니다.
각각의 예시는 아래와 같습니다.
 

select * from test_table where id = 1 for update;

LOCK TABLES test_table WRITE;
UNLOCK TABLES;

 
그리고 update, delete 와 같은 DML 들 또한 exclusive lock 이 적용됩니다.