5장. 트랜잭션과 잠금

[KKambi]

12/04 (5.1 ~ 5.3)

바이너리 로그 포멧의 종류

바이너리 로그 = 복제를 위한 것 (소스 서버 -> 레플리카 서버 / 카프카 커넥트 등)

--binlog-format=type

• 글로벌, 세션 스코프 모두 지원
• ROW : row 기반의 로깅 (default)
• STATEMENT : statement 기반 로깅
• MIXED

바이너리 로그 포멧을 설정한다고 해서, 바이너리 로깅이 활성화되는 것은 아니다.

• 8.0부터는 기본으로 활성화되어 있긴 함
• 일반적으로 런타임에 변경 가능

  mysql> SET GLOBAL binlog_format = 'STATEMENT';
  mysql> SET GLOBAL binlog_format = 'ROW';
  mysql> SET GLOBAL binlog_format = 'MIXED';

그럼 왜 여러 타입이 필요할까?

• 데이터베이스에 많고 작은 변경을 만드는 세션은 row-based logging을 원할 것
• WHERE절과 일치하는 수많은 row를 업데이트하는 세션은 statement-based logging을 원할 것
• 실행 시간이 오래 소요되는 STATEMENT지만, 변경되는 row는 적은 경우 row-based logging이 효과적일 것

innodb_autoin_lock_mode

InnoDB는 AUTO_INCREMENT 값의 채번을 위해 테이블당 하나만 사용되는 AUTO_INCREMENT 락을 제공한다.

• 5.7 버전에선 Consecutive mode(value 1) / 8.0 버전에선 Interleaved mode(value 2)
• Consecutive: 단일 INSERT는 연속된 자동 증가 값을 보장하고, 대량 INSERT는 보장 X (STATEMENT Bin Log와 호환)
• Interleaved: 연속 값 보장 X, 유니크한 값만 생성 보장 (ROW Bin Log와 호환)

그럼 Upsert 쿼리 문장을 사용하면 어떻게 될까? (팀원 경험)

• INSERT INTO table (columns...) VALUES (values...) ON DUPLICATE KEY UPDATE column = value
• PK가 중복되는 경우 업데이트, PK가 존재하지 않는 경우 INSERT
• 대량 INSERT 때와 동일하게, 미리 자동 증가 값을 할당받는다.
• 따라서 UPDATE가 이뤄지는 경우, 자동 증가 값은 누락되어 연속되지 않게 된다.

Replace 쿼리 문장

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

왜 2개의 행이 영향을 받을까?

1. Try to insert the new row into the table
2. While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
   1. Delete from the table the conflicting row that has the duplicate key value
   2. Try again to insert the new row into the table

궁금증1

배치 프로그램의 많은 레코드 변경 쿼리는 자주 데드락의 원인이 되곤 한다. -> 변경해야할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸기 때문

이런 경우 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결 -> Q. 원리가 뭐지? -> 동일 데이터 변경, 참조 = 같은 인덱스에 대해 락을 걸게 된다 -> 데드락 = 서로 동일한 데이터를 필요로 하게 되며 발생 -> 네임드 락으로 해소!

궁금증2

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식 -> Q. 아니 그래서 왜 이렇게 설계한거지? -> 인덱스와 레코드 사이의 정합성 때문일까?

[jjy0918]

READ

Dirty Read

• Commit 되지 않은 데이터를 읽는 것.
• Isolation Level이 READ UNCOMMITTED일 때 발생한다.
  • READ COMMITTED, REPEATABLE READ, SERIALIZABLE 에서는 커밋된 로그 or 언두 로그를 읽어 발생하지 않는다.

Non-Repeatable Read

• 트랜잭션 내에서 조회 시 결과가 달라지는 것.
• 일반적으로 다른 트랜잭션의 UPDATE에 의해 발생한다.

Phantom Read

• 트랜잭션 조회 시 다른 트랜잭션에 의해 데이터가 삽입되거나 제거되어 결과가 달라지는 것.
• 일반적으로 다른 트랜잭션의 INSERT, DELETE에 의해서 발생한다.

Transaction

• READ COMMITTED vs REPEATABLE READ
• READ COMMITTED는 Commit된 데이터를 기준으로 읽는다.
  • 데이터가 다른 트랜잭션에 의해 수정중이라면, 커밋된 데이터인 언두 로그를 읽는다.
  • 데이터가 다른 트랜잭션에 의해 수정 후 커밋되었다면, 해당 데이터를 읽는다.
  • 읽는 시점에 따라 조회 결과가 달라질 수 있다.
• REPEATABLE READ는 해당 트랜잭션 실행 시점의 데이터를 바탕으로 읽는다.
  • 이후 다른 트랜잭션에 의해 커밋이 되어도 해당 데이터가 아닌, 언두 로그 데이터를 읽는다.
  • 주의점은 락을 거는 경우(FOR UPDATE) 언두 로그가 아닌 데이터를 조회하기 때문에 다른 데이터를 조회할 수 있다.

[KKambi]

12/11 (5.4 ~ 6장 끝)

Q. Repeatable Read - 이전 트랜잭션이 내가 수행하는 도중에 커밋해버린다면?

이전 트랜잭션의 ID 값이 더 작으니까, Repeatable Read가 보장 안되지 않을까?

• 테이블의 레코드가 아니라, 언두 로그를 읽는 것이라서 괜찮다 + 이전의 값이 보존된다.
• 그렇다면 언두 영역에 백업된 레코드 중 몇 번째 버전까지 찾아 들어갈까?

Q. Read Committed vs Repetable Read - 언두 영역 버전 탐색 차이

Read Committed

• 트랜잭션 내에서 매번 read operation을 수행할 때마다 스냅샷 갱신

Repetable Read

• 트랜잭션 시작 후, 첫번째 read operation을 수행하는 시점에 스냅샷 생성
• 동일 트랜잭션에서 해당 스냅샷을 읽음
• Non-locking consistent read 보장

Q. InnoDB Repeatable Read - 팬텀 리드가 발생하지 않는다면서 자세히 안 알려주네

A. 칼럼 c1의 값이 각각 13 / 17 인 레코드 2개 SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE 수행 시 아래 범위에 갭 락

• 10 <= c1 <= 12
• 14 <= c1 <= 16
• 18 <= c1 <= 20

따라서 해당 트랜잭션 내에서 이미 조회한 범위에 대해서는 INSERT를 할 수 없으므로 Phantom Read도 발생하지 않는다.