티끌모아 태산
[MySQL] MyISAM - Table Lock의 종류 본문
InnoDB는 row level lock을 지원하지만
MyISAM 엔진의 경우 CRUD작업 수행시 Table 단위의 Lock이 발생하기 때문에 아래 특징을 잘 알고있어야 한다.
https://gywn.net/2011/12/mysql-three-features/
반드시 알아야할 MySQL 특징 세 가지 – gywndi's database
Overview MySQL 요구가 전보다 급증하고 있습니다. 이제 친숙해서 사용하는 간단한 소용량 DBMS 이 아닌, 많은 대형 업체에서도 사용되고 있기 때문에 많은 이슈가 되고 있습니다. 트위터, 페이스북,
gywn.net
Table Lock 이해
MySQL에서 Table Lock은 다음 기준에 의해서 부여됩니다.
Write Lock
아무런 Lock이 없으면, 해당 테이블에 Write Lock을 걸어서 데이터 읽기 또는 변경 작업을 수행하지 못하게 합니다. 만약 Read 혹은 Write Lock이 존재하면 Write Lock 큐에 Lock을 넣어서 해당 Lock이 풀릴 때까지 대기합니다.Read Lock
아무런 Write Lock이 없으면, Read Lock을 걸어서, 데이터 변경 작업을 수행하지 못하도록 합니다. 만약 Write Lock이 있으면 Read Lock 큐에 Lock을 넣고 데이터 변경 작업이 종료될 때까지 대기합니다.기본적으로 Write Lock이 Read Lock보다 우선 순위가 높지만,
다음과 같이 LOW_PRIORITY 로 변경 가능합니다.
Example)
mysql> INSERT INTO LOW_PRIORITY table_name…
mysql> DELETE LOW_PRIORITY FROM table_name…
mysql> UPDATE LOW_PRIORITY table_name SET…
위를 다시 간단하게 정리하자면, Write Lock 상태에서는 다른 세션이 해당 테이블 접근이 불가한 상태이고, Read Lock 상태는 다른 세션이 데이터를 Read까지만 가능하다고 볼 수 있습니다.
그러나! 만약에 Write 또는 Read 수행이 오래 걸리는 경우는 어떨까요? Read Lock은 다른 Read 세션에 영향을 미치지 않을 것으로 보이지만, 때로는 Dead Lock을 유발하는 요소가 될 수도 있습니다.
Example
다음과 같은 경우를 예를 들어보겠습니다. 테이블은 MyISAM 엔진입니다.
Session 1
## 수행 시간이 오래 걸리는 조회 쿼리 발생
mysql> SELECT * FROM tab01 WHERE sleep(1000);
이 경우 다른 세션에서도 tab01 테이블에서 얼마든지 데이터 조회가 가능합니다.
Session 2
## Read Lock 상태 테이블에 데이터 변경
mysql> UPDATE tab01 SET c1 = '' WHERE i = 4;
Update 쿼리는 Session1의 Select 쿼리가 종료될 때까지 대기합니다.
Session 3
## Read Lock 상태이고, Write Lock이 대기 상태에서 Select 수행
mysql> SELECT * FROM tab01 LIMIT 10;
Session 2 의 Write Lock에 의해 Read 불가한 상태로 빠집니다.
프로세스 현황을 확인해보면 아래와 같습니다.
정상적이라면 Select 수행되는 동안 다른 세션에서도 Select가 수행되어야 하는데, 쿼리 우선 순위에 의해서 Select 세션이 Lock 상태로 빠진 것을 확인할 수 있습니다.
Conclusion
MySQL Replication 사용 시 Slave 서버에서 Dead Lock은 위와 같은 상황에서 얼마든지 발생할 수 있기 때문에, 반드시 알고 있어야 합니다. (통계성 SQL이 실행되는 테이블에 Update 발생 시 다른 세션에서는 해당 테이블 데이터 조회 불가)
MyISAM 스토리지 엔진이 트랜잭션이 많은 경우 부적합한 가장 큰 이유입니다.^^
'환경 > MySQL' 카테고리의 다른 글
MySQL 테이블 이름 변경 (0) | 2023.12.18 |
---|---|
[MySQL]SQL 오류 (1206): The total number of locks exceeds the lock table size (0) | 2023.11.15 |
MySQL Driver에서 ConnectTimeout vs SocketTimeout (0) | 2023.10.16 |
[펌] MySQL 3가지 특성 (0) | 2023.09.18 |
[MySQL] DB엔진 비교: InnoDB vs MyISAM (1) | 2023.09.18 |