Mysql - 트랜잭션과 잠금

트랜잭션과 잠금

---

• 트랜잭션은 작업의 완전성을 보장해주는 기능입니다. 즉 논리적인 작업을 모두 완벽하게 처리하거나 또는 처리하지 못하는 경우에는 롤백해서 작업의 일부만 적용되는 현상이 발생하지 않도록 방지해주는 기능입니다.
• 잠금이란 여러 커넥션에서 동시에 동일한 레코드를 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있도록 해주는 역할을 합니다.
• 잠금은 동시성을 제어하기 위한 기능이고, 트랜잭션은 데이터의 정합성을 보장하기 위한 기능입니다.
• 격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지 결정하는 레벨을 의미합니다.

 

InnoDB와 MyISAM 스토리지 엔진의 트랜잭션 비교

• 아래 예제를 살행하면 모두 프라이머리 키 중복으로 인하여 에러가 발생합니다. 하지만 두 테이블 조회 시 MyISAM 스토리지 엔진을 사용하는 테이블은 실제로 값이 1, 2가 저장되고 그 다음 3을 저장할려는 순간 중복 키 오류가 발생합니다. 하지만 InnoDB 스토리지 엔진을 사용하는 테이블은 1, 2는 저장되지 않고 처음에 INSERT한 3만 저장이 되어 있습니다. 여기서 알 수 있는게 InnoDB는 쿼리 중 일부라도 오류가 발생하게 된다면 롤백을 하고 MyISAM은 부분 업데이트 현상이 발생합니다. 이렇게 MyISAM은 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 만들어 냅니다.

# MyISAM 스토리 엔진을 사용하는 테이블 생성 후 값 저장
CREATE TABLE tab_myisam (idx INT NOT NULL, PRIMARY KEY(idx)) ENGINE=MyISAM
INSERT INTO tab_myisam (idx) VALUES (3)

# InnoDB 스토리 엔진을 사용하는 테이블 생성 후 값 저장
CREATE TABLE tab_innodb (idx INT NOT NULL, PRIMARY KEY(idx)) ENGINE=InnoDB
INSERT INTO tab_innodb (idx) VALUES (3)

# tab_myisam 테이블에 1, 2, 3 저장
INSERT INTO tab_myisam (idx) VALUES (1), (2), (3)
# ==> SQL Error [1062] [23000]: Duplicate entry '3' for key 'tab_myisam.PRIMARY' 에러 발생

# tab_innodb 테이블에 1, 2, 3 저장
INSERT INTO tab_innodb (idx) VALUES (1), (2), (3)
# ==> SQL Error [1062] [23000]: Duplicate entry '3' for key 'tab_innodb.PRIMARY' 에러 발생

 

MySQL 엔진의 잠금

---

• MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있습니다. MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분이며, MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미칠 수 있습니다. 반면에 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않습니다.

글로벌 락

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

🧨  주의

글로벌 락을 거는 FLUSH TABLES WITH READ LOCK 명령은 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 겁니다. 해당 명령이 실행되기 전에 테이블이나 레코드에 쓰기 잠금을 거는 SQL이 실행됐다면 FLUSH TABLES WITH READ LOCK 명령은 테이블에 읽기 잠금을 걸기 위해 먼저  실행된 SQL과 트랜잭션이 완료될 때까지 기다려야 합니다. FLUSH TABLES WITH READ LOCK 명령은 테이블에 읽기 잠금을 걸기 전에 먼저 테이블을 플러시해야 하기 때문에 테이블에 실행 중인 모든 종류의 쿼리가 완료해야 합니다. 글래서 장시간 SELECT하는 쿼리가 실행되고 있으며면 FLUSH TABLES WITH READ LOCK 명령은 해당 SELECT 작업이 끝날때까지 기다립니다. 그리고 mysqldump 명령어 입력시 우리가 알지 못하는 사이에 이 명령을 내부적으로 실행할 수 있으므로 주의해야 합니다.

 

테이블 락

• 테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있습니다. 
  명시적으로는 LOCK TABLES 테이블명 [READ | WRITE] 명령으로 특정 테이블의 락을 획득할 수 있으며, 명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 반납할 수 있습니다. 명시적인 테이블 락도 특별한 상황이 아니면 사용할 일이 별로 없으며, 글로벌 락과 동일하게 온라인 작업에서 상당한 영향을 미칩니다.
• 묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하는 경우에 발생하는데 MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후 즉시 잠금을 해제하는 형태로 사용됩니다. 즉 묵시적인 테이블 락은 쿼리가 실행되는 동안은 자동으로 잠금을 걸고 쿼리가 종료되면 자동으로 해제됩니다. 하지만 InnoDB 테이블의 경우에는 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락은 설정되지 않습니다. 더 정확히는 InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 DDL의 경우에만 영향을 미칩니다.

 

네임드 락

• 네임드 락은 GET_LOCK() 함수를 사용하여 임의의 문자열에 대해 잠금을 설정할 수 있습니다. 이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터 베이스 객체가 아닙니다. 네임드 락은 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금입니다.

 

메타데이터 락

• 메터데이터 락은 데이터베이스 객체(테이블이나 뷰)의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다. 메타데이터 락은 명시적으로 획득하거나 해제할 수 있는게 아니고 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금입니다.

 

InnoDB 스토리지 엔진 잠금

---

• InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 가지고 있습니다. InnoDB는 레코드 기반의 잠금 방식 때문에 MyISAM보다는 훨씬 뛰어난 동시성 처리를 제공해줍니다.

InnoDB 스토리지 엔진의 잠금

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

레코드 락

• 레코드 자체만을 잠그는 것을 레코드 락이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 수행합니다. 다만 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스 레코드를 잠급니다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 사용해 잠금을 설정합니다.

갭 락

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

넥스트 키 락

• 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 합니다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적입니다. 그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생할 수 있는데 가능하다면 바이너리 로그 포맷을 ROW 형태로 하는 것이 좋습니다.

자동 증가 락

• MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공합니다. AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 합니다. 이를 위해 InnoDB는 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용합니다.
• AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며 UPDATE, DELETE 등의 쿼리에서는 발생하지 않습니다.
• AUYO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간에만 락이 걸렸다가 즉시 해제됩니다. AUTO_INCREMENT 락은 테이블에 단 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행되면 다른 쿼리는 AUTO_INCREMENT 락을 기다려야 합니다.

 

MySQL의 격리 수준

---

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

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생 (InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

 

READ UNCOMMITTED

• 홍길동이 INSERT를 하고 있는 도중에 이순신이 SELECT를 하게되면 이순신은 2건의 결과를 반환 받게 됩니다. 여기서 홍길동이 어떠한 문제가 발생해 INSERT된 내용을 롤백한다고 해도 이순신은 여전히 2건의 결과를 반환 받게 됩니다.
• 이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 불구하고 다른 트랜잭션에세 볼 수 있는 현상을 더티 리드하고 하며 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED입니다.

 

READ COMMITED

• 이 레벨에서는 더티 리드와 같은 현상은 발생하지 않습니다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있습니다.
• 이순신의 SELECT 결과는 Member 테이블이 아닌 언두 영역에 백업된 레코드에서 가져온 내용입니다. 

REPEATABLE READ

• REPEATABLE READ는 MySQL의 InnoDB 스토리 엔진에서 기본적으로 사용되는 격리 수준입니다. 이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 NON-REPEATABLE READ 부정합성은 발생하지 않습니다. 
• REPEATABLE READ는 MVCC를 위래 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장합니다. 사실 READ COMMITTED도 MVCC를 이용해 COMMIT되기 전의 데이터를 보여주는데 READ COMMITTED와 REPEATABLE READ의 차이점은 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가느냐에 달려있습니다.
• 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션 번호가 포함되어 있습니다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제합니다.
• 아래 그림에서는 홍길동의 트랜잭션 아이디는 13이고 이순신의 트랜잭션 아이디는 10입니다. 이때 홍길동이 광개토대왕의 도시를 서울특별시로 변경하고 커밋을 합니다. 그런데 이순신이 2번의 SELECT를 하지만 결과값이 동일한 것을 볼 수 있습니다. 그 이유는 이순신의 트랜잭션 아이디는 10이므로 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경한 것만 보게됩니다.

SERIALIZABLE

• 가장 단순한 격리 수준이며 동시에 가장 엄격한 격리 수준입니다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어집니다.

 

---

해당 내용은 Real Mysql 8.0 책을 바탕으로 작성되었습니다.