[Database] 09. 트랜잭션 처리 - COMMIT 과 ROLLBACK

 

트랜잭션 개념

 

트랜잭션이란? 

 

- 트랜잭션(Transaction)은 데이터베이스의 상태를 변환시키는 기능을 수행하는 작업의 단위

- 또는 한꺼번에 모두 수행되어야 할 일련의 연산들을 의미함

- 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위이다

- 업무의 실행단위 / 논리적인 명령단위 / 개념상의 실행단위

- 다른세션이 건드리지 못하도록 물리적인 명령어 단위인 LOCK을 사용

- 그 후 UNLOCK 과 COMMIT 을 이용하여 수정

 

ACID란? 

 

- ACID는 데이터베이스 트랜잭션이 안전하게 수행된다는 것을 보장하기 위한 성질을 가리키는 약어

- 종류 : 

• 원자성(Atomicity)
• 일관성(Consistency)
• 독립성(Isolation)
• 지속성(Durability)

 

ACID - 위키백과, 우리 모두의 백과사전 (wikipedia.org)

ACID - 위키백과, 우리 모두의 백과사전

 

 

트랜잭션 예시

 

1. 계좌이체 : 다른세션이 건드리지 못하도록 LOCK  ->  UPDATE : A--  ->  UPDATE : B++  ->  UNLOCK  ->  COMMIT

2. 이벤트 게시글 등록 : LOCK  ->  INSERT : 글생성  ->  UPDATE : 포인트++  ->  UNLOCK  ->  COMMIT

 

** 1, 2의 실행은 모두 성공적으로 한 번에 실행되어야 한다.

- 한쪽만 실행되어서는 안된다.

- ALL or NOTHING !

- 전부 실행되던지 아니면 모두 취소하던지 해야한다.

 

 

AUTOCOMMIT 모드 변경

 

1. AUTOCOMMIT 모드 확인

 

 

- AUTOCOMMIT  = 1 이면 True, 즉 COMMIT이 자동으로 이루어지고 있다는 말 (트랜잭션 처리 안함)

   -> '커밋'명령없이 자동 반영됨

 

- AUTOCOMMIT  = 0 이면 False 즉, COMMIT을 사용자가 직접 해주어야함

 

* 우리는 commit과 rollback을 공부하기 위해 AUTOCOMMIT 모드를 풀어주어야함

 

 

2. AUTOCOMMIT 모드로 만드는 작업 - my.ini 파일 수정

 

- C:\ProgramData\MySQL\MySQL Server 8.0 으로 들어가서 my.ini 를 메모장으로 열기

- [mysqld] 아랫줄에 autocommit=0 쓰고 저장  

 

 

 

3. AUTOCOMMIT 모드로 만드는 작업 - 서비스 다시 시작

 

- 서비스 > 윈도우 서비스 MySQL80  > 서비스 다시 시작 > 상태가 실행중이라고 뜨는지 확인

 

4. AUTOCOMMIT 모드로 만드는 작업 - cmd 창에서 수정 확인

 

- cmd 다시 키기 > @@autocommit 이라고 적기 > 0 으로 바뀐 것을 알 수 있음

- SET AUTOCOMMIT = TRUE / FALSE; 로 수정 가능

 

 

 

5. AUTOCOMMIT 모드로 만드는 작업 - workbench 에서 변경 및 확인

 

- workbench > root 계정 접속 > select @@autocommit; 실행 > autocommit = 0 임을 확인할 수 있음

 

- autocommit = 1 로 변경하는 방법  MySQL Workbench 상단 메뉴 > Edit > Preference 클릭

 

 

- SQL Editor > SQL Execution -> General 항목 > [  ] New connections use auto commit mode 체크를 해제한다.

 

- MySQL workbench 재접속 > select @@autocommit; 실행 > autocommit = 0 임을 확인할 수 있음

 

 

COMMIT / ROLLBACK

 

1. COMMIT 

 

- 레코드 추가 : INSERT INTO member(`ID`, `PWD`) VALUES('lee07', '7777');

- lee07 추가된거 확인 : SELECT * FROM lecture.member ;

- 상단 메뉴 아이콘 Reconnect to DBMS 클릭 후 조회하면 lee07이 추가되기 전 상태임 !

- 세션1의 실행은 임시저장소(UNDO)에 보관, 디스크는 변경되지 않음

- 즉, INSERT 결과는 임시 저장소에 저장되어 있다가 커밋/롤백을 완료해야 적용이 된다.

- 다시 레코드 추가 단계 실행

- 다른 계정에서 확인해보기 :  lee07 추가되지 않음을 알 수 있음

- 실행결과를 확인(SELECT)하고 이상이 없으면 다른 사용자도 참조할 수 있도록 COMMIT ; 실행

- 다시 다른 계정에서 상단 메뉴 아이콘 Reconnect to DBMS 클릭 후 조회 -> lee07가 추가된 것을 확인

 

2. ROLLBACK

 

- 레코드 추가 : INSERT INTO member(`ID`, `PWD`) VALUES('test', '8888');

- test 추가된거 확인 : SELECT * FROM lecture.member ;

- 되돌리기 : ROLLBACK ;

- test 사라진거 확인 : SELECT * FROM lecture.member ;

 

 

3. UPDATE

 

- 업데이트 : UPDATE member SET ID = 'han08', PWD = '8888' WHERE ID = 'lee07';    

- 업데이트된거 확인 : SELECT * FROM lecture.member ;

- COMMIT 실행 및 업데이트 된 결과 확인

- 그러나 ! 다른 계정으로 다시 들어와서 UPDATE 작업을 다시 실행

- 이번에는 다른 ID를 업데이트 :  UPDATE member SET ID = 'han08', PWD = '8888' WHERE ID = 'lee07' ;

 

에러발생 (계속 로딩중으로 뜸) !  ->  LOCK 상태이므로..

- Error Code: 2013. Lost connection to MySQL server during query

 

 

4. MySQL workbench 방법 

 

- 계정1에서 UPDATE 한 후 COMMIT ; 실행

- 계정2에서 UPDATE 하기전에 Reconnect to DBMS를 눌러서 재접속을 먼저 해주어야함

- 계정2에서 UPDATE하면 잘 실행이 되며 계정1에서 했던 작업도 반영이 된 것을 알 수 있음

 

 

5. CMD에서 처리하는 방법 (우리가 처음에 메모장 수정으로 했던 방법 오류 수정)

 

1. 계정1에서 COMMIT을 해줘도 계정2에는 반영이 안됨

 

**MySQL workbench에서는 재접속 버튼이 있어서 그걸 누르고 나서는 커밋 반영이 됨

 

해결법

 

1. 메모장을 관리자 권한으로 실행

2. [mysqld] 아랫줄에 autocommit=0 썼던 거 지우고 저장

3. 서비스 > mysql 다시 실행

4. MySQL Workbench >  [  ] New connections use auto commit mode 체크를 다시 적용

5. cmd 창 다시 실행

 

cmd 에서 트랜잭션 모드 설정하는 법 2가지

 

 

** 일단, 오라클은 세션이 연결되면 무조건 cmmit과 rollback을 해줘야되게끔 설정이 되어있음

 

[트랜잭션] 실습 1. SET AUTOCOMMIT = 0

 

- 특징 : commit이나 rollback 을 항상 사용하고 싶을 때 아예 모드를 바꿔놓음

 

- 실습환경 : MySQL cmd Client 콘솔창 2개(SESSION1,2)를 열어 실행

      [CMD1] root계정 접속 -- 세션1

      [CMD2] root계정 접속 -- 세션2

 

- SET AUTOCOMMIT = 0; 해주면 계정1에서 한거니까 본인꺼에는 COMMIT을 하지 않아도 잘 반영이 됨

 

- 하지만 계정2에서는 반영이 안되있음 (아직 COMMIT 하지 않았기때문에!)

- COMMIT 하면 잘 실행이 된 것을 알 수 있음

- 또한 SELECT @@AUTOCOMMIT;을 해보면 계속 0(FALSE)이 유지되어있음을 알 수 있음

 

 

[트랜잭션] 실습 2. START TRANSACTION

 

- 특징1 : commit이나 rollback 이 필요한 작업 때만 한 번씩 트랜잭션을 해줄 때 사용

- 특징2 : START TRANSACTION을 해줘도 select @@autocommit ;을 해보면 계속 1이다.

            -> autocommit모드에는 영향을 주지 않음을 알 수 있음

 

- 트랜잭션 처리 미적용 확인

- start transaction으로 해주면 autocommit은 변함없이 1이다

- 하지만 한 번의 작업에 한해 트랜잭션이 실행된다.

- 커밋을 하면 우선 계정1에는 당연히 생겨있고, 계정2(아래 사진)도 INSERT가 반영된 것을 볼 수 있다.

 

 

[트랜잭션] 실습3. 트랜잭션 이해를 위한 계좌이체 연습

 

-- 계좌 테이블 생성
CREATE TABLE `ACCOUNT`(
    `ACCNUM` CHAR(5) NOT NULL,
    `NAME` VARCHAR(50) NOT NULL,
    `BALANCE` BIGINT UNSIGNED DEFAULT 0,
    `UPDATE` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW(),
    PRIMARY KEY (`ACCNUM`)
);

-- 통장 개설
INSERT INTO `ACCOUNT`(
	`ACCNUM`, `NAME`
)VALUES(
	'01-11', '미미'
); 
INSERT INTO `ACCOUNT`(
	`ACCNUM`, `NAME`
)VALUES(
	'01-12', '나나'
);

-- 입금
UPDATE `ACCOUNT`
SET `BALANCE` = 10000
WHERE `ACCNUM` = '01-11'; 

UPDATE `ACCOUNT`
SET `BALANCE` = 10000
WHERE `ACCNUM` = '01-12'; 

-- 계좌이체
UPDATE `ACCOUNT`
SET `BALANCE` = `BALANCE` - 5000
WHERE `ACCNUM` = '01-11';

UPDATE `ACCOUNT`
SET `BALANCE` = `BALANCE` + 5000
WHERE `ACCNUM` = '01-12';

COMMIT;
ROLLBACK;
SELECT * FROM ACCOUNT;