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

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

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

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

실습 #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');

(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');

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

MariaDB 세션 여러개 실행(1)

MariaDB 세션 여러개 실행(2)

MariaDB 세션 여러개 실행(3)

MariaDB 세션 여러개 실행(4)

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

다른 트랜잭션에 의해 값이 덮혀쓰이는지 테스트 트랜잭션 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가 아닐까요?

다른 트랜잭션에 의해 값의 변경이 취소되는지 테스트 트랜잭션 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 전에 실행하는 것이 불가능함!!

실습 #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;

허상 읽기 발생 방지 테스트 트랜잭션 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;

실습 #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;

트랜잭션 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;

실습 #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

트랜잭션 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);