테이블관리7 - InnoDB장애대응

InnoDB는 기동시에 자동적으로 리커버리를 수행한다.

대개의 경우 자동복구된다.

그러나 장애 발생 후에 InnoDB가 기동하지 않던지 "SELECT *"한 다음에 크래쉬되었을 경우 다음처럼 mysqld옵션을 시도해 볼 수 있다.

innodb_force_recovery = { 0|1|2|3|4|5|6 }

0: 기본값. 특수한 경우가 아닌 경우이외에는 값을 바꾸지 말 것.

1(SRV_FORCE_IGNORE_CORRUPT): 페이지파괴를 검출했어도 계속 진행. SELECT *로 덤프가능

2(SRV_FORCE_NO_BACKGROUND): purge조작(필요없는 로그를 지우는 조작)시에 일어나는 크래쉬를 방지

3(SRV_FORCE_NO_TRX_UNDO): 트랜잭션 롤백을 동작시키지 않음.

4(SRV_FORCE_NO_IBUF_MERGE): insert buffer merge조작을 수행하지 않음. 테이블 통계정보도 계산되지 않음.

5(SRV_FORCE_NO_UNDO_LOG_SCAN): UNDO로그를 록하지 않음. 불완전한 트랜잭션을 커밋한 것으로 처리.

6(SRV_FORCE_NO_LOG_REDO): 리커버리로 로그의 roll-forward를 수행하지 않음.

InnoDB 모니터(SHOW ENGINE INNODB STATUS)

InnoDB에서는 내부상태를 출력하는 InnoDB모니터 기능을 준비하고 있어 성능등의 조정에 도움이 된다.

다음과 같은 방법으로 InnoDB 모니터가 가능하다.

>InnoDB 모니터를 개시한다.

CREATE TABLE innodb_monitor( a INT) ENGINE=INNODB;

CREATE TABLE innodb_monitor를 실행하면 에러 로그파일에 15초단위로 status가 출력된다.

>InnoDB 모니터를 중지한다.

DROP TABLE innodb_monitor;

출력을 멈추게 하기위해서는 DROP TABLE innodb_monitor를 실행한다.

>광범위 정보를 모니터한다.

SHOW ENGINE INNODB STATUS;

SHOW INNODB STATUS;

SHOW ENGINE INNODB STATUS에서는 InnoDB정보가 다른 SHOW문과 마찬가지로 표준출력으로 표시된다.

SHOW INNODB STATUS는 SHOW ENGINE INNODB STATUS로 개정되었다.

>MUTEX통계를 표시한다.

SHOW ENGINE INNODB MUTEX;

SHOW ENGINE INNODB MUTEX는 MUTEX정보(상호 배제)를 표시한다.

InnoDB 특징및 제한

InnoDB제한 및 그 특징은 다음과 같다.

• 테이블의 필드수는 1000개까지
• VARCHAR(), BLOB, TEXT필드를 제외하고 레코드크기의 최대는 1/2페이지사이즈
• 모든 VARCHAR()의 최대 사이즈의 합계는 65535까지
• FULLTEXT인덱스는 아직 지원하지 않음.
• SELECT COUNT(*)은 인덱스를 이용해서 계산
• SHOW TABLE STATUS는 InnoDB에 관해서는 정확한 값을 보여주지 못한다.
• 다음에 할당해야하는 AUTO INCREMENT값은 디스크에 기록되지 않는다. (MyISAM은 디스크에 기록)

InnoDB flush 2

innodb_flush_method

데이터 파일과 로그파일의 flush방법을 지정한다. 

이 지정은 Unix계열의 운영체제에서만 동작한다. 

Windows에서는 항상 무효로 async_unbuffered고정이다. 

innodb_flush_method={fdatasync|O_DSYNC|O_DIRECT}

지정가능한 값은 다음과 같다. 

fdatasync 표준값.  데이터 파일과 로그 파일을 fsync()를 사용해서 flush.

O_DSYNC O_SYNC로 로그 파일을 flush. 데이터 파일을 fsync()를 사용.

O_DIRECT O_DIRECT(Linux에서 사용가능)로 데이터 파일을 오픈.

                      fsync()로 데이터 파일과 로그 파일을 flush.

현재, SAN상에 데이터 파일과 로그 파일이 있으면 O_DIRECT인 경우에 퍼포먼스가 

나빠진다는 보고가 있다. 

InnoDB flush 1

innodb_flush_log_at_trx_commit

트랜잭션 상황을 로그에 써 넣는 타이밍은 innodb_flush_log_at_trx_commit옵션으로 설정한다. 

innodb_flush_log_at_trx_commit={0|1|2}

지정가능한 값은 다음과 같다. 

0  log buffer내용이 1초간격으로 로그파일에 쓰여지고 flush된다.  commit할 때는 아무것도 하지 않는다. 

1  log buffer내용이 commit할 때에 로그 파일에 쓰여지고 flush된다. 

2  log buffer내용이 commit할 때에 로그 파일에 쓰여지고 flush는 1초간격으로 이루어진다. 

표준은 1로 , 안전성은 0이 가장 낮다.  만약 서버가 고장났을 경우에 1초간의 트랜잭션은 사라지기 때문이다.  

물론, 1이나 2의 경우에도 급한 정전, 운영체제의 crash에 따른 영향을 100% 피할 수는 없다. 

replication을  실행하고 있는 다면 다음과 같이 설정하고 가능한한 손실을 적게하는 것이 낫다. 

replication을 사용하고 있는 경우 설정

[mysqld]

innodb_flush_log_at_trx_commit=1

sync_binlog=1

InnoDB 레코드 구조

레코드를 보존할 때에는 데이터에 관리정보가 추가된다. 

• Record Header(6bytes)  다음 레코드에 대한 포인터, 필드수등
• Transaction ID (7bytes)  타임스탬프
• Roll Pointer(7bytes)  변경전 레코드 포인터
• Field Pointer(1~2bytes/field) 다음 필드 개시위치 

테이블 스페이스 페이지, 세그먼트

InnoDB테이블 스페이스는 페이지 단위를 최소단위로서 데이터가 관리된다. 

관리단위는 다음과 같다. 

• 1 Page = 16KB
• 1 Extent = 64 Page(1MB)
• 1 Segment = N Extent
• 1 Table Space = M Segment 

(N,M은 정수)

레코드는 페이지안에 포함되어 한개의 페이지에 복수개의 레코드가 저장되게 된다. 

세그먼트는 사용목적에 따라서 여러개의 종류가 있다. 

• Rollback Segment (MVCC를 위해 구 버전의 레코드를 보존등)
• Leaf node Segment ( B+ Tree의 Leaf를 위한 세그먼트)
• non-Leaf node Segment

InnoDB의 인덱스

InnoDB에서는 레코드 저장에 「Clustered Index」라는 특별한 인덱스를 채용하고 있다. 

1. Clustered Index

InnoDB에서는 clustered index라는 인덱스를  한 테이블당  한개 작성한다. 

이것은 B+ Tree인덱스이다. 

인덱스의 Leaf에 실레코드가 직접저장된다. 

clustered index는 primary키를 중심으로 인덱스를 작성하고 있다. 

만약 사용자가 테이블에  primary 키 또는 unique키를 정의하지 않았을 경우 , InnoDB자신이 

내부에 자동적으로 primary키 대신의 것을 생성하고 그것에 따라서 인덱스를 만든다. 

InnoDB가 자동적으로 할당한 것은 사용자는 볼 수 없다. 

InnoDB에서는 이 clustered index키를 중심으로  행 lock를 동작시킨다. 

따라서 만약에 사용자가 primary 키 또는 unique 키를 테이블에 정의해놓지 않으면 행 lock은 동작하지 않으므로 주의 해야한다. 

2. Secondary Index

Secondary Index는  Primary 키이외의 인덱스에 대해서 작성되는 것이다. 

인덱스 leaf에 primary 키의 값과 non-primary키가 셋트로 기록된다. 

primary 키이외의 키가 query에서 사용되었을 경우 이 secondary index가 탐색되어 판명된 primary 키를 중심으로 레코드를 읽어 냄으로  2단계 처리가 이루어짐으로 검색의 속도가 떨어진다. 

secondary index의 소트 알고리즘도 B+ tree이다. 

또 한개의 non-unique인덱스당 한개가 작성된다. 

3. Adaptive Hash Index

테이블이   거의 메모리에서 처리가능할 때 InnoDB는 자동적으로 이 테이블의 hash index를 메모리(buffer pool)안에 작성한다.  이 인덱스를 사용하면  키 값으로 부터 레코드의 기술 위치를 알 수 있다. 이것이 Adaptive Hash Index이다. 

모든것이 index화되어서 buffer pool에서 넣어지는 것이 아니라 액세스 빈도가 많은 것이 있으면 그것에 맞추어서 작성된다. 

레코드 검색시 B+ tree 인덱스를 검색하기 전에 Adaptive Hash Index를 조사한다. 

만약 Adaptive Hash Index에 레코드의 기술위치정보가 있으면  그 밖의 B+ Tree 인덱스 검색은 건너뛰고 직접 레코드를  읽어들이게 된다. 

InnoDB 스레드

InnoDB처리를 실행하는 전용 스레드가 만들어지고 이것이 백그라운드에서 요구를 순차적으로 처리해 간다. 

이 스레드는 한개의 클라이언트당 한개의 스레드가 생성되는 것이 아니고 MySQL서버 한개당 한정된 수만큼만 생성된다. 

이 스레드 수는 다음의 MySQL서버의 옵션에서 변경가능하다. 

innodb_thread_concurrency 

추가적으로 MySQL버전에 따라서 표준 스레드수가 다르다. 

5.1.12-beta  8

5.0.8-beta ~ 5.0.18  20(무제한)

5.0.19~5.0.20  0(무제한)

5.0.19상위 버전에서는 20은 20개라는 의미로 변경되었다. 

현재, 이 수를 마구 늘리면 좋다는 것은 아니다.  테스트 툴등을 사용해서 어느정도의 클라이언트 수 일 때 최적인지 확인해두는 것이 좋을 것이다. 

우선은 8개전후로 시도해보는 것이 좋다. 

이외에도 I/O처리를 담당하는 스레드가 생성된다.  

「read thread」「write thread」「log thread」「insert buffer thread」라고 불리우는 4개의 스레드이다. 

다음의 옵션으로 이 수를 늘리는 것은 가능하지만(4이상) , 추가된 것은 read, write의 예비 역할을 하게 된다. 

그러나 이 옵션은 5.1.12-beta에서는 windows에서만 동작한다. 

innodb_file_io_threads

InnoDB의 버퍼2

#Insert buffer

Insert할 때에 변경해야하는 인덱스 페이지가 버퍼풀안에 없으면 인덱스에 넣는 레코드를 insert buffer에 넣어둔다. 

그리고, secondary index와 insert buffer내용을 merge하고 secondary index를 갱신한다. 

secondary index는 유일키가 아닌 경우가 많아서 insert할 때  secondary index변경을 빈번하게 수행한다는 것은 부하를 주기 십상이다. 

그것을 피하기 위한 조치이지만  반대로 검색할 때에는 Insert Buffer도 검색대상이 되는 경우도 있다.

merge는 insert트랜잭션이 commit된 후에 발생한다.   mysql 서버의 shutdown, 재기동시에도 발생한다.   merge할 때에는 디스크 액세스가 증가한다. 

#innodb log buffer

Innodb 트랜잭션로그를 잔류시켜놓는 버퍼이다. 

한개의 mysql서버당 한개의 버퍼가 할당된다.  

commit할 때에 로그 파일에 flush된다. 

innodb_flush_log_at_trx_commit옵션에 따라서 flush하는 간격을 지정가능하다. 

• innodb_log_buffer_size  InnoDB 로그 버퍼사이즈
• innodb_flush_log_at_trx_commit  0:1초간격으로 flush, 1: commit할  때 flush, 2: commit할 때 디스크 저장 그리고 1초간격으로 flush

innodb_flush_log_at_trx_commit는 2>1>0순으로 안전성이 뛰어나다. 

innodb_log_buffer_size의 상한값은 5.1.12-beta에서는 8M이다.(표준 1MB)

#innodb_additional_mem_pool_size

데이터 사전등의 InnoDB내부정보를 보존하는 버퍼이다. 

이 영역이 부족하게 되면 OS로부터 메모리를 확보하려고 한다. 

테이블이 늘어나면 그만큼 이 영역이 필요하게 된다. 

 20M정도 있으면 괜찮다고 한다. 

InnoDB 버퍼(buffer)

○InnoDB buffer pool 

InnoDB의 레코드, 인덱스등을 캐쉬하는 메모리 영역을 말한다. 

한개의 MySQL서버당 한개의 영역이 할당되어 페이지(16KB)단위로 관리된다. 

이 버퍼사이즈는 innodb_buffer_pool_size옵션으로 지정가능하다. 

많이 할당하면 디스크 액세스가 줄어드므로 가능한한 크게 할당한다. 

이 버퍼의  dirty 페이지(갱신된 페이지)가 innodb_max_dirty_pages_pct(%)에 설정된 수치에 도달하면 (표준은 90%), 체크포인트가 동작하기 시작한다.  

또, 버퍼의 dirty 부분이 로그 영역 사이즈(REDO로그 파일참조)에 가까워지면 체크포인트가 동작한다.  체크포인트가 동작하면 테이블스페이스에 쓰여진다. 

만약 데이터가 버퍼에 담어지지 않는다면 LRU(Least Recently Used) 알고리즘을 사용해서 디스크에 쓰여진다. 

InnoDB 파일배치3

[데이터, 인덱스를 테이블 단위로 보존]

mysqld옵션 innodb_file_per_table을 지정하면 데이터하고 인덱스를 각 테이블 단위로 개별 작성된다.  파일은 .frm파일과 같은 디렉토리에 작성된다.

파일명은 다음과 같다. 

  테이블명.ibd

이 한개의  파일안에 데이터와 인덱스 모두 포함된다. 

부수적으로  innodb_file_per_table을 지정했더라도 테이블 스페이스는 필요하다. 

데이터 사전과 UNDO로그는 이 영역을 사용하기 때문이다. 

[REDO로그 파일]

REDO로그 파일은 트랜잭션로그를 말하는 것으로 여러개의 파일로 구성된다. 

파일은 innodb_log_group_home_dir에 지정되는 디렉토리 밑에 작성된다. 

파일은 표준으로는 2개이지만 설정에 따라서 파일 수를 변경하는 것도 가능하다. 

ib_logfile0

ib_logfile1

보존위치, 파일수 , 파일의 사이즈는 다음 옵션으로 지정가능하다. 

innodb_log_group_home_dir  REDO로그파일 보존위치 디렉토리 

innodb_log_files_in_group REDO로그파일 수

innodb_log_file_size REDO로그 파일의 사이즈 

로그 영역사이즈는 다음과 같다. 

innodb_log_files_in_group  X  innodb_log_file_size 

이 사이즈는 체크포인트의 발생에도 관계가 있다.  

트랜잭션 처리가 가능한 정도의 크기가 설정되어져야한다. 

너무 크기가 크면 리커버리(복구)에 시간이 걸리게 됨으로  크게하더라도 Buffer Pool까지 이다. 

로그 파일을 미러링하는 기능은 현재 존재하지 않는다. 

InnoDB 파일배치2

[테이블 스페이스/데이터파일의 지정]

InnoDB는 데이터, 인덱스, 데이터사전, 롤백세그먼트(UNDO LOG)를 테이블스페이스라고 불리우는 파일에 보존한다. 

테이블 스페이스는 1개이상의 파일(또는 raw파일)으로 구성되어 데이터베이스디렉토리내에 ibdata1등의 이름파일로 작성된다. 

InnoDB테이블스페이스의 최대 사이즈는 64T바이트이다. 

기본적으로 mysqld의 옵션, innodb_data_home_dir옵션에 지정되는 디렉토리 밑에 파일이 작성된다. 

그러나 innodb_data_file_path의 지정에 따라서 다른 디렉토리에 작성하는 것도 가능하다. 

이 지정방법은 innodb_data_home_dir에서 상대패스로 지정됨으로 주의해야한다. 

복수의 파일을 사용하는 경우에는 설정파일 my.cnf(또는 my.ini)의 innodb_data_file_path에 지정한다. 

복수의  테이블스페이스를 사용하는 경우

innodb_data_file_path=/disk1/data/datafile:1G;/disk2/data/datafile2:1G

값의 서식은 다음과 같다. 

• 파일명:사이즈[;파일명:사이즈]
• 파일명:사이즈:autoextend
• 파일명:사이즈:autoextend:max:사이즈

파일명뒤에 콜론(:)으로 사이즈(바이트)를 지정한다. (단위로서는 K,M,G가 사용가능하다. )

파일정의를 여러개 할 경우에는 세미콜론(;)으로 지정한다. 

다음과 같이 스페이스를 넣으면 「 data2」처럼 스페이스가 들어간 파일명이 됨으로 조심해야한다. 

 data1:10M; data2:20M

마지막으로 지정된 파일에 대해서만 autoextend라는 키워드를 지정하는 것이 가능하다. 

이것은 영역이 부족하게 되었을 경우 파일을 자동으로 확장하는 것을 의미한다. 

자동확장의 한계치를 지정할 때에는 autoextend뒤에  :max:사이즈 를 붙인다. 

한번 지정한 autoextend키워드를 생략할 때에는  그 시점에서의 파일 사이즈를 지정해 둘 필요가 있다.  사이즈는 1024바이트의 배수이다. 

또, 다음과 같은 것들을 주의해야한다. 

• 한번 작성한 데이터파일의 기술순서를 바꾸어서는 안된다. 
• 테이블 스페이스를 한 번 작성한 후에 데이터파일을 지우는 것은 불가능하다. 
• 테이블 스페이스를 한 번 작성한 후에 기술의 최후에 새로운 데이터 파일을 추가하는 것은 가능하다. 
• 데이터 파일의 명칭과 패스변경은 가능하다. 

InnoDB 파일배치1

[ frm 파일]

    InnoDB 테이블의 정의 파일이다.  MyISAM테이블과 마찬가지로 데이터베이스 디렉토리 밑에 다음과 같은 파일이 생성된다.  

    테이블명.frm

MySQL은 테이블 정의 정보를 데이터베이스 디렉토리 내의 frm파일안에 가지고 있다. 

그러나 InnoDB는 frm파일과는 별도로 테이블관리정보를 테이블스페이스안에 가지고 있다. 

따라서 InnoDB테이블의 frm파일을 수동으로 지우는 것은  부정합을 일으키는 원인이 된다. 

InnoDB는 원래 MySQL AB 밖에서 작성된 엔진으로 2001년에 MySQL 하위층에 추가된 것이다. 

InnoDB내부에서 관리정보가 개별로 존재하고 MySQL 상위층과 연계를 위해서 frm파일이 존재한다는 것에 주의해야한다. 

  

InnoDB - 트랜잭션5

○트랜잭션 분리 레벨의 변경

InnoDB 트랜잭션 분리레벨은 표준으로는 REPEATABLE READ로 되어있다. 

사용 분리레벨을 변경하는 경우에는 다음과 같이 조작한다. 

트랜잭션분리레벨을 READ COMMITTED로 변경

mysql> SET TRANSACTION ISOLATION LEVEL Read Committed;

○MySQL표준의 트랜잭션 분리레벨의 변경

MySQL의 표준 트랜잭션 분리레벨은 REPEATABLE READ이다. 이것을 변경할 때는 가각 다음과 같이 조작한다. 

1. SET문을 이용해서 트랜잭션 분리레벨을 변경한다. 

  SET문에서 tx_isolation변수의 값을 변경함으로써 표준 트랜잭션분리레벨을 변경가능 하다. 

서버 변수를 이용하면 서버를 멈추지 않고 MySQL표준 트랜잭션분리레벨을 변경가능하다. 

SET문으로 변경한다.

mysql>SET GLOBAL tx_isolation="read-committed";

SET문으로 지정한 경우에는 명령을 실행한 후에 접속한 클라이언트에 대해서만 유효하다. 

SET문 실행중에 접속하고 있던 클라이언트(SET을 실행하고 있던 자신포함)에는 영향을 끼치지 않는다. 또 tx_isolation변수에 설정하는 값에는 다음과 같이 하이픈(-)를 적어 넣는 것을 잊어버리지 말기 바란다. 

READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

이 서식은 GLOBAL이외에도 SESSION 지정도 가능하다.  SESSION의 경우는 자신이 사용하는 분리레벨의 설정이 된다. 

2. --transaction-isolation옵션으로 트랜잭션 분리레벨을 변경한다. 

명령어 라인에서 --transaction-isolation옵션을 사용해서 변경하는 경우는 다음과 같이 조작한다. 

기동할 때 옵션을 변경한다. 

mysqld --transaction-isolation=read-committed 

이 경우에는 지정하는 트랜잭션 분리레벨에 하이픈(-)을 넣을 필요가 있다. 

또 이 옵션을 설정파일인 my.cnf 또는 my.ini의 [mysqld] 그룹에 추가하는 것으로 트랜잭션 레벨을 변경할 수 있다. 

설정파일을 추가한다. 

[mysqld]

transaction-isolation=read-committed

InnoDB - 트랜잭션4

○4개의 트랜잭션 분리레벨

여러개의 클라이언트가 동시에 같은 테이블에  대해서 트랜잭션처리를 실행하는 경우는 데이터의 조회, 저장의 동작이 문제가 되곤 한다. 

이것을 ANSI/ISO SQL규격에서 규정한 것이 「트랜잭션 분리레벨」이다. 

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

InnoDB는 이 4개의 트랜잭션 분리레벨을 전부 지원하고 있다. 

그러나 NDB하고 BDB는 READ COMMITTED만을 지원하고 있다. 

MySQL에서는 SQL문에서 사용하는 트랜잭션 분리레벨을 지정가능하다. 

이 4개의 트랜잭션 분리레벨에 대한 데이터의 조회방법의 차이는 다음과 같다. 

	dirty read	non repeatable read	phantom read
READ UNCOMMITTED	o	o	o
READ COMMITTED	x	o	o
REPEATABLE READ	x	x	o
SERIALIZABLE	x	x	x

o:발생을 허가, x:발생하지 않음

1. dirty read

여러 개의 클라이언트가 동시에 테이블에 접속해 있다고 하자. 

이 때 한쪽에서 아직 COMMIT하고 있는 데이터를 다른 한쪽에서 조회가 가능한 상황이 dirty read이다. 

2. non-repeatable read (반복불능 조회)

여러 개의 클라리언트가 동시에 테이블에 접속해 있다고 하자. 

한쪽(A)이 어떤 레코드를 SELECT하고 다른 한쪽(B)가 A가 SELECT한 레코드를 갱신하고 COMMIT한다. 

여기에서 다시 A가 같은 레코드를 SELECT했을 때  값이 B가 변경한 것으로 변해 있는 상황이 non repeatable read이다. 

다시 말하면 지난번 조회내용과 이번 조회내용이 다르다는 것이다. 

3. phantom read

non-repeatable read가 UPDATE에 관한 사항이었던 것에 비해 phantom read는 INSERT에 관한 사항이다. 

한쪽(A)이 어떤 레코드를 SELECT한 후 다른 한쪽(B)가 새로운 레코드를 INSERT하고 COMMIT한다고 하자.  여기에서 다시 A가 같은 조건으로 레코드를 조회할 경우 B가 추가한 새로운 레코드도 조회되는 상황이 phantom read이다. 

지난번 조회내용과 이번 조회내용이 다르다.

InnoDB에서는 REPEATABLE READ대해서 phantom read는 발생하지 않는다. 

또 InnoDB의 SERIALIZABLE는 lock방법을 명시하지 않은 모든 SELECT문을 

SELECT ... LOCK IN SHARE MODE로서 취급한다. 

InnoDB - 트랜잭션3

○auto commit모드를 무효로 하기

auto commit모드를 무효로 하기 위해서는 다음과 같이 조작한다. 

auto commit모드를 무효화하기

mysql> SET AUTOCOMMIT=0;

auto commit모드를 무효화하면 START TRANSACTION의 실행을 잊어버렸어도 자동으로 트랜잭션이 시작된다. 또  COMMIT, ROLLBACK을 실행하면 그 다음 트랜잭션이 자동적으로 개시된다. 

또 다른 auto commit모드를 무효화하는 방법에는 다음처럼 init-connect옵션을 지정하는 방법도 있다. 

init-connect옵션으로 auto commit모드를 무효화하기 

shell$ mysqld --init-connect='SET AUTOCOMMIT=0'

이것은 클라이언트가 서버에 접속할 때마다 최초에 자동적으로 SET AUTOCOMMI=0를 실행하는 옵션이다.  다만 이 기능은 SUPPER권한이 있는 유저로 접속해온 클라이언트에 대해서는 기능하지 않으므로 주의해야 한다. 

InnoDB - 트랜잭션2

◎트랜잭션이 무효인 SQL문

MySQL에서는 모든 SQL문이 트랜잭션 대상이 되지는 않는다. 

다음과 같은 SQL문이 실행되었을 때  만약 트랜잭션모드이었다고 하더라도 자동적으로  commit되어버린다.  주의 해야한다!

• ALTER FUNCTION  ※묵시적 commit은 5.0.13부터..
• ALTER PROCEDURE ※묵시적 commit은 5.0.13부터..
• ALTER TABLE
• BEGIN
• CREATE DATABASE  ※묵시적 commit은 5.0.8부터..
• CREATE FUNCTION  ※묵시적 commit은 5.0.13부터..
• CREATE INDEX
• CREATE PROCEDURE  ※묵시적 commit은 5.0.13부터..
• CREATE TABLE      ※묵시적 commit은 5.0.8부터..
• DROP DATABASE  ※묵시적 commit은 5.0.8부터..
• DROP FUNCTION  ※묵시적 commit은 5.0.13부터..
• DROP INDEX
• DROP PROCEDURE  ※묵시적 commit은 5.0.13부터..
• DROP TABLE
• LOAD MASTER DATA
• LOCK TABLES
• LOAD DATA INFILE
• RENAME TABLE
• SET AUTOCOMMIT=1
• START TRANSACTION
• TRUNCATE TABLE  ※묵시적 commit은 5.0.8부터..
• UNLOCK TABLES

◎스냅샷의 타이밍 

보통 InnoDB의 REPEATABLE READ에서는 SELECT한 시점의 데이터가 스탭샷용으로 보존된다.  그러나 START TRANSACTION WITH CONSISTENT SNAPSHOT의 경우에는 이 문장을 실행한 때의 데이터를 스냅샷용으로 보존한다.

InnoDB - 트랜잭션1

MySQL에서는 모든 스토리지 엔진이 트랜잭션기능을 지원하는 것은 아니다. 

트랜잭션을 지원하는 스토리지 엔진은 InnoDB하고 NDB, BDB이다. 

또  MySQL에는 auto commit 모드라는 개념이 있다. 

auto commit이 유효(MySQL의 표준)인 경우 트랜잭션개시에는 반드시 START TRANSACTION이나 BEGIN이 필요하다. 

이것을 잊어버리면 각 문장을 트랜잭션 처리가 되지 않고 실행할 때 곧바로 Commit되어 버린다. 

덧붙여 BEGIN은 Stored Preocedure에서도 사용하는 키워드이기도 하기 때문에 

이글에서 트랜잭션에 관해서는 START TRANSACTION을 사용하는 것으로 한다. 

START TRANSACTION예

mysql>START TRANSACTION

mysql>UPDATE t SET a=a+1 WHERE a<100;

mysql>INSERT t(a) values(200);

mysql>ROLLBACK;

MySQL에서는 트랜잭션중에  또다시 트랜잭션을 작성하는 것은 불가능하다. 

또 트랜잭션 도중에 COMMIT/ROLLBACK을 잊어버리고 다시 START TRANSACTION을 실행했을 때,  지금까지의 트랜잭션데이터는 데이터에 반영되어 버리기 때문에 주의 해야한다. 

InnoDB 3

●UPDATE/DELETE할 때의 lock

MyISAM에서는 UPDATE/DELETE할 때 테이블에 lock을 건다. 

InnoDB에서는 스캔된 레코드에 대해서 lock이 걸리게 된다. 

InnoDB에서는 SELECT...FOR UPDATE때 와 마찬가지로 UPDATE/DELETE에 있어서도 

스캔된 레코드 전부에 lock이 걸리게 된다. 

인덱스를 가진 컬럼을 WHERE문에 사용해서 스캔되는 행을 한정해야 한다. 

●Next-Key Locking

InnoDB에서는   팬텀리드현상이 갱신할 때 발생하기않게 하기 위해서 Next-key Locking이라는 lock을 도입하고 있다.  예를 들어 하나의 레코드를 갱신한다고 치자. 

mysql＞ SELECT a FROM t WHERE b＜10;

mysql＞UPDATE t SET a=100 WHERE b＜10;

이 때, 다른 쓰레드가 b＜10조건을 만족하는 레코드를 삽입했다고 하자. 

그러면 2개의 레코드가 변경될 가능성이 나오게 된다. 

팬텀(환상, 유령) 레코드가 생길 수 밖에 없다. 

이것을 방지하기 위해서 InnoDB에서는  레코드를 삽입할려고 하는 다른 스레드를 lock대기시킨다.  InnoDB에서는 디폴트로 Next-key Locking이 유효화 되어있다. 

이것을 해제하기 위해서는 MySQL 기동할 때 innodb_locks_unsafe_for_binlog옵션을 설정한다. 

※MyISAM의 INSERT할 때의 lock

MyISAM에서는 레코드를 추출할 때 테이블에 읽기 전용 lock을 건다. 

다만, 테이블 도중에 삭제된 레코드가 없다면 레코드를 추출중에도 레코드 삽입이 가능하다.

(동시 삽입)