MariaDB 강좌 / 두 연결로 격리 수준·잠금·교착 상태 확인하기

격리 수준은 동시에 실행되는 트랜잭션이 어떤 변경을 읽는지 정하고, 잠금은 충돌하는 쓰기 작업의 진행을 조정합니다. 두 개의 별도 연결에서 순서를 통제하면 반복 읽기·읽기 확정, 잠금 대기와 교착 상태의 차이를 실제로 확인할 수 있습니다.

서로 다른 연결 A와 B 준비하기

같은 학습용 서버·shopcourse에 접속한 명령줄 창 두 개를 엽니다. 각 창에서 CONNECTION_ID()가 다른지 확인하고 아래 설정 보관을 각각 실행합니다. 한 창에 A·B 코드를 모두 붙여 넣으면 동시 연결 실습이 되지 않습니다. 운영 서버에서는 이 잠금 실험을 하지 않습니다. 확정·취소의 기본 단위는 트랜잭션 시작하기: InnoDB·COMMIT·ROLLBACK에서 먼저 확인하고, 실습으로 바꾼 SESSION 값은 서버 설정·SQL 모드·시간대·로그 확인하기의 적용 범위와 구분해 복원합니다.

SELECT CONNECTION_ID(),DATABASE(),@@SESSION.tx_isolation;
SET @saved_isolation=@@SESSION.tx_isolation;
SET @saved_wait=@@SESSION.innodb_lock_wait_timeout;

A에서만 아래 테이블을 만듭니다. 이미 있는 이름이면 정의와 데이터를 확인하고 보관할 자료가 없는 이 실습 객체인지 먼저 판단합니다.

USE shopcourse;
CREATE TABLE accounts_36_lab (
 account_id INT PRIMARY KEY,balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts_36_lab VALUES(1,1000),(2,2000);

REPEATABLE READ를 A → B → A 순서로 비교하기

앞 절의 초기 상태인 1번 계정 잔액 1000에서 시작합니다. 아래 네 블록의 연결과 순서를 지키고 A가 읽은 뒤에만 B를 실행하세요.

A-1: 스냅샷 형성

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

B-1: 변경 확정

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

A-2: 같은 트랜잭션에서 다시 읽기

SELECT balance FROM accounts_36_lab WHERE account_id=1;

A-1과 A-2는 모두 1000입니다. 일반 SELECT의 일관된 읽기 스냅샷을 비교한 것이며 FOR UPDATE 같은 현재 읽기까지 같은 과거 값을 보장한다는 뜻은 아닙니다.

A-3: 종료 후 새 조회

ROLLBACK;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

마지막 조회는 1100입니다. 격리 수준의 적용 범위는 SET TRANSACTION 문서에서 확인할 수 있습니다.

READ COMMITTED도 초기화부터 순서대로 비교하기

B-0: 앞 실습 값 초기화

ROLLBACK;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1000 WHERE account_id=1;
COMMIT;

A-1: READ COMMITTED의 첫 읽기

ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

B-1: 1100으로 확정

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

A-2: 두 번째 읽기와 종료

SELECT balance FROM accounts_36_lab WHERE account_id=1;
ROLLBACK;

A의 첫 결과는 1000, 두 번째 결과는 1100입니다. 각 일반 읽기가 그 문장 시작 시점에 확정된 버전을 읽기 때문입니다. 실제 격리 수준 선택은 쓰기 충돌·업무 일관성 요구까지 함께 판단합니다.

행 잠금과 대기 시간 초과를 분리해서 확인하기

A-1: 행 1 잠금 유지

START TRANSACTION;
SELECT account_id,balance FROM accounts_36_lab
WHERE account_id=1 FOR UPDATE;

B-1: 같은 행 UPDATE 시도

SET SESSION innodb_lock_wait_timeout=3;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

A를 종료하지 않으면 B에서 약 3초 후 잠금 대기 시간 초과가 예상됩니다. 이 오류가 트랜잭션 전체를 자동 취소했다고 가정하지 말고 B와 A를 각각 끝냅니다.

B-2: 실패한 작업 정리

ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@saved_wait;

A-2: 잠금 해제

ROLLBACK;

FOR UPDATE의 트랜잭션 적용 조건

교착 상태를 네 번의 교차 실행으로 만들기

두 연결에 열린 트랜잭션이 없는지 확인합니다. 아래는 한 파일로 연속 실행하는 SQL이 아닙니다. A-1 → B-1 → A-2 → B-2 순서로 창을 바꾸며 실행합니다.

A-1: 첫 행만 잠그기

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

B-1: 다른 행만 잠그기

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

A-2: B가 보유한 행 요청

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

A는 대기하므로 A의 결과를 기다리지 말고 B 창으로 옮겨 다음 문장을 실행합니다.

B-2: A가 보유한 행 요청

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

InnoDB가 교착을 탐지하면 한 트랜잭션에서 Deadlock found 오류가 발생할 수 있습니다. 희생 연결은 고정하지 않습니다. 두 창에서 각각 ROLLBACK;을 실행해 남은 변경을 취소합니다. 프로그램은 전체 트랜잭션의 재시도·중복 방지·횟수 제한을 설계해야 합니다.

설정 복원과 동일한 잠금 순서로 개선하기

교착을 줄이려면 여러 행을 항상 같은 키 순서로 접근하고 트랜잭션을 짧게 유지합니다. 인덱스가 없거나 넓은 조건이면 잠금 범위가 커질 수 있습니다. 데드락은 단순 연결 오류와 구분해 실제 로그·쿼리·트랜잭션 순서를 조사합니다. 이 실습은 스케줄을 통제한 작은 기본키 예제입니다.

ROLLBACK;
SET SESSION tx_isolation=@saved_isolation;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
SELECT @@SESSION.tx_isolation,@@SESSION.innodb_lock_wait_timeout;

설정 보관과 복원은 각 창에서 각각 수행합니다. 연결을 끊었다면 사용자 변수는 사라지므로 새 연결의 현재 설정을 다시 조회합니다. 두 창의 트랜잭션을 모두 끝내고 보관할 데이터가 없는 이 실습용 테이블만 정리합니다.

DROP TABLE shopcourse.accounts_36_lab;