3장 쿼리 입문 에서 테이블 유형 4가지 중, 임시 테이블은 특정시점(트랜잭션이 끝날 때, DB세션이 닫힐 때 등)에 사라진다고 배웠었다. 트랜잭션에 대해 알아보자. (책의 내용만으로 조금 부족해서 여러 포스팅을 참고했다)
트랜잭션이란,
여러 글을 봤는데 내가 이해하기 가장 쉬운건 이거였다.
데이터베이스 상태를 바꾸는 작업 단위
단위 1개만 수행될 수도 있고, 여러개가 순차적으로 수행될 수도 있다. 하나의 트랜잭션은 Commit 되거나 Rollback 된다. (여기서 Commit 은 git의 commit과 유사하다고 생각하면 편하다)
특징
- 원자성 : 트랜잭션의 결과는 DB에 반영되거나 / 안되거나 2가지 결과만을 나타낸다.
- 일관성 : 트랜잭션이 진행되는동안 DB가 변경되더라도, 이전에 사용한 DB를 참조한다. 즉, 시작부터 종료될때까지 같은 형태의 DB를 참조한다.
- 독립성 : 트랜잭션이 2개 이상 실행될 때, 트랜잭션끼리 영향을 주지 못한다. 즉, 하나의 트랜잭션이 또 다른 트랜잭션의 결과를 참조할 수 없다.
- 영구성 : 트랜잭션이 성공적으로 완료(commit) 되었을 때 결과를 영구적으로 DB에 반영한다. / 만약, 영구스토리지에 반영되기 전 서버가 종료되면 서버가 다시 시작될 때 트랜잭션의 변경 사항을 다시 적용한다.
그럼 지금까지는...?
내가 여태까지 쓴 모든 쿼리들이 각각 하나의 트랜잭션이었던 거다. 근데 왜 난 트랜잭션을 쓴 적이 없을까?
MySQL 8.0은 기본적으로 Autocommit mode다. 즉, 내가 서버한테 "나 수동으로 트랜잭션할꺼야" 라고 말해주지 않고 INSERT, DELETE, UPDATE와 같은 쿼리를 쓰면 서버는 자동으로 commit을 했던 것.
SELECT의 경우에는 데이터 수정이 일어나는 것이 아니므로, 트랜잭션이 굳이 필요없다.
왜 써야 하지?
데이터베이스 자체를 여러명의 사용자가 동시에 사용하기 때문이다.
여지껏 예로 들어왔던 DVD 대여점을 생각해보자. 내가 이번주의 영화 대여 내역을 보고서로 만들려고 쿼리를 작성하는 동안에도, 아래와 같은 작업들을 또 다른 직원이 수행하고 있을 수 있다.
- 새로운 고객이 영화를 대여한다.
- 반납일 이후 고객이 영화를 반납하고, 연체료를 지불했다.
- 5개의 새로운 DVD를 재고 목록에 추가한다.
그럼 내가 작성하고 있는 보고서는 어떤 테이블을 기준으로 나와야할까? 이런 기준 때문에 잠금이 필요하다.
잠금 lock
데이터 서버가 데이터 자원의 동시 사용을 제어하는데 사용한다. DB 일부가 잠기면, 해당 데이터를 쓰거나write 읽으려는read 다른 사용자는 잠금이 해제될 때까지 기다려야 한다.
구글링을 해보니 lock에 대해서도 내용이 많은 것 같은데, 책에서는 간단히 다루고 있으니 나도 잠금이란 이런 것이구나 정도만 알고 넘어가본다...
- 스토리지 엔진 레벨의 잠금 : 스토리지 엔진 간 상호 영향을 미치지 않음
- MySQL 엔진 레벨의 잠금 : 모든 스토리지 엔진에 영향 (table locks ...)
잠금단위
스토리지 엔진이 어떤 거냐에 따라 잠금 단위는 달라질 수 있다. 기본적으로 3가지 수준만 살펴보면,
- 테이블 잠금(table locks) : 여러 사용자가 동일한 테이블의 데이터를 동시에 수정하지 못하도록 한다.
- 페이지 잠금(page locks) : 여러 사용자가 테이블의 동일한 페이지의 데이터를 동시에 수정하지 못하도록 한다. (한 페이지는 보통 2KB~16KB 범위의 메모리 세그먼트)
- 행 잠금(row locks) : 여러 사용자가 테이블에서 동일한 행을 동시에 수정하지 못하도록 한다.
각각 접근방식의 장단점이 있다. 전체 테이블을 잠그면 부기는 간단하지만, 사용자 수가 증가함에 따라 대기시간이 길어진다. 행별로 잠그면 더 많은 부기가 필요하지만, 여러 사용자가 각자 다른 행에 대해 작업한다면 동일한 테이블도 수정할 수 있다.
트랜잭션 사용하기
트랜잭션의 상태는 이렇게 나눠질 수 있다.
- 모든 트랜잭션이 잘 되었다 →
commit - 실패가 발생했다 →
rollback- 불완전한 트랜잭션을 찾아서 이전상태로 되돌린다.
트랜잭션의 프로세스를 잘 이해할 수 있는 쿼리를 보자. 0001 계좌에서 500달러를 0002 계좌로 보내는 시나리오다.
START TRANSACTION ;
-- 0001 계좌에서 잔고가 충분한지 확인하고, 돈을 출금
UPDATE account
SET avail_balance = avail_balance - 500
WHERE account_id = 0001
AND avail_balance > 500 ;
-- 0002 계좌로 입금
IF <1개의 행이 이전 쿼리로 인해 업데이트됨>
THEN
UPDATE account
SET avail_balance = avail_balance + 500
WHERE account_id = 0002 ;
-- [case1] 정상작동 : DB에 영구 적용
IF <1개의 행이 이전 쿼리로 인해 업데이트됨>
THEN
COMMIT ;
-- [case2] 문제발생 : 이 거래의 모든 변경사항을 취소
ELSE
ROLLBACK ;
END IF ;
-- [case3] 자금부족 또는 업데이트 중 오류 발생
ELSE
ROLLBACK ;
END IF ; 트랜잭션 시작
아래 명령어를 통해 트랜잭션을 실행할 수 있다.
START TRANSACTION ; 기본적으로 MySQL은 Autocommit mode이므로 아래 명령어를 통해 이를 비활성화 할 수 있다.
SET AUTOCOMMIT = 0 트랜잭션 종료
일단 Autocommit mode가 비활성화되면 commit 명령어를 사용해서 서버가 변경사항을 영구적으로 적용하고, 잠금 등을 해재할 수 있도록 지시해야 한다.
트랜잭션 시작 이후의 모든 변경사항을 실행취소하려면 rollback 명령어를 실행하면 된다.
트랜잭션이 종료될 수 있는 몇 가지 시나리오들
- 서버가 종료되고 서버가 재시작되면서 트랜잭션이 자동으로 롤백된다.
- 다른
start transaction명령어를 실행하면 이전 트랜잭션이 커밋된다. alter table과 같은 SQL 스키마 문을 실행하면 현재 트랜잭션이 커밋되고 새로운 트랜잭션이 시작된다.- 새 테이블 만들기, 인덱스 추가, 테이블에서 열 삭제와 같은 DB를 변경하는 작업은 롤백할 수 없으므로, 스키마를 변경하는 명령어는 트랜잭션 외부에서 수행되어야 한다.
- 서버가 교착상태deadlock를 감지했을 때 해당 트랜잭션이 원인이라고 판단되면 서버는 트랜잭션을 조기 종료한다. 이 경우 트랜잭션이 롤백되고 오류 메시지가 표시된다.
- 두 트랜잭션이 동일한 페이지 또는 동일한 행을 수정할 때, 잠금이 해제되기를 영원히 기다릴 수 있다. 이런 교착상태가 감지되면 아래와 같이 에러가 나온다.
Message: Deadlock found when trying to get lock; try restarting transaction- 이런 에러메시지를 안보려면 데이터 리소스가 항상 동일한 순서로 접근하도록 짜야 한다.
트랜잭션 세이브포인트
세이브포인트를 설정하면, 트랜잭션 전체를 롤백하지 않고 특정 위치(=세이브포인트)로 롤백할 수 있다.
모든 세이브포인트는 이름을 지정할 수 있기 때문에 단일 트랜잭션 내에서 여러 세이브포인트를 가질 수 있다.
세이브포인트 생성
SAVEPOINT 이름 ; 세이브포인트로 롤백하기 (실행)
ROLLBACK TO SAVEPOINT 이름 ; 주의하기
- 세이브포인트는 실행 전까지는 아무 일도 일어나지 않는다. 이름을 지정하더라도 아무것도 저장되지 않는다. 트랜잭션을 영구화하려면
commit을 실행해야한다. - 세이브포인트의 이름 지정없이
rollback을 실행하면 트랜잭션 내의 모든 세이브포인트가 무시되고 전체 트랜잭션이 실행 취소된다.
참고 : MySQL 스토리지엔진
MySQL 8.0 버전에는 아래와 같은 스토리지 엔진이 포함되어 있다. 기본엔진은 InnoDB다.
- MyISAM : 테이블 잠금을 사용하는 nontransaction 엔진
- MEMORY : 인메모리 테이블에 사용되는 nontransaction 엔진
- CSV : 데이터를 쉼표로 구분해서 파일에 저장하는 transaction 엔진
- InnoDB : 행 수준 잠금을 사용하는 transaction 엔진
- Merge : 여러개의 MyISAM 테이블을 단일 테이블로 표시하는 특수 엔진 (테이블 분할)
- Archive : 주로 보관 목적으로 대량의 인덱싱되지 않은 데이터를 저장하는 특수 엔진
InnoDB 엔진으로 변경하기
ALTER TABLE 테이블이름 ENGINE = INNODB ; 