[영남이공대 사이버보안과] MySQL 엔진, 저장엔진, 트랜잭션(transaction), auto_commit, GLOBAL LOCK, TABLE LOCK, 잠금 대기시간

MySQL ENGINE : SQL 문의 내용을 사전에 조사하는 기능, DB의 상위부분

STORAGE ENGINE : 상위 지시를 받아 검색이나 파일을 조작하는 기능

우리가 지금 배울 것은 STORAGE ENGINE 이다.

STORAGE ENGINE - TYPE

STORAGE ENGINE	Feature
MyISAM	MySQL 5.1 기본엔진, Not transaction
InnoDB	MySQL 5.5 기본엔진, Support transaction
ISAM	MyISAM의 기본 저장엔진, MySQL 5.0 부터 설치 불가능
MEMORY	데이터를 메모리에 보관하며 빠른 속도
MERGE	MyISAM의 여러 테이블을 하나의 테이블로 만듦

- 각 테이블 마다 저장엔진을 따로 쓸 수 있다.

SHOW STORAGE ENGINE

SHOW CREATE TABLE <table_name>;

table_name | CREATE TABLE 'table_name' ( ...

... ) ENGINE = InnodDB ....

구분문자를 \G 를 쓰면 좀 더 간결하게 보인다.

ALTER STORAGE ENGINE

ALTER TABLE <table_name> ENGINE=<engine_name>

##########################

Transaction

여러 단계의 처리를 한 단계에서 처리한다. 실행 결과를 반영하는 COMMIT 과 반영하지 않고 원래대로 되돌리는 ROLLBACK 이 있다.

Transaction 시작 및 복구

START TRANSACTION;

~

ROLLBACK;

##########################

자동 COMMIT 기능

명령이 실행되면 자동으로 상태를 저장한다. 

이 기능을 끄면 수동으로 저장해야지만 복구하기가 쉽다.

SHOW AUTO_COMMIT

SHOW VARIABLES LIKE 'autocommit';

SELECT @@AUTOCOMMIT;

OFF AUTO_COMMIT

SET AUTOCOMMIT = 0;

- DROP과 ALTER의 경우에는 설정에 상관없이 자동으로 COMMIT 되므로 주의한다.

##########################

LOCK "MySQL ENGINE"

LEVEL : MySQL & STORAGE ENGINE

MySQL ENGINE은 데이터 동기화를 위한 테이블 잠금, 사용자 잠금, 테이블 이름 잠금이 있다.

GLOBAL LOCK

MySQL 전체에 영향을 주기 때문에 조심해야한다.

접속한 한 세션에서 GLOBAL LOCK을 획득하면 다른 세션에서 SELECT를 제외한 문장을 실행하는 경우 해제될때까지 대기 상태가 된다.

FLUSH TABLES WITH <READ or WRITE> LOCK;

- READ는 SELECT 외에는 아무것도 할 수 없다.

- WRITE는 LOCK을 설정한 세션 이외에는 모든 문장을 실행할 수 없다.

UNLOCK GLOBAL LOCK

UNLOCK TABLES;

TABLE LOCK

특정 테이블에 설정하는 잠금이다.

SET TABLE LOCK

LOCK TABLES <table_name> <READ or WRITE>;

UNLOCK TABLE

UNLOCK TABLES;

- 테이블 잠금의 문제점은 한 세션이 잠금 중일 경우 다른 세션들은 모두 계속 대기 상태라는 것이다. 그 대기 시간이 지나면 에러 메세지를 출력하기때문에 대기 시간을 적당히 조정하면 좋다.

InnoDB 에서는 'innodb_lock_wait_timeout' 이고

MyISAM 에서는 'table_lock_wait_timeout' 이다.

대기 시간 확인

SHOW VARIABLES WHERE variable_name=<InnoDB or MyISAM의 대기시간 변수> \G

대기 시간 조정

SET <InnoDB or MyISAM의 대기시간 변수>=< * second>;

....