MySQL 8.0 이전 버전(MyISAM 엔진) → 트랜잭션 지원 X
MySQL 8.0 이후 버전(InnoDB 엔진) → 트랜잭션 지원 O
트랜잭션이란 데이터의 정합성을 위한 것으로 단위 작업의 모든 작업이 완벽하게 완료되면 성공으로 간주하고 아니면 실패로 간주하는 것으로 이해할 수 있다.
💡 [주의사항]
DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 커넥션도 짧으면 좋은 듯이 트랜잭션도 어쨋든 I/O 입출력이 필요한 작업이기 때문에 짧을수록 좋다. 하지만 모든 상황에서 짧은것이 좋은것은 아니니 주의해서 사용하도록 하자. → 특히 메일전송이나 FTP 파일 전송 또는 네트워크를 통해서 원격 서버와 통신하는 듯한 작업은 어떻게 해서든 트랜잭션에서 제거하는 것이 좋다.
가끔 락(Lock)과 트랜잭션(Transaction)을 헷갈리는 경우가 있는데, 락(Lock)은 동시성 제어를 위한 것이고 트랜잭션(Transaction)은 데이터 정합성을 위한 것이다.
- MySQL의 잠금 중에서 가장 범위가 큰 락(Lock)이다. 범위는 MySQL서버 전체로 보면 된다.
- 특징은 작업 대상 테이블이나 DB가 달라도 동일하게 영향을 미친다는 특징이 있다.
- 개별 테이블 단위로 설정되는 잠금이다.(명시적 또는 묵시적으로 테이블의 락을 획득 가능하다.)
명시적인 락은 LOCK TABLES 키워드를 사용해서 락을 획득하고, UNLOCK TABLES 키워드를 사용해서 락을 반납하는 방법이다. → 특별한 상황에서 사용한다.
묵시적인 락은 테이블의 데이터를 변경하면 발생하는 자동으로 실행되는 락이다. 즉, 데이터를 변경하는 동안에는 테이블에 잠금이 일어나고 락을 획득한다. 그리고 변경이 완료되면 즉시 락을 반납하면서 잠금이 해제된다.
→ InnoDB에서는 엔진차원에서 레코드 기반의 잠금을 제공하기 때문에 DML에서는 잘 사용하지 않는다.(DDL에만 영향을 미친다.)
GET_LOCK()함수를 이용해서 임의의 문자열에 대해서 잠금을 설정하는 방식이다.- 대상이 테이블이나 레코드 또는
AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다. - 단순히 사용자가 지정한 문자열(String)에 대해서 획득하고 반납(해제)하는 잠금이다.
→ 자주 사용되지 않는 락이다.
네임드 락의 경우는 자주 사용되는 락의 종류는 아니지만 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다.
예를들어 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 한다. 각 프로그램의 실행 시간을 분산하거나 프로그램의 코드를 수정해서 데드락을 최소화할 수 있지만, 이는 간단한 방법이 아니며 완전한 해결책이 될 수도 없다. 이러한 경우에 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다.
- DB 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.
- 명시적으로 획득할 수는 없고, 테이블의 이름을 변경하는 쿼리를 날리면 묵시적으로 획득할 수 있다.
- MySQL에서 제공하는 잠금과는 별개로 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다.
- 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락 또는 테이블 락으로 레벨업되는 경우는 없다.
- 따라서 MyISAM 보다는 훨씬 뛰어난 동시성 처리를 제공해주고 있다.
- 레코드 자체만을 잠그는 것을 의미하고, 다른 상용 DBMS의 레코드 락과 동일한 역할을 한다.
- 한 가지 중요한 것은 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
- 인덱가 하나도 없는 테이블이면 내부적으로 자동 생성된 클러스터 인덱스를 사용해서 잠금을 설정한다.
- PK 또는 유니크 인덱스에서 보통 사용하고, FK는 밑에서 설명할 넥스트 키 락 또는 갭 락을 사용한다.
- 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠근다.
- 제일 앞의 레코드는 그보다 더 앞에 올 모든 레코드에 대해서 락을 건다.
- 제일 뒤의 레코드는 그보다 더 뒤에에 올 모든 레코드에 대해서 락을 건다.
- 나머지는 만약 ID=10과 ID=20이 인덱스로 존재하고 ID=20에 갭 락을 건다면 ID=10과 ID=20에 대해서 락을 건다.
- 레코드 락과 갭 락을 합쳐놓은 형태의 잠금이다.
- 즉, 레코드에 대한 락과 이 앞의 갭락을 포함시킨 것이다.
- 갭 락과 범위는 거의 같고 차이점은 락을 걸 대상까지 락을 포함시켜 건다는 것이다.
- 예를들어 ID=20과 ID=30이 인덱스로 존재하고 ID=30에 락을 건다면 ID=20과 ID=30 사이에 갭 락을 걸고, ID=30에 레코드 락을 건다는 것이다.
- MySQL에서는 자동 증가하는 숫자 값을 추하기 위해서 AUTO_INCREMENT라는 컬럼 속성을 제공한다.
- AUTO_INCREMENT 컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우에 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야한다.
- 이 때, InnoDB 스토리지 엔진에서는 이를 위해서 내부적으로 AUTO_INCREMENT 락 이라고 하는 테이블 수준의 잠금을 제공한다.
트랜잭션 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 테이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.
격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 생각하는 사용자가 많은데, 사실 SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.
격리 수준은 크게 다음과 같이 4가지로 나눌 수 있다.
- READ UNCOMMITTED: DIRTY READ 발생
- READ COMMITTED: DIRTY READ 발생 X, NON-REPEATABLE READ 발생
- REPEATABLE READ: PHANTOM READ 발생(InnoDB는 없음)
- SERIALIZABLE: 아무것도 발생 X
- 다른 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부와는 상관없이 다른 트랜잭션에서 보인다.
- 예를들어 트랜잭션A에서 어떤 테이블에 데이터를 넣고 커밋을 하지 않은 상황에서 트랜잭션B가 그 테이블을 모두 조회하면 방금 트랜잭션A가 넣은 데이터가 보인다는 것이다.
- 이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을
더티 리드(Dirty read)라고 하고, 더티 리드가 허용되는 격리 수준이READ UNCOMMITTED다.
- 오라클 DBMS에서 기본으로 사용하는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다.
- 이 레벨에서는 더티 리드 현상이 발생하지 않는다. → 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.
- 예를들어 트랜잭션A에서 어떤 테이블에 데이터를 변경하고 커밋을 하지 않은 상황에서 변경 전의 데이터는 언두 영역으로 백업된다. 이 때, 트랜잭션B가 해당 데이터를 조회하면 언두 영역의 데이터를 조회하게 된다.
- 최종적으로 트랜잭션A가 변경된 내용을 커밋하면 그때부터 트랜잭션B는 언두 영역의 데이터가 아닌 새롭게 변경된 데이터를 가져올 수 있게된다.
하지만 READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ 부정합의 문제가 존재한다.
예를들어 트랜잭션 B가 어떤 데이터를 조회했을 때 데이터가 존재하지 않았다. 1초 뒤에 트랜잭션 A가 해당 데이터를 삽입하고 커밋을 했고 이 때 트랜잭션B가 똑같은 데이터를 조회했을 때 데이터를 찾아오게 된다.
이는 별다른 문제가 없어 보이지만, 사실 사용자B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리르 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋나는 것이다.
이러한 부정합 현상은 일반적인 웹 프로그램에서는 크게 문제될 것이 없어 보이지만 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수도 있다.
- REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리수준이다.
- 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준을 사용해야한다.
이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 NON-REPEATABLE READ 부정합이 발생하지 않는다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해서 변경되기 전에 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드의 값을 변경한다. → 이러한 변경 방식을 MVCC(Multi Version Concurrency Control)이라고 한다.
사실 READ COMMITTED도 MVCC를 이용해서 COMMIT되기 전의 데이터를 보여준다. 하지만 REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.
모든 InnoDB 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼있다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.
REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다. 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한 것은 아니다. → 더 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.
예를들어 사용자A의 트랜잭션 번호는 12였으며 사용자B의 트랜잭션 번호는 10이었다. 이때 사용자A는 사원의 이름을 “Toto”로 변경하고 커밋을 수행했다. 그런데 A트랜잭션이 변경을 수행하고 커밋을 했지만, 사용자 B가 emp_no=500000인 사원을 A 트랜잭션의 변경 전후 각각 한 번씩 SELECT 했는데 결과는 항상 “Lara”라는 값을 가져온다. 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하면서 10번이라는 트랜잭션 번호를 부여받았는데, 그때부터 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보게된다.
REPEATABLE READ 격리 수준에서도 다음과 같은 부정합이 발생할 수 있다.
예를들어 사용자 B가 emp_no ≥ 50000 조건에 해당하는 UPDATE 목적으로 조회를 했는데 결과가 1건이 나왔다. 그리고 사용자 A가 emp_no ≥ 50000 조건에 해당하는 데이터를 삽입하고 커밋을 하고, 사용자 B에서 똑같은 조건에 해당하는 목적으로 조회를 하면 결과가 2건이 보이게 된다.
이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 한다.
- 가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다.
- 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.
InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요성은 없어 보인다.