MySQL의 S-Lock, X-Lock 그리고 트렌잭션 격리수준까지 알아보자

오라클에서 발생하지 않을 법한 일들이 MySQL에서는 발생합니다. Oracle 신봉자들은 이런 일이 발생할 때마다 "그래서 오라클을 써야 해요"라고 말하지요. 하지만 난 생각이 다릅니다. '어떤 물건이건 그 가치에 맞게 활용한다면 된다'는 주의입니다. 우리는 왜 MySQL은 그런 Lock 메커니즘을 채택했는지 알면 그에 맞게 잘 사용할 수 있습니다. 
 

MySQL의 Lock 매커니즘

 

발생한 사건

현상은 이랬습니다. 제가 테이블을 ALTER문을 이용해 변경 작업을 해야 하는데, 특정 Select문이 Lock을 잡고 있어서 해당 테이블에 변경되지 않고 대기하는 상황이 발생했습니다. 근데 그 Lock은 김사원의 Session에서 발생하고 있었죠. 김사원은 단지 select만 했는데 Lock을 잡는다는 게 이해가 안 되더군요. 
 
그래서 이 문제를 분석해서 해결하기 위한 '김사원'과 테스트를 해 보기로 했습니다. MySQL의 Lock 메커니즘은 어떻길래 이런 현상이 발생하는 것일까요? 우선 MySQL의 Lock 메커니즘을 알기 위해서 S-Lock과 X-Lock부터 알아보겠습니다. 

S-Lock vs X-Lock

MySQL에서 데이터베이스 일관성을 위해 S-Lock과 X-Lock 두 가지 Lock이 있습니다. 보통 S-Lock을 먼저 설명하지만 이야기의 전개상 X-Lock을 먼저 설명하겠습니다.
 

1.X-Lock(Exclusive Lock)

X-Lock은 배타적인 Lock입니다. 하나의 트랜잭션만이 해당 테이블을 읽거나 쓸 수 있도록 허용하죠. X-Lock이 걸려있는 동안은 다른 트랜잭션이 S-Lock이나 X-Lock를 걸 수 없습니다. 내가 수정 완료하기 전까지는 기다리라는 Lock이죠. 여기서 S-Lock은 일반적인 조회(select)를 말하는 것은 아닙니다.

보통 데이터를 수정하는 DML, DDL 작업(Insert, Update, Delete 등)할 경우 발생합니다. 어떤 이는 그래서 쓰기 Lock이라고 설명하기도 합니다. 하지만 정확한 표현은 나 말고는 허용하지 않는 배타적인 Lock이 옳은 표현이죠.
 
이는 데이터 정합성을 위해서 꼭 필요한 Lock이기 때문에 누구나 그게 맞다고 생각할 겁니다. 내가 데이터를 수정하고 있을 때 다른 사람이 수정하면 꼬이잖아요. 그래서 다 수정이 완료된 후, 즉 commit 될 때까지 기다리는 것이죠.
 

2.S-Lock(Shared Lock)

다음은 S-Lock인데요. 이름의 Shared가 있어서 공유하는 Lock이라는 것을 알 수 있죠. 여러 트랜잭션이 동시에 동일한 리소스를 읽을 수 있습니다. 예를 들어, 같은 테이블을 서로 동시에 읽을 수 있는 것이죠.
 
이 것 또한 아마 당연히 되어야 한다고 생각할 것입니다. 왜냐하면 내가 조회하고 있다고 남들이 조회를 할 수 없다면 시스템의 활용도가 많이 떨어지겠죠.

그런데 약간 의아한 것이 있습니다. S-Lock은 다른 트랜잭션이 해당 테이블을 수정하는 것은 허용하지 않습니다. 이게 무슨 말이냐고요? S-lock이 걸려있는 동안에 X-Lock을 허용하지 않는다는 말입니다. 쉽게 말해 S-Lock이 걸려있으면, 테이블 수정을 할 수 없다는 말이죠.

여기서 김사원이 그러더군요. "왜 조회만 하는데 Lock이 생기죠?" 나또한 궁금한 부분이었습니다. 이는 다른 사용자가 Select만 했을 뿐인데 DBA가 테이블을 수정할 수 없는 일이 발생합니다. "MySQL 왜 이러는 걸까요?" 이 이유를 알려면 MySQL의 격리 수준을 알아야 합니다.

Transaction Isolation Level

MySQL에서는 트랜잭션 격리수준을 4가지로 규정하고 있습니다. 이는 여러 트랜잭션이 동시에 작업을 할 때, 특정 트랜잭션이 수정하거나 조회하는 데이터를 볼 수 있도록 허용하는 정도를 말합니다. 

1. READ UNCOMMITTED 

모든 트랜잭션이 commit되지 않은 데이터를 읽을 수 있습니다. INSERT, UPDATE, DELETE 후 commit, rollback 하지 않아도 현재 데이터를 읽어 옵니다. 그렇기 때문에 select 쿼리뿐 아니라 모든 경우 Lock이 발생하지 않습니다. 

2. READ COMMITTED

트랜잭션이 commit한 데이터만 읽을 수 있습니다. 많은 양의 데이터를 복제하거나 처리할 때 적합한 Level입니다. 오라클 database에서 기본 옵션인 Isolation Level입니다.

오라클에서는 일반적인 Select 쿼리는 commit이 의미가 없습니다. 그래서 Lock이 발생하지 않습니다. 하지만 MySQL의 경우는 명시적 commit을 하지 않거나, autocommit 설정을 하지 않으면 S-Lock이 걸립니다.
 
또한, select … for update, select … lock in share mode와 같은 쿼리는 당연히 S-Lock이 발생하게 됩니다. 

3. REPEATABLE READ

MySQL에서는 이 수준이 default입니다. Select시 현재 시점의 스냅샷을 만들고 그 스냅샷을 조회합니다. 이 Level에서는 Recode Lock, Gap Lock이 발생합니다. Create select문이나, Insert select문 작업 시 X-Lock이 발생하고, select 쿼리에서는 S-Lock이 발생합니다. 

4. SERIALIZABLE

가장 높은 격리수준으로 모든 트랜잭션이 순차적으로 처리됩니다. Select문을 사용할 때도 테이블에 S-Lock이 발생합니다. 
 

데이터베이스 설정 확인

자 이제 database의 설정을 확인할 차례입니다. 김사원과 나는 테스트 준비를 마치고, 우선 현재 dbms의 Transaction Isolation Level부터 확인해 보았습니다.

SELECT @@GLOBAL.transaction_isolation SELECT @@SESSION.transaction_isolation; show variables like 'transaction_isolation';

 
그런데 결과는 'READ-COMMITTED' 였습니다. 예상했던 MySQL의 디폴트 값인 'REPEATABLE READ'가 아닌 덜 엄격한 Level을 확인하게 되었죠. 그렇다면 commit되지 않은 데이터라 S-Lock이 걸렸다고 생각해 볼 수 있습니다.
 
바로 autocommit 옵션이 'ON' 되어 있지 않기 때문에 select문에서 S-Lock이 발생하고 있다고 추론할 수 있습니다. 그러면 바로 확인해 보시죠.

SELECT @@autocommit ; SHOW VARIABLES LIKE 'autocommit';

결과는 autocommit이 ‘ON’된 상태입니다. ‘엥~ 무엇이 문제지?’하며 하나 집히는 게 있었습니다. 바로 특정 세션인 '김사원' PC에서만 Lock이 발생했다는 것이었죠. 그래서 바로 김사원의 DBeaver의 설정을 확인했습니다. 먼저 아래 그림처럼 파일 메뉴에서 설정을 클릭하시기 바랍니다.
 

DBeaver 파일 - 설정

 
설정에서 연결을 클릭하고 다시 Global settings라는 글자를 클릭해야 합니다. 

설정 - 연결 - Global settings

 
아래와 같은 창이 나오는데요. 연결 유형을 클릭하면 Auto-commit by default 체크박스가 있습니다. 

Global settings - 연결 유형 - Auto-commit by default

 
확인했더니, 김사원의 DBeaver는 autocommit이 설정되어 있지 않았습니다. 참고로 설정말고 SQL편집창에서 세션의 autocommit을 확인하는 명령어는 아래와 같습니다. 

SELECT @@SESSION.autocommit;

 
김사원의 설정을 변경했으니, 김사원은 기존 select 쿼리를 실행하고, 난 다시 그 테이블을 ALTER문을 실행했습니다. Auto-commit 설정 변경 전에는 Lock걸려 ALTER문이 완료되지 않았는데요. 이번에는 바로 ALTER가 완료되었네요. 
 

결론

테스트를 완료한 후 몇가지 추론을 할 수 있었습니다. DBeaver 툴은 대이터베이스의 설정과 상관없이 세션 레벨의 설정을 가지고 들어간다. 예들 들어, autocommit을 체크 안한 경우 'OFF'로 들어간다.

그래서 MySQL에서 select시 S-Lock을 잡고 있지 않기 위해서는 autocommit을 ‘ON’으로 해야합니다. 그런데도 문제가 있는 경우는 Session Level에서 autocommit이 'OFF'되어 있을 수 있으니 꼭 확인해 보시기 바랍니다.