공유트랜잭션과 잠금

1. 트랜잭션이란?

• 작업의 완전성을 보장해주는 것
• 논리적인 작업 모음을 모두 완벽하게 처리하거나 또는 처리하지 못할 경우 원 상태로 복구해서 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 기능

MEMORY 스토리지 엔진 혹은 MyISAM 스토리지 엔진은 트랜잭션 기능이 없다.

트랜잭션은 DBMS의 커넥션과 동일하게 꼭 필요한 최소한의 코드에만 적용하는 것이 좋다. 프로그램의 코드가 DB connection을 가지고 있는 범위와 트랜잭션이 활성화되어 있는 프로그램의 범위를 최소화해야 한다는 것이다.

프로그램의 코드에서 라인 수는 한두 줄이라 하더라도 네트워크 작업이 있는 경우는 반드시 트랜잭션에서 배제해야 한다. 이런 실수로 인해 DBMS 서버가 높은 부하 상태로 빠지거나 위험한 상태에 빠지는 경우가 빈번히 나타나곤 한다.

트랜잭션의 특징

• 원자성(Atomicity) : 트랜잭션의 결과는 DB에 반영이 되거나 반영이 안 되거나 이 두 가지 결과만을 나타낸다.
• 일관성(Consistency) : 트랜잭션이 진행되는 동안 DB가 변경되더라도 이전에 사용한 DB를 참조한다. 즉 트랜잭션은 시작부터 종료 시까지 같은 형태의 DB를 참조한다.
• 독립성(Isolation) : 트랜잭션이 두 개이상 실행될 때 트랜잭션끼리 영향을 주지 못한다. 즉 하나의 트랜잭션이 또 다른 트랜잭션의 결과를 참조할 수 없다.
• 영구성(Durability) : 트랜잭션이 성공적으로 완료(Commit) 되었을 때 결과를 영구적으로 DB에 반영된다.

 

2. 트랜잭션 격리수준

동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지 결정하는 것

 

2-1. READ UNCOMMITED

이름 그대로 commit 되지 않은 레코드를 읽을 수 있게 하는 것이다.

따라서 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데 다른 트랜잭션에서 볼 수 있는 Dirty Read 현상이 나타난다. 정합성에 문제가 많은 격리 수준이다.

 

2-2. READ COMMITED

commit 된 레코드만 읽을 수 있게 하는 것이다. commit 전에 조회를 시도한다면 Undo 영역에 백업된 레코드를 조회할 수 있다. 하지만 NON-REPEATABLE READ 문제가 발생할 수 있다.

• NON-REPEATABLE READ : 한 트랜잭션에서 동일한 SELECT 쿼리의 결과가 다름

 

2-3. REPEATABLE READ

InnoDB 엔진에서 사용하는 기본 격리 수준으로 따로 격리 수준을 설정해 주지 않았다면 REPEATABLE READ 격리 수준으로 트랜잭션이 실행된다. REPEATABLE READ는 하나의 트랜잭션 내부에서 같은 SELECT문은 항상 같은 결과를 보여주는 것이다. InnoDB 엔진에서는 트랜잭션 별로 식별자를 주고 트랜잭션에서 변경하는 데이터를 Undo영역에 백업한다. 이 백업된 데이터와 트랜잭션 식별자로 동일 트랜잭션에서 동일 결과값을 보여줄 수 있도록 보장한다.

 

2-4. SERIALIZABLE

격리 수준 중 가장 높은 격리 수준으로 레코드를 조회할 때 Shared Lock을 획득해야만 조회할 수 있고, 데이터를 변경할 때에는 Exclusive Lock을 획득해야만 변경할 수 있다.

• Shared Lock : Read에 대한 lock이다. 일반적인 SELECT 쿼리는 lock을 사용하지 않고 DB를 읽어 들인다. 하지만 ‘SELECT … FOR SHARE’ 등 일부 SELECT 쿼리는 read 작업을 수행할 때 InnoDB가 각 row에 Shared lock을 건다.
• Exclusive lock : write에 대한 lock이다. ‘SELECT … FOR UPDATE’ 나 UPDATE, DELETE 등의 수정 쿼리를 날릴 때 각 row에 걸리는 lock이다.

즉 한 트랜잭션에서 사용하는 데이터는 다른 트랜잭션에서 접근할 수 없게 만든다. 데이터 정합성을 지키는 면은 가장 우수하지만 동시 처리 성능이 떨어진다.

 

3. 잠금

• 동시성을 제어하기 위한 기능
• 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 수행
• MySQL 엔진 레벨 잠금과 스토리지 엔진 레벨 잠금 두 가지 종류가 있음

 

4. MySQL 엔진의 잠금

4-1. 글로벌 락

a. FLUSH TABLES WITH READ LOCK 명령으로만 획득할 수 있으며, 한 세션에서 글로벌 락을 획득하면 MySQL 서버 전체에 락이 걸린다. 다른 세션은 SELECT을 제외한 대부분의 DDL 문장이나 DML 문장 실행을 대기하게 된다.

 

4-2. 테이블 락

a. 개별 테이블 단위로 설정되는 잠금

b. 명시적 잠금 : LOCK TABLES table_name [READ | WRITE], 해제 : UNLOCK TABLES

c. 온라인 작업에 상당한 영향을 미치기 때문에 특별한 상황이 아니면 애플리케이션에서 거의 사용할 필요가 없다.

d. 묵시적 잠금은 MyISAM 혹은 MEMORY 테이블에 데이터를 변경하는 쿼리가 실행되면 발생한다.

e. InnoDB는 레코드 기반의 잠금을 제공하기 때문에 DDL이 발생하는 경우에만 테이블 락이 걸리게 된다.

 

4-3. 유저 락

a. GET_LOCK() 함수를 이용해 임의로 잠금을 설정할 수 있는데, DB 객체에 대해 잠금을 거는 것이 아니라, 사용자가 지정한 문자 열에 대해 잠금을 걸고 해제하는 방식이다.

b. 한꺼번에 많은 레코드를 변경하는 쿼리의 경우 데드락의 원인이 될 수 있기 때문에 이 경우 유저 락을 활용할 수 있다.

 

4-4. 네임 락

a. 데이터베이스 객체(테이블 or 뷰)의 이름을 변경하는 경우 획득하는 잠금이다. 명시적으로 획득하거나 해제할 수 없고 RENAME TABLE 등의 명령어를 사용했을 때 잠금 되었다 해제된다.

 

5. MyISAM, MEMORY 스토리지 엔진 잠금

• 읽기 잠금 : 테이블에 쓰기 잠금이 걸려 있지 않으면 읽기 잠금을 획득하고 읽기 작업을 시작할 수 있다.
• 쓰기 잠금 : 테이블에 아무런 잠금이 걸려 있지 않아야만 쓰기 잠금을 획득할 수 있고, 그렇지 않다면 다른 잠금이 해제될 때까지 기다려야 한다.

 

테이블 잠금 확인법

 

SHOW OPEN TABLES FROM db LIKE '%s';

• In_use : 해당 테이블을 잠그고 있는 클라이언트의 수 + 그 테이블의 잠금을 기다리는 클라이언트의 수
• Name_locked : 네임 락 여부

 

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

 

SHOW PROCESSLIST;

• State가 비어 있다면, 현재 잠금을 걸고 있다는 것
• State가 Locked라면 잠금을 기다리고 있는 것

 

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

• 비관적 잠금 : “내가 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할 수 있다”라는 비관적인 전제를 가지고 레코드를 변경하기 전에 아무도 못 들어오게 잠가두는 방식
• 낙관적 잠금 : 트랜잭션이 같은 레코드를 변경할 가능성은 희박하다고 가정하고, 일단 잠금 없이 변경한 후에 마지막에 충돌이 있었는지 확인하여 ROLLBACK처럼 처리하는 방법
• 레코드 락 : 레코드만을 잠그는 것. 중요한 차이는 InnoDB의 경우 인덱스의 레코드를 잠그게 된다. 보조 인덱스를 이용한 변경 작업은 넥스트 키 락이나 갭 락을 사용하거나 PK 혹은 유니크 인덱스에 의한 변경 작업은 레코드 락을 건다.
• 갭 락 : 레코드 자체뿐 아니라 바로 인접한 레코드 사이의 간격만을 잠그는 것. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다. 이것은 실존하는 것이 아니라 개념일 뿐이며, 넥스트 키 락의 일부로 사용된다.
• 넥스트 키 락 : 레코드 락과 갭 락을 합쳐 놓은 형태이다. STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE_READ 격리 수준을 사용해야 한다. InnoDB의 갭 락이나 넥스트 락은 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다. 그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다.
• 자동 증가 락 : 자동 증가하는 숫자 값을 추출하기(Auto Increment) 위한 락으로 명시적으로 획득/해제할 수 없다.