트랜잭션 격리 수준은 데이터베이스 동시 제어의 핵심이며, 트랜잭션 간의 격리 방식과 한 트랜잭션이 다른 트랜잭션의 변경 사항을 볼 수 있는 정도를 정의합니다. 본 글은 sql_store 데이터베이스를 기반으로, 코드 예제를 통해 MySQL이 지원하는 네 가지 표준 트랜잭션 격리 수준과 그 영향을 상세히 설명합니다. 시나리오를 통해 격리 수준의 역할을 설명하고, 데드락 문제 및 회피 방법을 분석하여 독자들이 트랜잭션 동시 관리를 깊이 이해하도록 돕습니다.
학습 내용
1. 트랜잭션 격리 수준 개요
MySQL은 네 가지 표준 트랜잭션 격리 수준을 지원하며, 낮은 순서부터 높은 순서로 다음과 같습니다.
격리 수준이 높을수록 동시성 성능은 낮아지지만, 데이터 일관성은 높아집니다. ATM 이체 시나리오에서 적절한 격리 수준은 계좌 잔액 업데이트 시 다른 트랜잭션으로부터 방해받지 않도록 보장합니다.
2. 격리 수준 보기 및 설정
SHOW VARIABLES LIKE 'transaction_isolation' 를 통해 현재 격리 수준을 확인할 수 있으며, 기본적으로 REPEATABLE-READ__로 표시됩니다. 다음 명령문을 사용하여 격리 수준을 설정할 수 있습니다:
3. 동시성 문제와 격리 수준
다른 격리 수준은 더러운 읽기, 불반복 읽기 및 환영 읽기에 대한 제어 능력이 다릅니다:
4. 데드락
데드락은 동시 트랜잭션에서 두 개 이상의 트랜잭션이 서로 다른 리소스를 해제하기를 기다리면서 발생하며, 이로 인해 트랜잭션이 계속 실행될 수 없습니다. MySQL은 데드락을 감지하고 하나의 트랜잭션을 종료하며 오류 메시지를 표시합니다. 데드락을 줄이기 위해 일관된 리소스 접근 순서를 유지하거나 트랜잭션 길이를 단축해야 합니다.
예제 코드 및 설명
1. 격리 수준 확인
SHOW VARIABLES LIKE 'transaction_isolation';
현재 격리 수준을 표시하며, 기본값은 REPEATABLE-READ이며, MySQL InnoDB의 표준 구성입니다.
2. 격리 수준 설정
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
현재 세션의 격리 수준을 SERIALIZABLE로 설정하여 이후 트랜잭션을 순차적으로 실행하도록 하여 모든 병행 문제를 방지합니다.
3. 읽기 미완료 (READ UNCOMMITTED)
콘솔 1 :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points FROM 고객 WHERE 고객아이디 = 1;
콘솔 2：
START TRANSACTION;
UPDATE 고객 SET 포인트 = 20 WHERE 고객아이디 1;
-- 미제출
컨트롤 타임 1에서 READ UNCOMMITTED 를 설정한 후, 고객 1의 포인트(원래는 2303)를 조회합니다. 컨트롤 타임 2에서 포인트를 20으로 업데이트하지만 제출하지 않으면, 컨트롤 타임 1에서 다시 조회할 때 20을 볼 수 있어 터빈 읽기가 나타납니다. 만약 컨트롤 타임 2에서 롤백하면, 컨트롤 타임 1의 조회 결과는 무효화되어 비즈니스 결정을 오인시킬 수 있습니다.
4. 커밋된 읽기（READ COMMITTED）
컨트롤 타임 1：
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT points FROM customers WHERE customer_id = 1;
콘솔 2：
START TRANSACTION;
UPDATE 고객 SET 포인트 = 20 WHERE 고객아이디
= 1;
COMMIT;
컨트롤 타워 1에서 READ COMMITTED 를 설정한 후, 포인트를 조회합니다. 컨트롤 타워 2에서 변경 사항이 커밋되지 않았다면, 컨트롤 타워 1은 여전히 원래 값 2303을 볼 수 있어 오염된 읽기를 방지합니다. 컨트롤 타워 2가 커밋된 후, 컨트롤 타워 1은 20을 조회합니다. 하지만 동일한 트랜잭션 내에서 여러 번 조회할 경우, 다른 트랜잭션이 커밋되어 결과가 일관성 없게 되어 반복 불가 읽기가 발생할 수 있습니다.
콘솔 1（반복 읽기 예제가 아님）：
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
-- 반환 20
SELECT points FROM customers WHERE customer_id = 1;
-- 반환 30
COMMIT;
콘솔 2：
START TRANSACTION;
UPDATE customers SET points = 30 WHERE customer_id
= 1;
COMMIT;
콘솔 1 최초 쿼리에서 20을 얻음, 콘솔 2 커밋 후 업데이트되어 30으로 변경됨, 콘솔 1 두 번째 쿼리에서 30을 얻음, 이는 비반복읽기로 나타나 데이터 일관성이 영향을 받을 수 있음.
반복 가능한 읽기(REPEATABLE READ)
콘솔 1：
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT points FROM 고객 WHERE 고객아이디 = 1;
-- 30
SELECT 포인트 FROM 고객 WHERE 고객아이디 = 1;
-- 여전히 30
COMMIT;
콘솔 2：
START TRANSACTION;
UPDATE customers SET points = 40 WHERE customer_id
= 1;
COMMIT;
에서 반복 가능한 읽기 아래, 콘솔 1 트랜잭션 시작 시 일관성 있는 뷰를 생성하며, 트랜잭션 시작 시의 스냅샷(누적 점수 30)을 기반으로 합니다. 콘솔 2는 변경 사항을 40으로 제출하지만, 콘솔 1의 두 번째 쿼리는 여전히 30을 반환하여 반복 불가능한 읽기를 방지합니다. MVCC는 트랜잭션 내 데이터 일관성을 보장합니다.
환상 읽기 예제：
콘솔 1：
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'CO';
-- 반환 1 개의 레코드
COMMIT;
콘솔 2：
START TRANSACTION;
UPDATE customers SET state = 'CO' WHERE customer_id
= 1;
COMMIT;
콘솔 1 쿼리 state = 'CO' 의 고객은 초기에 1개의 기록만 있었다. 콘솔 2 고객 1의 상태를 'CO'로 변경하고 제출하라. 콘솔 1이 트랜잭션을 제출한 후 다시 쿼리하면 여전히 1개의 기록만 얻으며, 데이터베이스의 최신 상태를 반영하지 않아 허상읽기가 발생한다. InnoDB의 간격 잠금은 허상읽기를 일부 완화할 수 있다.
6. 순차화(SERIALIZABLE)
콘솔 1：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'CO';
COMMIT;
콘솔 2：
START TRANSACTION;
UPDATE customers SET state = 'CO' WHERE customer_id = 2;
COMMIT;
SERIALIZABLE 하에서, 콘솔 1은 state = 'CO' 인 고객(초기 2개)을 조회합니다. 콘솔 2는 고객 2의 상태를 'CO'로 변경하려 하지만 커밋되지 않습니다. 콘솔 1의 트랜잭션이 콘솔 2의 커밋을 기다리며 차단되어 환영을 방지합니다. 커밋 후, 콘솔 1은 최신 데이터를 정확하게 반영합니다.
7. 데드락
콘솔 1：
START TRANSACTION;
UPDATE customers SET state = 'VA' WHERE customer_id
= 1;
UPDATE orders SET status = 1 WHERE order_id
= 1;
COMMIT;
콘솔 2：
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id
= 1;
UPDATE customers SET state = 'VA' WHERE customer_id
= 1;
COMMIT;
콘솔 1 잠금 customers 의 줄에서 orders를 잠금을 시도하고, 콘솔 2는 orders를 잠금을 시도하며 customers를 잠금을 시도하여 순환 종속성을 형성하고, 이로 인해 데드락이 발생합니다. MySQL은 데드락을 감지한 후 하나의 트랜잭션을 종료하고 오류 메시지를 표시합니다. 데드락을 피하기 위해서는 자원 접근 순서를 통일하거나 트랜잭션 길이를 단축해야 합니다.
는
트랜잭션 격리 수준이 동시 트랜잭션의 데이터 가시성과 일관성을 결정한다고 요약합니다. READ UNCOMMITTED 는 성능이 높지만 위험이 크고, SERIALIZABLE 는 안전하지만 성능이 낮으며, REPEATABLE READ 는 MySQL InnoDB의 기본적인 절충안입니다. ATM 이체 시나리오는 격리 수준이 데이터 일관성에 미치는 중요성을 설명합니다. 본문은 기반으로 sql_store 데이터베이스를 통해 코드 예제를 통해 네 가지 격리 수준과 데드락 문제를 설명합니다. 이후 내용은 인덱스 설계 또는 쿼리 최적화를 탐구하며, 주의 깊게 살펴주시기 바랍니다.
이 콘텐츠는 인셔셔RSS(RSS 리더)가 자동으로 집계한 것으로 읽기 참고용입니다. 원문 출처 — 저작권은 원저작자에게 있습니다.