[Real Mysql(8.0)]트랜잭션과 잠금

트랜잭션이란?

트랜잭션은 작업의 완전성을 보장해 주는 것으로  논리적인 작업 처리를 모두 완벽하게 처리하거나,  그렇지 못할 경우 원상태로 복구해서 작업의 일부만 처리되는 현상(Partial update)를 방지해주는 기능이다.

잠금(Lock) vs 트랜잭션  vs 격리수준

잠금은 여러 커넥션에서 동시에 동일한 자원에 요청할 경우 순서대로 한 시점에는 하나의 거넥션반 변경하게 해주도록 하는 동시성을 제어하기 위한 기능이다. 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다. 

격리수준은 트랜잭션내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

주의사항

DBMS의 커넥션처럼 트랜잭션 역시 꼭 필요한 코드에만 최소화 하여 적용하는 것이 좋다. 

일반적으로 데이터베이스 커넥션은 개수가 제한적이므로 각 단위의 프로그램이 커넥션을 소유하는 시간이 길어질 수록 사용가능한 커넥션의 수는 줄어들기 때문에 실제로  DBMS에 저장하는 작업 직전에 커넥션을 생성(또는 커넥션 풀에서 가져오기) 하는것이 좋다. 

또한 메일전송, FTP 파일 전송등 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은  DBMS 트랜잭션에서 제거하는 것이 좋다.

외부 서버(네트워크)와 통신할 수 없는 상황이 발생한다면 DBMS서버에 부하가 발생해 심각한 상황에 다를 수 있다.

---

MySQL 엔진과 잠금

MySQL 잠금은 스토리지 엔진 레벨에서의 잠금과  MySQL 엔진 레벨에서의 잠금으로 나눌 수 있다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진간 영향을 미치지 않는다.

(MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 생각하면 된다.)

 

글로벌 락(GLOBAL LOCK)

글로벌락은 잠금 가운데 가장 범위가 큰 잠금이다. 한 세션이 글로벌 락을 획득 하게 되면 다른 세션에서  SELECT를 제외한 대부분의  DDL문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 대기상태가 된다. 

대상 테이블이나 데이터베이스가 다르더라도 MySQL서버 전체에 영향을 미치기 때문에  글로벌락을 걸기전에 장시간 시행되는 쿼리가 있는지 확인하고 장시간 쿼리가 종료된 후에 실행하는 것이 좋다.  (웹서비스용 mysql서버에서는 가급적 사용하지 않는 것이 좋다)

글로벌 락의 실행 명령
FLUSH TABLES WITH READ LOCK
백업락의 실행 명령

Mysql 8.0버전부터는 Xtrabackup 이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행환경을 위해 백업락이 도입 되었다.

특정세션에서 백업락을 획득하면 모든 세션에서 다음 정보를 변경할 수 없게 된다.

• 데이터베이스 및 테이블 등 모든 객체 생성및 변경, 삭제
• REPAIL TABLE과 OPTIMIZE TABLE 명령
• 사용자 관리 및 비밀번호 변경

하지만 주로 백업은 레플리카 서버에서 실행되므로 백업락에서는 (변경은  소스서버(마스터)에서 발생하므로) 일반적인 테이블의 변경이 허용된다고 할 수 있다. ????

테이블 락(TABLE LOCK)

테이블락은 개별 테이블 단위로 설정되는 잠금이다. 아래와 같이 명령을 통해 명시적인 방법으로 테이블의 락을 획득 할 수 있다. 

테이블 락 실행명령
LOCK TABLES table_name [ READ | WRITE ] 
테이블락 잠금 해제
UNLOCK TABLES ..

묵시적으로 테이블 락을 발생시킬 수도 있는데, MyISAM 이나 MEMORY 엔진에서 테이블에 데이터를 변경하는 쿼리를 실행하면 자동으로 락이 획득됐다가 해제된다. MYSQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 방식으로 사용된다. 

InnoDB의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인한 테이블락은 설정되지 않는다.  스키마를 변경하는 쿼리(DDL)의 경우에만 묵시적 테이블 락이 설정된다. 

 

네임드 락(Named LOCK)

네임드락은 사용자가 지정한임의의 문자열에 대해 잠금 획득하고 반납(해제)하는 잠금이다. 자주 사용되지는 않는다. 

주로 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용한다.

Mysql8.0버전부터는 네임드락을 중첩해서 사용할 수 이게 됐으며, 현재 세션에서 획득한 네임드 락을 한번에 모두 해체하는 기능도 추가됐다.

네임드 락 실행명령
// 'some lock'이라는 문자열에 대해 잠금을 획득한다.
// 이미 잠금을 사용중이면 2초 동안만 대기한다.(2초 후 잠금 자동 해제)
SELECT GET_LOCK('some lock', 2);

// 'some lock'이라는 문자열에 대해 잠금이 설정돼 있는지 확인
SELECT IS_FREE_LOCK('some lock');

// 'some lock'이라는 문자열에 대해 잠금을 반납(해제)한다.
SELECT RELEASE_LOCK('some lock');
//현재 세션의 모든 네임드락 해제
SELECT RELEASE_ALL_LOCKS();

메타데이터 락(Metadata LOCK)

메타데이터 락은 데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 명시적으로 획득/해제 할 수 있는것은 아니고 

테이블 이름을 변경하는 경우 자동으로 획득되는 잠금이다. 테이블명을 변경하는 경우 변경 전/후  두개의 이름에 모두 잠금을 설정한다. 

---

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은  MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 가지고 있어 MyISAM보다 뛰어난 동시성 처리를 제공할 수 있다.

하지만 이원화된 잠금처리 때문에  InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 까다로운데, 최근버전에서 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다.

(Mysql 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블로 트랜잭션별 잠금상태를 확인할 수 있으며, Performance Schema를 이용해 스토리지 엔진의 내부잠금(세마포어)에 대한 모니터링 방법도 추가 되었다.)

레코드 락(Record lock, Record only lock)

레코드 자체만을 잠그는 것이며 다른 DBMS의 레코드락과 동일한 역할을 하지만 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점에서 차이가 있다. 인덱스가 없는 테이블이라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. 

보조 인덱스를 이용한 변경 작업은 넥스트 키 락 이는 갭 락을 상요하지만 프라이머리키나 유니크 인덱스 키에 의한 변경 작업에서는 레코드 자체에 대해서만 락을 건다.

 

갭 락(Gap lock)

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것이다. 갭락은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다. 갭락은 넥스트ㅡ 키 락의 일부로 자주 사용된다.

 

넥스트 키 락(Next key lock)

레코드락과 갭 락을 합쳐놓은 형태의 잠금을 넥스트 키 락이라고 한다.  로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스(마스터)서버에서 만들어 낸 결과와 동일한 결과를 만들어 내도록 보장하는 것이 주 목적이다??

 

자동증가 락(Auto increment lock)

AUTO_INCREAMENT 컬럼이 사용된 테이블에 동시에 여러 레코드가 insert되는 경우, 저장되는 각 레크드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다. InnoDB스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT락이라고 하는 테이블 수준의 잠금을 사용한다. 

insert, replace 와 같은 새로운 레코드를 저장하는 쿼리에서만 해당락이 사용되며 update, delete에서는 사용되지 않는다. 

다른 레코드 락이나 넥스트 키락과는 달리 자동증가락은 트랜재션과 관계없이 insert나 replace쿼리에서  AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다. 

 

자동증가 락의 모드 변경(작동방식 변경)

1.  innodb_autoinc_mode=0

모든 insert문은 자동 증가 락을 사용한다.

2. innodb_autoinc_mode=1

mysql서버가 insert되는 레코드의 건수를 정확히 예측할 수 있을때는 자동증가락을 사용하지 않고 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다. 개선된 래치는 자동증가 락과 달리 아주 짧은 시간만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 잠금이 해제된다. 

서버가 건수를 예측할 수 없을때에는 자동증가 락을 사용한다. 대량 insert가 수행될 때는 InnoDB의 스토리지 엔진은 여러개의 자동증가 값을 한번에 할당받아서 insert되는 레코드에 사용한다. 하지만 한번에 할당받은 자동증가 값이 남아서 사용하지 못하면 폐기하므로 대량 insert문 이후에 누락된 자동증가 값이 발생 할 수 있다. 

3. innodb_autoinc_mode=2

이 설정에서 InnoDB스토리지 엔진은 절대 자동증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다. 이 설정에서는 하나의 insert문으로 insert되는 레코드라고 하더라도 연속된 자동증가값을 보장하지는 않는다. 이 모드에서는 대량 insert문이 실행되는 도중 다른 커넥션에서 insert를 수행 할 수 있으므로 동시처리 성능이 높아진다.

STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동증가 값이 달라질 수도 있기 때문에 주의해야 한다. 

인덱스와 잠금

InnoDB의 레코드 잠금은 인덱스를 잠그는 방식으로 처리된다고 랬다. 즉, 변경할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 건다. 예를들어 아래와 같은 업데이트 쿼리를 처리한다고 생각해보자.

( employees 테이블에는 first_name에만 인덱스가 걸려있고, 해당 조건을 만족하는 데이터는 단 1건 존재한다.)

 update employees SET hire_date =NOW() where first_name = 'rose' and last_name = 'park' 

업데이트 되는 데이터 건 수는 한건이지만, update문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name뿐이며,

first_name='rose'인 데이터 건수가 342건이라고 한다면  해당 데이터의 업데이트가 이루어지는동안 342건의 레코드가 모두 잠기게 된다. 따라서 업데이트 쿼리를 위한 적절한 인덱스가 준비돼 있지 않다면 동시성이 상당히 떨어져 udpate시 클라이언트가 대기해야 하는 상황이 발생할 수도 있고, 이때문에 Mysql InnoDB의 인덱스 설계가 중요하다.

 

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

레코드 수준의 잠금은 그 레코드가 자주 사용되지 않는다면 오랜시간동안 잠겨진 상태로 남아있어도 잘 발견되지 않는다. 

레코드 잠금과 잠금을 대기하는 클러이언트의 정보를 확인 하는 방법은 My SQL 서버에서 제공하는 performance_schema, data_locks, data_lock_waits 테이블 등에서 확인 할 수 있다.

 

---

MySQL의 격리 수준(isolation level)

트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다. 격리 수준은 크게  네가지로 나뉜다.

READ UNCOMMITTED

READ UNCOMMITTED격리 수준에서는 각 트랜잭션의 변경 내용이 Commit이나 rollback여부에 상관 없이 다른 트랜잭션에서 보인다. 예를 들어 A라는 트랜잭션에서 employee_no=10인 "summer" 라는 직원을 등록하고 있다고 가정하자.

이때  B 라는 트랜잭션에서 employee_no=10 인 직원을 조회했을때 summer가 조회된다.

A 트랜잭션에서  commit을 하지 않은 상태이고, 심지고  A 트랜잭션에서 문제가 발행해 rollback이 된 상황에서도 B 트랜잭션에서는 summer 사원에 대한 처리를 계속 진행 할 수 있다.

이렇게 READ UNCOMMITTED격리 수준에서는 DirtyRead가 발생 할 수 있고 정합성에 문제가 많은 격리 수준으로 비권장 된다.

Dirty Read란
어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상

READ COMMITTED

READ COMMITTED는 오라클 DBMS에서 기본으로 사용되는 격리주순이며, 온라인 서비스에서 가장 많이 선택되어지는 격리수준이다. 

이 레벨에서는  어떤 트랜잭션에서 데이터를 변경했더라고 commit이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문에 더티리드가 발생하지 않는다. 하지만 'REPEATABLE READ' 문제가 발생 할 수 있다.

예를 들어 A 트랜잭션에서 employee_no=10인 "summer" 인 직원을 검색 했는데 일치하는 결과가 없었다.

하지만 B트랜잭션에서  employee_no=10인 "summer" 인 직원을 등록하고 커밋하면 A트랜잭션에서 employee_no=10인 "summer" 인 직원을 조회하면 1건이 조회 될 것이다. 이는 하나의 트랜잭션 내에서 똑같은 SELECT쿼리를 실행 했을때는 항상 같은 결과를 가져와야 한다는 "REPEATABLE READ" 정합성에 어긋난다.

REPEATABLE READ 문제는 일반적인 웹 서비스에서는 문제가 되지 않지만 입출금이 빈번한 예끔의 잔여를 표기하는 경우와 같이 금적적인 문제와 직결될때 문제가 될 수 있다.

REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 

InnoDB엔진은 트랜젝션이 롤백될 가능성에 대비해 변경되기전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경한다. 

그리고 이 언두 영역에 백업된 데이터를 이용해 동일 트랜잭션내에서는 동일한 결과를 보여줄 수 있도록 보장한다. 

 

모든 트랜잭션은 고유한 트랜잭션의 번호(순차증가)를 가지며 언두영역에 백업된 모든 레코드에는 해당 트랜잭션의 번호가 포함돼 있다.

undo 영역의 데이터는 InnoDB스토리지 엔진이 불필요하다고 판단되는 시점에 주기적으로 삭제한다.

그리고 이 언두 영역을 이용해 커밋되기 전 데이터를 보여준다.

예를 들어 5번 트랜잭션에 의해 emp_no=10,  name="rose"인 직원이 등록되었다고 하자.  8번 트랜잭션에서 B 사용자가 emp_no=10인 직원의 이름을  rose로 조회해서 작업을 하고 있다. 그러던 도중 10번 트랜잭션에서  A라는 사용자가 emp_no=10 인 직원의 이름을 summer로 변경후 commit 했다고 하자. 이후에 B 사용자가 다시 emp_no=10인 직원의 이름을 조회해도. rose 로 조회가 된다.

B의 8번 트랜잭션 안에서 실행되는 모든 select쿼리는 트랜잭션 번호가 본인의 트랜잭션 번호(8)보다 작은 트랜잭션 번호에서 변경한 것만 보게 되기 때문이다. 

 

하지만 2번 트랜잭션에서 emp_no=10,  name="rose"인 직원을 등록하고, 3번 트랜잭션에서 emp_no=11,  name="winter"인 직원을 등록했다고 하자. 이때 2번 트랜잭션에서 SELECT * FROM employees WHERE emp_no >= 10 FOR UPDATE을 조회 한다면 rose, summer 두 건이 나온다.  SELECT...FOR UPDATE 와 같은 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데 언두 레코드에는 잠금을 걸 수 없기때문에 현재 레코드의 값을 가져오고 이렇게 다른 트랜잭션에서 수행한 변경작업에 의해 레코드가 보였다 안보였다 하는 현상을 "PHANTOM READ" 라고 한다. 

SERIALIZABLE

가장 단순한 격리 수준이면서 가장 엄격한 격리 수준이다. 읽기 작업도 공유잠금(읽기 잠금)을 획득하며, 동시에 다른 트랜잭션은 해당 레코드를 변경하지 못한다. 한 트랜잭션에서 읽고 쓰는 레코드는 다른 트랜잭션에서 접근 할 수 없기 때문에 동시 처리 성능이 떨어진다. PHANTOM READ' 문제는 발생하지 않지만 이미 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 에서도 이미 PHANTOM READ 문제가 발생하지 않기 때문에 굳이 SERIALIZABLE 을 사용할 필요는 없다.

 

 

 

위 내용은 RealMysql8.0의 5장 트랜잭션과 잠금 을 읽고 정리한 내용입니다.