mysql 잠금 - 락의 종류

낙관적 락과 비관적 락

---

낙관적 락

• 충돌이 드물게 발생한다고 가정
• 데이터 변경 시점에 다른 사용자에 의해 값이 변경됬는지 체크

낙관적 락은 데이터를 변경하는 시점에 다른 사용자에 의해 값이 변경됬는지 체크하는 방식으로

update나 delete 쿼리 수행시 버전 정보나 타임 스탬프를 통해 다른 사용자에 의한 변경을 체크할 수 있다.

애플리케이션 수준에서 동시성 제어를 해보았다면 update나 delete 쿼리 수행시 where 문에 조건절을 걸어 

반영된 레코드가 존재하는지 여부로 판단하는 구현이 대표적인 낙관적 락의 예시이다.

 

비관적 락

• 총돌이 발생한다고 가정
• 레코드 자체에 락을 걸며, 쓰기 작업에 잠금을 하는 공유 락과 읽기/쓰기 모두 못하게 하는 배타 락이 존재한다.

비관적 락은 레코드 자체에 잠금을 걸고 변경 작업을 수행하는 방식이다.

읽기는 가능하게 하며 쓰기를 불가능하게 하는 공유 락(shared lock)과

읽기와 쓰기 모두 불가능하게 하는 배타락(exclusive lock)이 존재한다.

 

공유락은  SELECT ... LOCK IN SHARE MODE

배타락은 SELECT ... FOR UPDATE 구문을 통해 획득하게 된다.

락의 반납은 오로지 트랜잭션이 끝나는 커밋이나 롤백을 통해서만 이루어진다.

 

이전에 mysql 아키텍쳐에서 언두로그를 통해 select 수행시 잠금 없이 일관된 읽기가 가능하다는 것을 배웠다.

mysql innoDB는 select 작업시 어떠한 잠금도 사용되지 않기에 동시 처리 성능이 높다.

 

InnoDB 스토리지 엔진 잠금

---

 1. 레코드 락

레코드 자체만을 잠그는 것이 레코드락이다.
정확하게는 인덱스의 레코드를 잠금 처리한다. (세컨더리 및 클러스터 인덱스 모두 잠금, 세컨더리 없는 경우 클러스터 인덱스 잠금)

만약 인덱스나 pk 조건 없이 update나 delete 작업을 수행하게 되면 테이블 풀스캔을 진행하며 모든 레코드를 잠금 처리한다.

모든 레코드에 잠금처리를 진행하게되면 당연히 동시 성능 또한 현저히 떨어지게 된다.

레코드락은 변경 작업 dml 쿼리 수행시 자동으로 사용되므로 적절한 인덱스 칼럼을 선정하여 동시 처리 효율에 대해 고려할 필요가 있다.

 

2. 갭 락

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

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

 

3. 넥스트 키 락

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

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

 

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

state_ment는 쿼리 형식으로 로그를 기록한다는 것인데, 복제시 쿼리 메커니즘에 의해 또 다시 잠금 처리가 걸려 부하를 유발할 수 있다.

이러한 경우 바어너리 로그 포맷을 row 값으로 설정하여 행 단위 반영을 통해 락을 유발하지 않게 할 수 있다.

 

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

row 형태는 변경되기 이전 이후의 행 정보를 기록하여 반영함

용량은 많이 차지할 수 있으나 쿼리 실행이 아니기에 잠금을 유발하지 않음

 

[잠깐] 인덱스 수준 잠금

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;

 

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

 

4. 자동증가 락

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

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

자동증가 락에는 auto_increment 락과 래치 락이 존재한다.

auto_increment 락은 여러 건의 레코드를 insert하는 상황에서 값의 순차적인 증가를 보장한다.

반면 래치의 경우 auto increment 값 중간에 다른 insert 쿼리문의 수행 결과가 반영될 수 있다.

하나의 쿼리에 대해 완벽한 원자적인 자동 증가 번호를 제공하는 것은 아니지만 테이블 내에서는 유니크 값을 보장하기에 높은 동시성 수준을 보장한다.

여러 insert 쿼리를 수행할 때, 각 쿼리 내에서 생성되는 레코드가 1씩 증가하지 않더라도 전체 결과가 1씩 증가한다면 유니크한 값을 보장하고 데이터의 일관성을 지킬 수 있으므로 mysql 8.0에서는 동시 처리 성능이 더욱 높은 래치 방식이 default로 설정되었다.

 

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

 

Mysql 엔진의 잠금

---

1. 글로벌 락

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

-- 락 반납
UNLOCK UNLOCK TABLES;

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

 

주로 백업 시에 사용하는 락이다.

MyIsam이나 MEMORY 스토리지 엔진에서는 select를 제외한 대부분의 DDL문과 DML문이 글로벌 락에 의해 수행될 수 없다.

쿼리문의 수행은 막을 수 있으나 복제 이벤트 발생시 바이너리 로그로 인한 DDL문은 막을 수 없다.

 

InnoDB에서는 위 글로벌 락을 사용하지 않고 대신 백업락을 사용한다.

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

백업 중 복제로 인한 DDL 로그 이벤트 발생시 복제를 일시 중단 시키고 백업을 우선 처리한다.  

(ddl 잠금 처리는 별도의 락이 존재)

 

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

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

만일 레플리카 서버에서 글로벌 락을 획득하여 백업을 수행하는 과정 중에
소스 서버에서 ddl이 발생하여 레플리카 서버에 반영하게 되면 백업한 테이블 구조와 새롭게 반영한 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문이 존재하기에 DB에서 제공하는 락으로 제어가 가능하지만

insert는 db에서 제공하는 락이 마땅히 없기에 네임드 락을 활용하여 제어한 것으로 보인다.

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번과 같이 진행하면 테이블에 락을 걸고 위 두 작업의 원자성을 보장해준다.