5.트랜잭션과 잠금 - 트랜잭션과 MySQL 엔진의 잠금

이번 포스트에서는 트랜잭션과 MySQL 엔진의 잠금에 대해서 알아보겠습니다.

 

트랜잭션(Transaction)

트랜잭션이란 작업의 완전성을 보장해주는 것 입니다.

논리적인 작업셋을 모두 완벽하게 처리하거나 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상

(Partial Update)이 발생하지 않게 만들어주는 기능입니다.

 

즉, 하나의 논리적인 작업셋이(여러 SQL 문) 100% 적용되거나(COMMIT) 아무것도 적용되지 않아야(ROLLBACK 또는 트랜잭션을 ROLLBACK 시키는 오류가 발생했을 때)함을 보장해줍니다.

 

MyISAM, MEMORY 스토리지 엔진은 트랜잭션 기능을 제공하지 않기 때문에 Partial Update 현상이 발생하고,

InnoDB 스토리지 엔진은 트랜잭션 기능을 제공하기 때문에 Partial Update 현상이 발생하지 않고 이전의 상태로 복구됩니다.

 

트랜잭션 사용시 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화 돼있는 범위를 최소화해야 합니다.

특히 네트워크 작업이 있는 경우 반드시 트랜잭션에서 배제해야 합니다. 배제하지 않으면 DBMS 서버가 높은 부하 상태로 빠질 수 있습니다.

 

잠금과 트랜잭션은 서로 비슷한 개념 같지만

잠금은 여러 커넥션에서 동시에 동일한 자원(테이블 또는 레코드)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경 할 수 있게 하도록( 동시성을 제어하기 위해 )사용하는 기능이고, 트랜잭션은 데이터의 정합성을 보장하기 위해 사용하는 기능입니다.

 

Transaction의 ACID 속성

• Automicity(원자성)
  • transaction은 논리적으로 더 이상 쪼갤 수 없는 작업 단위이기 때문에 transaction 안에 SQL 문은 모두 성공하거나(commit) 중간에 SQL 문이 실패하면 지금까지의 작업은 모두 취소하여(rollback) 아무 일 도 없었던 것처럼 transaction이 시작하기 전 상태로 돌아가야 합니다.
  • 개발자는 transaction 단위를 어떻게 정의하고 언제 commir과 rollback을 할지 잘 결정해야 합니다.
• Consistency(일관성)
  • DB의 정의된 rule을 위반했다면 rollback 시켜서 DB의 일관성을 유지시켜 줘야 합니다.
  • DBMS는 transaction이 DB의 정의된 rule을 위반했는지 constraints, trigger 등을 통해 확인합니다.
  • 개발자는 transaction이 consistent 하게 동작하게끔 신경 써줘야 합니다.
• Isolation(격리성)
  • 모든 transaction은 다른 transaction 으로부터 독립되어야 합니다.
  • DBMS는 여러 종류의 Isolation level을 제공합니다.
  • 개발자는 Isolation level 중 어떤 level로 transaction을 시작할지 잘 결정해야 합니다.
• Durability(영존성)
  • commit된 transaction은 DB에 영구적으로 반영되어야 합니다.
  • DB system에 문제가 생겨도(power fail or DB crush) commit된 transaction은 DB에 남아있어야 합니다.
  • 영구적으로 저장되어야 하기 때문에 비휘발성 메모리(HDD, SSD)에 저장됩니다.
  • 기본적으로 transaction의 Durability는 DBMS가 보장합니다.

5.트랜잭션과 잠금 - MySQL 격리 수준

 

MySQL 엔진의 잠금

잠금은 크게 MySQL 엔진 레벨과 스토리지 엔진 레벨로 나눌 수 있습니다.

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진간 상호 영향을 미치지 않습니다.

 

MySQL 엔진의 잠금은 글로벌락, 테이블 데이터 동기화를 위한 테이블 락, 데이블 구조를 잠그는 메타데이터 락, 사용자의 필요에 맞게 사용할 수 있는 네임드 락이 있습니다.

 

글로벌 락

글로벌 락은 MySQL에서 제공하는 잠금 범위 중 가장 크고, FLUSH TABLES WITH READ LOCK 명령으로 획득이 가능합니다.

한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 문장이 글로벌 락이 해제될 때 까지 대기 상태로 남습니다.

글로벌 락이 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미칩니다.

 

MySQL 8.0 백업락

특정 세션에서 백업 락을 획득하면 모든 세션에서

데이터베이스 및 테이블 등 모든 객체 생성 및 변경 삭제, REPAIR TABLE과 OPTIMIZE TABLE 명령, 사용자 관리 및 비밀번호 변경을 할 수 없습니다.

 

MySQL 서버는 Source Server, Replica Server로 구성이 되는데 백업은 주로 Replica Server에서 일어나므로 특정 세션에서 백업 락을 획득 하더라도 일반적인 테이블의 데이터 변경은 가능합니다.

 

테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 테이블 락을 획득 할 수 있습니다.

 

명시적으로는 LOCK TABLES table_name [READ | WRITE] 명령으로 락을 획득하고 UNLOCK TABLES 명령으로 반납 할 수 있습니다.

 

묵시적인 테이블 락은 MyISAM, MEMORY 테이블에 데이터를 변경하는 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료되면 자동으로 해제됩니다.

 

InnoDB 테이블의 경우에는 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 데이터 변경 쿼리로 인해 묵시적인 테이블 락을 획득하지 않습니다. DML 쿼리에서는 무시가 되고 DDL 쿼리에 대해서는 테이블 락을 획득합니다.

 

네임드 락

네임드 락은 대상이 테이블이나 레코드, AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라 사용자가 지정한 문자열(String)에 대해 락을 획득하고 반납하는 잠금입니다.

 

데이터베이스 서버 1대에 여러대 웹 서버가 접속해서 서비스 하는 상황에서 여러대의 웹 서버가 어떤 정보를 동기화해야 하는 요건 처럼 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 이용하면 됩니다.

 

SELECT GET_LOCK('gyubin', 10)을 사용하여 gyubin이라는 문자열에 대해 락을 획득하고 이미 누군가 획득 한 락이면 락을 획득하기 위해 10초동안 대기합니다.

 

SELECT IS_FREE_LOCK('gyubin')을 사용하여 gybin이라는 문자열에 대해 락이 설정돼 있는지 확인 할 수 있습니다.

 

SELECT RELEASE_LOCK('gyubin')을 사용하여 gyubin이라는 문자열에 대해 획득했던 락을 해제 할 수 있습니다.

 

 

메타데이터 락

메타데이터 락은 데이터베이스 객체(대표적으로 테이블이나 뷰)의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다.

락을 명시적으로 획득하거나 해제할 수 있는 것이 아니고 RENAME TABLE a TO b 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금입니다. 원본 이름과 변경 될 이름 두 개 모두 한꺼번에 잠금을 설정합니다.