[MySQL & PHP] 13장 트랜잭션 활용하기

정리

1. 저장 엔진이란 무엇이며, 어떤 종류의 저장 엔진이 있는가?

1. 저장 엔진이란?

1.1 저장 엔진이란 무엇인가?

트랜잭션을 설명하기 전에 MySQL의 저장 엔진에 대해 알아 두어야 합니다.

MySQL의 기능은 크게 두 가지로 나눌 수 있습니다.

첫 번째는 접속 기능과 SQL 문의 내용을 사전에 조사하는 기능, 즉 데이터베이스의 상위 부분이고, 두 번째는 그 상위 부분의 지시를 받아 실제로 검색이나 파일을 조작하는 기능의 하위 부분입니다. 이 하위 부분을 '저장 엔진'이라고 합니다.

1.2 저장 엔진의 종류

MySQL에는 여러 가지의 저장 엔진이 있는데, 사용자는 목적과 자신의 취향에 따라 저장 엔진을 선택할 수 있습니다. 또한, A 테이블은 ◯ ◯ 저장 엔진, B 테이블은 Х Х 저장 엔진과 같은 방식으로 테이블별로 저장 엔진을 지정할 수 있습니다.

이처럼 사용자가 독립된 형태의 저장 엔진을 선택할 수 있는 것은 수많은 RDBMS 중에서도 MySQL만의 고유한 특징입니다.

MySQL의 주요 저장 엔진

저장 엔진	특징
MyISAM	MySQL 5.1까지의 기본 엔진. 트랜잭션을 지원하지 않는다.
InnoDB	MySQL 5.5 이후의 기본 엔진. 트랜잭션을 지원한다.
ISAM	MySQL의 기본이 되는 저장 엔진. 예전 형식으로, MySQL 5.0부터는 설치되지 않는다.
MEMORY	데이터를 모두 메모리상에 보관하며, 동작이 빠르다.
MERGE	MyISAM의 여러 개의 테이블을 하나의 테이블처럼 다룬다.

여기서는 처음부터 기본 엔진으로 설정되어 있는 InnoDB를 그대로 사용하겠습니다. 사실은 MySQL 5.1까지는 MyISAM이 기본 엔진으로 설정되어 있었습니다. 이 MyISAM은 당시의 InnoDB에 비하면 처리 속도가 매우 빨랐으나 안타깝게도 트랜잭션을 지원하지 않았습니다.

그래서 당시에는 트랜잭션을 사용하지 않을 때에는 MyISAM을, 사용할 때에는 InnoDB를 이용하기도 했습니다. 그러나 지금은 InnoDB의 처리속도가 많이 향상되었기 때문에, 여기서는 MyISAM은 사용하지 않겠습니다.

저장 엔진을 보다 효율적으로 사용하려면 저장 엔진별로 세심한 튜닝이 필요하고, 새로운 저장 엔진에 대한 연구도 필요합니다. 하지만 여기서는 기초를 확실히 하는 것을 목표로 하고 있기 때문에, 현시점에서는 InnoDB만 사용해서 설명할 것입니다. 단, 저장 엔진의 기능은 점점 진화하고 있습니다. MySQL을 사용하는 이상, 목적에 최적화된 저장 엔진이 있다면 그 엔진을 사용하는 것이 효율적입니다. 미래에 대비하여 여기에서는 저장 엔진을 변경하는 방법만 기억합시다.

MyISAM과 InnoDB는 어떻게 읽을까?

읽는 방법은 개인마다 다르지만, 일반적으로 MyISAM은 '마이아이삼', InnoDB는 '이노디비'라고 불리고 있습니다.

2. 저장 엔진 설정하기

2.1 저장 엔진 확인하기

그럼 지금까지 사용했던 테이블의 저장 엔진을 확인해 보겠습니다. 테이블의 상세한 정보를 표시할 때에는 SHOW CREATE TABLE 명령을 사용합니다.

SHOW CREATE TABLE tb;

저장 엔진은 'ENGINE=Х Х'를 통해 확인할 수 있습니다. 앞의 예에서는 테이블 tb에 InnoDB가 사용되고 있다는 것을 알 수 있습니다. 테이블을 만들 때 저장 엔진을 별도로 지정하지 않으면 기본 설정인 InnoDB가 선택됩니다.

MySQL 콘솔창 사용 팁 - 쌍반점(;) 대신 \G 사용하기

앞의 예에서 SHOW CREATE TABLE;과 같은 명령을 실행하면 1행에 표시하는 데이터가 많아서 외관상 보기가 매우 불편합니다. 이럴 때 명령문의 마지막에 쌍반점(;) 대신 \G를 사용해봅시다. 이때, G는 반드시 대문자를 사용합니다.

\G를 사용하면 다음과 같이 칼럼별로 알아보기 쉽게 표시됩니다.

SELECT 명령과 같이 표시할 칼럼이 많거나 표시할 항목이 길 때 사용하면 편리합니다. 꼭 기억해 두기 바랍니다.

2.2 저장 엔진 변경하기

테이블의 저장 엔진은 나중에 변경할 수도 있습니다. 여기에서는 InnoDB가 설정된 테이블 tb1A의 저장 엔진을 MyISAM으로 변경해 보겠습니다.

저장 엔진을 변경할 때에는 ALTER TABLE 명령을 사용합니다.

저장 엔진을 MyISAM으로 변경하기

ALTER TABLE 테이블_이름 ENGIN=MyISAM;

'Query OK'라고 표시되어도 저장 엔진이 변경되지 않는 경우가 있습니다. 반드시, SHOW CREATE TABLE tb1A;를 실행해서 MyISAM으로 변경 되었는지 확인합니다.

2. 트랜잭션이란 무엇인가?

3. 트랜잭션이란?

트랜잭션을 설명할 때 자주 등장하는 예가 '입출금 처리'입니다.

예를 들어, 박진영의 계좌에서 윤종신의 계좌로 10만원을 송금한다고 했을 때, '박진영의 계좌 잔고에서 10만원을 감산'하고, '윤종신의 계좌 잔고에 10만원을 가산'하는 처리가 이루어집니다. 만일, '박진영의 계좌 잔고에서 10만 원을 감산'하는 시점에 오류가 발생했다면 어떻게 될까요? '윤종신의 계좌 잔고에 10만원을 가산'하는 처리는 이루어 지지 않은 채 박진영의 계좌에 있던 10만 원만 어디론가 사라지고 마는 것입니다. 이는 입출금 처리에서 절대로 있어서는 안 되는 사태입니다.

그래서, '10만 원의 감산'과 '10만 원의 가산'을 '분할할 수 없는 하나의 처리'로 취급해서, 만일 10만 원의 가산이 실패하면 10만 원의 감산도 취소하도록 합니다. 이렇게 하면 적어도 10만 원이 사라지는 일은 피할 수 있습니다.

이처럼, 여러 단계의 처리를 하나의 처리처럼 다루는 기능을 트랜잭션(transection)이라고 합니다. 트랜잭션의 실행 결과를 데이터베이스에 반영하는 것을 커밋(commit)이라 하고, 반영하지 않고 원래 상태로 되돌리는 것을 롤백(roll-back)이라고 합니다.

Excel 등의 컴퓨터 처리에 익숙하다면, 무엇이든지 ctrl + z 로 되돌릴 수 있다고 생각하기 쉽습니다. 그러나 데이터베이스 세계에서는 트랜잭션을 사용하지 않으면 일단 한번 변경된 데이터는 원래 상태로 되돌릴 수 없다고 생각해야 합니다.

3. 트랜잭션을 시작하는 방법

4. 트랜잭션 사용하기

트랜잭션의 기능을 직접 체험해 보겠습니다. 이번에는 지금까지 수없이 등장했던 매출정보 테이블 tb의 모든 레코드를 대담하게 삭제해 보겠습니다. 그러고 나서, 그 처리를 되롤려(ROLLBACK) 보겠습니다.

4.1 실행 전 주의사항

만일, 중요한 데이터가 삭제되어 버렸는데 원래 상태로 되돌릴 수 없다면 곤란하겠죠? 그렇기 때문에, 트랜잭션을 테스트할 때에는 반드시 잃어버려도 곤란하지 않을 데이터를 사용하기 바랍니다. 그리고 DROP 등 몇 가지 명령은 롤백이 되지 않으니 주의해야 합니다.

또한, 저장 엔진이 MyISAM인 테이블에서는 트랜잭션 기능을 사용할 수 없습니다. 테이블 tb의 저장 엔진이 InnoDB로 되어 있는지 확인하고 실행하도록 합니다.

4.2 트랜잭션 시작하기

먼저, 테이블 tb에 있는 데이터를 표시해 보겠습니다.

데이터가 있는 것을 확인했으면 트랜잭션을 시작해 보겠습니다. 트랜잭션을 시작할 때에는 START TRANSACTION이라는 명령을 실행합니다.

트랜잭션 시작하기

START TRANSACTION;

또는, BEGIN이나 BEGIN WORK를 입력해도 됩니다. 다음과 같이 'Query OK'라는 메시지가 표시되는 것을 확인합니다.

이 메시지가 표시되지 않으면 트랜잭션이 작동하지 않는 것입니다.

그럼, 테이블 tb의 모든 레코드를 대담하게 삭제해 보겠습니다.

DELETE FROM tb;

WHERE 등으로 조건을 설정하지 않았기 때문에 테이블 tb의 모든 데이터가 삭제되었습니다.

실제 이 시점에서 테이블의 일부 기능에 록(lock)이 걸립니다. 즉, 잠금 처리가 됩니다. 따라서 다른 세션에서 INSERT 등의 명령을 실행할 수가 없습니다. 예를 들어, 이 상태에서 명령 프롬프트를 하나 더 실행하여, MySQL 콘솔창에서 테이블 tb에 INSERT 명령을 실행해 봅시다.

이때, 트랜잭션을 실행한 MySQL 콘솔창에서 COMMIT;이나 ROLLBACK; 명령을 실행할 때까지 INNSERT 명령은 실행되지 않습니다.

4. 커밋 또는 롤백 사용하기

4.3 테이블의 내용 확인하기

자, 이제 테이블 tb에는 아무 레코드도 남아 있지 않을 겁니다. 확인해 보겠습니다. 트랜잭션을 실행한 MySQL 콘솔창에서 확인합니다(이 시점에서는 다른 MySQL 콘솔창에서 SELECT를 실행하면 레코드가 남아 있는 것처럼 보입니다).

SELECT * FROM tb;

'Empty set'이라고 표시되었습니다. 레코드가 남아 있지 않다는 의미입니다. 과연 레코드를 원래대로 되돌릴 수 있을까요?

롤백을 실행해서 원래 상태로 되돌리기

그럼, ROLLBACK 명령을 사용해서 레코드를 원래대로 되돌려 보겠습니다.

롤백(되돌리기)

ROLLBACK;

이제 DELETE 명령의 결과는 반영되지 않습니다. SELECT 명령으로 결과를 확인해 보겠습니다.

데이터가 무사히 복원되었습니다.

트랜잭션은 ROLLBACK;을 실행한 시점에 확정됩니다. 다시 한번 테스트를 하려면 반드시 START TRANSACTION;으로 트랜잭션을 시작해야 합니다.

만일, ROLLBACK; 대신 COMMIT;을 사용하면 레코드를 삭제한 결과가 반영되어 모든 레코드가 영구적으로 삭제되고 맙니다.

커밋(반영하기)

COMMIT;

5. 자동 커밋 기능이란 무엇인가?

5. 자동 커밋 기능

일반적으로는 MySQL에서 명령을 실행하면 그대로 반영됩니다. 즉, 사용자가 의식하지 않아도 모든 명령이 자동으로 COMMIT되는 것입니다. 특히, MySQL 5.1까지 기본 엔진이었던 MyISAM에서는 트랜잭션 기능이 없기 때문에 모든 명령은 커밋(반영)됩니다.

이처럼, 명령을 실행하면 그대로 반영하는 기능을 자동 커밋 기능이라고 합니다. 이 자동 커밋 기능의 기본 설정은 ON입니다. 그러나 저장 엔진이 InnoDB인 경우, START TRANSACTION(또는 BEGIN)을 실행하면 COMMIT 명령을 할 때 까지 커밋(반영)되지 않습니다. 그 덕분에 ROLLBACK 명령도 가능해진 것입니다.

자동 커밋 기능은 사용자가 강제로 OFF로 설정할 수 있습니다. 자동 커밋 기능을 OFF로 설정하면 SQL 문을 실행해도 곧바로 반영되지 않기 때문에, COMMIT과 ROLLBACK 기능을 사용할 수 있습니다.

5.1 자동 커밋 기능을 OFF로 설정하기

그럼, 자동 커밋 기능을 OFF로 바꿔보겠습니다.

자동 커밋 기능을 OFF로 하려면, 다음과 같이 SET AUTOCOMMIT=0;이라는 명령을 실행합니다.

자동 커밋 기능을 OFF로 설정하기

SET AUTOCOMMIT=0;

이제 명령을 실행해도 자동으로 반영되지 않게 되었습니다. 그럼, INNSERT 명령을 실행해 보겠습니다.

INSERT INTO tb VALUES('test', 555, 555);

내용을 확인해 봅시다. SELECT * FROM tb;를 실행해 보니 추가한 레코드가 표시되는군요.

과연, 롤백을 실행하면 어떻게 될까요?

ROLLBACK;

SELECT * FROM tb;를 실행해 보면 추가한 레코드가 표시되지 않습니다.

명령이 자동으로 반영되지 않는다는 것을 확인했습니다. 실제로 변경한 데이터를 확정하려면 COMMIT;을 실행해야 합니다.

이쯤 되면 자동 커밋 기능을 OFF로 설정하면 언제든지 명령을 되롤릴 수 있어서 더 좋다 라고 생각하는 분이 있을 것입니다. 그러나 자동 커밋 기능을 OFF로 설정한 상태에서 작업하다가, 만일 COMMIT을 하지 않고 MySQL을 종료하면 그 작업 내용은 반영되지 않으니 주의해야 합니다.

5.2 OFF로 설정한 자동 커밋 기능을 ON으로 바꾸기

OFF로 설정한 자동 커밋 기능을 원래대로 되돌리는 방법입니다. 다음 명령을 이용해서 자동 커밋 기능을 원래의 기본 설정으로 되돌려 놓습니다.

SET AUTOCOMMIT=1;

자동 커밋 기능의 설정 상태를 확인하려면?

자동 커밋 기능의 현재 설정 상태를 확인하려면 SELECT @@AUTOCOMMIT 명령을 실행하니다. ON이면 '1', OFF면 '0'으로 표시됩니다.

5.3 트랜잭션을 이용할 수 있는 범위

트랜잭션으로 모든 명령을 롤백으로 되돌릴 수 있는 것은 아닙니다. 예를 들어, 다음과 같은 명령은 자동 커밋 됩니다.

* DROP DATABASE

* DROP TABLE

* DROP

* ALTER TABLE

앞의 명령은 트랜잭션을 실행해도 원래의 상태로 되돌릴 수가 없으니 주의해야 합니다.

저장 엔진을 자유롭게 선택할 수 있다는 것은 MySQL의 커다란 매력중의 하나 입니다. 현재 사용할 수 있는 저장 엔진은 SHOW ENGINES \G 명령으로 확인할 수 있습니다. 여기서의 환경에서 사용할 수 있는 트랜잭션은 InnoDB뿐인 것을 확인할 수 있을 겁니다.

체크!

★ 저장 엔진에 대해 이해했다.

★ 트랜잭션을 시작할 수 있다.

★ 명시적으로 커밋 또는 롤백을 실행할 수 있다.

★ 자동 커밋 기능을 이해하고 설정할 수 있다.

★ 롤백을 실행할 수 없는 명령이 있다는 것을 알고 있다.