10주 MariaDB에서 트랜잭션 지원 및 동시성 제어 기능

트랜잭션 개념 트랜잭션(transaction) 데이터베이스에서 트랜잭션을 정의하는 이유 DBMS에서 데이터를 다루는 논리적인 작업의 단위 데이터베이스에서 트랜잭션을 정의하는 이유 데이터베이스에서 데이터를 다룰 때 장애가 일어날 때 데이터를 복구하 는 작업의 단위가 됨. 데이터베이스에서 여러 작업이 동시에 같은 데이터를 다룰 때가 이 작업 을 서로 분리하는 단위가 됨. 트랜잭션은 전체가 수행되거나 또는 전혀 수행되지 않아야 함(all or nothing) 예)은행 업무를 보는데 A 계좌(박지성)에서 B 계좌(김연아)로 10,000원을 이체할 경우 BEGIN ① A 계좌(박지성)에서 10,000원을 인출하는 UPDATE 문 ② B 계좌(김연아)에 10,000원을 입금하는 UPDATE 문 END

트랜잭션 개념 트랜잭션의 4가지 성격 원자성(atomicity) : 트랜잭션은 전부, 전무(all or nothing)의 실행만이 있다 일부 실행으로 트랜젝션의 기능을 가질 수는 없다. 일관성(consistency) : 트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관된 데이터베이스 상태로 된다. 격리성(isolation) : 연산의 중간결과에 다른 트랜잭션이나 작업이 접근할 수 없다. 영속성(durability) : 트랜잭션의 일단 그 실행을 성공적으로 끝내면 그 결과를 어떠한 경우에라도 보장받는다.

1. 트랜잭션

트랜잭션 제어 명령문 SET AUTOCOMMIT COMMIT ROLLBACK SET TRANSACTION 트랜잭션을 종료하고 트랜잭션의 변경 내용을 영구적으로 보존 ROLLBACK 트랜잭션 실행 내용을 원래대로 되돌리고 트랜잭션을 종료함 SET TRANSACTION 고립 수준 등 트랜잭션의 다양한 속성을 설정

COMMIT/ROLLBACK 개념 (1) CREATE TABLE trantest ( empID char(4), empName varchar(20) ) ENGINE=InnoDB; SET autocommit = 0; INSERT INTO trantest values ('A001', 'Kim Byung'); COMMIT; SELECT * FROM trantest; INSERT INTO trantest values ('A002', 'Hong Kil'); INSERT INTO trantest values ('A003', 'Park Moon'); ROLLBACK; SET autocommit = 1;

COMMIT/ROLLBACK 개념 (2) SET autocommit = 0; INSERT INTO trantest values ('A002', 'Hong Kil'); INSERT INTO trantest values ('A003', 'Park Moon'); INSERT INTO trantest values ('A004', ‘Kim Dong'); COMMIT; SELECT * FROM trantest; SET sql_safe_updates = 0; DELETE FROM trantest WHERE empID = 'A002'; ROLLBACK; SET autocommit = 1;

COMMIT/ROLLBACK 개념 (3) 예제-1: 학생이 없는 학과를 테이블에서 삭제함 CREATE PROCEDURE proc (IN in_dno INT) BEGIN DECLARE cnt INT; SELECT COUNT(*) INTO cnt FROM std_heap WHERE dno = in_dno; IF cnt = 0 THEN DELETE FROM dept_heap ELSE SELECT '자료가 존재하므로 삭제 불가.' ; END IF; COMMIT; END $$ CALL proc(3);

COMMIT/ROLLBACK 개념 (4) 예제-2: 학생이 없는 학과를 테이블에서 삭제함 CREATE PROCEDURE proc2 (IN in_dno INT) BEGIN DECLARE cnt INT; DELETE FROM dept_heap WHERE dno = in_dno; SELECT COUNT(*) INTO cnt FROM std_heap WHERE dno = in_dno; IF cnt = 0 THEN COMMIT; ELSE ROLLBACK; SELECT '자료가 존재하므로 삭제 불가.' ; END IF; END $$ CALL proc2 (3);

동시성 제어 세션을 여러 개 실행하여 동시에 여러 트랜잭션을 실행

동시성 제어

동시성 제어

동시성 제어

트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 SET autocommit = 0; SET sql_safe_updates = 0; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; UPDATE trantest SET empName = ‘AA AAAA’ WHERE empID = ‘A001’ UPDATE trantest SET empName = ‘BB BBBB’ WHERE empID = ‘A001’; UPDATE trantest SET empName = ‘CC CCCC’ WHERE empID = ‘A002’; wait COMMIT;

트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 UPDATE trantest SET empName = ‘AA AAAA’ WHERE empID = ‘A001’ UPDATE trantest SET empName = ‘BB BBBB’ WHERE empID = ‘A002’; UPDATE trantest SET empName = ‘CC CCCC’ WHERE empID = ‘A003’; UPDATE trantest SET empName = ‘DD DDDD’ wait

트랜잭션 고립 수준 트랜잭션 고립 수준 명령어(transaction isolation level instruction) DBMS가 트랜잭션을 동시에 실행할 때 데이터의 일관성을 유지하는 정도 를 선택하는 명령어 트랜잭션 고립 수준 명령어와 발생 현상 문제 고립 수준 오손 읽기 반복불가능 읽기 유령데이터 읽기 READ UNCOMMITTED 가능 READ COMMITTED 불가능 REPEATABLE READ 가능/불가능(InnoDB) SERIALIZABLE

READ UNCOMMITTED(Level = 0) 갱신 데이터에만 배타락을 걸고, 읽는 데이터에는 아무런 락도 걸지 않음 오손 읽기(Dirty read) 발생 가능함 표 8-6 READ UNCOMMITTED 모드 요약 모드 READ UNCOMMITTED LOCK SELECT 문 - 공유락 걸지 않음. 다른 트랜잭션의 배타락이 걸린 데이터를 제한없이 읽음 UPDATE 문 - 배타락 설정 SQL 문 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 문제점 오손 읽기, 반복불가능 읽기, 유령데이터 읽기

READ UNCOMMITTED(Level = 0)

READ UNCOMMITTED(Level = 0) 갱신 손실(Lost Update) 문제 방지 테스트 트랜잭션 T1 READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET autocommit = 0; SET sql_safe_updates = 0; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; UPDATE trantest SET empName = ‘AAAAA’ WHERE empID = ‘A003; UPDATE trantest SET empName = ‘BBBBB’ Running … ROLLBACK; SELECT empName FROM trantest WHERE empID = ‘A003’; ROLLBACK

READ UNCOMMITTED(Level = 0) READ UNCOMMITTED 테스트 – 오손 읽기(Dirty read) 발생 테스트 트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; UPDATE trantest SET empName = ‘AAAAA’ WHERE empID = ‘A003; SELECT empName FROM trantest WHERE empID = ‘A003’; COMMIT; ROLLBACK;

READ COMMITTED(Level=1) 갱신 데이터에 배타 락을 걸고 백업 버전을 생성하고 갱신함. 읽는 데이터에 공유 락을 걸고, 읽고 난 후에 즉시 락을 해제함. 공유 락 을 획득하지 못할 경우 백업 버전의 데이터를 읽음 (MVCC 방법). 표 8-7 READ COMMITTED 모드 요약 모드 READ COMMITTED LOCK SELECT 문 - 공유락을 걸고 끝나면 바로 해지, 다른 트랜잭션의 배타락이 설정된 경우 백업 버전을 읽음. UPDATE 문 - 배타락 설정 SQL 문 SET TRANSACTION ISOLATION LEVEL READ COMMITTED 문제점 반복불가능 읽기, 유령데이터 읽기

READ COMMITTED(Level=1)

READ COMMITTED(Level=1) 오손 읽기(Dirty read) 방지 테스트 트랜잭션 T1 READ COMMITTED 모드 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE trantest SET empName = 'AAAAA' WHERE empID = 'A003'; SELECT empName FROM trantest WHERE empID = 'A003'; ROLLBACK; (또는 COMMIT;)

REPEATABLE READ(Level=2) 자신이 읽은 데이터에 대한 공유락을 트랜잭션이 종료할 때까지 유지하여 같은 데이터를 반복적으로 읽을 수 있도록 함. 다른 트랜잭션이 동일한 데이터를 갱신하고자 할 경우, 새 버전을 만들어 서 갱신함 표 8-8 REPEATABLE READ 모드 요약 모드 REPEATABLE READ LOCK SELECT 문 - 공유락을 걸고 트랜잭션을 끝까지 유지 UPDATE 문 - 배타락 설정. 다른 트랜잭션의 공유락이 설정된 경우 새 버전을 만들어 갱신함 SQL 문 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 문제점 유령데이터 읽기

REPEATABLE READ(Level=2)

REPEATABLE READ(Level=2) UNREPEATABLE READ 문제 테스트 트랜잭션 T1 READ COMMITTED 모드 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT empName FROM trantest WHERE empID = 'A003'; UPDATE trantest SET empName = 'AAAAA' WHERE empID = 'A003'; COMMIT;

REPEATABLE READ(Level=2)

REPEATABLE READ(Level=2) READ COMMITTED 모드 트랜잭션 T2 REPEATABLE READ 모드 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ; SELECT empName FROM trantest WHERE empID = 'A003'; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE trantest SET empName = ‘BBBBB' WHERE empID = 'A003'; COMMIT;

SERIALIZABLE(Level=3) 고립 수준이 가장 높은 명령어로, 트랜잭션의 조건 범위 내에 새로운 데이터의 생성을 방지함 (유령 레코드를 방지함) . 읽은 데이터의 범위에 공유락을 트랜잭션 종료시까지 유지함 트랜잭션이 종료할 때까지 조건 범위내에 새로운 데이터 삽입 방지함 (즉 유령(PHANTOM) 레코드 생성을 방지함. 표 8-9 SERIALIZABLE 모드 요약 모드 SERIALIZABLE LOCK SELECT 문 – 조건 범위에 공유락을 걸고 트랜잭션을 끝까지 유지 UPDATE 문 - 배타락 설정. 범위내에 공유락이 있으면 대기함. SQL 문 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 문제점 없음

SERIALIZABLE(Level=3)

SERIALIZABLE(Level=3) 유령 데이터 읽기 문제 테스트 트랜잭션 T1 READ COMMITTED 모드 트랜잭션 T2 REPEATABLE READ 모드 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ; SELECT empName FROM trantest WHERE empID = 'A001'; WHERE empID = 'A003'; SELECT empName FROM trantest ; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; INSERT INTO trantest VALUES('A002', 'Lee Dong'); COMMIT; DELETE FROM trantest WHERE empID = ‘A002’;

SERIALIZABLE(Level=3) 유령 데이터 읽기 문제 방지 테스트 트랜잭션 T1 READ COMMITTED 모드 트랜잭션 T2 SERIALIZABLE 모드 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ; SELECT empName FROM trantest ; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; INSERT INTO trantest VALUES('A002', 'Lee Dong'); Running … COMMIT;