4장. 트랜잭션과 잠금

트랜잭션

- 작업의 완전성 보장

- 논리적 작업셋을 모두 완벽하게 처리하거나 또는 처리하지 못한경우에는 원상태로 복구하도록

- 작업의 일부만 적용되는 현상이 발생하지 않도록 

- 데이터의 정합성(무결성)을 보장하기 위함

잠금 

- 동시성을 제어하기 위한 기능

- 여러 커넥션에서 동시에 동일한 자원(레코드, 테이블 등)을 요청할 경우 순서대로 한시점에 하나의 커넥션만 변경할수 있도록

트랜잭션 격리수준 

- 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지의 레벨

---

트랜잭션 (Transaction)

1. MySQL에서의 트랜잭션

- 하나의 논리적인 작업 셋에 하나 또는 두개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나 적용되지 않거나

- MyISAM : 트랜잭션 지원하지 않음. 부분 업데이트 현상 발생가능

- InnoDB : 트랜잭션 지원함, 쿼리 중 일부라도 오류가 발생하면 실행하지 전 상태로 그대로 복구(Rollback)

# MyISAM 테이블과 INNODB 테이블을 생성 후 제약조건 Primary key 설정

# MyISAM 엔진을 사용하는 테이블 생성 후 데이터 입력

root@localhost:test 16:00:24>CREATE TABLE tab_myisam (fdpk INT NOT NULL, PRIMARY KEY(fdpk)) ENGINE=MyISAM;

Query OK, 0 rows affected (0.28 sec)

root@localhost:test 16:01:07>INSERT INTO tab_myisam (fdpk) VALUES (3);

Query OK, 1 row affected (0.02 sec)

# INNODB 엔진을 사용하는 테이블 생성 후 데이터 입력

root@localhost:test 16:01:27>CREATE TABLE tab_innodb (fdpk INT NOT NULL, PRIMARY KEY(fdpk)) ENGINE=INNODB;

Query OK, 0 rows affected (1.47 sec)

root@localhost:test 16:01:58>INSERT INTO tab_innodb (fdpk) VALUES (3);

Query OK, 1 row affected (0.18 sec)

# 중복된 데이터가 입력되어 제약조건에 위배되기 때문에 ERROR 발생

root@localhost:test 16:02:12>INSERT INTO tab_myisam (fdpk) VALUES (1),(2),(3);

ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

root@localhost:test 16:02:35>INSERT INTO tab_innodb (fdpk) VALUES (1),(2),(3);

ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

# 각 테이블조회

# MyISAM 테이블은 값3에 대한 에러가 발생했음에도 불구하고 나머지 데이터들이 입력됨 (부분업데이트, Partial Update)

# 부분업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 삭제하는 재처리 작업 필요

# 재처리작업을 하지 않으면 부분업데이트의 결과로 쓰레기 데이터가 테이블에 남아있을 가능성이 높음

# 2개 이상의 쿼리가 실행되는 경우라면 재처리작업은 복잡해짐 (IF~ELSE 중복사용)

root@localhost:test 16:02:55>SELECT * FROM tab_myisam;

+------+

| fdpk |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.00 sec)

# InnoDB 테이블은 값 3에 대한 에러가 발생하여 오류 발생하기 전 상태로 복구 

# 트랜잭션이 지원되는 InnoDB의 경우 간단한 코드(TRY~CATCH)로 완벽구현가능

root@localhost:test 16:03:11>SELECT * FROM tab_innodb;

+------+

| fdpk |

+------+

|    3 |

+------+

1 row in set (0.00 sec)

2. 트랜잭션 주의사항

- 꼭 필요한 최소의 코드에만 트랜잭션을 적용하는 것이 좋음

- 프로그램 코드가 데이터베이스 커넥션을 가지고 있는 범위 최소화!

- 트랜잭션이 활성화 되어있는 프로그램의 범위를 최소화!

- 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제 (DBMS가 높은 부하상태로 빠질 위험이 있음)

# 사용자가 게시판에 게시물을 작성한 후 저장 버튼을 클릭했을 때 서버에서 처리하는 내용 순서

1. 처리시작

-> 데이터베이스 커넥션 활성화

-> 트랜잭션 시작 (START TRANSACTION)

2. 사용자의 로그인 여부 확인

3. 사용자의 글쓰기 내용의 오류 여부 확인

4. 첨부로 업로드된 파일 확인 및 저장

5. 사용자의 입력 내용을 DBMS에 저장

6. 첨부파일 정보를 DBMS에 저장

7. 저장된 내용 또는 기타 정보를 DBMS에서 조회

8. 게시물 등록에 대한 알림 메일 발송

9. 알림 메일 발송 이력을 DBMS에 저장

<- 트랜잭션 종료 (COMMIT)

<- 데이터베이스 커넥션 반납

10. 처리완료

<문제점>

- 실제로 DBMS에 데이터를 저장하는 작업은 5번부터 이므로 2,3,4번의 절차를 트랜잭션에 포함시킬 필요 없음

- 데이터베이스 커넥션 개수는 제한적이므로 커넥션을 소유하는 시간이 길어질수록 사용가능 커넥션 수 줄어듬 (대기발생)

- 8번과 같이 네트워크 작업은 제거하는것이 좋음 (서버와 통신할수 없는경우 웹서버 뿐만아니라 DBMS서버까지 위험)

* 메일전송, FTP파일전송, 네트워크를 통한 원격서버와 통신 

- DBMS 작업 : 5번, 6번, 7번, 9번

5번, 6번 : 사용자가 입력한 정보를 저장하는 작업이므로 하나의 트랜잭션으로 묶음

7번 : 단순한 확인 및 조회 작업이므로 트랜잭션에 포함시킬 필요 없음

9번 : 7번 작업으로 인해 분리

<재설계>

1. 처리시작

2. 사용자의 로그인 여부 확인

3. 사용자의 글쓰기 내용의 오류 여부 확인

4. 첨부로 업로드된 파일 확인 및 저장

-> 데이터베이스 커넥션 활성화

-> 트랜잭션 시작 (START TRANSACTION)

5. 사용자의 입력 내용을 DBMS에 저장

6. 첨부파일 정보를 DBMS에 저장

<- 트랜잭션 종료 (COMMIT)

7. 저장된 내용 또는 기타 정보를 DBMS에서 조회

8. 게시물 등록에 대한 알림 메일 발송

-> 트랜잭션 시작 (START TRANSACTION)

9. 알림 메일 발송 이력을 DBMS에 저장

<- 트랜잭션 종료 (COMMIT)

<- 데이터베이스 커넥션 반납

10. 처리완료

---

잠금 (Lock)

MySQL 엔진 레벨	글로벌락, 테이블락, 유저락, 네임락	- 모든 스토리지 엔진에 영향을 미침
스토리지 엔진 레벨	1. MyISAM과 MEMORY 스토리지 엔진의 잠금  2. InnoDB 스토리지 엔진의 잠금	- 스토리지 엔진 간 상호 영향을 미치지 않음

1. MySQL 엔진의 잠금

종류	특징	명령
글로벌락  (GLOBAL LOCK)	- MySQL에서 제공하는 잠금 중 가장 범위가 크다  - 글로벌락 획득시 SELECT를 제외한 DDL, DML 대기상태  - 영향범위 : MySQL 서버 전체  - 모든 테이블에 영향을 미치므로 가급적 사용 자제  - mysqldump로 일관된 백업시 글로벌락 사용  - mysqldump 옵션에 따라 어떤 잠금을 거는지 확인필요	FLUSH TABLES WITH READ LOCK  -- 위 명령 실행 전에 잠금을 걸고 있는 SQL문 이 있다면 먼저 실행된 SQL문이 완료되고 해당 트랜잭션이 완료될때 까지 대기  -- FLUSH 명령어는 내부 캐시메모리를 비우기 때문에 작업중인 테이블의 작업 완료 후 실행되어야 함  -- 최악의 경우 글로벌락으로 인해 INSERT, UPDATE, DELETE 명령어가 오랫동안 대기
테이블락  (TABLE LOCK)	- 개별 테이블 단위로 설정되는 잠금  - 명시적 테이블 락 : 명령으로 특정 테이블 락 획득  - 묵시적 테이블 락 : 데이터 변경되는 테이블에 자동적으로  락 설정, 변경 후 즉시잠금 해제  - InnoDB 테이블에도 테이블락이 설정되지만 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경으로 테이블 락이 설정되지 않음 (DML에서는 거의 무시, DDL의 경우에만 영향)	-- 명시적 테이블 락  -- 거의 사용할 필요 없음. 온라인 작업에 영향  LOCK TABLES table_name [READ|WRITE]  UNLOCK TABLES  -- 묵시적 테이블 락  -- 쿼리가 실행되는 동안 자동으로 획득/해제
유저락  (USER LOCK)	- 단순히 사용자가 지정한 문자열에 대해 락 획득/해제    - 사용 예)  웹서버 5대, DB서버 1대 서비스     ㄴ 5대의 웹서버가 어떤 정보들을 동기화 할때    ㄴ 여러 클라이언트가 상호 동기화 처리해야 할때	-- "mylock"이라는 문자열에 대해 잠금 획득  -- 이미 잠금 사용중이면 2초 동안만 대기  SELECT GET_LOCK ('mylock', 2);  -- "mylock" 문자열에 대해 잠금 설정 확인  SELECT IS_FREE_LOCK('mylock');  -- "mylock" 문자열에 대해 획득한 잠금 반환  SELECT RELEASE_LOCK('mylock');  -- 정상 작동 시 1 반환, 아니면 NULL, 0 반환
네임락  (NAME LOCK)	- 데이터베이스 객체의 이름을 변경할때 획득하는 잠금  - 테이블, 뷰 등  - 명시적으로 획득/해제하는 것이 아님  - RENAME 명령어를 통해 변경하는 경우 자동 획득	-- 한번에 작업시 바로 대체 가능  RENAME TABLE rank TO rank_backup, rank_new TO rank;  -- 2개로 나눠서 실행시 짧은 순간이라도 테이블이 존재하지 않는 순간이 생기므로 에러 발생  RENAME TABLE rank TO rank_backup;  RENAME TABLE rank_new TO rank;

2. MyISAM과 MEMORY 스토리지 엔진의 잠금

- MyISAM이나 MEMORY 스토리지 엔진은 자체적인 잠금을 가지지 않고 MySQL에서 제공하는 테이블락을 그대로 사용

- ARCHIVE, MERGE 등과 같은 스토리지 엔진도 동일

- 쿼리 단위로 필요한 잠금을 한꺼번에 모두 요청해서 획득하기 때문에 데드락이 발생할 수 없음

2.1. 잠금 획득

- 읽기 잠금 : 테이블에 쓰기 잠금이 걸려있지 않으면 바로 읽기 잠금을 획득하고 읽기 작업 가능

- 쓰기 잠금 : 테이블에 아무런 잠금이 걸려있지 않아야만 쓰기 잠금을 획득 가능, 다른 잠금이 걸려있다면 해제될 때까지 대기

2.2. 잠금 튜닝

- 테이블 락에 대한 작업 상황은 MySQL 상태 변수를 통해 확인 가능

root@localhost:test 20:01:36>SHOW STATUS LIKE 'Table%';

- Table_locks_immediate : 바로 잠금을 획득한 횟수

- Table_locks_waited : 잠금 대기 누적 횟수

- 잠금 대기 쿼리 비율 = Table_locks_waited / (Table_locks_immediate + Table_locks_waited) * 100;

- 잠금 대기 쿼리 비율이 높고, 테이블 잠금 때문에 경합이 많이 발생하고 있다면 처리 성능이 영향을 받고 있음을 의미

- InnoDB 스토리지 엔진의 경우에는 레코드 단위의 잠금을 사용하기 때문에 집계에 포함되지 않음

2.3. 테이블 수준의 잠금 확인 및 해제

- MyISAM이나 MEMORY 등과 같은 스토리지 엔진을 사용하는 테이블은 모두 테이블 단위의 잠금 사용

- 하나의 테이블에서 전혀 다른 레코드라 하더라도 동시에 변경하는 것은 불가능하기 때문에 동시성이 떨어짐

# FROM DB명 : 해당 DB에 생성된 테이블에 대해 잠금 상태 표시

root@localhost:test 20:10:42>SHOW OPEN TABLES FROM employees;

# FROM DB명 LIKE '패턴' : 해당 DB에 생성된 패턴이 일치하는 테이블의 잠금 상태 표시

root@localhost:test 20:11:40>SHOW OPEN TABLES FROM employees LIKE 'employees';

# MySQL 서버의 모든 테이블에 대한 잠금 상태 표시

root@localhost:test 20:10:51>SHOW OPEN TABLES;

- Database : 데이터베이스 이름

- Table : 테이블 이름

- In_use : 해당 테이블을 잠그고 있는 클라이언트의 수 뿐만 아니라 그 테이블의 잠금을 기다리는 클라리언트의 수까지 

- Name_locked : 테이블 이름에 대한 네임락이 걸려 있는지 

# 어떤 클라이언트의 커넥션이 잠금을 기다리고 있는지 확인

root@localhost:test 20:11:56>SHOW PROCESSLIST;

- State : 클라이언트의 상태 (Locked 테이블락 대기중)

# 클라이언트 강제 종료 (문제가 되는 커넥션을 강제종료하여 차례대로 처리를 진행할수 있도록)

root@localhost:test 20:11:56>KILL 1

3. InnoDB 스토리지 엔진의 잠금

- MySQL 에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재

- MyISAM보다는 훨씬 뛰어난 동시성 처리를 제공하나 이원화된 잠금 처리 탓에 InnoDB에서 사용되는 잠금에 대한 정보를 얻기 까다로움

- MySQL 5.1 부터 InnoDB 플러그인 스토리지 엔진이 도입되면서부터 트랜잭션과 잠금, 대기 등의 목록을 조회할수 있는 방법 도입

3.1. InnoDB의 잠금 방식

- 비관적잠금 (Optimistic) : 현재 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할수 있다는 가정, 먼저 잠금을 획득

- 낙관적잠금 (Pessimistic) : 각 트랜잭션이 같은 레코드를 변경할 가능성이 희박하다고 낙관적으로 가정, 작업 수행 후 잠금충돌여부 확인

3.2. InnoDB의 잠금 종류

락 종류	설명
레코드 락	- 레코드 자체만을 잠그는 것 (다른 상용 DBMS의 레코드락과 동일)  - InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠금  - 인덱스가 없는 테이블이라 하더라도 자동 생성된 클러스터 인덱스를 이용해 잠금 설정    - 보조인덱스를 이용한 변경작업 : 넥스트 키 락, 갭락 사용  - primary key, unique index : 레코드 자체에 대해서만 락 설정 (갭락 사용하지 않음)
갭 락	- 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것  - 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어 (INSERT)
넥스트 키 락	- 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서의 결과와 동일한 결과를 보장하기위함  - 갭락, 넥스트 키락으로 인해 데드락이 발생하거나 대기현상 자주 발생     ㄴ statement 포맷방식 바이너리로그 사용 시 Repeatable read 격리 수준 이상 사용해야 함    ㄴ innodb_locks_unsafe_for_binlog =0(비활성화) 시 변경을 위해 검색하는 레코드에 넥스트 키락 잠금 사용    ㄴ 넥스트 키락과 갭락으로 인해 데드락, 대기현상이 의외로 자주 발생하므로 row포맷 바이너리 사용?    ㄴ But, row포맷의 바이너리 로그 방식은 널리 사용되지 않고, 안정성 확보 어려우며, 파일사이즈가 커질수 있음
자동 증가 락	[MySQL 5.0 이하 버전]  - 자동 증가하는 숫자 값을 채번하기 위해 AUTO_INCREMENT 컬럼 속성 사용  - 해당 컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT될때, 중복되지 않고 순서대로 증가된 일련번호 할당  - InnoDB 엔진에서 테이블 수준의 잠금인 AUTO_INCREMENT 락을 내부적으로 사용함  - INSERT, REPLACE 문장에서 AUTO_INCREMENT 값을 가져온느 순간만 락이 걸렸다가 즉시 해제함  - INSERT, REPLACE 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요 (UPDATE, DELETE 문장은 불필요)  - AUTO_INCREMENT 락은 테이블에 단 하나만 존재하므로, 두개의 INSERT가 동시에 실행 시 하나는 대기함  - 명시적으로 자동 증가 락을 획득하고 해제하는 방법은 없음  - 대부분 짧은 시간 동안만 걸렸다가 해제되는 잠금이기 때문에 거의 문제가 되지 않음 [MySQL 5.1 이상 버전]  - innodb_autoinc_lock_mode 파라미터를 이용해 작동방식 변경 가능  - innodb_autoinc_lock_mode = 0    ㄴMySQL 5.0과 동일한 잠금 방식으로 모든 INSERT 문장은 자동 증가 락을 사용.  - innodb_autoinc_lock_mode = 1    ㄴ INSERT 되는 레코드 건수를 정확이 예측가능할 때는 자동 증가락을 사용하지 않고 가볍고 빠른 래치사용    ㄴ INSERT 되는 레코드 건수를 정확이 예측불가능할 때는 MySQL 5.0에서와 같이 자동증가락 사용    ㄴ 대량 INSERT 수행시에는 한번에 여러개의 자동증가값을 할당 받아서 사용함.    ㄴ 하지만, 한번에 여러개의 자동증가값 할당 받은 후 남아서 사용하지 못하면 폐기    ㄴ INSERT 문장 이후에 실행되는 INSERT 되는 레코드들 사이의 자동증가값이 누락될 수도 있음    ㄴ 최소한 하나의 INSERT문장 내에서는 연속된 자동 증가 값을 보장 : 연속모드(Consecutive mode)  - innodb_autoinc_lock_mode = 2    ㄴ 절대 자동 증가락을 걸지 않고 항상 경량화된 래치(뮤텍스)사용    ㄴ 이 설정에서는 하나의 INSERT 문장으로 들어오는 레코드라 하더라도 연속된 자동 증가값을 보장하지 않음    ㄴ 인터리빙 모드 (Interleaved mode)    ㄴ 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT 수행가능 -> 동시성 처리 높아짐    ㄴ 자동 증가 기능이 유니크 한 값을 보장    ㄴ 복제를 사용하는 경우 마스터와 슬레이브의 자동증가값이 달라질 가능성도 있음 (주의)

3.3. InnoDB의 인덱스와 잠금

- InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리

- 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠가야 함.

- 테이블에 인덱스가 하나도 없다면, 테이블을 풀 스캔하면서 UPDATE 작업을 하게 되는데, 테이블의 모든 레코드를 잠그게 됨.

3.4. InnoDB의 트랜잭션 격리 수준과 잠금

- InnoDB의 넥스트 키 락으로 인해 불필요한 잠금 현상 발생

- 넥스트 키 락을 필요하게 만드는 주 원인은 복제를 위한 바이너리 로그 때문

- 아직 많이 사용하지는 않지만 레코드 기반의 바이너리 로그(Row based)를 사용하거나, 바이너리로그를 사용하지 않으면 넥스트 키 락의 사용을 대폭 줄일 수 있음 -> 넥스트 키 락을 줄이면, 사용자의 쿼리 요청을 동시에 많이 처리 가능

- 아래 표와 같은 조합으로 InnoDB에서 사용하는 대부분의 갭락, 넥스트 키락을 제거 가능

버전	설정 조합	설명
MySQL 5.0	innodb_locks_unsafe_for_binlog=1  트랜잭션 격리수준 : READ-COMMITTED 설정
MySQL 5.1 이상	[방법1]  바이너리 로그 비활성화  트랜잭션 격리수준 : READ-COMMITTED 설정  [방법2]  레코드 기반의 바이너리 로그 사용  innodb_locks_unsafe_for_binlog=1  트랜잭션 격리수준 : READ-COMMITTED 설정	- 유니크 키, 외래키에 대한 갭락은 없어지지 않음  - 인덱스만을 비교해서 일치하는 레코드에 대해 베타적 잠금을 걸고, 나머지 조건을 비교해서 일치하지 않는 레코드는 즉시 잠금 해제(불필요한 잠금제거 가능)

3.5. InnoDB의 레코드 수준 잠금 확인 및 해제

- 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸림

- MySQL 5.0 이하 버전에서는 레코드 잠금에 대한 메타정보를 제공하지 않음

- MySQL 5.1 이상부터 레코드 잠금과 잠금 대기에 대한 조회 가능!

- 강제로 잠금 종료 시 프로세스를 KILL 명령으로 강제 종료

root@localhost:(none) 18:03:58>show engine innodb status\G;

*************************** 1. row ***************************

  Type: InnoDB

  Name: 

Status: 

=====================================

2015-08-17 18:04:24 7f95aa121700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 58 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 5646 srv_active, 0 srv_shutdown, 1544544 srv_idle

srv_master_thread log flush and writes: 1550190

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 153626

OS WAIT ARRAY INFO: signal count 151746

Mutex spin waits 30329, rounds 2291003, OS waits 114171

RW-shared spins 30653, rounds 619162, OS waits 30531

RW-excl spins 1242, rounds 174208, OS waits 8231

Spin rounds per wait: 75.54 mutex, 20.20 RW-shared, 140.26 RW-excl

------------

TRANSACTIONS

------------

Trx id counter 23898

Purge done for trx's n:o < 23887 undo n:o < 0 state: running but idle

History list length 791

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started --트랜잭션상태 : not started

MySQL thread id 155, OS thread handle 0x7f95aa121700, query id 29109 localhost root init --트랜잭션 실행중인 프로세스 ID

show engine innodb status

---TRANSACTION 23896, not started

MySQL thread id 154, OS thread handle 0x7f95aa09f700, query id 29108 localhost root cleaning up

---TRANSACTION 23897, ACTIVE 29 sec starting index read --트랜잭션상태 : ACTIVE, 실행시간 29초

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id 153, OS thread handle 0x7f95aa01d700, query id 29106 localhost root updating

update employees set hire_date=NOW() where emp_no=10001

------- TRX HAS BEEN WAITING 29 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 7 page no 4 n bits 408 index `PRIMARY` of table `employees`.`employees` trx id 23897 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0

 0: len 4; hex 80002711; asc   ' ;;

 1: len 6; hex 000000005d56; asc     ]V;;

 2: len 7; hex 73000001c90275; asc s     u;;

 3: len 3; hex 8fbf11; asc    ;;

 4: len 6; hex 47656f726769; asc Georgi;;

 5: len 7; hex 466163656c6c6f; asc Facello;;

- 레코드를 오랫동안 잠그고 있는 프로세스 존재 여부 확인

- 트랜잭션의 상태가 ACTIVE이며 실행시간이 최대한 큰 값을 가진 트랜잭션 확인

- 하지만, 각 트랜잭션이 어떤 잠금을 가지고 있는지 명확하지 않음 -> 활성화된 트랜잭션의 프로세스로 예측

# INNODB_LOCKS : 어떤 잠금이 존재하는 지 관리

SELECT * FROM information_schema.innodb_locks;

# INNODB_TRX : 어떤 트랜잭션이 어떤 클라이언트 프로세스에 의해 기동되며 어떤 잠금을 기다리는지 관리

SELECT * FROM information_schema.innodb_trx; (SELECT * FROM information_schema.innodb_trx\G;)

# INNODB_LOCK_WAITS : 잠금에 의한 프로세스 간의 의존관계 관리

SELECT * FROM information_schema.innodb_lock_waits;

# 위 세개 테이블을 조인하여 잠금 대기 체크 가능

SELECT r.trx_id as waiting_trx_id,

r.trx_mysql_thread_id as waiting_thread,

r.trx_query as waiting_query,

b.trx_id as blocking_trx_id,

b.trx_mysql_thread_id as blocking_thread,

b.trx_query as blocking_query

FROM information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G;

*************************** 1. row ***************************

 waiting_trx_id: 23903 --트랜잭션23903번(커넥션 154번)이 트랜잭션 23902번(커넥션 153번)을 기다리고 있음.

 waiting_thread: 154

  waiting_query: update employees set birth_date=NOW(), hire_date=NOW() where emp_no=100001

blocking_trx_id: 23902

blocking_thread: 153

 blocking_query: update employees set hire_date=NOW() where emp_no=100001

*************************** 2. row ***************************

 waiting_trx_id: 23903

 waiting_thread: 154

  waiting_query: update employees set birth_date=NOW(), hire_date=NOW() where emp_no=100001

blocking_trx_id: 23899

blocking_thread: 152

 blocking_query: NULL

*************************** 3. row ***************************

 waiting_trx_id: 23902

 waiting_thread: 153

  waiting_query: update employees set hire_date=NOW() where emp_no=100001

blocking_trx_id: 23899

blocking_thread: 152

 blocking_query: NULL

3 rows in set (0.00 sec)

- waiting_trx_id / waiting_thread : 잠금을 기다리는 트랜잭션이나 프로세스

- blocking_trx_id / blocking_thread : 잠금을 해제하지 않아서 다른 트랜잭션을 기다리게 하고 있는 트랜잭션이나 프로세스

---

MySQL 격리수준 (isolation level)

1. READ UNCOMMITTED

- COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에 의해 변경된 내용이 보여짐

- 더티리드 (Dirty Read) : 어떤 트랜잭션에서 처리한 작업이 완료(commit)되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상

2. READ COMMITTED

- 오라클 기본 격리수준

- 온라인 서비스에서 가장 많이 선택되는 격리수준

- 더티리드 현상이 발생하지 않음

- 데이터 변경 시 commit 되기 전 데이터는 undo영역으로 백업되어 commit전에 다른 트랜잭션에서 동시에 조회할 경우 undo영역을 보여줌

- 어떤 트랜잭션에서 변경한 내용이 완료(commit)되기 전까지는 다른 트랜잭션에서 그러한 내역을 조회할 수 없음

- [부정합] NON-REPEATABLE READ 발생 : 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리 실행 시 항상 같은 결과를 보장할 수 없음

- SELECT 시 해당 트랜잭션이 끝나기 전에 다른 트랜잭션에서 변경 된 데이터가 있으면 하나의 트랜잭션 범위 내에서도 SELECT 결과가 다를 수 있음.

3. REPEATABLE READ

- MySQL의 InnoDB 스토리지 엔진 기본 격리수준

- 더티리드 현상 발생하지 않음 (undo영역 사용)

- Non-Repeatable read 부정합 발생하지 않음

- MVCC (Multi Version Concurrency Control) : 트랜잭션 rollback 가능성을 대비해 undo 공간에 백업 후 실제 레코드 값 변경하는 방식

- MVCC를 위해 undo영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줌

- MVCC를 보장하기 위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 undo영역의 데이터는 삭제 불가

- [부정합] PHANTOM READ 발생 : 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안보였다가 하는 현상

- SELECT ... FOR UPDATE 쿼리는 SELECT하는 레코드에 잠금을 걸어야 하는데 undo 레코드에는 잠금을 걸수 없기 때문에

- SELECT ... FOR UPDATE , SELECT ... LOCK IN SHARE MODE 로 조회되는 레코드는 언두영역의 변경전 데이터가 아닌 현재 레코드를 가져옴

4. SERIALIZABLE

- 가장 단순하지만 가장 엄격한 격리수준

- 동시 처리 성능이 많이 떨어짐

- InnoDB테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금이 없지만, Serializable로 설정되면 읽기작업도 공유 잠금을 획득하여 다른 트랜잭션에서 절대 접근할 수 없다.

- PHANTOM READ 발생하지 않음 

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	발생하지 않음	발생	발생
REPEATABLE READ	발생하지 않음	발생하지 않음	발생 (InnoDB는 발생하지 않음)
SERIALIZABLE	발생하지 않음	발생하지 않음	발생하지 않음