[컴][DB] MySQL 에서 transaction 과 isolation level

mysql / mysql race condition / read shadow read /lock

MySQL 에서 transaction 과 isolation level

아래는 정리되지 않은 글이다. 정리는 추후에...

mysql 의 default isolation level

MySql 의 InnoDB 는 Repeatable-read 가 기본 isolation level 이다.

기본 sql standard

sql에서 Repeatable-read 는 아래 3가지를 만족한다.

  • dirty reads (non committed data)
  • non repeatable reads (executing the same query twice should return the same values)
  • allows phantom reads (new rows are visible).

MY SQL 의 default isolation 변경법

실행시에 --transaction-isolation=level option 을 사용하면 된다.(참고)

현재 isolation level 확인방법

-- to check the current isolation level
SHOW variables like 'tx_isolation'

isolation levels

  • SQL Server 트랜잭션 잠금 및 행 버전 관리 지침 | Microsoft Docs
  • SERIALIZABLE : REPEATABLE READ 보다 더 엄격 하다. XA transactions 이나 concurrency 와 deadlocks의 troubleshooting 을 위해서
  • REPEATABLE READ : 가장 높은 수준의 consistency(일관성)
  • READ COMMITTED : locking 에 대한 overhead 가 더 중요해서 precise consistency 와 반복적인 결과들이 덜 중요할때 사용할 수 있다.
  • READ UNCOMMITTED : READ COMMITTED 와 같다. 단 dirty read(select 가 nonlocking 된 상태로 수행된다.) 가 허용된다.(consistency 를 보장하지 않는다.)
격리 수준 커밋되지 않은 읽기 반복되지 않는 읽기 가상
READ UNCOMMITTED
READ COMMITTED 아니요
REPEATABLE READ 아니요 아니요
스냅숏 아니요 아니요 아니요
직렬화 가능 아니요 아니요 아니요

가상읽기(Phantom read)가 가능하기 때문에 만약 아래와 같은 2개의 transaction 이 동시에 실행되는 경우 transaction 1 의 첫 SELECT 와 두번째 SELECT 가 다른 결과를 같게 된다. (두번째 select 의 결과 row 가 더 많을 것이다.)

-- Transaction 1
SELECT * FROM table1
SELECT * FROM table1

-- Transaction 2
INSERT INTO table1 ...

아래와 같은 transaction 이 동시에 일어나는 경우, INSERT 가 2번 된다. 이를 막기 위해 insert 시에 index 를 지정해서 넣어주면, 같은 index 에 insert 를 피할 수 있다. 하지만 문제는 끝부분에서 중복이슈는 없지만, 중복 이슈와 상관없는 경우에도 insert 가 실패하기 때문에 문제가 될 수 있다.

그리고 상품이 2개이상이 동시에 열리는 경우 2개의 상품의 마지막투자가 같은 순간에 이뤄지면, 둘다 같은 index 를 next 로 잡아서 insert 를 할 것이고, 그것이 충돌을 일으킬 수 있다.

해결책

결국 queue 를 두는 방식으로, 즉 worker 로 넘기는 방식을 쓰기로 했다. 아무래도 그래야만 consistency 가 유지될 수 있다고 본다.

-- Transaction 3
SELECT * FROM table1 ...
-- insert depending on the result of the select
INSERT INTO table1 ... 

See Also

  1. 쿠…sal: [컴][db] RDBMS 내부를 설명한 자료

References

  1. Understanding MySQL Isolation Levels: Repeatable-read | Official Pythian® Blog
  2. MySQL :: MySQL 8.0 Reference Manual :: 15.5.2.1 Transaction Isolation Levels

댓글 없음:

댓글 쓰기