[MySQL] START TRANSACTION, COMMIT, 그리고 ROLLBACK문

트랜잭션 문법은 다음과 같습니다.

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

 

이 문법은 트랜잭션 사용에 대한 제어를 제공합니다 :

+ START TRANSACTION 또는 BEGIN 문법으로 새로운 트랜잭션을 시작합니다.

+ COMMIT는 현재 트랜잭션을 커밋하여 변경 사항을 영구적으로 만듭니다.

+ ROLLBACK은 현재 트랜잭션을 롤백하여 변경을 취소합니다.

+ SET autocommit는 현재 세션에 대한 기본 자동 커밋 모드를 비활성화하거나 활성화합니다.

 

 

 

기본적으로 MySQL은 자동 커밋 모드가 활성화된 상태로 실행됩니다. 즉, 트랜잭션 내부에 있지 않은 경우 각 명령문은 마치 START TRANSACTION 및 COMMIT로 둘러싸인 것처럼 원자적입니다. ROLLBACK을 사용하여 효과를 취소할 수 없습니다. 그러나 명령문 실행 중에 오류가 발생하면 명령문이 롤백됩니다.

 

일련의 명령문들의 단일 묶음에 대해 자동 커미트 모드를 내재적으로 사용하지 않으려면 START TRANSACTION 문을 사용합니다.

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

START TRANSACTION을 사용하면 COMMIT 또는 ROLLBACK으로 트랜잭션을 종료할 때까지 자동 커밋이 비활성화 된 상태로 유지됩니다. 그런 다음 자동 커밋 모드는 이전 상태로 돌아갑니다.

 

START TRANSACTION은 트랜잭션 특성을 제어하는 ​​여러 수정자를 허용합니다. 여러 수정자를 지정하려면 쉼표로 구분합니다.

+ WITH CONSISTENT SNAPSHOT 수정자는 이를 수행할 수 있는 스토리지 엔진에 대해 일관된 읽기를 시작합니다. 이것은 InnoDB에만 적용됩니다. 결과는 START TRANSACTION을 발행한 후 InnoDB 테이블에서 SELECT를 실행하는 것과 같습니다. WITH CONSISTENT SNAPSHOT 수정자는 현재 트랜잭션 분리(isolation) 레벨을 변경하지 않으므로 현재 분리(isolation) 레벨이 일관된 읽기를 허용하는 레벨인 경우에만 일관된 스냅샷을 제공합니다. 일관된 읽기를 허용하는 유일한 격리 수준은 REPEATABLE READ입니다. 다른 모든 분리(isolation) 레벨의 경우 WITH CONSISTENT SNAPSHOT 절이 무시됩니다. MySQL 5.7.2부터 WITH CONSISTENT SNAPSHOT 절이 무시되면 경고가 생성됩니다.

 

+ READ WRITE 및 READ ONLY 수정자는 트랜잭션 액세스 모드를 설정합니다. 트랜잭션에 사용된 테이블에 대한 변경을 허용하거나 금지합니다. READ ONLY 제한 사항은 트랜잭션이 다른 트랜잭션에 표시되는 트랜잭션 및 트랜잭션이 아닌 테이블을 수정하거나 잠그지 못하게합니다. 트랜잭션은 여전히 ​​임시 테이블을 수정하거나 잠글 수 있습니다.

MySQL은 트랜잭션이 읽기 전용인 것으로 알려진 경우 InnoDB 테이블의 쿼리에 대해 추가 최적화를 가능하게 합니다. READ ONLY를 지정하면 읽기 전용 상태를 자동으로 결정할 수 없는 경우 이러한 최적화가 적용됩니다.

액세스 모드를 지정하지 않으면 기본 모드가 적용됩니다. 기본값이 변경되지 않은 경우 읽기/쓰기입니다. 동일한 명령문에서 READ WRITE와 READ ONLY를 모두 지정할 수 없습니다.

읽기 전용 모드에서는 DML문을 사용하여 TEMPORARY 키워드로 작성된 테이블을 변경할 수 있습니다. 영구 테이블과 마찬가지로 DDL 문으로도 변경이 허용되지 않습니다.

read_only 시스템 변수가 사용 가능한 경우 START TRANSACTION READ WRITE로 트랜잭션을 명시 적으로 시작하려면 SUPER 특권이 필요합니다.

 

중요사항

MySQL클라이언트 응용 프로그램 (예 : JDBC)을 작성하는데 사용되는 많은 API는 클라이언트에서 START TRANSACTION 문을 보내는 대신 사용할 수 있는 트랜잭션을 시작하는 고유한 방법을 제공합니다.

 

자동 커밋 모드를 명시적으로 비활성화하려면 다음 문을 사용합니다.

SET autocommit=0;

autocommit 변수를 0으로 설정하여 자동 커밋 모드를 비활성화한 후에는 transaction-safe 테이블 (InnoDB 또는 NDB의 테이블)에 대한 변경 사항이 즉시 영구적으로 적용되지 않습니다. COMMIT을 사용하여 디스크에 변경 내용을 저장하거나 ROLLBACK을 사용하여 변경 내용을 무시합니다.

 

자동 커밋은 세션 변수이며 각 세션마다 설정해야합니다. 물론 각각의 새 연결에 대해 자동 커밋 모드를 비활성화할 수도 있습니다.

 

BEGIN 및 BEGIN WORK는 트랜잭션 시작을위한 START TRANSACTION의 별명으로 지원됩니다. START TRANSACTION은 표준 SQL 구문이며 임시(ad-hoc) 트랜잭션을 시작하는데 권장되는 방법이며 BEGIN이 하지 않는 수정자를 허용합니다.

 

BEGIN 문은 BEGIN ... END 복합 명령문을 시작하는 BEGIN 키워드 사용과 다릅니다. 후자는 거래를 시작하지 않습니다.

 

노트

모든 저장된 프로그램 (저장 프로 시저 및 함수, 트리거 및 이벤트) 내에서 파서는 BEGIN [WORK]를 BEGIN ... END 블록의 시작으로 처리합니다. 대신 START TRANSACTION으로 이 컨텍스트에서 트랜잭션을 시작하십시오.

 

선택적 WORK 키워드는 CHAIN ​​및 RELEASE 절과 같이 COMMIT 및 ROLLBACK에 지원됩니다. CHAIN ​​및 RELEASE는 트랜잭션 완료에 대한 추가 제어를 위해 사용될 수 있습니다. completion_type 시스템 변수의 값은 기본 완료 동작을 결정합니다.

 

AND CHAIN ​​절은 현재 트랜잭션이 종료 되자마자 새 트랜잭션을 시작하도록 하며 새 트랜잭션은 방금 종료된 트랜잭션과 동일한 분리 레벨을 갖습니다. 새 트랜잭션은 방금 종료된 트랜잭션과 동일한 액세스 모드 (READ WRITE 또는 READ ONLY)를 사용합니다. RELEASE 절은 서버가 현재 트랜잭션을 종료한 후 현재 클라이언트 세션의 연결을 끊도록합니다. NO 키워드를 포함하면 CHAIN ​​또는 RELEASE 완료가 억제됩니다. 이는 completion_type 시스템 변수가 기본적으로 체인 또는 릴리스 완료를 발생 시키도록 설정된 경우 유용합니다.

 

트랜잭션을 시작하면 보류중인 트랜잭션이 커밋됩니다. 기존 트랜잭션이 보류중인게 있는지 확인합니다.

 

또한 트랜잭션을 시작하면 UNLOCK TABLES를 실행한 것처럼 LOCK TABLES로 얻은 테이블 잠금이 해제됩니다. 트랜잭션을 시작해도 FLUSH TABLES WITH READ LOCK으로 얻은 전역 읽기 잠금은 해제되지 않습니다.

 

최상의 결과를 얻으려면 단일 transaction-safe 스토리지 엔진에서 관리하는 테이블만 사용하여 트랜잭션을 수행해야합니다. 그렇지 않으면 다음과 같은 문제가 발생할 수 있습니다.

+ 하나 이상의 트랜잭션 transaction-safe 엔진(예: InnoDB)의 테이블을 사용하고 트랜잭션 격리 레벨이 SERIALIZABLE이 아닌 경우, 하나의 트랜잭션이 커밋될 때 동일한 테이블을 사용하는 다른 진행 중인 트랜잭션이 첫 번째 트랜잭션의 변경 사항 중 일부만 볼 수 있습니다. 즉, 혼합 엔진에서는 트랜잭션의 원자성이 보장되지 않으며 불일치가 발생할 수 있습니다. 혼합 엔진 트랜잭션이 자주 발생하지 않는 경우 SET TRANSACTION ISOLATION LEVEL을 사용하여 필요에 따라 트랜잭션별로 분리 레벨을 SERIALIZABLE로 설정할 수 있습니다.

+ 트랜잭션 내에서 transaction-safe하지 않는 테이블을 사용하면 자동 커밋 모드의 상태에 관계없이 해당 테이블에 대한 변경 내용이 한 번에 저장됩니다.

+ 트랜잭션 내에서 비 ​​트랜잭션 테이블을 업데이트 한 후 ROLLBACK 문을 발행하면 ER_WARNING_NOT_COMPLETE_ROLLBACK 경고가 발생합니다. 트랜잭션 안전 테이블에 대한 변경 사항은 롤백되지만 트랜잭션 안전 테이블에 대한 변경 사항은 롤백되지 않습니다.

 

각 트랜잭션은 COMMIT에 따라 이진 로그에 하나의 청크로 저장됩니다. 롤백된 트랜잭션은 기록되지 않습니다. (예외 : 비 트랜잭션 테이블에 대한 수정 사항은 롤백 할 수 없습니다. 롤백 된 트랜잭션에 비 트랜잭션 테이블에 대한 수정 사항이 포함 된 경우, 트랜잭션이 아닌 테이블에 대한 수정 사항이 복제되도록하기 위해 전체 트랜잭션이 끝에 ROLLBACK 문으로 로그됩니다.)

 

SET TRANSACTION 문을 사용하여 트랜잭션의 격리 레벨 또는 액세스 모드를 변경할 수 있습니다.

 

롤백은 사용자가 명시 적으로 요청하지 않고 (예 : 오류가 발생한 경우) 암시 적으로 발생할 수있는 느린 작업 일 수 있습니다. 이 때문에 SHOW PROCESSLIST는 ROLLBACK 문으로 수행된 명시적 롤백뿐만 아니라 암시적 롤백에 대해서도 세션의 상태열에 롤백을 표시합니다.

 

노트

MySQL 5.7에서 BEGIN, COMMIT 및 ROLLBACK은 --replicate-do-db 또는 --replicate-ignore-db 규칙의 영향을받지 않습니다.