5. 트랜잭션과 잠금

Real MysQL 8.0 1권을 읽고 정리한 내용입니다.
잘못된 내용이 있을 경우 댓글로 남겨주시면 감사하겠습니다🙏

트랜잭션

논리적인 작업셋이 모두 처리되거나, 모두 처리되지 않는 기능입니다.
이로서, 작업의 완전성을 보장해주게 됩니다.

ACID 라는 트랜잭션이 안전하게 수행된다는 것을 보장하는 성질이 있습니다.

• Atomic : 원자성, 트랜잭션 내의 작업들은 모두 성공하거나, 모두 실패함
• Consistency : 일관성, 데이터의 일관성(무결성)을 보장해야함
• Isolation : 독립성(상호배제성), 다른 트랜잭션의 영향을 받지 않아야함
• Durability : 지속성, 성공하면 영구적으로 반영되고 장애시에도 데이터 정합성에 문제가 없어야 함

MySQL 에서의 트랜잭션

트랜잭션은 여러개의 변경 작업들이 조합되었들 때 뿐만 아니라, 작업의 개수에 상관없이 원자성을 보장해야 합니다.

아래 예제를 통해 알아보겠습니다.

-- 두 테이블의 c1 은 PRIMARY KEY 임

> INSERT INTO tbl_myisam ( c1 ) VALUES (3);
> INSERT INTO tbl_innodb ( c1 ) VALUES (3);

> SET autocommit=ON;

> INSERT INTO tbl_myisam ( c1 ) VALUES (1),(2),(3);
> INSERT INTO tbl_innodb ( c1 ) VALUES (1),(2),(3);

두 쿼리의 실행결과는 아래와 같습니다.

> INSERT INTO tbl_myisam ( c1 ) VALUES (1),(2),(3);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

> INSERT INTO tbl_innodb ( c1 ) VALUES (1),(2),(3);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

> SELECT c1 FROM tbl_myisam;
+----+
| c1 |
+----+
|   1|
|   2|
|   3|
+----+


> SELECT c1 FROM tbl_myisam;
+----+
| c1 |
+----+
|   3|
+----+

위 실행결과에서 tbl_myisam 은 일부만 반영되고, tbl_innodb 는 아예 반영되지 않았습니다.
이런 차이점이 보이는 이유는 MyISAM 엔진은 트랜잭션을 지원하지 않기 때문입니다.
반면에 InnoDB 엔진 테이블은 트랜잭션이 적용되어, 롤백 된것을 볼 수 있습니다.

위와 같은 현상을 부분 업데이터(Partial Update) 라고 표현합니다.
이는 트랜잭션을 지원하지 않기 때문에 발생하는 문제입니다.
그래서 실제로 이런 문제가 발생하면, 데이터 정합성을 맞추기가 매우 어렵다고 합니다.

주의사항

트랜잭션은 최소한의 코드에만 적용하는것이 좋습니다.
여러 작업이 있을 때, 불필요한 작업까지 트랜잭션의 범위에 넣을 필요가 없다는 의미입니다.

1. 처리 시작
    => DB Connection START
    => Transaction START
2. 사용자의 로그인 여부 확인
3. 게시글 내용 오류 여부 확인
4. 첨부파일 확인 및 저장
5. 사용자의 게시글 내용을 DB 에 저장
6. 첨부파일을 DB 에 저장
7. 저장된 내용 또는 기타 정보를 DBMS 에서 조회
8. 게시글 등록 알림 메일 발송
9. 알림 메일 발송 이력을 DB 에 저장
    <= Transaction COMMIT
    <= DB Connection END
10 처리 완료

위와 같은 작업 흐름을 보면, 너무 많은 작업들이 엮여 있습니다.
이런 트랜잭션의 범위가 넓은 구조는 다음과 같은 단점들이 있습니다.

• DB Connection 을 소유하는 시간이 길다는 점
  • 실제 DB 작업은 5번 부터인데, 1번 부터 갖고있음
  • Connection 의 개수는 제한적이기 때문에, 필요할때 받아서 사용하는것이 좋음
• DB 가 아닌 다른 외부 서버와의 통신이 트랜잭션 내부에 있음
  • 메일 발송 때문에, 트랜잭션 내의 전체 작업이 취소될 수 있음
• 5,6 은 하나의 트랜잭션이어야 하지만, 7 은 그럴 필요가 없음
  • 적당히 작은 단위로 트랜잭션을 나누는게 좋음

위와 같은 단점들을 해결하기 위해서는 아래 처럼 개선해야 합니다.

1. 처리 시작
2. 사용자의 로그인 여부 확인
3. 게시글 내용 오류 여부 확인
4. 첨부파일 확인 및 저장
    => DB Connection START
    => Transaction START
5. 사용자의 게시글 내용을 DB 에 저장
6. 첨부파일을 DB 에 저장
    => Transaction COMMIT
7. 저장된 내용 또는 기타 정보를 DBMS 에서 조회
8. 게시글 등록 알림 메일 발송
    => Transaction START
9. 알림 메일 발송 이력을 DB 에 저장
    => Transaction COMMIT
    => DB Connection END
10 처리 완료

MySQL 엔진의 잠금

MySQL 에서 사용되는 잠금은 크게 다음 처럼 나눌 수 있습니다.

• MySQL 엔진 잠금 : 스토리지 엔진에 영향을 미침
• 스토리지 엔진 잠금 : 스토리지 엔진간 상호 영향을 주지는 않음

MySQL 엔진은 다음과 같은 잠금들이 있습니다

• 테이블 락 : 테이블을 잠금
• 메타데이터 락(Metadata Lock) : 테이블의 구조를 잠금
• 네임드 락(Named Lock) : 사용자의 필요에 맞게 사용해야할 경우

글로벌 락(Global Lock)

글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있고, MySQL 에서 가장 범위가 큽니다.
한 세션에서 글로벌 락을 획득하면, 다른 세션의 SELECT 를 제외한 거의 대부분의 DDL, DML 은 대기 상태가 됩니다.

MySQL 이 발전하며 InnoDB 엔진의 사용이 일반화 되었습니다.
InnoDB 는 트랜잭션을 지원하기 때문에 글로벌 락의 필요성은 감소되었고, 더 가벼운 글로벌 락이 필요해졌습니다.
그리고 MySQL 8.0 부터는 Xtrabackup 이나 Enterprise Backup 의 안정적인 실행을 위해 백업 락이 도입되었습니다.

> LOCK INSTANCE FOR BACKUP
-- 백업 작업 실행
> UNLOCK INSTANCE

백업락이 획득되면 테이블의 스키마나 사용자 인증 관련 정보들에 잠금이 걸립니다.
하지만, 데이터의 변경은 허용됩니다.

테이블 락

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

• 명시적
  • 잠금 : LOCK TABLES table_name [READ | WRITE ]
  • 해제 : UNLICK TABLES

테이블 락도 일반적인 애플리케이션에서는 거의 사용할 필요가 없습니다.
테이블 전체를 잠그는 것은 동시성을 매우 떨어뜨리는 행위이기 때문에, 온라인 환경에서 영향이 큽니다.

묵시적인 잠금은 MyISAM, MEMORY 테이블에 데이터 변경 쿼리를 실행하면 발생합니다.

네임드 락

GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있습니다.

DB 1대에 5대의 웹서버가 물려있는 상황에서, 상호 동기화를 처리해야 할 때 아래와 같이 활용할 수 있습니다.

-- 'mylock' 이라는 문자열에 대해 잠금을 획득
-- 이미 잠금을 사용중이면 2초 후 자동 잠금 해제
> SELECT GET_LOCK('mylock', 2);

-- 'mylock' 이라는 문자열에 잠금이 걸려있는지 확인
> SELECT IS_FREE_LOCK('mylock', 2)

-- 'mylock' 이라는 문자열에 대한 잠금을 반환
> SELECT RELEASE_LOCK('mylock', 2)

-- 3개의 함수 모두 정상적으로 잠금을 획득하거나 해제한 경우 1 을
-- 아니라면 NULL 또는 0 을 반환함

메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금입니다.
명시적으로 획득되는 락은 아니고, RENAME TABLE tab TO tab_b 같이 이름을 변경하는 경우 자동으로 획득됩니다.

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL 에서 제공하는 잠금과는 별개로, 엔진 내부에서 레코드 기반의 잠금을 탑재하고 있습니다.
레코드 기반 잠금 덕분에, 테이블 잠금으로 동작하는 MyISAM 보다 훨씬 높은 동시성 처리를 제공합니다.
최근에는 InnoDB 잠금에 대한 모니터링이 강화되며, Performance Schema 를 이용해 InnoDB 내부 잠금(세모포어)에 대한 모니터링 방법도 추가되었습니다.

InnoDB 스토리지 엔진의 잠금

레코드 기반의 잠금을 제공하며, 잠금 정보가 차하는 공간이 작습니다.
그래서 락 에스컬레이션은 없습니다.

다른 DBMS 와는 다르게, 레코드 사이의 간격을 잠그는 GAP LOCK 이라는 것이 존재하는데, 아래 그림처럼 레코드 사이를 잠그는 락을 확인할 수 있습니다.

레코드 락

레코드 자체만 잠그는 것을 의미합니다.
하지만 다른 DBMS 와는 다르게 InnoDB 는 인덱스의 레코드를 잠근다는 것 입니다.

보조 인덱스를 이용한 변경 작업은 넥스트 키락(Next-key lock) 또는 갭락(Gap lock)을 사용합니다.
프라이머리키 혹은 유니크 인덱스에 의한 변경 작업은 레코드 락만 걸립니다.

갭락(Gap lock)

레코드와 레코드의 사이를 잠궈서, 사이에 새로운 레코드가 생성(INSERT) 되는것을 방지합니다.

넥스트 키 락(Next-key lock)

레코드 락과 갭락을 합쳐놓은 형태로, REPEATABLE READ 격리 수준을 사용해야 합니다.
바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때, 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는것이 주 목적이라고 합니다.

자동 증가 락(AUTO_INCREMENT lock)

MySQL 에서는 자동으로 증가하는 숫자 값을 채번하기 위해 AUTO_INCREMENT 라는 컬럼 속성을 제공합니다.
AUTO_INCREMENT 이 적용된 컬럼에 동시에 여러 레코드가 INSERT 되는 경우 중복된 값이 저장되지 않게 하기 위해서 사용되는 락 입니다.

자동 증가 락은 INSERT 와 REPLACE 같이 새로운 레코드를 생성하는 작업에만 걸리고,
UPDATE 혹은 DELETE 에는 걸리지 않습니다.

인덱스와 잠금

InnoDB 의 잠금과 인덱스는 중요한 연관관계가 있습니다.
레코드 락에서는 데이터가 있는 레코드를 잠그는 것이 아닌 인덱스의 레코드를 잠급니다.