InnoDB와 MyISAM으로 알아보는 원자성
이 글은 MySQL 8.1.0 ARM64를 기준으로 작성되었습니다. 참고 자료는 다음과 같습니다.
- 우아한테크캠프 6기 - 성능 개선 강의
- MySQL storage engines - w3resource
- 15.7.2.2 autocommit, Commit, and Rollback - MySQL
InnoDB? MyISAM?
InnoDB와 MyISAM은 MySQL에서 사용하는 스토리지 엔진으로 MySQL 5.5 이상부터는 InnoDB를 기본으로 사용합니다. 둘의 가장 큰 차이점은 트랜잭션 지원 여부입니다. InnoDB는 트랜잭션을 지원하는 반면, MyISAM은 트랜잭션을 지원하지 않습니다.
따라서 트랜잭션을 지원하지 않는 MyISAM은 트랜잭션의 ACID 속성을 보장하지 못합니다. 이를 위해 데이터의 무결성과 일관성을 유지하기 위한 추가 작업이 필요합니다.
SQL로 알아보는 ACID
이를 실제 SQL 예제로 알아보겠습니다.
CREATE DATABASE transaction_test;
USE transaction_test;
우선 예제에서 사용할 데이터베이스를 생성하고 선택합니다.
트랜잭션을 지원하는 InnoDB
먼저 트랜잭션을 지원하는 InnoDB 스토리지 엔진부터 살펴보겠습니다.
CREATE TABLE inno_db_test (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
) ENGINE = InnoDB;
InnoDB을 스토리지 엔진으로 사용하는 테이블을 정의합니다.
INSERT INTO inno_db_test values (3, 'tommy');
테이블에 값 하나를 추가합니다.
INSERT INTO inno_db_test values (1, 'neo'), (2, 'b'), (3, 'tommy');
이때 이미 추가된 데이터와 함께 다른 데이터를 삽입하면 어떻게 될까요?
쿼리 실행 시 PK 제약조건에 의하여 에러 메시지를 출력합니다. 그렇다면 테이블에는 어떤 값이 있을까요?
문제가 있었던 데이터는 하나였지만, 3개의 데이터 모두 삽입에 실패하였습니다.
트랜잭션을 지원하지 않는 MyISAM
이번에는 트랜잭션을 지원하지 않는 MyISAM 스토리지 엔진에 대해 알아보겠습니다.
CREATE TABLE myisam_test (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
) ENGINE = MyISAM;
MyISAM을 스토리지 엔진으로 사용하는 테이블을 정의합니다.
INSERT INTO myisam_test VALUES (3, 'tommy');
이전과 동일하게 테이블에 값 하나를 추가합니다.
INSERT INTO myisam_test VALUES (1, 'neo'), (2, 'brie'), (3, 'tommy');
아까처럼 이미 추가된 데이터와 함께 다른 데이터를 삽입하면 어떻게 될까요?
마찬가지로 에러 메시지를 출력합니다. 그렇다면 테이블에는 어떤 값이 있을까요?
이번에는 먼저 삽입했던 (1, ‘neo’), (2, ‘brie’)가 테이블에 추가되었습니다.
INSERT INTO myisam_test VALUES (3, 'tommy'), (4, 'gugu'), (5, 'solar');
이번에는 중복 데이터를 앞에 두고 새로운 데이터는 뒤에 추가하는 쿼리를 실행해보겠습니다. 테이블에는 어떤 값이 있을까요?
모두 추가에 실패하였습니다.
INSERT INTO myisam_test VALUES (4, 'gugu'), (5, 'solar'), (3, 'tommy'), (6, 'brown');
이번에는 중복 데이터를 중간에 두고 앞뒤로 새로운 데이터를 추가하는 쿼리를 실행해보겠습니다. 과연…?
중복 데이터가 있는 위치 이전에 있는 값들은 추가되었고 이후에 있는 값들은 추가 되지 않았습니다.
왜 그런걸까?
MySQL에서는 auto commit이라는 옵션이 존재합니다. 해당 옵션이 활성화되어 있을 경우 각각의 SQL 문장은 개별적인 트랜잭션으로 처리되어 데이터베이스에 반영됩니다.
In
InnoDB, all user activity occurs inside a transaction. Ifautocommitmode is enabled, each SQL statement forms a single transaction on its own.
기본 값은 ‘ON’이며 이는 다음 쿼리로 확인할 수 있습니다.
SHOW VARIABLES LIKE 'autocommit';
즉, 트랜잭션을 지원하는 InnoDB는 각각의 insert문이 개별적인 트랜잭션에서 처리되었고 값을 추가하는 도중에 에러가 발생할 경우 트랜잭션의 ACID의 Atomicity으로 인해 트랜잭션이 롤백됩니다.
반대로 MyISAM의 경우 트랜잭션을 지원하지 않기 때문에 ACID 속성을 보장받지 못하여 이미 삽입된 값에 대한 별도의 처리를 하지 않습니다.
결론
이번 글에서는 MySQL에서 사용하는 스토리지 엔진인 InnoDB와 MyISAM에 대해 알아보았습니다. 트랜잭션을 지원하는 InnoDB와 지원하지 않는 MyISAM에서 insert문을 실행하며 트랜잭션의 All or Nothing, 원자성을 확인할 수 있었습니다. 또, 트랜잭션을 지원하지 않는 MyISAM의 경우 삽입하는 데이터의 순서에 따라 결과가 달라질 수 있다는 것을 확인하였습니다.
이번 글에서 사용한 SQL문은 아래의 학습 테스트에서 확인할 수 있습니다.
댓글남기기