이번에는 MySql의 트랜잭션과 잠금에 대해서 알아보도록 하겠습니다.
트랜잭션 정의
트랜잭션은 작업의 완정성을 보장해 주는 것입니다. 논리적인 작업 셋을 모두 완벽하게 처리하거나 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 기능입니다.
MySql에서의 트랜잭션
MySql 스토리지 엔진중에 InnoDB만 트랜잭션을 지원하고 MyISAM과 MEMORY 스토리지 엔진은 트랜잭션을 지원하지 않습니다. 다음 예제를 통해 트랜잭션을 이해해보도록 하겠습니다.
InnoDB
CREATE TABLE tab_innoDB (fdpk INT NOT NULL, PRIMARY KEY(fdpk)) ENGINE=INNODB;
INSERT INTO tab_innodb (fdpk) VALUES (3);
SET autocommit=ON;
// 오류 발생
INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);위와 같이 쿼리를 실행하게 되면 마지막 1,2,3를 insert 하게 되면 이미 3이 insert 되어 있기 때문에 기본키 중복오류로 인해 오류가 발생합니다. InnoDB 같은 경우는 기본적으로 트랜잭션을 지원한다고 했습니다. 그렇기 때문에 tab_innodb를 조회하게 되면 맨 처음에 insert 해주었던 3만 남고 1과 2는 롤백이 되어 이전 상태로 돌아갑니다.
MyISAM
CREATE TABLE tab_myisam (fdpk INT NOT NULL, PRIMARY KEY(fdpk)) ENGINE=MyISAM;
INSERT INTO tab_myisam (fdpk) VALUES (3);
SET autocommit=ON;
// 오류 발생
INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);MyISAM도 위와 같은 오류가 발생하게 됩니다. 그러면 조회시에는 어떤 값이 남아 있을까요? InnoDB와는 다르게 1, 2 ,3 모두 조회가 됩니다. MyISAM은 트랜잭션을 지원하지 않기 떄문에 1과 2가 롤백되지 않고 insert된 상태로 남아있게 됩니다. 이러한 현상을 부분 업데이트(Partial Update)라고 합니다.
💡 트랜잭션이 없으면 데이터 정합성에 문제가 생긴다.물론 쿼리를 통해 데이터 정합성을 지킬 수 있지만 손수 확인하면서 삭제처리 변경처리 해주거나 조건문으로 처리할 순 있지만 엄청 번거로운 작업이 될 것입니다.
트랜잭션 처리시 주의사항
하나의 트랜잭션이 실행되면 트랜잭션에 포함된 로직이 모두 종료될때까지 커넥션을 반납하지 않기 때문에 중간에 네트워크 통신 작업이 껴었어서 지연되는 경우 다른 요청이 컨넥션을 얻지 못하는 경우가 생길 수 있습니다. 이러한 문제때문에 꼭 필요한 최소의 코드에만 트랜잭션을 적용하는 것이 좋습니다. 이러한 네트워크 작업이 트랜잭션안에서 수행되는 경우 DBMS 서버가 높은 부하 상태로 빠지거나 위험한 상태에 빠지는 경우가 발생할 수 있습니다.
잠금
잠금은 동시성을 제어하기 위한 기능입니다. 하나의 레코드를 여러 커넥션에서 동시에 변경하려고 하는데 잠금이 없다면 하나의 데이터를 여러 커넥션에서 동시에 변경하게 되서 해당 레코드의 값은 예측할 수 없는 상태가 됩니다.잠금은 여러 커넥션에서 동시에 동이할 자원을 요청할 경우 순서대로 한 시점에서는 하나의 커넥션만 변경할 수 있게 해주는 역할을 합니다. MySql 잠금의 종류는 여러가지가 있는데 이러한 잠금의 종류에 대해서 알아보도록 하겠습니다.
글로벌 락(Global Lock)
- MySql에서 지원하는 잠금 가운데 가장 범위가 큼
- 한 세션에서 글로벌 락을 획득하면 다른 세션에서는 SELECT를 제외한 DDL이나 DML를 실행하게 되면 락이 해제될 때까지 대기 상태로 남음
- MySql 8.0 버전부터 가벼운 글로벌 락인 백업 락 도입됨
- 백업 락은 스키마나 사용자의 인증 관련 정보를 변경할 수는 없지만 일반적인 테이블의 데이터 변경은 허용
- 백업 락의 목적은 백업 도중에 DDL이 발생하는 것을 막기 위함
- InnoDB의 경우 데이터 정합성을 위해 트랜잭션을 지원하기 때문에 글로벌 락을 걸 필요가 없기 때문에 백업 락 사용
테이블 락(Table Lock)
- 테이블 단위로 설정되는 잠금
- 명시적 또는 묵시적으로 테이블 락을 획득
- 테이블 락은 특별한 이유가 아니라면 어플리케이션에서 사용할 일이 거의 없음
네임드 락(Named Lock)
- 일반적인 잠금이랑 다르게 테이블이나 레코드가 잠금대상이 아닌 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금
- 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랙재션에 유용하게 사용
- 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 데드락의 원인이 되는데 이러한 경우에 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하여 데드락 해결
메타데이터 락(Metadata Lock)
- 데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조에 대한 잠금
InnoDB 스토리지 엔진 잠금
InnoDB 스토리지 엔진은 MySql에서 제공하는 잠금과는 별개로 여러가지 락을 지원합니다.
- 레코드 락
- 레코드 자체만을 잠그는 락
- 레코드 자체가 아닌 인덱스의 레코드를 잠금
- 갭 락
- 레코드와 바로 인접한 레코드 사이의 간격만을 잠금
- 레코드와 레코드 사이에 다른 레코드가 Insert 되는 것을 제어
- 넥스트 키 락
- 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금
- 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하기 위해 사용
- 데드락이나 트랜잭션을 지연시키는 일이 자주 발생하기 때문에 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋음
- 자동 증가 락
- AUTO_INCREMENT 칼럼이 사용된 테이블에 중복되지 않고 순서대로 증가하는 일련번호 값을 가지기 위해 사용하는 테이블 수준의 잠금
- INSERT나 REPLACE 쿼리와 같이 새로운 레코드를 저장하는 경우에만 적용되고 변경과 삭제관련 쿼리에는 적용되지 않음
인덱스와 잠금
InnoDB의 잠금은 레코드를 잠그는 것이 아닌 인덱스를 잠그는 방식으로 처리됩니다. 그렇기 떄문에 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠가야 합니다. 그래서 하나의 레코드만 업데이트를 하는 작업인데도 인덱스를 사용하지 못하는 경우에는 모든 레코드가 탐색범위에 들어가기 때문에 모든 레코드의 인덱스가 잠기게 됩니다. 이렇게 되면 동시성을 제대로 사용하지 못해 다른 클라이언트가 그 테이블을 수정하거나 삭제하려고 할때 기다려야 하는 상황이 발생합니다.
MySql 격리 수준
MySql의 격리 수준은 크게 READ UNCOMMITTED, READ COMMITTED , REPEATABLE READ, SERIALIZABLE 이렇게 나뉘어집니다. SERIALIZABLE 격리 수준을 제외한 나머지 격리 수준에서는 아래와 같은 세 가지 부정합의 문제가 발생할 수 있습니다.
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
|---|---|---|---|
| READ UNCOMMITTED | 발생 | 발생 | 발생 |
| READ COMMITTED | 없음 | 발생 | 발생 |
| REPEATABLE READ | 없음 | 없음 | 발생(InnoDB는 없음) |
| SERIALIZABLE | 없음 | 없음 | 없음 |
READ UNCOMMITTED
해당 격리 수준에서는 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK이랑 상관없이 다른 트랜잭션에서 보입니다. 아래의 예제를 통해 한번 이해해보도록 하겠습니다. 식당 메뉴 테이블이 존재하고 해당 테이블에는 음식과 금액 컬럼이 존재합니다.
- A,B 트랜잭션 시작
- A 트랜잭션에서 가격이 50000원 짜리 햄버거 메뉴를 insert 해줍니다.
- B 트랜잭션에서 가격이 50000원 짜리 메뉴를 조회합니다 A 트랜잭션에서 insert한 메뉴를 조회하게 됩니다.
- 어떤 문제가 생겨서 A 트랜잭션 Rollback
- B 트랜잭션에서는 롤백된 데이터를 가지고 주문 로직 실행
- B 트랜잭션 Commit
READ UNCOMMITED 격리 수준에서는 분명 A 트랜잭션에서 롤백했지만 없는 데이터를 계속 있다고 가정하고 로직을 처리하기 때문에 문제가 발생하게 됩니다. 이러한 문제를 Dirty Read라고 합니다.
READ COMMITTED
READ COMMITTED는 가장 많이 선택되는 격리 수준입니다. 이러한 격리 수준에서는 위에서 발생하는 Dirty Read는 발생하지 않으며 항상 Commit된 데이터만 읽어오게 됩니다. 하지만 이러한 격리 수준에서도 NON-REPEATABLE READ라는 부정합의 문제가 발생합니다.
- A,B 트랜잭션 시작
- A 트랜잭션에서 가격이 50000원 짜리 햄버거 메뉴를 insert 해줍니다.
- B 트랜잭션에서 가격이 50000원 짜리 메뉴를 조회합니다 하지만 A 트랜잭션이 Commit 되지 않았기 때문에 아무것도 조회되지 않습니다
- A 트랜잭션 Commit
- B 트랜잭션에서 가격이 50000원 짜리 메뉴를 조회합니다 50000원 짜리 햄버거 메뉴 조회를 해줍니다.
이렇게 여러번 조회했을때 서로 다른결과를 가져오게 됩니다. 그렇기 때문에 항상 같은 결과를 가져와야하는 REPEATABLE READ 정합성에 어긋나게 됩니다.
REPEATABLE READ
MySql InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준입니다. MySql에서는 바이너리 로그를 가지고 있기 때문에 최소 REPEATABLE READ의 격리 수준으로 설정하는게 좋습니다. 왜냐하면 READ COMMITTED로 설정된 경우 MySQL 서버는 트랜잭션을 수행하는 동안 다른 트랜잭션에 의해 변경된 데이터를 읽을 수 있습니다. 이 경우, 바이너리 로그에서 기록된 쿼리는 트랜잭션의 실제 결과와 달라질 수 있습니다. 즉, 바이너리 로그에 데이터 정합성에 문제가 생긴채로 기록될 가능성이 있습니다. 하지만 이러한 격리 수준에도 부정합이 발생할 수 있습니다. 아까 위에서 이야기 했던 테이블을 기준으로 예시를 들자면 50000원 짜리 메뉴 하나만 저장되어있을 때 B라는 트랜잭션이 SELECT … FOR UPDATE 쿼리를 통해 데이터를 조회했을때에는 한건만 조회하게 됩니다. 그 다음 A라는 트랜잭션에서 50000원 짜리 메뉴 하나를 insert하고 commit합니다. B라는 트랜잭션에서 SELECT .. FOR UPDATE 쿼리를 통해 조회하게 되면 2개의 데이터가 조회됩니다. 이렇게 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 합니다. 이런 문제가 생기는 이유는 SELECT … FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데 언두 레코드에는 잠금을 걸 수 없습니다. 그렇기 때문에 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 됩니다.
SERIALIZABLE
가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준입니다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준에 비해 많이 떨어집니다. 해당 격리 수준은 읽기 작업도 항상 잠금을 획득해야만 하고 동시에 다른 트랜잭션은 해당 레코드를 변경하지 못합니다. 즉 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없습니다. InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않습니다. 그렇기 때문에 굳이 해당 격리 수준을 사용할 필요는 없습니다.
