[Real MySQL] 4장. 트랜잭션과 잠금

트랜잭션 vs 잠금

트랜잭션

데이터의 정합성을 보장하기 위한 기능

논리적인 작업셋을 모두 완벽하게 처리하거나 처리하지 못할 경우 원 상태로 복구해서 작업의 일부만 적용되는 현상(Partial Update)이 발생하지 않게 만들어주는 기능.

 

잠금

동시성을 제어하기 위한 기능

여러 커넥션에서 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할

 

---

1. 트랜잭션

MySQL 스토리지 엔진별 트랜잭션 지원

스토리지 엔진 중 MyISAM, MEMOERY은 트랜잭션을 지원하지 않음.

반면 InnoDB는 트랜잭션을 지원함. (참고로 트랜잭션은 개발자를 괴롭히는 것이 아니라, 애플리케이션 개발에서 고민해야 할 문제를 엄청 줄여주는 천사 같은 기능이다)

 

트랜잭션 주의사항

트랜잭션의 범위를 최소화하자.

특히 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 어떻게 해서든 DBMS의 트랜잭션 내에서 제거하는 것이 좋다.

---

2. MySQL 잠금의 종류

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눠볼 수 있음.

 

MySQL 엔진 레벨의 잠금

• 모든 스토리지 엔진에 영향을 미침

 

스토리지 엔진 레벨의 잠금

• 스토리지 엔진 간 상호 영향을 미치지 않음

 

MySQL 엔진의 잠금

글로벌 락

• MySQL 서버 전체에 미치는 락.

 

테이블 락

• 개별 테이블 단위로 설정되는 잠금
• MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 묵시적인 테이블 락이 발생한다.
• 반면 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반 잠금을 제공해서 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지 않음 (대부분 DDL의 경우에만 영향을 미침)

 

유저 락

• 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금. (잘 사용 하지 않음)

 

네임 락

• 데이터베이스 객체(테이블, 뷰 등)의 이름을 변경하는 경우 획득하는 잠금.

 

3. MyISAM, MEMORY 스토리지 엔진의 잠금

MyISAM이나 MEMORY 스토리지 엔진은 자체적인 잠금을 가지지 않고, MySQL 엔진에서 제공하는 테이블 락을 그대로 사용한다.

 

4. InnoDB 스토리지 엔진의 잠금

MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재. (덕분에 훨씬 뛰어난 동시성 처리를 제공)

InnoDB는 비관적 잠금 방식을 채택.

 

cf) 비관적 잠금 vs 낙관적 잠금

비관적 잠금

• 잠금을 획득하고 변경 작업을 처리하는 방식
• 일반적으로 높은 동시성 처리에는 비관적 잠금이 유리

 

낙관적 잠금

• 우선 변경 작업을 수행하고 마지막에 잠금 충돌이 있었는지 확인해 문제가 있었다면 롤백하는 방식

 

InnoDB의 잠금 종류

레코드 락

• 레코드 자체만을 잠그는 락
• InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

 

갭(GAP) 락

• 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 락
• 레코드와 레코드 사이의 간격에 새로운 레코드가 INSERT되는 것을 제어

 

넥스트 키 락

• 레코드 락과 갭 락을 합쳐놓은 형태의 잠금
• STATEMENT 포맷의 바이너리 로그를 사용하는 (백업을 위해) MySQL 서버에서는 REPEATABLE-READ 격리 수준을 사용해야 한다.
• InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적.
• 근데, 의외로 이 두 락에 의해 데드락이 발생하는 등 문제가 자주 발생한다
• 따라서 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.
• (그러나, ROW 포맷 로그는 그다지 널리 사용되지 않아 안정성을 확인하는 것이 어려운 상태이며, 바이너리 로그에 비해 로그 파일의 크기가 상당히 커질 가능성이 많음)

 

자동 증가 락 (Auto increment lock)

• INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 AUTO_INCREMENT 락이 걸렸다가 즉시 해제된다.
• 아주 짧은 시간 동안만 걸렸다가 해제되는 잠금이라 대부분의 경우 문제 되지 않는다.

---

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.

즉 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠그는 방식.

만약 테이블에 인덱스가 하나도 없다면, 테이블을 풀 스캔 하면서 UPDATE 작업을 하는데, 이 과정에서 테이블에 있는 모든 레코드를 잠그게 된다 ⇒ MySQL의 InnoDB에서 인덱스 설계가 중요한 이유

---

트랜잭션 격리 수준과 잠금 TIP

불필요한 레코드의 잠금 현상은 InnoDB의 넥스트 키 락 때문에 발생.

InnoDB에서 넥스트 키 락을 필요하게 만드는 주 원인은 바로 복제를 위한 바이너리 로그이다.

따라서 레코드 기반의 바이너리 로그 (Row based binary log)를 사용하거나 바이너리 로그를 사용하지 않는 경우에는 InnoDB의 갭 락이나 넥스트 키 락의 사용을 대폭 줄일 수 있다.

---

5. MySQL의 격리 수준

트랜잭션 격리 수준

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

 

5-1. READ UNCOMMITED

• 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.
• 더티 리드: 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상

READ UNCOMMITTED

 

5-2. READ COMMITED

• 더티 리드가 발생하지 않음. (SELECT 쿼리 결과가 언두 영역에 백업된 레코드에서 가져오기 때문에)
• 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋난다.

READ COMMITED

cf) READ COMMITED, REPEATABLE READ에서의 트랜잭션 내, 밖에서의 SELECT 차이

READ COMMITED

• 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없음.

REPEATABLE READ

• 트랜잭션 내에서 실행되는 SELECT는 온종일 동일한 쿼리를 반복해서 실행해봐도 동일한 결과만 보게 된다(아무리 다른 트랜잭션에서 그 데이터를 변경하고 COMMIT을 실행하더라도)

 

5-3. REPEATABLE READ

MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준.

바이너리 로그를 가진 MySQL 장비에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.

REPETABLE READ는 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장한다.

(InnnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가)를 가지며, 언두 영역에 백업된 모든 레코드는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다.)

• REPEATABLE READ에서도 PHANTOM READ라는 다른 트랜잭션에서 수행한 작업에 의해 레코드가 보였다가 안 보였다가 하는 현상이 발생한다. (InnoDB에서는 특성으로 인해 발생하지 않는다)

 

5-4. SERIALIZABLE

• 가장 엄격한 격리 수준
• 읽기 작업도 공유 잠금을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.
• InnoDB 스토리지 엔진에서는 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에, 굳이 SERIALIZABLE을 사용할 필요가 없음.