[MySQL]MySQL 벼락치기(4) - 트랜잭션과잠금(1)

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다. 

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

트랜잭션

트랜잭션은 논리적인 작업셋을 모두 완벽하게 처리하거나 또는 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상(Partial update)이 발생하지 않게 만들어서 작업의 완전성을 보장해주기 위한 기능이다.
잠금(Lock) 과 트랜잭션은 서로 비슷한 개념 같지만 사실 잠금은 동시성(https://ko.wikipedia.org/wiki/ACID - Isolation)을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성(https://ko.wikipedia.org/wiki/ACID - Atomicity)을 보장하기 위한 기능이다. 

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눠볼 수 있다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치게 되지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다. 

글로벌 락(GLOBAL LOCK)

글로벌 락은 "FLUSH TABLES WITH READ LOCK" 명령으로만 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 일단 한 세션에서 글로벌 락을 획득하면 다른 세선에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될때까지 해당 문장이 대기상태로 남는다. 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며 작업 대상 테이블이나 데이터베이스가 다르다 하더라도 동일하게 영향을 미친다. 
글로벌 락을 해제하려면 "UNLOCK TABLES" 명령을 실행해야 한다. 

2개의 Session(배경 색깔별로 Session이 다르다) 을 이용한 예제로 확인해보자. 

Step1. 글로벌 락 획득하기

Step2. 별도의 Session 에서 Insert 명령어 실행하기 (글로벌 락이 해제될때까지 대기한다...)

Step3. 글로벌 락 해제하기

Step4. Insert 명령어가 실행된것 확인하기

테이블 락(TABLE LOCK)

테이블락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다. 명시적으로는 "LOCK TABLES table_name [ READ | WRITE ]" 명령으로 특정 테이블의 락을 획득 할 수 있다. 획득한 잠금을 해제하기 위해서는 글로벌락과 동일하게 "UNLOCK TABLES" 명령으로 잠금을 반납 할 수 있다.

이번에는 3개의 Session(배경 색깔별로 Session이 다르다) 을 이용한 예제로 확인해보자.

Step1. zipcode 라는 테이블에 테이블락을 걸어보자!

Step2. 별도의 Session 에서 zipcode 라는 테이블에 Insert 명령어를 실행시켜서 테이블락이 걸린것을 확인하자.

Step3. 또다른 Session에서 테이블락이 안걸린 다른테이블(test_table) 은 정상적으로 동작하는것을 확인하자.

Step4. 테이블락을 해제하자. 

Step5. 테이블락이 해제되면서 Insert 명령어가 실행된것을 확인하자. 

유저 락(USER LOCK)

유저 락은 단순히 사용자가 지정한 문자열에 대해 획득하고 반남하는 잠금이다. GET_LOCK 함수를 이용해서 임의로 잠금을 설정할 수 있다. 

위의 예제는 my_user_lock 이라는 유저락을 설정하고 이미 사용중이면 10초동안만 대기하도록 한다. 
그다음 my_user_lock 이라는 락이 잠금설정이 되어 있는지를 IS_FREE_LOCK 명령어를 통해서 확인하고
my_user_lock 이라는 락을 RELEASE_LOCK 명령어를 이용해서 해제한다!
그다음 다시한번 IS_FREE_LOCK 명령어로 락이 해제되었는지 확인한다.
3개의 명령어는 모두 정상적으로 락을 획득하거나 해제한경우에는 1을 아니면 NULL 이나 0을 반환한다. 

네임 락(NAME LOCK)

네임락은 데이터베이스 객체(테이블,뷰)의 이름을 변경하는 경우 획득하는 잠금이다. 네임 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니고 "RENAME TABLE tab_a TO tab_b" 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다. 

InnoDB 스토리지 엔진의 잠금

InnoDB 는 비관적 잠금을 사용하는데 비관적 잠금(Pessimistic locking)과 낙관적 잠금(Optimistic locking)에 대해서 짧게 알아보고 가자

• 비관적 잠금
  현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 먼저 획득하고 변경 작업을 처리하는 방식이다. 이 처리방식은 이름에서도 느낄수 있듯이 현재 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할 수 있다라는 비관적인 가정을 하기때문에 먼저 잠금을 획득한다.
• 낙관적 잠금
  트랜잭션이 같은 레코드를 변경할 가능성은 상당히 희박할 것이라고(낙관적으로) 가정한다. 그래서 변경작업을 수행하고 마지막에 잠금 충돌이 있었는지를 확인해 문제가 있었다면 ROLLBACK 처리를 하는 방식을 의미한다.

InnoDB 스토리지 엔진의 잠금 종류

• 레코드 락(RECORD LOCK)
  레코드 자체만을 잠그는 것을 레코드 락이라고 한다. 다른 상용 DBMS 와의 한가지 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다. 만약 인덱스가 하나도 없는 테이블이라 하더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. 프라이머리 키 또는 유니크 인덱스에 의한 변경작업은 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.
• 갭 락(GAP LOCK)
  갭 락은 레코드 그 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT) 되는 현상을 방지하는 것이다. 갭 락은 개념일 뿐이지 자체적으로 사용되지 않고 이어서 설명할 넥스트 키 락의 일부로 사용된다.
• 넥스트 키 락(NEXT-KEY LOCK)
  레코드 락과 갭 락을 합쳐놓은 형태의 잠금을 넥스트 키 락이라고 한다. InnoDB 에서 보조인덱스를 이용하는 변경작업은 넥스트 키 락을 사용한다.  
  
• 

넥스트 키 락(http://idea-sketch.tistory.com/46)에 대해서는 다음 포스팅에서 조금더 자세하게 알아볼 예정이다.

MySQL 의 격리 수준

트랜잭션의 격리수준은 동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것이다. 격리수준은 크게 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 의 4가지로 나뉘고 4개의 격리수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터격리 정도가 높아지며 동시에 동시성도 떨어지는것이 일반적이라고 볼 수 있다. 

데이터 베이스의 격리수준을 이야기하면 항상 함께 언급되는 3가지 부정합 문제점이 있다. 이 3가지 부정합의 문제는 격리 수준의 레벨에 따라 발생할 수 도 있고 발생하지 않을 수도있다. 

READ UNCOMMITTED

READ UNCOMMITTED 수준에서는 트랜잭션에서의 변경내용이 COMMIT 이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보여진다. 그리고 이러한 현상때문에 DIRTY READ 가 발생한다.
아래의 예를 보자.

Step1. 두개의 Session 을 준비하고 격리수준을 확인하자. 

Step2. Session 1개의 격리수준을 READ-UNCOMMITTED 로 변경하자. 

Step3. 하나의 Session 에서 트랜잭션을 걸고 INSERT를 실행한다. 

Step4. READ-UNCOMMITTED 격리수준인 Session 에서 해당 테이블을 조회한다!

Step5. INSERT 를 실행했던 Session 에서 ROLLBACK 을 하자

Step6. READ-UNCOMMITTED 격리수준인 Session 에서 다시 테이블을 조회한다!

이처럼 어떤 트랜잭션에서 처리한 작업이 완료(COMMIT/ROLLBACK)되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상을 더티리드(Dirty read) 라고 한다. 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 애플리케이션 개발자와 사용자를 상당히 혼란스럽게 만든다. 

READ COMMITTED

READ COMMITTED는 오라클 DBMS 에서 기본적으로 사용하는 격리 수준이며 온라인 서비스에서 가장 많이 선택되는 격리수준이다. 이 레벨에서는 위에서 언급한 더티리드와같은 현상은 발생하지 않는다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT 이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다. (완료전에 다른 트랜잭션에서 조회를 하면 언두영역에 백업된 레코드에서 값을 가져온다.)

하지만 READ COMMITTED 격리 수준에서도 "NON-REPEATABLE READ" 라 불리우는 부정합 문제가 있다. 
이번에도 예시를 통해 알아보자.

Step1. 두개의 Session 을 준비하고 격리수준을 확인하자. 

Step2. Session 1개의 격리수준을 READ-COMMITTED 로 변경하자. 

Step3. READ-COMMITTED Session 에서 트랜잭션을 걸고 데이터를 조회하자.

Step4. 다른 Session 에서 데이터를 INSERT 하고 COMMIT 하자. 

Step5. 다시한번 Step3 에서 실행한 쿼리를 재실행 해보자.

다른 트랜잭션에서 데이터를 INSERT 하고 COMMIT 한 후에는 READ COMMITTED 격리수준의 Session 에서 데이터가 조회된다. 이는 별다른 문제가 없어보이지만 사실 사용자가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 "REPEATABLE READ" 정합성에 어긋나는 것이다.

REPEATABLE READ

REPEATABLE READ는 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다. REPEATABLE READ 와 READ COMMITTED 의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하는지에 있다. 이 격리수준에서는 위에서 설명한 NON-REPEATABLE READ 이 발생하지 않는다. NON-REPEATABLE READ가 발생하지 않는 이유는 만약 REPEATABLE READ 격리수준에서 바로 위의 예제를 다시실행해보면 Step3. 에서 트랜잭션을 생성할때 트랜잭션 번호를 부여받게 되는데 그때부터 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 작은 트랜잭션 번호에서 변경한 것만 보이게되기 때문이다. 그래서 Step4 에서 할당한 트랜잭션의 번호가 Step3 보다 높기때문에 Step3 에서 할당받은 트랜잭션에서는 INSERT 한 정보가 보이지 않게된다.

REPEATABLE READ 에서도 Phantom rows(https://dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html) 라는 부정합이 발생할 수 있는데 InnoDB 에서는 넥스트 키 락에 의해서 부정합이 발생하지 않는다. Phantom rows 에 대해서는 아래의 그림으로 보자.   

Phantom rows 가 발생하는 이유는 SELECT ... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기잠금을 걸어야 하는데 언두 영역의 레코드에는 잠금을 걸수가 없다 그래서 SELECT ... FOR UPDATE 나 SELECT ... LOCK IN SHARE MODE 로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것이다. 

SERIALIZABLE

SERIALIZABLE 레벨에서는 읽기 작업도 공유잠금(읽기잠금)을 획득해야 하며 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.  즉 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근 할 수 없게 된다. 

쓰기잠금과 읽기잠금

잠금에 대해서 얘기하다 보면 배타적 잠금(쓰기 잠금)과 공유 잠금(읽기 잠금)에 대해서 얘기하게 된다. 이 둘에 대해서 한번 알아보자.

• 배타적 잠금(쓰기 잠금)
• 배타적 잠금은 해당 트랜잭션에서 그 레코드나 간격을 변경하기 위해 획득해야 하는 잠금이고 내가 쓰기를 하는 동안 남들이 쓰지 못하게 하기 위해 획득하는 잠금이다
• 공유 잠금(읽기 잠금)
• 공유 잠금은 해당 트랜잭션에서 그 레코드나 간격을 읽을 때 다른 트랜잭션이 변경하지 못하게 하는 용도로 획득하는 잠금이다. 이는 내가 읽는 동안 남들이 내가 읽고 있는 데이터를 변경하거나 삭제하지 못하게 하는 장치이다. 

쓰기잠금과 읽기잠금 둘다 잠금을 건 데이터를 변경하거나 삭제하지 못하게 하는 작업은 똑같은데 둘의 차이점은 쓰기 잠금은 하나의 트랜잭션이 점유하면 다른 트랜잭션은 선점된 점유가 풀릴때까지 기다려야 하지만 읽기 잠금은 다른 점유가 풀릴때까지 기다리지 않고 다른 트랜잭션들과 읽기잠금을 공유 할 수 있다는 것이다. 

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~