트랜잭션과 잠금 (Real Mysql 8.0)
트랜잭션이란?
논리적인 작업의 가장 작은 단위로, 작업의 일관성을 보장해주기 위한 단위입니다. 이러한 특성에 의해 작업이 부분적으로 실패하거나 성공하지 않고, 완전히 성공 또는 실패하게 됩니다.
트랜잭션의 특성
트랜잭션에는 아래 네가지 특성이 존재하며 각 특성의 앞글자를 따서 ACID로 불립니다.
- 원자성 (Atomicity)
- 트랜잭션이 하나의 수행단위이며, 완전히 수행(Commit) 되거나 전혀 수행되지 않아야 합니다(Rollback)
- 일관성 (Consistency)
- 트랜잭션이 끝난 전후로 데이터베이스는 항상 일관된 상태(제약조건 만족)를 유지해야 합니다
- 고립성 (Isolation)
- 서로 다른 트랜잭션은 독립적이며 간섭할 수 없는 상태입니다.
- 지속성 (Durability)
- 트랜잭션이 Data를 변경시킨 뒤 Commit 되면 이후 데이터는 손실되지 않고 유지됩니다.
트랜잭션 특성에 따른 결과 차이
- MySQL에서 대부분 사용되는 엔진은 InnoDB입니다. InnoDB는 트랜잭션을 지원하는 스토리지 엔진이며, Default 스토리지 엔진입니다.
- MySQL에서 사용가능 한 MyISAM 엔진은 트랜잭션을 지원하지 않으며, 그로 인해 데이터 정합성 보장이 되지 않습니다.
- 트랜잭션 지원 여부에 따라 달라지는 결과에 대해 아래 테스트를 이용해 확인해봤습니다.
- MyISAM 테이블의 경우, 트랜잭션이 실패했지만 결과가 부분적으로 성공 (=Partial Update)
-- myisam 테이블 생성
mysql> CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO tab_myisam (fdpk) VALUES (3);
Query OK, 1 row affected (0.01 sec)
-- innodb 테이블 생성
mysql> CREATE TABLE tab_innodb ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO tab_innodb (fdpk) VALUES (3);
Query OK, 1 row affected (0.01 sec)
-- PK 중복 에러가 발생하도록, (1,2,3) Insert
mysql> INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
ERROR 1062 (23000): Duplicate entry '3' for key 'tab_myisam.PRIMARY'
mysql> INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);
ERROR 1062 (23000): Duplicate entry '3' for key 'tab_innodb.PRIMARY'
-- Insert 작업 실패 후, 테이블 데이터 확인
mysql> SELECT * FROM tab_myisam;
+------+
| fdpk |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM tab_innodb;
+------+
| fdpk |
+------+
| 3 |
+------+
1 row in set (0.01 sec)
잠금(Lock)
잠금이란, 다중 트랜잭션 환경에서 데이터 일관성을 유지할 수 있게 마련된 장치입니다. 잠금 동작은 트랜잭션 Isolation Level에 따라 영향을 받습니다. 각 Isolation level에 따른 특징은 아래 링크를 참조하시면 됩니다. MySQL 기본 Isolation Level은 Reapeatable Read입니다.
MySQL에서 잠금은 크게 아래 두 가지 레벨로 분류됩니다.
-
- 스토리지 엔진 레벨
- 모든 스토리지 엔진에 영향을 주는 잠금 (ex) 테이블 Lock, 메타데이터 Lock, 네임드 Lock
-
- MySQL 엔진 레벨
- 스토리지 엔진 상호 간 영향을 주지 않는 잠금 (ex) 스토리지 엔진 잠금 이외에 모든 Lock
잠금의 종류
글로벌 락
- 글로벌 락은
FLUSH TABLES WITH READ LOCK명령어를 통해 획득할 수 있으며, 현재 MySQL에서 제공하는 Lock 중 가장 큽니다. - 한 세션에서 글로벌 락을 소유할 경우, 조회를 제외한 모든 DDL, DML 세션이 대기하게 됩니다.
- 동일 오브젝트가 아닌 다른 오브젝트에 대한 모든 접근이 영향을 받습니다.
- 일관된 데이터를 mysqldump툴을 이용한 백업을 받을 때 사용되는 Lock입니다. (옵션에 따라 조절 가능)
-- Session 1
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)
-- Session 2 (SELECT, DML, DDL 시도)
mysql> SELECT * FROM tab_innodb;
+------+
| fdpk |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql> INSERT INTO tab_innodb VALUES (1), (2), (4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> ALTER TABLE tab_innodb ADD add_col varchar(10), ALGORITHM = INPLACE, LOCK = NONE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 글로벌 Lock으로 인해 조회 쿼리를 제외한 모든 쿼리가 실패한다.
백업 락
- InnoDB의 경우, 트랜잭션을 지원하며 일관된 상태를 보장하므로 모든 데이터 변경 작업을 멈추는 것이 비효율적입니다.
- 글로벌 락에 비해 좀 더 가벼운 락의 필요성이 생기게 되어 새로 도입된 Lock입니다.
- 백업 락이 걸려있을 경우, 데이터 조회, 변경은 가능하며 아래 명령어들은 잠금에 의해 영향을 받습니다.
- 스키마 관련 작업 (DDL)
- REPAIR TABLE / OPTIMIZE TABLE
- 사용자 관리 / 비밀번호 변경
- Replica에서 Xtrabackup 툴을 이용한 백업 도중 기존에는 DDL 실행 시, 백업이 실패했지만 백업 락을 이용해 DDL이 실행되더라도 백업은 성공하고, 복제는 멈추게 됩니다.
-- Session 1
mysql> LOCK INSTANCE FOR BACKUP;
Query OK, 0 rows affected (0.00 sec)
-- Session 2 (SELECT, DML, DDL 시도)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tab_innodb VALUES (1), (2), (4);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE tab_innodb ADD add_col varchar(10), ALGORITHM = INPLACE, LOCK = NONE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 백업 Lock으로 인해 DDL은 실패
테이블 락
- 개별 테이블 단위의 잠금입니다.
-
LOCK TABLES [테이블명] [READ | WRITE]명령어를 이용해 명시적으로 잠금을 획득합니다.[실제 운영에서 사용되는 경우는 거의 없습니다]
- InnoDB 엔진 테이블의 경우, 레코드 기반 잠금을 지원하여 DML 작업에서 테이블 Lock은 발생하지 않습니다.
특정 유형의 DDL 실행 시, 잠금이 발생합니다. ALGORITHM=INPLACE, LOCK=NONE 옵션을 이용해 작업 유형별 Online DDL이 가능한 경우합 있습니다.
네임드 락
- 네임드락은 GET_LOCK() 함수를 이용하여 특정 문자열에 대한 잠금 획득이 가능합니다.
- 데이터베이스 관련 오브젝트가 아닌, 단순 사용자 지정 문자열에 대한 잠금입니다.
메타데이터 락
- 메타데이터 락은 객체 이름이나 구조를 변경 (ex. DDL, RENAME)할 때 잠금을 획득합니다.
- 해당 Lock이 걸린 객체에 대해, DML이나 DDL은 불가합니다.
-
performance_schema.metadata_locks테이블을 통해 확인 가능합니다.- 8.0 버전으로 업그레이드 되면서 Default로 활성화되도록 변경됐습니다.
-- 테스트 테이블 생성 & 1000만 건 데이터 생성
mysql> CREATE TABLE `lock_table` (
`id` int NOT NULL AUTO_INCREMENT,
`calendar` date DEFAULT NULL,
`num` int DEFAULT NULL,
`num_2` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_calendar` (`calendar`),
KEY `idx_num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=10026856 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql> INSERT lock_table(calendar, num, num_2)
SELECT (CURRENT_DATE - INTERVAL FLOOR(RAND() * 14) DAY),
cast(rand() * 100 as unsigned),
cast(rand() * 100 as unsigned)
FROM information_schema.TABLES a
CROSS JOIN information_schema.TABLES B
CROSS JOIN information_schema.TABLES C
LIMIT 10000000;
Query OK, 10000000 rows affected (2 min 28.57 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
-- Session 1 (INDEX 생성 시도)
mysql> ALTER TABLE lock_table ADD INDEX lock_idx3 (calendar, num_2);
-- Session 2 (metadata Lock 정보 확인)
mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, SOURCE, OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA <> 'performance_schema';
+-------------+---------------+--------------+---------------------+--------------------+-----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | SOURCE | OWNER_THREAD_ID |
+-------------+---------------+--------------+---------------------+--------------------+-----------------+
| SCHEMA | test | NULL | INTENTION_EXCLUSIVE | sql_base.cc:5399 | 92 |
| TABLE | test | lock_table | SHARED_UPGRADABLE | sql_parse.cc:6162 | 92 |
| TABLE | test | #sql-28c7_30 | EXCLUSIVE | sql_table.cc:16434 | 92 |
+-------------+---------------+--------------+---------------------+--------------------+-----------------+
3 rows in set (0.02 sec)
레코드 락
- 테이블 인덱스 레코드별로 잠금을 획득합니다.
- 인덱스가 없는 레코드의 경우, InnoDB에서는 내부적으로 auto_inc PK를 생성해 레코드 잠금을 획득합니다.
- 레코드 잠금은 Primary Key, Unique Key을 사용하는 작업에서 할당됩니다.
- 레코드 락의 종류에는 세 가지가 존재합니다.
- S-lock : 다른 트랜잭션이 S-lock을 소유한 오브젝트에 대해 S-lock만 획득 가능한 Lock입니다.
- X-lock : 다른 트랜잭션이 X-lock을 소유한 오브젝트에 대해 S-lock, X-lock을 획득할 수 없도록 제한합니다.
-
Intention-lock : Row-level / Table-level 락이 함께 사용되는 다중 잠금 계층을 지원합니다.
- Intention Shared Lock (IS) : 해당 테이블 각 행에, S-lock 설정
- Intention Exclusive Lock (IX) : 해당 테이블 각 행에 X-lock 설정
- Intention Lock (의도락)을 통해 어떤 Lock을 획득할 것인지 미리 알려, 트랜잭션의 접근을 효과적으로 제어하는 방법입니다. 그러므로 Intention Lock을 Table-Level로 건 후, Row-Level Lock을 할당합니다.
서로 다른 두 트랜잭션이 호환성으로 인해 충돌할 경우, 선행 트랜잭션이 잠금해제할 때까지 대기합니다. 각 트랜잭션이 서로 소유한 Lock에 대해 상호 대기할 경우, Deadlock이 발생합니다.
갭(GAP) 락
- 레코드 사이의 일정 구간에 부여하는 Lock을 의미합니다.
- (ex)
SELECT * FROM table WHERE a BETWEEN 5 and 10 FOR UPDATE - 위 예제와 같은 경우, 5~10 범위에 대해 Insert가 불가합니다. (기본 Isolation-level인 READ-REPEATABLE의 경우)
- (ex)
- READ-COMMITTED isolation-level에서는 갭락이 비활성화 됩니다.
- 해당 잠금이 할당된 구간에 Insert 되는 것을 방지합니다.
- 넥스트키 락의 일부로 자주 사용됩니다.
넥스트 키 락
- 레코드 락 + 갭 락 형태의 잠금을 의미합니다.
- 해당 락은 binlog 기반으로 Replica 장비에서 실행될 때 데이터 일관성을 보장하기 위해 고안된 Lock입니다.
- 넥스트 키 락과 갭 락으로 인해, 데드락이 발생하거나 대기가 발생하는 경우가 있어, 최대한 줄이는 것이 좋습니다.
-- 테스트 테이블 생성 * 데이터 Insert
mysql> CREATE TABLE next_key (A int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int DEFAULT NULL, c char(1) DEFAULT 'Y', INDEX ix_b(b));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO next_key (b, c) VALUES (1,'N'), (4,'S'), (10, NULL),(11, 'Y'), (14,'Y'), (15, 'N');
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
-- 데이터 확인
mysql> SELECT * FROM next_key;
+---+------+------+
| A | b | c |
+---+------+------+
| 1 | 1 | N |
| 2 | 4 | S |
| 3 | 10 | NULL |
| 4 | 11 | Y |
| 5 | 14 | Y |
| 6 | 15 | N |
+---+------+------+
6 rows in set (0.01 sec)
-- Session 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM next_key WHERE b BETWEEN 5 AND 13 FOR UPDATE;
+---+------+------+
| A | b | c |
+---+------+------+
| 3 | 10 | NULL |
| 4 | 11 | Y |
+---+------+------+
2 rows in set (0.03 sec)
-- Session 2 (b 데이터 범위별 Insert 실행)
mysql> INSERT INTO next_key (b,c) VALUES (3, 'S');
Query OK, 1 row affected (0.01 sec)
-- 4 <= b < 14 Lock으로 인해 wait_timeout 발생
mysql> INSERT INTO next_key (b,c) VALUES (4, 'A');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO next_key (b,c) VALUES (10, 'A');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO next_key (b,c) VALUES (13, 'A');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- b < 4 / b >=14 Lock의 영향을 받지 않고 Insert 성공
mysql> INSERT INTO next_key (b,c) VALUES (14, 'A');
Query OK, 1 row affected (0.02 sec)
자동 증가 락
- AUTO_INCREMENT 속성을 가진 컬럼에 대해 채번을 위해 사용하는 락입니다.
- 해당 Lock은 테이블 수준의 잠금이 사용됩니다.
-
Insert, Replace와 같이 새로운 값을 저장하는 쿼리에서만 사용됩니다.
-
관련 옵션
-
innodb_autoinc_lock_mode=0- 모든 Insert문장에 자동 증가 락을 사용
-
innodb_autoinc_lock_mode=1[5.7이하 기본값]- 단순 Insert문 (INSERT INTO ~ VALUES)처럼 몇건이 Insert될지 예측이 되는 경우, 훨씬 가볍고 빠른 래치 (=mutex)를 이용해 처리합니다.
- insert ~ select문과 같이 몇 건인지 알 수 없을 경우 자동 증가 락을 할당합니다.
- 대량 Insert가 발생할 때, 한번에 여러 개의 채번값을 할당하여 사용합니다.
- 사용되지 못할 경우 값을 버리고 다음 번호를 할당하는데 이 경우 채번값이 번호가 연속되지 않는 케이스가 발생할 수 있습니다.
-
innodb_autoinc_lock_mode=2[8.0이상 기본값]- 자동 증가 락을 사용하지 않고, 항상 뮤텍스를 이용해 Insert 작업을 진행합니다.
- 대량 Insert문 진행 중에 다른 Insert가 가능합니다.
- 복제 구성된 환경에서는 결과 값이 달라질 수 있으므로 주의해야 됩니다.
-
-- MySQL 8.0 기본값
mysql> show global variables like '%autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 2 |
+--------------------------+-------+
1 row in set (0.01 sec)
격리 수준(Isolation-Level)
- 여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션의 변경/조회 작업을 제어하는 방법입니다.
- 총 4개의 단계로 이루어져 있으며 단계가 높아질수록 동시성이 떨어집니다.
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
- Isolation Level에 따라 부정합 현상이 발생할 수 있으며, 아래 표와 같습니다.
- dirty Read: Commit 되지 않은 데이터를 다른 트랜잭션이 조회 가능
- Non Repeatable Read: 동일 트랜잭션 내에서 Select문의 결과가 다름
- Phantom Read: 동일 트랜잭션에서 조회 시 없던 결과가 생김.
Isolation Level에 따른 특징
-
READ UNCOMMITTED
- Commit 여부에 상관없이 다른 트랜잭션 결과를 그대로 조회합니다.
- 위에 설명한 세가지 부정합 현상이 모두 발생합니다.
-- Session 1
mysql> SET SESSION transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM next_key;
+---+------+------+
| A | b | c |
+---+------+------+
| 1 | 1 | N |
| 2 | 4 | S |
| 3 | 10 | NULL |
| 4 | 11 | Y |
| 5 | 14 | Y |
+---+------+------+
5 rows in set (0.01 sec)
-- Session 2
START TRANSACTION;
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql> DELETE FROM next_key WHERE A=5;
Query OK, 1 row affected (0.03 sec)
mysql> INSERT next_key (b) VALUES (100);
Query OK, 1 row affected (0.02 sec)
-- Session 1 (Commit 되지 않은 결과 확인) = Dirty Read
mysql> SELECT * FROM next_key;
+----+------+------+
| A | b | c |
+----+------+------+
| 1 | 1 | N |
| 2 | 4 | S |
| 3 | 10 | NULL |
| 4 | 11 | Y |
| 15 | 100 | Y |
+----+------+------+
5 rows in set (0.03 sec)
-- Session 2
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
-- Session 1 (동일 트랜잭션 내 변경된 결과: Non-Repeatable Read / 이전에 없던 b=100 생성: Phantom-Read)
mysql> SELECT * FROM next_key;
+---+------+------+
| A | b | c |
+---+------+------+
| 1 | 1 | N |
| 2 | 4 | S |
| 3 | 10 | NULL |
| 4 | 11 | Y |
| 5 | 14 | Y |
+---+------+------+
5 rows in set (0.01 sec)
-
READ COMMITTED
- 각 명령문마다 Commit 트랜잭션 ID값을 사용해 레코드 버전 제어
- Non Repeatable Read 현상이 발생 (InnoDB에선 Phantom Read 미발생)
- 각 명령문마다 자기만의 스냅샷을 소유하여, 정합성을 유지할 수 있음.
- Lock이 발생하지 않음
-- Session 1
mysql> SET SESSION TRANSACTION isolation level READ COMMITTED;
Query OK, 0 rows affected (0.01 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM next_key;
+---+------+------+
| A | b | c |
+---+------+------+
| 1 | 1 | N |
| 2 | 4 | S |
| 3 | 10 | NULL |
| 4 | 11 | Y |
| 5 | 14 | Y |
+---+------+------+
5 rows in set (0.03 sec)
-- Session 2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM next_key WHERE A=5;
Query OK, 1 row affected (0.02 sec)
mysql> INSERT next_key(B) VALUES (100);
Query OK, 1 row affected (0.02 sec)
-- Session 1 (동일한 결과 / Dirty Read 방지)
mysql> SELECT * FROM next_key;
+---+------+------+
| A | b | c |
+---+------+------+
| 1 | 1 | N |
| 2 | 4 | S |
| 3 | 10 | NULL |
| 4 | 11 | Y |
| 5 | 14 | Y |
+---+------+------+
5 rows in set (0.02 sec)
-- Session 2
mysql> COMMIT;
-- Session 1 (동일 트랜잭션 내 변경된 결과: Non-Repeatable Read / 이전에 없던 b=100 생성: Phantom-Read)
mysql> SELECT * FROM next_key;
+----+------+------+
| A | b | c |
+----+------+------+
| 1 | 1 | N |
| 2 | 4 | S |
| 3 | 10 | NULL |
| 4 | 11 | Y |
| 16 | 100 | Y |
+----+------+------+
5 rows in set (0.02 sec)
-
REPEATABLE READ
- MySQL의 기본 isolation level입니다.
- 트랜잭션이 시작될 때 스냅샷을 생성하고, 언제나 동일한 결과셋을 읽도록 제어합니다.
- 동일 트랜잭션에서 내에서 같은 결과를 읽어와 Phantom READ를 방지하지만, 발생하는 예외 케이스가 존재합니다.
- (ex)
SELECT ~ FOR UPDATE와 같은 조회에 잠금 할당이 필요한 명령문
- (ex)
-
SERIALIZABLE
- SELECT가 하나의 트랜잭션이며, 조회 시 S-Lock이 할당됩니다.
- select문에 영향받는 object에 대해 DML, DDL 불가합니다.
- 가장 높은 격리 수준이며, 동시성이 제일 떨어집니다.