트랜잭션과 동시성 제어
트랜잭션을 다룰 수 있는 테이블을 작성해 보자
트랜잭션이란
한 덩어리의 쿼리 처리 단위 또는 복수 쿼리를 한 단위로 묶은 것
트랜잭션 4가지 특성, ACID
- Atomicity(원자성)
- Consistency(일관성)
- Isolation(고립성 또는 격리성)
- Durability(지속성)
Atomicity, 원자성
데이터의 변경(INSERT/UPDATE/DELETE)을 수반하는 일련의 데이터 조작이 전부 성공할지 전부 실패할지를 보증하는 구조.
특정 순서의 사건들이 모두 잘 실행되면 COMMIT을 실행,
실패할 경우에는 ROLLBACK을 실행하여 처리 과정 첫번째 순서의 직전 상태까지 돌아감.
Consistency, 일관성
데이터베이스에는 데이터베이스 오브젝트에 대해 각종 정합성 제약을 추가할 수 있음. 일련의 데이터 조작 전후에 그 상태를 유지하는 것을 보증하는 구조
예를 들어, 사용자를 임의로 식별하기 위해 일련번호(사용자 번호)를 사용자에게 지급할 때, 유니크 제약(유일성 제약)을 설정하면 중복된 사용자 번호를 저장할 수 없음.
Isolation, 고립성 또는 격리성
일련의 데이터 조작을 복수 사용자가 동시에 실행해도 '각각의 처리가 모순없이 실행되는 것을 보증'
복수 사용자가 동시에 접근해도 문제가 발생하지 않도록 잠금(Lock)을 걸어서 후속처리를 블록(Block)하는 방법이 있음.
잠금 단위에는 테이블 전체, 블록, 행 등이 있는데 MySQL 트랜잭션 처리는 주로 행 단위의 잠금 기능을 이용.
모순이 없는 상태란, 복수의 트랜잭션이 순서대로 실행되는 경우와 같은 결과를 얻을 수 있는 상태.
Durability, 지속성
일련의 데이터 조작(트랜잭션 조작)을 완료(COMMIT)하고 완료 통지를 사용자가 받는 시점에서 그 조작이 영구적이 되어 그 결과를 잃지 않는 것.
격리 수준 단계
- 커밋되지 않은 읽기, Read Uncommitted
- 커밋된 읽기, Read Committed
- 반복 읽기, Repeatable Read
- 직렬화 가능, Serializable
다른 커넥션에서는 어떻게 보일지 생각해 보자
다른 커넥션에서 테이블을 본다
-
DDL에 따른 암묵적인 커밋
-
오토커밋 설정
트랜잭션의 개시(BEGIN TRANSACTION, START TRANSACTION, SET TRANSACTION 등)가 명시적으로 지정되지 않았을 때 트랜잭션을 구별하는 방법으로 2가지가 있음.
A. 하나의 SQL문이 하나의 트랜잭션으로 구분.
B. 사용자가 COMMIT 또는 ROLLBACK을 실행하기까지가 하나의 트랜잭션.
DDL, DML, DCL
데이터 정의 언어, DDL, Data Definition Language
데이터를 저장하는 그릇인 스키마(데이터베이스) 또는 테이블 등을 작성하거나 제거.
CREATE, DROP, ALTER 등.
데이터 조작 언어, DML, Data Manipulation Language
테이블의 행을 검색하거나 변경하는 데 사용.
SELECT, INSERT, UPDATE, DELETE 등.
데이터 제어 언어, DCL, Data Control Language
데이터베이스에서 실행한 변경을 확정하거나 취소하는 데 사용.
COMMIT, ROLLBACK 등.
복수 커넥션에서 읽기와 쓰기를 하자
mysql> prompt Transaction 트랜잭션명
PROMPT set to Transaction 트랜잭션명
트랜잭션명>
트랜잭션 격리 수준에 따른 외관상 차이
MVCC에 따른 MySQL의 특성
MySQL(InnoDB형 테이블)은 현재 DBMS의 주류가 된 'MVCC, Multi Versioning Concurrency Control' 라는 기술을 사용.
- 읽기를 수행할 경우 갱신 중이라도 블록되지 않는다.
- 읽기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있다.
- 갱신 시 배타적 잠금을 얻는다. 잠금은 기본적으로 행 단위로 얻으며 트랜잭션이 종료할 때까지 유지한다. 격리 수준이나 InnoDB의 설정에 따라 실제로 잠금하는 행의 범위가 다른 경우가 있다.
- 갱신과 갱신은 나중에 온 트랜잭션이 잠금을 획득하려고 할 때 블록된다, 일정 시간을 기다리며 그 사이에 잠금을 획득할 수 없을 경우에는 '잠금 타임아웃, Lock timeout'이 된다.
- 갱신하는 경우 갱신 전의 데이터를 UNDO 로그로 '롤백 세그먼트'라는 영역에 유지한다. 이 'UNDO 로그'는 용도가 2가지인데, 첫 번째는 갱신하는 트랜잭션의 롤백 시 갱신 전으로 되돌리는 것이고, 두 번째는 복수의 트랜잭션으로부터 격리 수준에 따라 대응하는 갱신 데이터를 참조하는 데 이용한다.
잠금 타임아웃과 교착 상태를 시험하자
잠금 타임아웃
Transaction A> set innodb_lock_wait_timeout = 5;
Transaction A> start transaction;
Transaction B> start transaction;
Transaction B> insert into t1 values(4,'Oracle');
Transaction A> insert into t1 values(4,'JavaDB');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
'갱신'과 '갱신'이 부딪히는 경우에는 나중에 온 갱신이 잠금 대기 상태가 됨. 기다린다면 어느 정도를 기다릴지를 설정할 수 있음.
mysql> set innodb_lock_wait_timeout = 5;
타임아웃 오류 후 명시적으로 ROLLBACK을 실행
innodb_rollback_on_timeout 시스템 변수를 설정
교착 상태
Transaction A> create table a(i1 int not null primary key, v2 varchar(20)) engine=innodb;
Transaction A> create table b(i1 int not null primary key, v2 varchar(20)) engine=innodb;
Transaction A> set innodb_lock_wait_timeout = 50; start transaction
Transaction B> start transaction;
Transaction A> insert into a values(1,'Firebird');
Transaction B> insert into b values(1,'MySQL');
Transaction A> insert into b values(1,'Firebird');
Transaction B> insert into a values(1,'MySQL');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
트랜잭션 A가 테이블 a의 잠금을 얻고 트랜잭션 B가 테이블 b의 잠금을 얻었다고 가정.
이 잠금을 유지한 채 서로 잠금을 건 자원에 잠금이 필요한 처리(INSERT/UPDATE/DELETE)를 실행하면 아무리 기다려도 상황이 바꾸지 않는 상태가 됨. == 교착 상태
교착 상태의 빈도를 낮추는 대책
DBMS 전반적인 대책
- 트랜잭션을 자주 커밋.
- 정해진 순서로 테이블에 액세스.
- 필요 없는 경우에는 읽기 잠금 획득을 피함.
- 쿼리에 의한 잠금 범위를 더 좁히거나 잠금 정도를 더 작은 것으로 함.
- 테이블 단위의 잠금을 획득해 갱신을 직렬화.
MySQL(InnoDB)의 대책
- 테이블에 적절한 인덱스를 추가해 쿼리가 이를 이용하게 함.
해서는 안 되는 트랜잭션 처리
주의 1. 오토커밋
주의 2. 긴 트랜잭션
- 대량 처리를 한 개의 트랜잭션이 실행
- 아무것도 하지 않는 트랜잭션 유의
- 트랜잭션 중에 대화 처리 추가
- 처리 능력 이상의 트랜잭션 수
트랜잭션 관련 설정 확인
집계와 서브쿼리
서브쿼리
SELECT 명령에 의한 데이터 질의, 상부가 아닌 하부의 부수적인 질의.
주로 WHERE구에서 사용. WHERE구는 SELECT,DELETE,UPDATE 구에서 사용할 수 있는데 이들 중 어떤 명령에서든 서브쿼리를 사용할 수 있음.
(SELECT 명령)
DELETE의 WHERE구에서 서브쿼리 사용하기
sample 54 테이블의 a column의 최솟값을 지우고 싶은데 그 최솟값을 모를 때,
DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);
변수로 설정해서 사용.
변수 = (SELCT MIN(a) FROM Sample54);
DELETE FROM sample54 WHERE a=변수;
mysql> set @a= (SELCT MIN(a) FROM Sample54);
DELETE FROM sample54 WHERE a=@a;
스칼라 값
서브쿼리의 패턴
- 하나의 값을 반환하는 패턴
- 복수의 행이 반환되지만 열은 하나인 패턴
- 하나의 행이 반환되지만 열이 복수인 패턴
- 복수의 행, 복수의 열이 반환되는 패턴
SELECT 명령이 하나의 값만을 반환하는 것을 '스칼라 값을 반환한다'고 함.
SELECT 구에서 서브쿼리 사용하기
MySQL에서는 FROM구 생략 가능.
SELECT (SELECT COUNT(*) FROM sample51) AS sq1, (SELECT COUNT(*) FROM sample54) AS sq2;
Oracle 등 전통적인 데이터베이스 제품에서는 FROM 생략 불가. 따라서 FROM DUAL로 지정 필요. DUAL은 시스템 쪽에서 데이터베이스에 기본적으로 작성되는 테이블.
SELECT (SELECT COUNT(*) FROM sample51) AS sq1, (SELECT COUNT(*) FROM sample54) AS sq2 FROM DUAL;
SET 구에서 서브쿼리 사용하기
UPDATE sample54 SET a=(SELECT MAX(a) FROM sample54);
FROM 구에서 서브쿼리 사용하기
SELECT * FROM (SELECT * FROM sample 54) sq;
SELECT * FROM (SELECT * FROM sample 54) AS sq;
SELECT 명령 안에 SELECT 명령이 들어있는 것을 네스티드(nested) 구조, 중첩 구조, 내포구조라 함.
sq는 테이블의 별명.
INSERT 명령과 서브쿼리
INSERT INTO sample541 VALUES ((SELECT COUNT(*) FROM sample51),(SELECT COUNT(*) FROM sample54));
VALUES 구에서 서브쿼리 사용. 자료형 일치해야함.
INSERT INTO sample541 SELECT 1,2;
INSERT SELECT라 불리는 명령으로 INSERT와 SELECT를 합친 것과 같은 명령.
INSERT INTO sample541 VALUES (1,2)와 같은 의미.
상관 서브쿼리
EXISTS
EXISTS(SELECT 명령)
데이터가 존재하는 지 아닌지를 판별.
UPDATE sample551 SET a= '있음' WHERE EXISTS(SELECT * FROM sample552 WHERE no2 = no);
NOT EXISTS
행이 존재하지 않는 상태가 참.
UPDATE sample551 SET a= '없음' WHERE NOT EXISTS(SELECT * FROM sample552 WHERE no2 = no);
IN
열명 IN (집합)
SELECT * FROM sample551 WHERE no IN (SELECT no2 FROM sample 552);
집합 안의 값이 존재하는 지, 비교.
IN에서는 오른쪽에 집합을 지정. 왼쪽에 지정된 값과 같은 값이 집합 안에 존재하면 참을 반환.
IN에서는 집합 안에 NULL 값이 있어도 무시하지 않음.
NULL을 비교할 때는 IS NULL.
NOT IN
집합에 값이 포함되어 있지 않을 경우 참.
집합 안에 NULL 값이 있으면 설령 왼쪽 값이 집합 안에 포함되어 있지 않더라도 참을 반환하지 않음. 결과는 불명(UNKNOWN).
