Mariadb 트랜잭션과 동시성 제어 장종원 Phobos90@naver.COM.

Presentation on theme: "Mariadb 트랜잭션과 동시성 제어 장종원 Phobos90@naver.COM."— Presentation transcript:

1 Mariadb 트랜잭션과 동시성 제어 장종원

2 트랜잭션 데이터베이스 시스템에서 최소의 업무처리 단위 SQL문의 하나의 논리적 작업 단위로 성공하거나 실패하는 일련의 SQL문
MariaDB 서버는 트랜잭션을 기본으로 데이터의 일관성을 보증

3 커밋과 롤백 커밋(Commit) 롤백(Rollback) 트랜잭션에 의해 변경된 데이터에 대해 확정하는 문장
수행된 모든 문장에 대한 데이터의 변경 사항을 영구적으로 반영 실행 후 트랜잭션이 종료됨 롤백(Rollback) 트랜잭션의 변경을 취소하는 문장

4 커밋과 롤백 SQL 1 SQL 2 SQL 3 COMMIT 트랜잭션 트랜잭션 시작 트랜잭션 종료 ROLLBACK

5 실습 #1 Commit과 Rollback 실습
(1) std_heap 테이블을 다시 생성함 DROP TABLE std_heap; CREATE TABLE std_heap ( stid int, name char(20), grade int, age int, addr char(60), dno int ) ENGINE=InnoDB; (2) 트랜잭션 실행 준비 SET autocommit = 0; SET sql_safe_updates = 0; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; (3) COMMIT/ROLLBACK 실습 INSERT INTO std_heap VALUES (101, 'JS Kim', 3, 22, 'Chuncheon', 3); INSERT INTO std_heap VALUES (102, ‘PK Lee', 2, 21, ‘Seoul', 1); INSERT INTO std_heap VALUES (105, ‘AB Park', 2, 20, ‘Wonju', 2); INSERT INTO std_heap VALUES (106, ‘CD Hwang', 1, 19, ‘Chuncheon', 2); INSERT INTO std_heap VALUES (110, ‘XY Choi', 2, 21, ‘Seoul', 3); COMMIT; SELECT * FROM std_heap ; DROP TABLE Dept; CREATE TABLE Dept ( dept_no int, dept_name varchar(100) ); INSERT INTO Dept VALUES(1, 'A'); INSERT INTO Dept VALUES(3, 'C'); INSERT INTO Dept VALUES(2, 'B'); INSERT INTO Dept VALUES(5, 'E'); SELECT * FROM Dept; DELETE FROM Dept where dept_name = 'B'; INSERT INTO Dept values(4, 'D'); DROP TABLE index_Dept; CREATE TABLE index_Dept ( dept_no int PRIMARY KEY, ) ORGANIZATION INDEX; INSERT INTO index_Dept VALUES(1, 'A'); INSERT INTO index_Dept VALUES(3, 'C'); INSERT INTO index_Dept VALUES(2, 'B'); INSERT INTO index_Dept VALUES(5, 'E'); SELECT * FROM index_Dept; DELETE FROM index_Dept where dept_name = 'B'; INSERT INTO index_Dept values(4, 'D'); drop index emp_dept_cluster_idx; drop cluster emp_dept_cluster including tables; -- 테이블 동시 삭제 --drop table cluster_Dept; create cluster emp_dept_cluster (dept_no int) size 1000; create index emp_dept_cluster_idx on cluster emp_dept_cluster; CREATE TABLE cluster_Dept ( ) cluster emp_dept_cluster(dept_no); INSERT INTO cluster_Dept VALUES(1, 'A'); INSERT INTO cluster_Dept VALUES(3, 'C'); INSERT INTO cluster_Dept VALUES(2, 'B'); INSERT INTO cluster_Dept VALUES(5, 'E'); SELECT * FROM cluster_Dept; DELETE FROM cluster_Dept where dept_name = 'B'; INSERT INTO cluster_Dept values(4, 'D'); drop cluster hash_cluster including tables; CREATE CLUSTER hash_cluster ( hash_key int ) HASHKEYS 10000 SIZE 256 SINGLE TABLE HASH IS hash_key; CREATE TABLE hash_Dept ( ) cluster hash_cluster(dept_no); INSERT INTO hash_Dept VALUES(1, 'A'); INSERT INTO hash_Dept VALUES(3, 'C'); INSERT INTO hash_Dept VALUES(2, 'B'); INSERT INTO hash_Dept VALUES(5, 'E'); SELECT * FROM hash_Dept; DELETE FROM hash_Dept where dept_name = 'B'; INSERT INTO hash_Dept values(4, 'D');

6 (3) COMMIT/ROLLBACK 실습 (계속)
INSERT INTO std_heap VALUES (201, ‘EE Moon', 2, 22, 'Kangrung', 1); INSERT INTO std_heap VALUES (202, ‘PL Lim', 3, 23, 'Chuncheon', 4); ROLLBACK; SELECT * FROM std_heap ; UPDATE std_heap SET name = ‘XX XXXX’, grade = 9 WHERE stid = 101; COMMIT; UPDATE std_heap SET name = ‘AA AAAA’, grade = 7 WHERE stid = 102; UPDATE std_heap SET name = ‘BB BBBB’, grade = 8 WHERE stid = 102; UPDATE std_heap SET name = ‘CC CCCC’, grade = 9 WHERE stid = 102; DROP TABLE Dept; CREATE TABLE Dept ( dept_no int, dept_name varchar(100) ); INSERT INTO Dept VALUES(1, 'A'); INSERT INTO Dept VALUES(3, 'C'); INSERT INTO Dept VALUES(2, 'B'); INSERT INTO Dept VALUES(5, 'E'); SELECT * FROM Dept; DELETE FROM Dept where dept_name = 'B'; INSERT INTO Dept values(4, 'D'); DROP TABLE index_Dept; CREATE TABLE index_Dept ( dept_no int PRIMARY KEY, ) ORGANIZATION INDEX; INSERT INTO index_Dept VALUES(1, 'A'); INSERT INTO index_Dept VALUES(3, 'C'); INSERT INTO index_Dept VALUES(2, 'B'); INSERT INTO index_Dept VALUES(5, 'E'); SELECT * FROM index_Dept; DELETE FROM index_Dept where dept_name = 'B'; INSERT INTO index_Dept values(4, 'D'); drop index emp_dept_cluster_idx; drop cluster emp_dept_cluster including tables; -- 테이블 동시 삭제 --drop table cluster_Dept; create cluster emp_dept_cluster (dept_no int) size 1000; create index emp_dept_cluster_idx on cluster emp_dept_cluster; CREATE TABLE cluster_Dept ( ) cluster emp_dept_cluster(dept_no); INSERT INTO cluster_Dept VALUES(1, 'A'); INSERT INTO cluster_Dept VALUES(3, 'C'); INSERT INTO cluster_Dept VALUES(2, 'B'); INSERT INTO cluster_Dept VALUES(5, 'E'); SELECT * FROM cluster_Dept; DELETE FROM cluster_Dept where dept_name = 'B'; INSERT INTO cluster_Dept values(4, 'D'); drop cluster hash_cluster including tables; CREATE CLUSTER hash_cluster ( hash_key int ) HASHKEYS 10000 SIZE 256 SINGLE TABLE HASH IS hash_key; CREATE TABLE hash_Dept ( ) cluster hash_cluster(dept_no); INSERT INTO hash_Dept VALUES(1, 'A'); INSERT INTO hash_Dept VALUES(3, 'C'); INSERT INTO hash_Dept VALUES(2, 'B'); INSERT INTO hash_Dept VALUES(5, 'E'); SELECT * FROM hash_Dept; DELETE FROM hash_Dept where dept_name = 'B'; INSERT INTO hash_Dept values(4, 'D');

7 동시성 제어 트랜잭션이 동시에 수행 되면 발생할 수 있는 문제 트랜잭션 고립 수준 명령어
Lost Update(갱신 손실): 갱신 내용을 잃어버림 Dirty Read(오손 판독): 존재하지 않는 값을 읽음 Non-Repeatable Read(비반복 읽기): 여러 번 읽을 때 값이 서로 다름 Phantom Read(유령 읽기): 읽는 범위 내에 존재하는 삭제/삽입된 유령 레코드를 읽음/읽지 못함 트랜잭션 고립 수준 명령어 DBMS는 트랜잭션을 동시에 실행시키면서 Lock보다 좀 더 완화된 방법 으로 문제를 해결하기 위해 제공하는 명령어

8 MariaDB 세션 여러개 실행(1)

9 MariaDB 세션 여러개 실행(2)

10 MariaDB 세션 여러개 실행(3)

11 MariaDB 세션 여러개 실행(4)

12 실습 #2 갱신 손실 방지 실습 곰돌이 시간 힌둥이 Update 통장 set money = 10만 Where 이름 = 커플통장
뭐야 왜 5만원으로 되어있지? T3

13 다른 트랜잭션에 의해 값이 덮혀쓰이는지 테스트
트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 SET autocommit = 0; SET sql_safe_updates = 0; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT name FROM std_heap WHERE stid = 101; UPDATE std_heap SET name = ‘YY YYYY’ WHERE stid = 101; UPDATE std_heap SET name = ‘ZZ ZZZZ’ COMMIT; 왜 ZZ ZZZZ가 아닐까요?

14 다른 트랜잭션에 의해 값의 변경이 취소되는지 테스트
트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 SELECT name FROM std_heap WHERE stid = 101; UPDATE std_heap SET name = ‘AA AAAA’ WHERE stid = 101; UPDATE std_heap SET name = ‘BB BBBB’ COMMIT; ROLLBACK; COMMIT을 ROLLBACK 전에 실행하는 것이 불가능함!!

15 실습 #3 허상 읽기 실습 허상 읽기 발생 테스트 트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2
SELECT name FROM std_heap WHERE stid = 101; UPDATE std_heap SET name = ‘CC CCCC’ WHERE stid = 101; ROLLBACK; COMMIT;

16 허상 읽기 발생 방지 테스트 트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 READ COMMITTED 모드
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT name FROM std_heap WHERE stid = 101; UPDATE std_heap SET name = ‘CC CCCC’ WHERE stid = 101; COMMIT; ROLLBACK;

17 실습 #4 비 반복적 읽기 실습 아래 각 트랜잭션의 SELECT 결과를 화면 캡처하고, 그 이유를 작성하여 제출하시오.
(1) READ COMMITTED 모드 트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 READ COMMITTED 모드 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT name FROM std_heap WHERE stid = 101; UPDATE std_heap SET name = ‘CC CCCC’ WHERE stid = 101; COMMIT;

18 트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 REPEATABLE READ 모드
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT name FROM std_heap WHERE stid = 101; UPDATE std_heap SET name = ‘DD DDDD’ WHERE stid = 101; COMMIT;

19 실습 #4 직렬화 (유령 레코드 읽기) 아래 각 트랜잭션의 SELECT 결과를 화면 캡처하고, 그 이유를 작성하여 제출하시오.
(1) REPEATABLE READ 모드 – 유령 레코드 발생 트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 REPEATABLE READ 모드 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT stid, name FROM std_heap WHERE stid >= 102 AND stid <= 105; INSERT INTO std_heap VALUES (103, ‘KK KKKK’, 3, 20, ‘Chuncheon’, 2); COMMIT; SELECT name FROM std_heap

20 트랜잭션 T1 READ UNCOMMITTED 모드 트랜잭션 T2 SERIALIZABLE 모드
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; DELETE FROM std_heap WHERE stid = 103; COMMIT; SELECT stid, name FROM std_heap WHERE stid >= 102 AND stid <= 105; INSERT INTO std_heap VALUES (103, ‘KK KKKK’, 3, 20, ‘Chuncheon’, 2);