💡 잠금은 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 합니다.
💡 격리 수준(Isolation Level)은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨입니다.
💡 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능입니다.
트랜잭션을 지원하는 엔진 → InnoDB
try {
START TRANSACTION;
INSERT INTO tab a ...;
INSERT INTO tab b ...;
COMMIT ;
} catch(exception) {
ROLLBACK;
}트랜잭션을 지원하지 않는 엔진 → MyISAM
- 부분 업데이트를 Partial Update라고 부릅니다.
- 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 만들어 냅니다.
💡 네트워크 작업이 있는 경우 반드시 트랜잭션에서 배제해야 합니다. 프로그램을 실행하는 동안 통신 서버와의 통신을 할 수 없는 상황이라면 웹 서버뿐 아니라 DBMS 서버까지 위험해지는 상황이 발생하기 때문입니다.
MySQL 엔진의 잠금
글로벌 락(Global Lock)
FLUSH TABLES WITH READ LOCK명령을 통해 획득할 수 있습니다.- MySQL에서 제공하는 잠금 가운데 가장 범위가 큰 잠금입니다.
- 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남습니다.
- 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체입니다.
- 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일괄된 백업을 받아야 할 때는 글로벌 락을 사용해야 합니다.
- InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채택되면서 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없어졌습니다. 조금 더 가벼운 글로벌 락으로 Xtrabackup이나 Enterprise Backup이 도입됨
- 백업 락으로 사용하는데 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 합니다
mysql> LOCK INSTANCE FOR BACKUP;
-- // 백업 실행
mysql> UNLOCK INSTANCE;테이블 락(Table Lock)
- 개별 테이블 단위로 설정되는 잠금입니다.
- 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있습니다.
- 명시적으로는LOCK TABLES table_name [ READ | WRITE ]명령을 통해 특정 테이블 락을 획득할 수 있습니다. 테이블 락은 MyISAM뿐 아니라 InnoDB 스토리지 엔진을 사용하는 테이블도 동일하게 설정할 수 있습니다. 명시적으로 획득한 잠금은UNLOCK TABLES명령을 통해 잠금을 반납할 수 있습니다.
- 묵시적인 테이블락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생합니다. MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용됩니다. 즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제됩니다. 하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않습니다. 더 정확히는 InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미칩니다.
네임드 락(Named Lock)
- GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있습니다.
- 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 이용하면 쉽게 해결 할 수 있습니다.
-- // "mylock'’01라는 문자열에 대해 잠금을 획득한다.
-- // 이미 잠금을 사용 중이면 2초 동안만 대기한다. (2초 이후 자동 잠금 해제됨)
mysql> SELECT GET_LOCK('mylock', 2);
-- // "mylock"OI라는 문자열에 대해 잠금이 설정돼 있는지 확인한다.
mysql> SELECT IS_FREE_LOCK('mylock');
-- // "mylock"이라는 문자열에 대해 획득했던 잠금을 반납(해제)한다.
mysql> SELECT RELEASE_LOCK('mylock');
-- // 3개 함수 모두 정상적으로 락을 획득하거나 해제한 경우에는 1을,
-- // 아니면 NULLOI나 0을 반환한다.- 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있습니다.
- MySQL 8.0 버전부터는 네임드 락을 중첩해서 사용 가능합니다. 획득한 락을 한 번에 해제하는 기능도 추가되었습니다.
mysql> SELECT GET_LOCK('mylock_1' , 10);
-- // mylock_1에 대한 작업 실행
mysql> SELECT GET_LOCK('mylock_2' , 10);
-- // mylock_1과 mylock_2에 대한 작업 실행
mysql> SELECT RELEASE_LOCK('mylock_2');
mysql> SELECT RELEASE_LOCK('mylock_1');
-- // mylock_1과 mylock_2를 동시에 모두 해제하고자 한다면 RELEASE_ALL_LOCKS() 함수 사용
mysql> SELECT RELEASE_ALL_LOCKS();메타데이터 락(Metadata Lock)
- 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다.
RENAME TABLE tab_a TO tab_b같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금입니다.
-- // 배치 프로그램에서 별도의 임시 테이블 (rank_new)에 서비스용 랭킹 데이터를 생성
-- // 랭킹 배치가 완료되면 현재 서비스용 랭킹 테이블 (rank)을 rank_backup으로 백업하고
-- // 새로 만들어진 랭킹 테이블(rank_new)을 서비스용으로 대제하고자 하는 경우
mysql> RENAME TABLE rank TO rank_backup , rank_new TO rank;위의 쿼리를 밑의 쿼리와 같이 변경할 경우 아주 짧은 시간이지만 rank 테이블이 존재하지 않는 순간이 생깁니다.
mysql> RENAME TABLE rank TO rank_backup;
mysql> RENAME TABLE rank_new TO rank;InnoDB 스토리지 엔진 잠금
- MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있습니다.
- 레코드 기반의 잠금 방식으로 인해 뛰어난 동시성 처리를 제공합니다.
- 하지만, 이원화된 잠금 처리로 인한 불편함을 개선하기 위해 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐습니다.
- MySQL 서버의 information_schema 데이터베이스에 존재하는
INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS라는 테이블을 조인해서 사용
- MySQL 서버의 information_schema 데이터베이스에 존재하는
레코드 락(Record lock, Record only lock)
- 레코드 자체만을 잠그는 것
- InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠그는 것이 특징
갭 락(Gap lock)
- 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것
- 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것 → 레코드와 레코드 사이의 간격이라는 말은 메모리 파편화로 인해 생긴 공간을 말하는 것인가(?)
넥스트 키 락(Next key lock)
- 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금
- STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 합니다.
- 또한, innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸립니다.
- 바이너리 로그에 기로되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적입니다.
- 바이너리 로그 포맷 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋습니다.
자동 증가 락(Auto increment lock)
- AUTO_INCREMENT가 걸린 컬럼은 동시에 여러 레코드가 INSERT되는 상황에서도 저장된 순서대로 증가하는 일련번호를 가져야 합니다. 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용합니다.
- InnoDB의 다른 잠금(레코드 락이나 넥스트 커 락)괴는 달리 AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값 을 가져오는 순간만 락이 걸렸다가 즉시 해제됩니다.
| innodb_autoinc_lock_mod=0 | 모든 INSERT 문장은 자동 증가 락을 사용한다. |
|---|---|
| innodb_autoinc_lock_mod=1 | INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락보다 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다. |
| innodb_autoinc_lock_mod=2 | 경량화된 래치(뮤텍스)를 사용한다. 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다. 하지만 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있음. |
INSERT 쿼리가 실패했더라도 한 번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남는다 → 이는 트랜잭션 롤백 상황에서 이 롤백 수행 과정을 다른 스레드들에서는 기다려야 하기 때문에 문제가 발생하기 때문일까? 즉 성능을 빠르게 하기 위해서
인덱스와 잠금
InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식입니다.
💡 레코드가 오래 사용되지 않는다면 레코드 수준의 잠금은 테이블 수준의 잠금 방식보다 발견하기가 쉽지 않습니다. 하지만 MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능합니다.
MySQL의 performance_schema와 information_schema 테이블을 이용해 잠금과 잠금 대기를 확인할 수 있습니다.
MySQL의 격리 수준
격리 수준(isolation level) : 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것
격리 수준의 종류
- READ UNCOMMITED → “DIRTY READ” 문제 발생
- READ COMMITED
- REPEATABLE READ
- SERIALIZABLE
뒤로 갈수록 고립의 정도가 높아지며, 동시 처리 성능도 떨어지는 것이 일반적입닏.
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
|---|---|---|---|
| READ UNCOMMITED | 발생 | 발생 | 발생 |
| READ COMMITED | 없음 | 발생 | 발생 |
| REPEATABLE READ | 없음 | 없음 | 발생(InnoDB는 없음) |
| SERIALIZABLE | 없음 | 없음 | 없음 |
READ UNCOMMITED
💡 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보입니다.Dirty read
: 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상
→ 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 애플리케이션 개발자와 사용자를 혼란스럽게 만듭니다.
READ COMMITED
💡 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있습니다.NON-REPEATABLE READ
: 반복 읽기를 했을 때 데이터 부정합의 문제가 발생합니다. (하나의 트랜잭션 내에서 반복 읽기를 수행했을 때 같은 결과값을 보장해야 합니다.)
REPEATABLE READ
💡 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준입니다. 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경합니다.언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장합니다.
모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있습니다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제합니다.
REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없습니다.
다음 시나리오를 통해 이를 이해할 수 있스빈다. 사용자 A가 em_no가 500000인 사원의 이름을 변경하는 과정에서 사용자 B가 emp_no=500000인 사원을 SELECT할 때 어떤 과정을 거칠까요?
사용자 B가 트랜잭션을 시작하고 emp_no이 500000인 사원을 조회하면 Lara가 나옵니다. 사용자 B가 트랜잭션을 종료하지 않은 상황에서 사용자 B가 emp_no가 500000인 Lara의 이름을 Toto로 변경시키고 트랜잭션을 커밋시킵니다. 하지만 사용자 B의 트랜잭션은 커밋되지 않았기 때문에 emp_no가 500000인 사원을 조회했을 때 반복 가능한 읽기 수준을 보장하며 Lara를 반환해야 합니다. 사용자 B의 10번 트래잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보이게 됩니다.
PHANTOM READ
: 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상
SELECT …. FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없습니다. 그래서 SELECT … FOR UPDATE나 SELECT … LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것입니다.
SERIALIZABLE
💡 가장 엄격한 격리 수준으로, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없습니다.InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 “PHANTOM READ”가 발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요성은 없습니다.
