[MySQL] 잠금은 어떻게 이뤄졌는가

안녕하세요. 백엔드 개발과 인프라 운영을 동시에 하던 (그리고 지금도 배우고 있는) 개발자입니다. 오늘은 과거 MySQL을 잘 모르던 시절, "DB는 쿼리가 전부"라고 착각하며 저질렀던 창피한 실수들과 그로부터 배운 교훈들을 공유하고자 합니다.

1. 운영서버에 mysqldump

예전 백엔드 개발과 동시에 인프라를 운영하던 꼬꼬마 시절이 있었습니다. (지금도 꼬꼬마입니다) 보수적이다보니 항상 백업에 대한 부분에 대해서 민감했는데 AWS RDS 백업설정을 다했음에도 무언가 작업 시 어떤 이슈가 터질지 몰라서 쉘을 통한 백업을 항상 수행해왔습니다.

 

물론 저는 MySQL을 잘 모르던 시절 백업을 하다보니 운영서버를 mysqldump를 뜨는 미친 짓을 수행했었죠 그리고 간간히 서버를 멈추게 만들었습니다. 창피한 얘기지만 DB는 쿼리가 전부라고 착각하던 시절이 있었고 그러다보니 왜 멈추는지 모르고 당연히 데이터가 많아서 멈추는 것이기에 이런 문제가 발생했다고 생각했습니다

 

mysqldump는 데이터의 일관성을 맞추기 위해 특정 옵션없이는 테이블에 락(Lock)을 획득하려 시도합니다. 운영 서버의 테이블에 락이 걸리니, 다른 모든 트랜잭션이 대기 상태에 빠지면서 서비스 전체가 멈췄던 것입니다.

 

이 문제는 나중에 클러스터를 도입하고 스탠바이 Readonly 클러스터에서 백업 스크립트를 수행하면서 멈추는 일은 없었습니다.
물론 이후에 그냥 RDS 백업과 로그를 통해 해결이 가능하다는 것을 이해하고 백업을 수행하지 않거나 아니면 외부 툴을 통해 락없이 Dump를 수행하는 방법을 알았습니다.

2. 인덱스 과감하게 걸고 과감히 삭제

한창 EXPLAIN을 통한 실행 계획 분석과 인덱스 튜닝에 재미를 붙이던 시절이 있었습니다. 엉망진창이었던 쿼리 성능이 인덱스 하나로 극적으로 개선되는 것을 보며, 꽤 과감하게 인덱스를 적용해보곤 했습니다.

물론 락(Lock)에 대한 개념을 어렴풋이 공부하던 시기였지만, ALTER TABLE이 운영에 어느 정도의 영향을 주는지에 대해서는 깊이 테스트해보지 않았습니다.

결국 사건은 터졌습니다. 전날 잘못 적용한 인덱스가 다음 날 운영 환경에서 심각한 슬로우 쿼리)를 유발했습니다. 문제를 해결하기 위해 저는 급하게 인덱스를 재정의해야 했습니다.

인덱스를 변경하는 행위는 메타 데이터 락으로 인해 각종 쿼리에 문제를 발생시킵니다.

3. Key 없는 UPDATE와 DELETE

현재에서 저는 다양한 코드를 보면서 안타까운 경우를 발견하곤합니다. update where 또는 delete where에 키가 전혀 없는 케이스입니다. 이는 두 가지 문제를 야기하는데 1. 동시성 문제, 2. 인덱스 미포함을 통한 성능문제를 야기합니다

 

당장은 문제가 없을거라고 생각할 수도 있지만 데이터가 누적되면서 점점 많은 양이 쌓인 테이블은 결국 병목을 만들어 낼 수 있습니다.

그렇게에 MySQL workbench 내에서 Safe모드를 켤 경우 키가 포함되어있지 않으면 쿼리가 실행되지 않습니다. 락이 어떻게 발생되는지 확인하는 방법은 생각보다 쉽습니다

set autocommit = 0;

// 업데이트 수행

update 
	sbtest1
set
	c = 1
where id = 1;

먼저 오토커밋을 꺼줍니다.
이후에 pk 기반으로 업데이트를 수행한 뒤 SELECT * FROM performance_schema.data_locks를 통해 현재 락을 확인해보겠습니다

 

 

 

위 이미지와 같이 레코드 락이 발생되는 것을 확인할 수 있습니다. 위에 있는 테이블 락의 경우는 레코드 락이 수행되는 동안 다른 DDL이 발생되지 않도록 미리 알려주는 것입니다. DDL 수행 전 만약 이렇게 하지않는다면 모든 레코드를 검색해서 락을 체크해야하는 비효율적인 비용을 발생시키기 때문입니다

 

다음은 키가 없는 테이블 업데이트를 수행해보고자합니다. 단 1건만요 쿼리는 아래와 같습니다

update 
	sbtest1
set
	k = 1
where c = '84211604163-09260634180-97255104782-97863283580-25667516789-86310487231-50087627122-42096455221-64933882550-28524194254';

 

그 다음에 락을 확인해봅니다

 

인덱스 기반으로 락을 수행하기 때문에 (성능적인 이점, 동시성 극대화) 인덱스가 안걸린 조건의 update와 delete는 테이블 풀스캔을 발생시킵니다. 

 

너무 당연한 내용이지만 생각보다 인지하지 않고 쿼리를 작성하는 케이스를 봤습니다. 사실 현업에서 키를 제외하고 update와 delete를 수행하는 경우는 많이 없지만 항상 예외는 발생합니다. 

추가로 MySQL WorkBench Safe Mode에서는 다음과 같은 에러 코드를 볼 수 있습니다

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

위 Safe모드에서 막는 것들은 안티패턴이기 때문에 실제 코드를 작성할 때 유의하시는 것이 좋습니다