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 ... 