mysql, 트랜잭션과 락

트랜잭션과 락

• 트랜잭션(Transaction)은 작업의 완전성, 데이터의 정합성을 보장하는 기능. 논리적인 작업 셋에 대하여
  • 모두 완벽하게 처리하거나
  • 처리하지 못할 경우에는 원 상태로 복구하여 작업의 일부만 적용하는 현상(Partial update)이 발생하지 않게 만들어주는 기능.
• 잠금(Lock)은 동시성을 제어하기 위한 기능. 여러 커넥션이 동시에 동일한 자원을 요청할 경우, 하나의 커넥션만 변경할 수 있도록 해주는 역할. 트랜잭션의 데이터 정합성을 위하여 사용된다.

MySQL 엔진의 잠금

• MySQL의 잠금은 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.
• 아래는 MySQL 엔진 레벨에서의 잠금이다.

글로벌 락

• 글로벌 락은 MySQL 서버의 모든 변경 작업을 멈춘다.
• FLUSH TABLES WITH READ LOCK 명령으로 획득한다.
• InnoDB는 트랜잭션을 통해 일관된 데이터 읽기를 지원한다. 글로벌 락을 통한 모든 데이터 변경 작업을 멈출 필요가 없다.
• MySQL 8.0 이후 Xtrabackup이나 Enterprise Backup 등 백업 툴이 안정화되며 글로벌 락이 더는 필요 없게 되었다. 백업 락을 사용하며 이는 테이블의 데이터 변경을 허용한다.

테이블 락

• 테이블 락이란 개별 테이블 단위로 설정하는 잠금.
• LOCK TABLES {table_name} [READ|WRITE]의 형태로 테이블 락을 명시적으로 얻거나 DDL 등 특정 명령으로 테이블 락을 암묵적으로 획득할 수 있다.
• InnoDB에서는 레코드 기반 잠금을 제공하므로 DML에 대한 테이블 락을 필요로 하지 않는다. MyISAM이나 MEMORY는 데이터 변경 시 테이블 락이 필요했다.
• InnoDB에서도 테이블락이 사용되는 경우가 존재하나, 테이블 락이 발생하더라도 DML 쿼리는 락에 영향을 받지 않고 DDL에 한정하여 영향을 받는다.

네임들 락

• 네임드 락(Named Lock)은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.
• 네임드 락은 자주 사용되지 않지만, 여러 클라이언트 어플리케이션이 하나의 데이터베이스 서버나 테이블에 많은 레코드에 대한 많은 변경이 필요한 경우 효과적으로 사용 가능하다. 네임드 락을 활용하여 동기적으로 처리하도록 강제할 수 있기 때문이다.

메타데이터 락

• 메타데이터 락(Metadata Lock)은 데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금이다.
• 명시적으로 획득할 수는 없고 RENAME TABLE a to b 등 특정 쿼리에서 잠금이 발생한다. RENAME의 경우 이름 두 개에 잠금이 발생한다.

스토리지 엔진 잠금

• InnoDB 스토리지 엔진은 레코드 기반의 잠금 방식을 채택으로 MyISAM에 대비하여 뛰어난 동시성 처리 기능 제공한다.

잠금의 종류

• InnoDB에서 존재하는 락은 아래 그림과 같다.
  • 레코드락
  • 넥스트 키 락
  • 갭락
  • 자동증가락

• InnoDB의 경우 레코드 락이 갭락이나 테이블 락 등으로 레벨업(락 에스컬레이션)하는 경우는 없다.

레코드 락

• 레코드 자체를 잠금.
• 다른 DBMS와 다르게 레코드 자체가 아닌 인덱스를 통해 레코드를 잠금. 인덱스가 없을 경우 내부적으로 자동 생성된 클러스터 인덱스를 사용.
• 프라이머리 키, 유니크 인덱스의 경우 해당 레코드만 잠근다. 나머지 상황은 넥스트 키 락, 갭락을 사용하여 레코드 사이의 간격을 잠근다. 결과적으로 MySQL의 트랜잭션을 관리할 때, 최대한 인덱스를 활용하여 잠그도록 유도해야 한다.

갭락

• 레코드 사이의 간격만을 잠가서 새로운 레코드가 생성(insert)되는 것을 제어.
• 다른 DBMS에는 없는 종류의 락.
• 아래 t004 예제에 따르면 2부터 4까지 락을 걸었으나 레코드가 존재하는 1부터 6까지 락이 발생한다. 6을 초과하는 7부터 다른 트랜잭션이 삽입 가능하다.

-- given
create table t004 (
      id int primary key,
      name varchar(1000)
);
insert into t004(id, name) values (1, 'kim'), (6, 'lee');

-- tx1
select * from t004 where id between 2 and 4 for update;

-- tx2, 실패
insert into t004 (id, name) values (3, 'choi');

-- tx2, 실패
insert into t004 (id, name) values (5, 'choi');

-- tx2, 성공
insert into t004 (id, name) values (7, 'choi');

넥스트 키 락

• 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금.
• 갭 락과 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에 실행될 때 소스 서버에 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다.
• STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REFEATABLE READ로 격리수준을 사용하여 동일한 결과를 만들어내는 것을 목적으로 한다. 다만 서비스 중 데드락이 발생하는 경우가 종종 있으므로 로그 포맷은 ROW 형태를 사용하는 것을 권장한다.

자동 증가 락

• MySQL은 AUTO_INCREMENT라는 칼럼 속성을 통해 자동 증가하는 숫자 값을 제공하여, 이는 여러 레코드가 동시에 INSERT 하더라도 중복되지 않는다. 이를 위해 내부적으로 AUTO_INCREMENT 락을 사용한다.
• INSERT와 REPLACE 등 새로운 레코드를 저장하는 쿼리에서만 사용된다.
• AUTO_INCREMENT 값을 가져오는 순간만 락이 걸린다.
• 자동 증가 락을 제어하는 방법은 없다.
• 대부분의 경우 자동 증가 락으로 인한 문제가 발생하지 않는다. 다만, 자동 증가 락의 작동 방식은 변경하여 성능을 개선하거나 상황에 따른 락 동작 방식을 선택할 수 있다.
  • innodb_autoinc_lock_mode=0|1|2
  • 0: 모든 INSERT 문장은 자동 증가 락을 사용한다.
  • 1: 연속모드
    • INSERT되는 레코드의 건수를 정확하게 예측할 수 있을 경우 자동 증가 락이 아닌 래치를 사용하여 성능 효과를 누린다.
    • 다만, INSERT…SELECT의 경우 건수를 예측할 수 없으므로 자동 증가 락을 사용한다. 자동 증가 락을 걸고 여러 개의 값을 미리 할당 받아 연속된 순서를 보장받는다. 미사용한 값은 폐기한다.
  • 2: 인터리빙 모드
    • 언제나 래치를 사용한다.
    • INSERT…SELECT 등 건수를 예측할 수 없는 레코드의 경우 연속된 레코드 간 값이 하나 씩 증가하는 것을 보장하지 않는다.
    • STATEMENT 포맷으로 바이너리 로그를 작성하는 경우 소스 서버와 레플리카 서버 간 자동 증가 값이 달라질 수 있다.
  • MySQL 8.0의 기본값은 2이다. 바이너리 로그를 ROW로 사용할 경우 1을 권장.

innoDB의 인덱스와 잠금

• MySQL은 레코드를 잠그는 것이 아니라 인덱스를 잠금.
• 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 전체에 락을 걸어야 한다.

create table t003(
 id int auto_increment primary key,
 name varchar(100) ,
 address varchar(100),
 dt datetime,
 index ix_name(name)
);
    
insert into t003(name, address) values ('lee', 'seoul'), ('kim', 'pusan'), ('kim', 'seoul'), ('lee', 'pusan');

-- tx1
update t003
set dt = now()
where name='kim' and address = 'pusan';

-- tx2
-- 1. 정상
update t003
set dt = now()
where name = 'lee';

-- 2. 정상
update t003
set dt = now()
where name = 'lee' and address = 'pusan';

-- 3. 락
update t003
set dt = now()
where name = 'kim';

-- 4. 락
update t003
set dt = now()
where address = 'seoul';

-- 5. 락
update t003
set dt = now()
where address = 'pusan';

• tx1가 udpate를 수행했을 때, 인덱스로 처리된 name을 기준으로 락을 걸어 버린다. 그러므로 해당 인덱스 이외의 데이터인 name=any에 대해서는 다른 트랜잭션이 접근 가능하다.
• 하지만 4와 5인 address=any는 인덱스가 없고 레코드 전체에 락을 걸어야 하기 때문에 tx1이 락을 놓을 때까지 대기 상태에 빠지게 된다.
• 결과적으로 인덱스 설정은 단순히 select 성능을 위해서가 아니라 DML의 로직 처리를 위하여 매우 중요하다.

잠금의 조회 및 해제

• MySQL 엔진과 스토리지 엔진의 락이 서로 다르며 각 각을 동시에 관리하는 것은 어렵다.
• MySQL의 버전이 올라가며 이를 모니터링할 수 있는 기능이 확장되었다.
• mysql 8.0 이전에는 information_schema을 사용하여 관리했다.
  • information_schema.INNODB_TRX
  • information_schema.INNODB_LOCKS
  • information_schema.INNODB_LOCK_WAITS
• 다만, MySQL 8버전 이후 Performance Schema을 사용한다.
  • performance_schema.data_locks
  • performance_schema.data_lock_waits
• 아래 예제는 tx1이 레코드에 대해 잠금 후 대기 중이며, tx2와 tx3는 tx1이 해당 레코드의 잠금을 해제할 때까지 대기 중이다.

create table t001 (
  id int primary key,
  name varchar(1000)
);

insert into t001(id, name) values (1, 'kim'), (2, 'lee');

-- tx1
update t001 set name = 'changed' where id = 1;
-- tx2
update t001 set name = 'changed' where id = 1;
-- tx3
update t001 set name = 'changed' where id = 1;

• 해당 프로세스와 잠금은 아래와 같이 조회하고 처리할 수 있다.

-- 프로세스를 조회한다.
show processlist;

-- 스레드를 중지 시킨다.
kill :threadName;

-- 락과 관련한 상태를 확인한다.
SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;

select * from performance_schema.data_locks;

MySQL과 InnoDB의 격리 수준

• 트랜잭션의 격리 수준(isolation level)이란 여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것을 의미한다.
• 격리수준은 크게 4가지로 나뉜다.
  • READ UNCOMMITED
  • READ COMMITED
  • REPEATABLE READ
  • SERIALIZABLE
• READ UNCOMMITED은 일반적으로 사용하지 않는다.
• SERIALIZABLE은 동시성이 중요한 경우 사실상 사용하지 않는다.
• READ COMMITED와 REPEATABLE READ은 성능 상 차이가 거의 없다. 다만 오라클은 전자를 MySQL은 후자를 주로 사용 한다.

격리 수준	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생 (InnoDB 는 없음)
SERIALIZABLE	없음	없음	없음

READ UNCOMMITTED

• 다른 트랜잭션의 커밋과 롤백의 여부와 관계 없이 변경된 데이터를 즉각 보여준다. 이처럼 다른 트랜잭션의 완료되지 않는 작업을 볼 수 있는 현상을 더티 리드(Dirty read)라 한다.
• 더티 리디의 문제로 RDBMS 표준에서는 이를 격리 수준으로조차 인정하지 않는다.

READ COMMITTED

• 오라클 및 많은 온라인 서비스에서 가장 많이 선택하는 격리 수준.
• 더티 리드가 발생하지 않는다.
• 다만 NON-REPEATABLE-READ가 발생한다. 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 “REPEATABLE READ” 정합성에 어긋난다.
• 트랜잭션 내의 SELECT 쿼리의 일관성이 중요한 로직에서는 사용할 수 없다.

• NON-REPEATABLE-READ 실습. 격리수준 READ COMMITTED과 REPEATABLE READ을 비교한다.

-- tx1 read committed
select * from t001 where id = 1;

-- tx2 update and commit
update t001 set name = 'changed' where id = 1;

-- tx1 read committed
select * from t001 where id = 1;
-- > changed

-- tx1 repeatable read
select * from t001 where id = 1;

-- tx2 update and commit
update t001 set name = 'changed' where id = 1;

-- tx1 read committed
select * from t001 where id = 1;
-- > kim

REPEATABLE READ

• MySQL InnoDB의 기본 격리 수준으로 REPEATABLE READ 정합성을 달성.
• 바이너리 로그를 가진 MySQL 서버에서 사용해야하는 최소한의 격리 수준.
• MVCC는 READ COMMITTED와 REPEATABLE READ에 모두 사용된다.
  • READ COMMITTED는 레코드의 가장 최근의 언두만 리턴하면 된다.
  • REPEATABLE READ은 트랜잭션이 시작 시점에서의 스냅샷을 사용해야 하며 이로 인하여 보관해야 하는 언두 데이터의 양이 READ COMMITTED에 대비하여 크다.

• for udpate를 사용할 경우 팬텀 리드(Phantom Read)로 인한 부정합이 발생할 수 있다.

• 팬텀리드 실습.

create table t001 (
  id int primary key,
  name varchar(1000)
);

insert into t001(id, name) values (1, 'kim'), (2, 'lee');

-- tx1
select * from t001 where id >= 2;
-- > lee

-- tx2, insert and commit
insert into t001(id, name) values (3, 'choi');
commit;

-- tx1
select * from t001 where id >= 2;
-- > lee
select * from t001 where id >= 2 for update;
-- > lee, choi

SERIALIZABLE

• select 쿼리마다 공유 잠금(읽기 잠금)을 획득한다. 즉, SERIALIZABLE 트랜잭션이 읽은 레코드를 다른 트랜잭션은 수정할 수 없어서 팬텀 리드가 발생하지 않는다.
• 다만, 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ에서도 팬텀 리드를 방지할 수 있으므로 반드시 쓸 필요는 없다.
• InnoDB의 기본적인 원칙인 잠금이 필요 없는 일관된 읽기를 지키지 않는 격리 수준이다.
• 가장 엄격하되, 성능 상 손해가 발생한다.

RealMySQL 8.0을 참고하여 작성하였습니다.