[MySQL] MySQL의 트랜잭션과 잠금

 

MySQL의 트랜잭션과 잠금에 대해서 알아보자

 

 

트랜잭션이란?

트랜잭션의 완정송을 보장해주는 것이다. 즉 성공했을 때 COMMIT를 하고 실패했을떄는 ROLLBACK을 하는것이다.  기본적인 트랜잭션의 개념은 "트랜잭션이란" 포스팅을 참고해주세요

---

 

MySQL에서의 트랜잭션

기본적으로 위에서 설명한 대로, 트랜잭션은 논리적으로 성공할 경우(COMMIT) 또는 실패할 경우 이전 상태로 롤백(ROLLBACK)되는 것을 보장합니다.

 

MySQL에서는 InnoDB와 MyISAM 엔진을 사용합니다. 두 엔진 간의 트랜잭션에 대한 차이를 먼저 살펴보겠습니다

 

create table tab_myisam (fdpk int NOT NULL, primary key(fdpk) ) engine=MyISAM;
insert into tab_myisam (fdpk) values (3);

create table tab_innodb (fdpk int NOT NULL, primary key(fdpk) ) engine=InnoDB;
insert into tab_innodb (fdpk) values (3);

set autocommit = on;

insert into tab_myisam (fdpk) values (1),(2),(3);
insert into tab_innodb (fdpk) values (1),(2),(3);

select * from tab_myisam;
select * from tab_innodb;

 

• MyISAM 엔진으로 실행하는 tab_myisam 테이블 과 InnoDB엔진으로 실행하는 tab_nnodb 테이블을 생성후 각각 값(3)을 넣어준다.
• set autocommit = on 을 실행해 자동으로 트랜잭션이 시작하게 한다.
• 각 테이블에 값(1,2,3)을 넣고 테이블을 조회한다.

MyISAM 엔진을 사용한 'tab_myisam' 테이블은 3개의 값이 저장되어 있습니다. 이와 달리, InnoDB 엔진을 사용한 'tab_innodb' 테이블은 마지막 값 '3'만 저장되어 있습니다. 이 차이는 MyISAM 엔진이 트랜잭션을 지원하지 않아서 INSERT 문이 3번 실행되면 각각의 값을 저장하지만, InnoDB 엔진은 트랜잭션을 지원하므로 이전의 INSERT 문들을 롤백시켜 마지막 INSERT 문의 결과만을 저장한 것입니다.

MyISAM 엔진을 사용하여 트랜잭션을 구현하려면 IF-ELSE 문을 통해 코드를 작성해야 하기 때문에 코드가 복잡하고 가독성이 떨어집니다. 반면에 InnoDB 엔진은 try-catch 문을 활용하여 간단하게 트랜잭션을 구현할 수 있습니다.

 

 

데이터베이스 커넥션의 유효 범위와 활성화된 트랜잭션의 범위를 최소화해야 합니다.

코드에서 네트워크 작업이 있는 경우에는 라인 수가 적더라도 반드시 트랜잭션에서 배제되어야 합니다.

---

 

 

MySQL 엔진의 잠금

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

 

---

글로벌 락

굴로벌 락(Global Lock)은 데이터베이스에서 전체 시스템이나 특정 리소스에 대한 락을 의미한다. FLUSH TABLES WITH READ LOCK 명령으로 회득할수 있으며, MySQL에서 제공되는 잠금중에 가장 큰 범위를 갇는다. 세션에서 SELECT를 제외한 대부분의 DDL, DML 문장은 실행하는경우 락이 해제될때 까지 해당 문장을 대기 상태로 둔다.

테이블 락

개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 회득할수 있습니다. 명시적으로 "LOCK TABLES table_name [READ | WRITE]" 명령어를 통해 회득 하루 있고 회득한 락은 UNLOCK TABLES 명령으로 잠금을 반납 할수도 있습니다.

네임드 락

네임드 락(Name Lock)은 GET_LOCK() 함수를 이용해 임이의 문자열에 대해 잠금을 설정할수 있다.

이러한 락은 특정 테이블, 레코드, AUTO_INCREMENT와 같은 데이터베이스 객체에 직접 연결되지 않습니다. 대신, 사용자가 지정한 임의의 문자열(락의 이름)을 기반으로 잠금을 설정합니다.

메타데이터 락

데이터베이스 객체(대표적으로 테이블이나 뷰등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 메타데이터 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니고 "RENAME TABLE tab_a TO tab+b"같이 테이블의 이름을 변경하는 경우 자동으로 회득하는 잠금이다.

---

 

InnoDB 스토리지 엔진 잠금

 

InnoDB 스토리지 엔진은 MySQL에서 제공하는잠금 과는 별개로 스스로 엔진 내부에 레코드 기반의 잠금 방식을 탑재하고있다.

MySQL 서버의 infomation_schema 데이터 베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LICKWAITS라는 테이블을 조인해 현재 어떤 테이블이 트랜잭션에 잠겨있는지 확인할수 있다.

 

레코드 락

레코드 자체만 잠그는 것을 레코드 락이라고한다. InnoDB 엔진은 레코드 자체를 잠그는 것이아니라 인덱스의 레코드 잠금

 

갭락

레코드 자체를 락을 거는것이 아닌 레코드와 인접한 레코드 사이의 간격만은 잠그는것이다.(INSERT 구문 제어)

 

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금

바이러니 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.

 

자동 증가 락

자동 증가하는 숫자 값을 추출하기 위해서 락을 제공한다. 짧은 시간동안 걸렸다가 해제되는 경우가많다. 만약 21에서 오류가나더라고 21은 무시되고 다음값인 22가 나오게된다.

 

인덱스 와 잠금

board안 'contet'  11개의 데이터가 있다고 해보자 

 

select count(*) from board where content ='contet';

11개 데이터가 존재

select count(*) from board where content ='contet' and writer='writer';

작성자가 'wirter' 인것은 한명만 나온다.

update board set date_time=NOW() where content='contet' and writer='writer';

이 오늘 날짜로 수정하는 업데이트 문을 작성하면 1개의 레코드만 업데이트 될것이다. 하지만 이업데이트가 되기 위해서는 1개의 레코드만 락을 거는것이아닌 11개의 레코드가 잠긴다. 그렇다는 건 다른 사용자는 업데이트를 락이걸려 업데이트를 사용할수 없다 그래서 이런 MySQL에 레코드와 락을 이해하고 설계해야한다.

 

---

 

MySQL의 격리 수준

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

 

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

일반적으로 MySQL은 온라인서비스 상에서 READ_COMMITTED 수준을 많이 사용한다.

 

• DIRTY READ(더티 리드) : 한트랜잭션이 커밋되지 않았는데 다른 트랜잭션에서 커밋되지않은 데이터를 가져가 읽는 상황
• NON-REPEATABLE READ: 한 트랜잭션이 동일한 쿼리를 두 번 실행할 때 결과가 일관성 없게 나타날수 있는 상황
• PHANTOM READ : 한 트랜잭션이 동일한 쿼리를 두번 실행할 때 ,결과 행의 수가 다를수 있는상황

 

• READ_UNCOMMITTED : 하나의 트랜잭션이 아직 커밋되지 않은 다른 트랜잭션의 변경 상항을 읽을수 있다.
• READ_COMMITTED : 커밋된 데이터만 읽을수 있다.
• REPEATBLE_READ : 한 트랜잭션 내에서 같은 쿼리를 실행하더라고 결과가 항상 같아야한다.
• SERIALIZABLE : 가장 높은 격리 수준으로, 모든 트랜잭션은 순차적으로 샐행되는 것처럼 처리된다. 

---

참고

RealMySQL8.0 1권