[Real MySQL 정리], 4장 트랜잭션과 잠금

트랜잭션과 잠금

트랜잭션

MySQL에서의 트랜잭션

트랜잭션은 작업의 완전성을 보장해주는 것이다. 즉 논리적인 작업 셋을 모두 완벽하게 처리하거나 또는 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용 되는 현상이 발생하지 않게 만들어주는 기능이다.

 

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 떄만 의미있는 개념은 아니다. 트랜잭션은 하나의 논리
적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나(COMMIT을 실행했을 때) 또는 아무것도 적용되지 않아야(ROLLBACK 또는 트랜잭션을 ROLLBACK 시키는 오류가 발생했을 때)함을 보장해 주는 것이다.

주의사항

• 일반적으로 데이터베이스 커넥션은 개수가 제한적이라서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 개수는 줄어들 것이다. 그리고 어느 순간에는 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상횡이 발생할 수도 있다.
• 메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 어떻게 해서든 DBMS의 트랜잭션 내에서 제거하는 것이 좋다. 프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버뿐만 아니라 DBMS 서버까지 위험해지는 상황이 발생 할 것이다.
• 사용자가 입력한 정보를 저장하는 작업을 하나의 트랜잭션으로 묶는다. 하지만 저장된 데이터의 단순 확인 및 조회등은 트랜 잭션에 포함할 필요 없다.

MySQL 엔진의 잠금

MySQL애서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눠볼 수 있다. MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 되는데, MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치게 되지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다. MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 말고도 사용자의 필요에 맞게 사용할 수 있는 유저 락과 테이블 명에 대한 잠금을 위한 네임 락이라는 것도 제공한다.

글로벌 락(GLOBAL LOCK)

• 글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로만 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
• 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.

테이블 락(TABLE LOCK)

• 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다. 명시적으로는 LOCK TABLES table_name[ READ | WRITE ] 명령으로 특정 테이블의 락을 획득할 수 있다.
• 명시적으로 획득한 잠금은 UNLOCK TABLES명령으로 잠금을 반납(해제)할 수 있다.
• InnoDB 테이블에서는 테이블 락이 설정되지만 대부분 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

유저 락(USER LOCK)

• GET_LOCK() 함수를 이용해 임의로 잠금을 설정할 수 있다. 이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다. 유저락은 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금이다.
• 잘 사용되지는 않지만, 서버 1대에 5대의 웹 서버가 접속해서 서비스를 하고 있는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 여러 클라이언트가 상호 동기화를 처리해야할 때 데이터 베이스의 유저 락을 이용하면 쉽게 해결할 수 있다.
• 또한 많은 레코드를 한 번에 변경하는 트랜잭션의 경우에 유용하게 사용할 수 있다. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 한다. 이러한 경우에 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 유저 락을 걸고 쿼리를 실행 하면 아주 간단히 해결할 수 있다.

네임 락

• 데이터베이스 객체(대표적으로 테이블이나 뷰)의 이름을 변경하는 경우 획득하는 잠금이다. 네임락은 명시적으로 획득하거나 해체할 수 있는 것이 아니고 RENAME TABLE tab_a TO tab_b같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다. RENAME TABLE 명령의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다.

InnoDB 스토리지 엔진의 잠금

• InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다.
• MySQL 5.1부터 InnoDB 플러그인 스토리지 엔진이 도입되면서부터 InnoDB의 트랜잭션과 잠금 그리고 대기중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다.
• MySQL 서버 INFORMATION_SCHEMA라는 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고,해당 잠금은 어느 트랜잭션이 가지고 있는지 확인할 수 있으며 장시간 잠금을 가지고 있는 클라이언트를 종료시키는 것도 가능하다.

InnoDB의 잠금 방식

비관적 잠금

• 현재 트랜잭션에서 변경하고자 하는 레코드에 비해 잠금을 획득하고 변경 작업을 처리하는 방식을 비관적 잠금이라고 한다
• 일반적으로 높은 동시성 처리에는 비관적 잠금이 유리하다고 알려져 있으며 InnoDB는 비관적 잠금 방식을 채택하고 있다.

낙관적 잠금

• 낙관적 잠금에서는 기본적으로 각 트랜잭션이 같은 레코드를 변경할 가능성은 상당히 희박할것이라고 가정한다.
• 그래서 우선 변경 작업을 수행하고 마지막에 잠금 충돌이 있었는지 확인해 문제가 있었다면 ROLLBACK 처리하는 방식을 의미한다.

InnoDB

• InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이
  페이지 락으로 또는 테이블 락으로 레벨업되는 경우(락 에스컬레이션)는 없다.
• InnoDB 스토리지 엔진에서는 레코드 락뿐 아니라 레코드와 코드 사이의 간격을 잠그는 갭락이라는 것이 존재한다.

레코드 락

• 레코드 자체만을 잠근 것을 레코드 락이라고 한다. InnoDB 스토리징 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
• 만약 인덱스가 하나도 없는 테이블이라 하더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
• 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업은 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.

갭락

• 갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.
• 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다.
• 넥스트 키의 일부로 사용된다.

넥스트 키 락

• 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다.
• innodb_locks_unsafe_for_binlog 파라미터가 비활성화되려면(파라미터 값이 0으로 설정되면) 변경을 위해 검색하는 레코드에는
  넥스트 키 락 방식으로 잠금이 걸린다.
• InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서 만들어낸 결과와 동일한
  결과를 만들어내도록 보장하는 것이 주 목적이다.

자동 증가 락(AUTO_INCREMENT lock)

• AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 AUTO_INCREMENT 락이 걸렸다가 즉시 해제된다.
• AUTO_INCREMENT은 테이블에서 단 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO_INCREMENT 락을 걸게 되면 나머지 쿼리는 AUTO_INCREMENT 락을 기다려야 한다.
• MySQL 5.1 이상부터느 Innodb_autoinc_lock_mode라는 파라미터를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.
  MySQL 서버가 INSERT 되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가락을 사용하지 않고, 훨씬 빠른 래치(뮤텍스)를 이용해 처리한다.

인덱스와 잠금

• InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.
• 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠가야 한다.
• 만약 테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 작업을 한다. 이럴경우 테이블에 있는 모든 레코드를 잠그게
  되기 때문에 좋은 설계라 할 수 없다

트랜잭션 격리 수준(isolation Level)

동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록
허용할지 말지를 결정하는 것이다. 격리 수준은 크게

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

"DIRTY READ"라고도 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서는 거의 사용하지 않고, SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다. 4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시에 도이성도 떨어지는 것이 일반적이라고 볼 수 있다. 격리 수준이 높아진다고해서 서버의 처리 성능이 많이 떨어지지는 않는다. SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.

	DIRTY	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	발생하지 않음	발생	발생
REPEATABLE READ	발생하지 않음	발생하지않음	발생(InnoDB는 발생하지 않음
SERIALIZABLE	발생하지 않음	발생하지 않음	발생하지 않음

일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITTED와 REPEATABLE READ 둘 중에서 하나를 사용한다.
MySQL에서는 REPEATABLE READ를 주로 사용한다.

트랜잭션 격리 수준의 필요성

• 데이터베이스는 ACID 같이 트랜잭션이 원자적이면서도 독립적인 수행을 하도록 한다.
• 트랜잭션이 DB를 다루는 동안 다른 트랜잭션이 관여하지 못하게 막는 Locking 이라는 개념이 등장한다.
• 무조건적인 Locking 으로 동시에 수행되는 많은 트랜잭션들을 순서대로 처리할 시에 DB 성능은 떨어지게 된다.
• 반대로, 응답성을 높이기 위해 Locking 범위를 줄인다면 잘못된 값이 처리 될 여지가 있다.
• 최대한 효율적인 Locking 방법이 필요하다.

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 아래 그림과 같이 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보여진다.

• 사용자 A는 emp_no가 500000이고 first_name이 "Lara"인 새로운 사원을 INSERT 하고 있다.
• 사용자 B가 변경된 내용을 커밋하기도 전에 사용자 B는 emp_no=500000인 사원을 검색하고 있다.
• 하지만 사용자 B는 사용자 A가 INSERT한 사원의 정보를 커밋되지 않은 상태에서도 조회할 수 있다.
• 이때 문제는 만약 사용자 A가 처리 도중 알 수 없는 문제가 발생해 INSERT된 내용을 롤백해버린다 하더라도 여전히
  사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 계속해서 처리하게 되리라는 것이다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상을 더티 리드라 하고, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED다.

 

이 레벨은 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리수준이다. MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다.

READ COMMITED

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

• 사용자 A는 emp_no = 500000인 사원의 first_name을 "Lara"에서 "Toto"로 변경했는데, 이때 새로운 값인 "Toto"는
  employees 테이블에 즉시 기록되고 이전 값인 "Lara"는 언두 영역으로 백업된다.
• 사용자 A가 커밋을 수행하기 전에 사용자 B가 emp_no=500000인 사원을 SELECT하면 조회된 결과의 first_name 칼럼의 값은
  "Toto"가 아니라 "Lara"로 조회된다.
• 여기서 사용자 B의 SELECT 쿼리 결과는 employees 테이블이 아니라언두 영역에 백업된 레코드에서 가져온 것이다.

READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없기 떄문이다. 최종적으로 사용자 A가 변경된 내용을 커밋하면 그때부터는 다른 트랜잭션에서도 백업된 언두 레코드("Lara") 가 아니라 새롭게 변경된 "Toto"라 하는 값을 참조할 수 있게 된다.

 

바이너리 로그(STATEMENT 포맷을 사용하는)가 활성된 MySQL 서버에서는 READ COMMITTED 격리 수준을 사용할 수 없다. 하지만 이 격리 수준에서도 NON-REPEATABLE READ라는 부정합 문제가 있다.

사용자 B가 BEING 명령으로 트랜잭션을 시작하고 first_name이 "Toto%"인 사용자를 검색했는데 일치하는 결과가 없었다. 하지만 A가 사원번혹 50000인 사원의 이름을 "Toto"로 변경하고 커밋을 실핸한 이후, 사용자 B가 똑같은 SELECT 쿼리로 다시 조회하면 이번에는 결과가 1건이 조회된다. 사실 사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ정합성에 어긋나는 것이다.

REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDVB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL의 장비에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다. 이 격리 수준에서는 NON REPEATABLE READ부정합이 발생하지 않는다.

 

REPEATABLE READ는 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장한다. 이 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다. 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한 것은 아니다.

더 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 하는 것이다.

시나리오가 실행되기 전에 employees 테이블은 번호가 6인 트랜잭션에 의해 INSERT됐다고 가정하자. 그래서 그림 employess 테이블의 초기 두 레코드는 트렌잭션 번호가 6인 것으로 표현된 것이다. 그림의 시나리오에서는 emp_no가 500000인 사원의 이름을 변경하는 과정에서 사용자 B가 emp_no=50000인 사원을 SELECT 할때 어떤 과정을 거쳐서 처리되는지 보여준다.

• 사용자 A의 트랜잭션 번호는 12였으며 사용자 B의 트랜잭션 번호는 10이었다. 이때 사용자 A는 사원의 이름을 "Toto"로 변경하고 커밋을 수행한다.
• 그런데 사용자 B가 emp_no=500000인 사원 A 트랜잭션이 변경을 실행하기 전과 변경을 실행한 후에 각각 한번 씩 SELECT했지만, A 트랜잭션이 변경을 수행하고 커밋을 했음해도 상항 변하지 않고 "Lara"라는 값을 SELECT한다.
• 사용자 B가 BEGIn 명령으로 트랜잭션을 시작하면서 10번이라는 트랜잭션 번호를 부여받았는데, 그때부터 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.

REPEATABLE READ격리 수준에서도 다음과 같은 부정합이 발생할 수 있다

• 그림에서 사용자 B는 BEGIN 명령으로 트랜잭션을 시작한 후, SELECT를 수행하고 있다. 그러므로 REPEATABLE READ에서 배운 것처럼 두 번이 SELECT 쿼리 결과는 똑같아야 한다.
• 하지만 그림에서 사용자 B가 실행하는 두 번의 SELECT .. FOR UPDATE 쿼리 결과는 서로 다르다.
• 이렇게 다른 트랜잭션에서 수행한 변경작업에 의해 레코드가 보였다가 안 보였다가 하는 현상을 PHANTOM READ라고 한다.
• SELECT .. FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다. 그래서 FOR UPDATE나 SELECT .. LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경된 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것이다.

SERIALIZABLE

• 가장 단순한 격리 수준이지만 가장 엄격한 격리 수준이다.
• 성능 측면에서는 동시 처리 성능이 가장 낮다.
• SERIALIZABLE 에서는 PHANTOM READ 가 발생하지 않는다. 하지만, DB 에서 거의 사용되지 않는다.

---

참고 문헌 :
개발자외 DBA를 위한 Real MySQL / 이성민