DB

mysql 잠금 - 락의 종류

코딩공장공장장 2025. 2. 9. 12:46

락의 종류

1. 글로벌 락

-- 글로벌 락 획득
Flush TABLES WITH READ LOCK;

-- 락 반납
UNLOCK UNLOCK TABLES;

글로벌 락은 위 명령어를 통해 사용할 수 있다.

 

MyIsam이나 MEMORY 스토리지 엔진에서는 select를 제외한 대부분의 DDL문장이나 DML 문장에 락을 건다.

테이블 전체를 읽기 전용으로 만들며 주로 백업 시에 사용한다.

 

InnoDB에서는 위 글로벌 락을 사용하지 않는다.

백업락이라는 데이터베이스와 테이블 스키마,  사용자 정보에 대해서만 잠금 처리를 진행하는 락을 사용한다.

트랜잭션을 통한 데이터의 일관성이 보장되기에 데이터 변경 관련 ddl에는 잠금 처리가 필요 없기 때문이다.

 

소스 서버 변경사항을 레플리카로 복제시, 글로벌 락은 ddl을 막아내지 못함

소스 서버(master)는 원본 서버이고 레플리카 서버(복제 서버)이다.
read 작업과 write 작업의 부하를 분산하기 위해 일반적으로 위와 같은 구조로 db를 구성하기도 한다.
소스 서버에 변경 작업이 반영되면 레플리카 서버로 복제가 된다.

글로벌 락은 백업시에 주로 활용된다. 소스-레플리카 구조에서 백업은 주로 레플리카 서버에서 이뤄진다.
백업시 레플리카 서버의 글로벌 락은 소스 서버에는 적용되지 않는다.(당연히 서로 다른 프로세스이므로)
만일 레플리카 서버의 백업 도중 소스 서버에서 ddl이 발생하면 소스서버의 변경사항을 레플리카로 복제하는 과정에서 백업은 실패한다.
데이터의 변경에는 영향 없이 일관된 백업 작업이 가능하게 하는 플러그인들이 존재하지만
복제 작업에 포함된 ddl은 막아낼 수 없다.

이러한 이유로 백업락이 생겨나게 됬고 백업락은 복제시 ddl 작업이 발생하면 복제를 일시 중단하고 백업을 진행하도록 처리되어있다.

 

2. 테이블 락

-- 테이블 락 획득 READ시 다른 세션 읽기 전용, WRITE시 다른 세션 접근 불가
LOCK TABLES table_name [ READ | WRITE ]

-- 락 반납
UNLOCK table_name

 

개별 테이블 단위로 설정되는 잠금이다.

MyISAM이나 MEMORY 테이블에서 데이터 변경시 사용되나

InnoDB는 레코드 수준 락이 제공되기에 데이터 변경시 사용되지 않고 DDL에서 사용된다.

 

3. 네임드 락

-- 락 획득, 이미 락 사용중일 시 timeout 시간만큼만 대기
SELECT GET_LOCK('custom_lock_name', timeout);

-- 락 사용 가능한지 여부
SELECT IS_FREE_LOCK('custom_lock_name');

-- 락 반환
SELECT RELEASE_LOCK('custom_lock_name');

 

네임드락은 사용자가 직접 락을 생성하고 관리할 수 있는 기능이다.

결과 값은 각 구문의 해석대로 동작하면  1, 그렇지 않으면 0이다.

특정 이름의 락을 직접 생성하여 제어할 수 있다.

 

배치 프로그램에서 동시에 같은 테이블의 레코드가 수정되는 배치가 여러개 실행될 때, 데드락의 원인이 될 수 있어 네임드 락을 통해 직접 동시성을 제어할 수 있다.

허나, 이미 데이터 베이스가 동시성 제어를 수준 높게 해주고 있으므로 네임드 락을 활용하여 db의 잠금 메커니즘 보다 높은 수준의 동서성 제어가 쉽지 않아 거의 사용되지 않는다고 한다.

네임드 락을 통해 동시성 제어를 하다 오히려 동시성 문제를 유발할 수 있고, db의 기본 동시성 제어 메커니즘을 활용하는게 더욱 효율적인 경우가 많다.

 

아래 네임드 락을 활용하여 동시성 제어를 진행한 사례가 있다.

insert 쿼리에서 동시성에 문제가 있는 것으로 판단된다. update나 delete 쿼리는 where문이 존재하기에 쿼리 수준에서 적용되는 락으로 제어가 가능하지만 insert는 where문이 없어 제어가 쉽지 않고 별도의 중앙 서버를 두어 해결하는 것도 인프라 비용에 문제가 있으니 네임드 락을 활용한 것 같다.

https://techblog.woowahan.com/2631/

 

4. 메타데이터 락

테이블 이름이나 구조를 변경하는 경우 사용되는 락으로 사용자가 직접 획득 할 수는 없다.

 

[예제] 테이블 스키마 변경으로 신규 테이블에 반영하고 데이터 이관

1. 현 시점 데이터를 pk 기준으로 작업 단위를 나누어 멀티스레드로 신규테이블에 insert

2. 기존 테이블에 테이블 락

3. 1번 과정 실행 중 insert된 데이터를 신규 테이블에 insert

3. RENAME 명령어를 통해 기존 테이블은 old로 신규 테이블은 기존 테이블의 이름을 갖도록 함

RENAME TABLE access_log to access_log_old, access_log_new TO access_log;

4. 테이블락 반환

 

3번 과정에서 메타 데이터 락이 걸렸다.

만일 위 과정을 아래와 같이 두개의 명령어로 나누어 작업하면 일시적으로 access_log 테이블이 존재하지 않는 상황이 생긴다.

RENAME TABLE access_log to access_log_old;
RENAME TABLE access_log_new TO access_log;

따라서 3번과 같이 진행하면 테이블에 락을 걸고 위 두 작업의 원자성을 보장해준다.

 

InnoDB 스토리지 엔진 잠금

 1. 레코드 락

레코드 자체만을 잠그는 것이 레코드락이다.
레코드 자체를 잠그는 것은 아니면 인덱스의 레코드를 잠근다.(세컨더리 없는 경우 클러스터 인덱스 잠금)

세컨더리 인덱스를 이용한 변경 작업은 넥스트 키락이나 갭락이 사용되지만 pk나 유니크 인덱스는 레코드 자체에 락을 건다.

 

2. 갭 락

레코드와 레코드 사이의 간격에 새로운 레코드가 insert되는 것을 제어하는 목적이며 넥스트 키락의 일부로 자주 사용된다.

변경 작업에서 where절에 범위 조건이 있는 경우 주로 사용된다.

 

3. 넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태를 넥스트 키 락이라고 한다.

트랜잭션 격리 수준이 REAPEATABLE READ에서 사용된다.(팬텀리드 방지 목적)

주로 바어너리 로그가 state_ment로 설정됬을 때 소스 서버의 변경사항 쿼리가 레플리카 서버에서 복제될 때 사용된다.

state_ment라는 것은 변경사항을 실행 쿼리 그대로 로그로 기록한다는 것인데, 복제시 레플리카 서버에서 쿼리 메커니즘에 의해 또 다시 락을 수행된다.

row 값으로 설정시  변경되기 전 값과 변경 후 칼럼 값을 행 단위로 보존하기에 행 단위 반영을 통해 락을 유발하지 않는다.

(8.0에서 바이너리 로그 row가 기본값)

 

4. 자동증가 락

AUTO_INCREMENT 값을 채번하기 위해 사용되는 락이다.

insert 쿼리에서 AUTO_INCREMENT 값을 가져오는 순간에만 락이 걸린다.

innodb_autoinc_lock_mode 설명
0 auto_increment 락 사용
1 - auto_increment 락 보다 더 가볍고 빠른 래치(뮤텍스)를 사용
- insert 쿼리에 추가할 레코드 건수가 명시적으로 나타난 경우 래치 사용
- insert … select와 같이 추가할 레코드 건수를 서브 쿼리를 실행해봐야 아는 경우에는 auto_increment 락 사용
2 - 8.0 부터 기본 값
- 래치(뮤텍스)만 사용
- insert … select 쿼리 실행 도중 다른 insert문 실행 가능
(insert … select 쿼리로 실행되는 레코드 사이에 다른 insert 문의 레코드가 존재 가능, 이때 유니크 값은 보장하기에 높은 동시성 처리 수준을 보여준다.)

 

[잠깐] 인덱스 수준 잠금

innoDB의 데이터 변경시 레코드 자체를 잠그는게 아니라 인덱스의 레코드를 잠근다.

이는 변경 작업시 변경 대상을 찾기 위해 검색한 인덱스의 레코드 모두를 잠근다는 것이다.

where절 조건에 세컨더리 인덱스 칼럼 조건을 건다면 해당 조건에 검색되는 모든 칼럼들은 락이 걸린다.
이때  클러스터 인덱스에도 락이 같이 걸리기에 다른 인덱스 조건으로 변경작업을 진행하는 트랜잭션이 존재하더라도 동시성 문제는 발생되지 않는다.

개인적인 생각으로 인덱스의 레코드를 잠그지 않는다면 데이터 페이지 수준에서 잠금이 이뤄져야하는데
이렇게 되면 많은 페이지에 잠금을 걸어야하고 결과적으로 인덱스 수준의 잠금보다 더 많은 잠금 처리를 진행하게 되니
인덱스 수준에서 잠금 처리를 진행하지 않았을까 생각이 든다.

 

 

[잠깐] 레코드 수준 잠금 확인 법

show processlist;

 

세션을 3개 열어서 똑같은 레코드에 대해 update를 수행하니 위와 같은 결과가 나타난다.

Time 칼럼을 통해 트랜잭션 작업이 얼마나 길어지고 있는지 확인 가능하다.

 

SELECT r.trx_id AS waiting_trx_id,
	   r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
  FROM performance_schema.data_lock_waits w
  JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_engine_transaction_id
  JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_engine_transaction_id;

 

위 쿼리 수행 결과로 레코드 잠금으로 대기중인 스레드 정보 또한 확인 가능하다.

반응형
저작자표시