데이터베이스 트랜잭션(Transaction)

트랜잭션(Transaction)이란 데이터베이스에서 수행되는 작업들의 논리적인 단위를 말합니다. 예를 들어 회원 가입이라는 작업을 처리할 때 아래와 같은 작업이 순차적으로 수행된다고 가정해봅시다.

  1. 회원 테이블에 INSERT
  2. 가입 축하 포인트 지급을 하기위해 포인트 테이블 INSERT
  3. 회원 결제 정보 테이블 INSERT

3개의 작업에서 1번과 2번이 성공하고 3번을 처리하다 오류가 발생한다면 회원의 결제 정보가 없는 상태로 등록되어 버립니다. 이러한 문제점을 해결하기 위해 3개의 작업을 하나의 작업 단위(트랜잭션)로 묶어서 처리하고 작업 이전 상태의 데이터들을 복원용 저장소에 저장합니다. 만약 3번에서 오류가 발생하면 복원용 저장소를 이용해 작업 전 상태로 되돌릴 수 있습니다. 이런한 특성을 트랜잭션의 원자성(Atomicity)이라 부르며 이 외에도 일관성(Consistency), 격리성(Isolation), 지속성(Durability) 특성이 있습니다. 그리고 트랜잭션의 4가지 특성을 ACID라고 부릅니다.

복원용 저장소은 롤백 시 데이터를 이전 상태로 복원하기 위해 임시로 저장하는 곳으로 Oracle과 MySQL의 경우 Undo Segment(=Rollback Segment)에 저장하고 SQL Server는 Transaction Log에 저장합니다.

트랜잭션 특성

특성 설명
원자성(Atomicity) - 트랜잭션의 작업은 데이터베이스에 모두 반영되거나 모두 반영되지 않아야 합니다.
일관성(Consistency) - 트랜잭션 수행 전, 후의 데이터베이스 무결성 상태는 일관성을 유지해야 합니다.
격리성(Isolation) - 트랜잭션들이 병행 처리될 때 각 트랜잭션은 다른 트랜잭션의 수행에 영향을 받지 않고 독립적으로 수행되어야 합니다.
지속성(Durability) - 트랜잭션이 성공적으로 완료되고 커밋되면 어떠한 상황에서도 영구 보존되어야 합니다.

트랜잭션 상태

image-20211118001920847

상태 설명
Active - 트랜잭션이 실행되고 있는 상태
Partially Committed - 트랜잭션의 실행은 완료되었지만 커밋은 되지 않은 상태
Commited - 트랜잭션의 모든 작업을 성공적으로 실행되어 커밋된 상태
Failed - 트랜잭션이 실패하여 중단된 상태
Aborted - 트랜잭션이 Failed 상태가 되어 작업 전으로 롤백한 상태

동시성 제어(Concurrency Control)

여러 트랜잭션이 동시에 실행되는 병행 처리 환경에서는 트랜잭션 간의 간섭으로 문제가 발생할 수 있습니다. 트랜잭션 간의 간섭을 차단 위해서는 트랜잭션들을 순차적으로(Serializable) 실행 해야되기 때문에 병행 처리를 할 수 없어 성능에 좋지 않습니다. 병행 처리(동시성)를 최대한 보장하면서 데이터의 무결성을 유지하기 위해서는 상황에 맞는 Locking과 트랜잭션 격리 수준을 적용해야 합니다. 동시성 제어는 크게 두 가지로 나뉩니다.

비관적 동시성 제어: 병행 트랜잭션이 발생하고 높은 격리성이 요구 되는 제어라고 가정하고 Locking을 걸고 작업을 수행합니다.

낙관적 동시성 제어: 단일 트랜잭션 또는 낮은 격리성으로도 충분히 제어가 가능하다고 가정하고 Locking을 걸지 않고 작업을 수행합니다.

만약 동시성 제어가 제대로 안될 경우 아래와 같은 문제들이 발생할 수 있습니다.

  • Lost Update: 하나의 트랜잭션에서 갱신한 값이 다른 트랜잭션의 갱신 값에 의해 덮어 쓰기되어 잃어 버리는(Lost) 현상을 말합니다. 한가지 예로 Consistent Read된 데이터를 Update의 값으로 사용하여 Lost Update가 발생한 케이스입니다. T1에서 AMOUNT 6500에서 +3500을 더해 10000이 되었고, T2에서 -1500을하여 8500을 기대했지만 T2에서 스냅샷된 AMOUNT는 6500이기 때문에 6500-1500이되어 5000의 결과가 나옵니다. 따라서 +3500의 업데이트는 분실(Lost)된 상태가 됩니다. 만약 이러한 애플리케이션에서 동일 계좌를 여러명이 동시에 입출금하게 되면 정합성 문제가 발생하게 되는거죠.

    image-20211128220847208

  • Dirty Read: 특정 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 데이터를 읽는 것을 말합니다. 이는 T1 트랜잭션이 롤백되면 T2 트랜잭션에서 논리적 오류가 발생할 수 있습니다.

    image-20211122103746671

  • Non-Repeatable Read: 한 트랜잭션 내에서 동일 쿼리의 결과가 달라지는 현상을 말합니다.

    image-20211122104954925

  • Phantom Read: Non-Repeatable Read는 동일 레코드의 대한 변화라고 한다면 Phantom Read는 첫번째 쿼리에 없던 레코드가 두번째 쿼리에서 나타나는 현상을 말합니다.

    image-20211123100825560

트랜잭션 격리 수준(Transaction Isolation Level)

트랜잭션의 격리 수준은 트랜잭션 간의 간섭으로 발생할 수 있는 문제들을 해결하기 위해 DBMS의 Locking 매커니즘을 이용해 격리성의 강도를 지정하는 것입니다. 격리성의 강도는 무결성과는 비례하지만 동시성과는 반비례하므로 상황에 맞는 적절한 수준을 선택해야 합니다. **ANSI/ISO 표준 격리수준 **에는 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 4가지 격리 수준을 정의하고 있으며 DBMS마다 지원하는 격리 수준과 매커니즘이 다릅니다.

참고

s-lock : 공유잠금(Shared Lock)

x-lock : 배타적잠금(Exclusive Lock)

s-lock(O or X) 표기에서 O: 사용 , X: 사용하지 않음

MySQL은 InnoDB 기준으로 설명합니다.

격리 수준 DBMS 설명
Read Uncommitted SQL Server - 다른 트랜잭션에서 아직 커밋하지 않은 데이터를 읽을 수 있습니다.
- Dirty Read, Non-Repeatable Read, Phantom Read가 발생할 수 있습니다.
- s-lock(X), x-lock(O)
MySQL - 다른 트랜잭션에서 아직 커밋하지 않은 데이터를 읽을 수 있습니다.
- 특정 버전의 스냅샷을 읽는 Consistent Read하는 것이 아니라 Current Block을 읽는 Current Read를 합니다.
- Dirty Read, Non-Repeatable Read, Phantom Read가 발생할 수 있습니다.
- s-lock(X), x-lock(O)
Oracle Read Uncommitted 격리 수준을 지원하지 않습니다.
Read Committed SQL Server - 커밋된 데이터만 다른 트랜잭션에서 읽도록 허용합니다.
- READ_COMMITTED_SNAPSHOT 옵션에 따라 다르게 동작하는데 READ_COMMITTED_SNAPSHOT이 OFF이면 s-lock(O), x-lock(O) 이때 공유잠금은 읽기가 끝남과 동시에 해제되지만, 다른 트랜잭션에서 먼저 쓰기작업을 하고 있다면 블로킹됩니다. READ_COMMITTED_SNAPSHOT이 ON이면 s-lock(X), x-lock(O) 그래서 다른 트랜잭션에서 쓰기작업을 하고 있더라도 최신 커밋 스냅샷으로 읽기 작업을 할 수 있습니다. 만약 READ_COMMITTED_SNAPSHOT(ON)인 상태에서 s-lock이 필요하다면 READCOMMITTEDLOCK 테이블 힌트를 사용할 수 있습니다.
- Non-Repeatable Read, Phantom Read가 발생할 수 있습니다.
- SQL Server 기본 격리 수준
MySQL - 커밋된 데이터만 다른 트랜잭션에서 읽도록 허용합니다.
- 읽기 작업 마다 새로운 스냅샷을 만들기 때문에 다른 트랜잭션의 커밋이 적용되어 보일 수 있습니다.
- Non-Repeatable Read, Phantom Read가 발생할 수 있습니다.
- 이 격리 수준은 충돌할 트랜잭션이 거의 없는 데이터베이스 환경에 적합합니다.
- s-lock(X), x-lock(O)
- Oracle 기본 격리 수준
Oracle
Repeatable Read SQL Server - 일관성 읽기를 위해 읽기 작업에 s-lock이 걸리고 트랜잭션이 끝날때까지 유지합니다. 따라서 T1에서 읽은 Row를 다른 트랜잭션(T2)에서 수정하려고 하면 T2는 T1이 완료될 때까지 블러킹 상태가 됩니다. 또한 동일 트랜잭션 내에서 읽기 후 쓰기를 하는 경우에는 s-lock에서 x-lock으로 변환됩니다.
- 다른 트랜잭션에서 새행 쓰기(INSERT)가 허용하기 때문에 Phantom Read가 발생할 수 있습니다.
- s-lock(O), x-lock(O)
MySQL - 트랜잭션이 시작할 때 생성한 스냅샷을 트랜잭션이 종료될 때까지 유지하기 때문에 다른 트랜잭션의 변경에 영향을 받지 않습니다. 이러한 특성 때문에 Non-Repeatable Read 뿐만 아니라 Phantom Read 또한 발생하지 않습니다.
- s-lock(X), x-lock(O)
- MySQL 기본 격리 수준
Oracle 명시적으로 지원하지 않습니다.하지만 SELECT...FOR UPDATE를 사용해 x-lock으로 SELECT 결과를 보장할 수는 있습니다.
SNAPSHOT SQL Server - MySQL의 Repeatable Read처럼 트랜잭션이 시작할 때 존재한 데이터 버전과 일관성이 유지되도록 지정합니다.
- Non-Repeatable Read 뿐만 아니라 Phantom Read 또한 발생하지 않습니다.
- SNAPSHOT 격리수준을 사용하려면 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정해야 합니다.
- 주의할점은 동시에 2개 이상의 트랜잭션에서 동일 Row를 갱신할 경우 선행 트랜잭션은 성공하고 후행 트랜잭션에서 오류가 발생합니다.(= Lost Update 방지)
-x-lock(O), s-lock(X) 단, 롤백 중인 데이터에 다른 트랜잭션이 읽으려고 하면 블로킹 시킵니다.
READ ONLY Oracle - Serializable 격리 수준과 비슷하게 일관된 읽기를 하며 쓰기 작업은 할 수 없고 읽기 전용 트랜잭션이기 때문에 쓰기 작업의 쿼리를 실행하면 SQL Error [1456] [72000]: ORA-01456: READ ONLY 트랜잭션은 삽입/삭제/갱신 작업을 수행할 수 없습니다.라는 오류가 발생합니다.
- 읽기 일관성에 필요한 실행 취소 데이터가 다른 트랜잭션에서 재사용되어 snapshot too old오류가 발생할 위험이 있습니다.
- 트랜잭션 ID를 부여하지 않기 때문에 성능의 이점을 볼 수 있습니다.
- Dirty Read, Non-Repeatable Read, Phantom Read가 발생하지 않습니다.
- x-lock(X), s-lock(X)
Serializable SQL Server - Repeatable Read와 비슷하게 작동하지만 차이점은 Repeatable Read 격리 수준에서는 읽어드린 Row들에 대해서만 s-lock을 사용하기 때문에 INSERT에 의해 Phantom Read가 발생하는 반면, Serializable 격리 수준에서는 RangeS-S(범위 공유잠금)을 설정하여 Insert되는 새행을 블로킹하기 때문에 Phantom Read가 발생하지 않습니다. 단 조건절에 PK 컬럼을 equals로 조회할 경우에는 1개의 Row만 검색될 수 있으므로 s-lock만 걸립니다.
-x-lock(O), s-lock(O), RangeS-S(O)
MySQL 일반 SELECT문을 SELECT...FOR SHARE로 암시적으로 변환하고 읽기 작업시 s-lock(gap lock, next key lock)이 걸립니다. 또한 SELECT...FOR SHARE는 Locking Read 모드로 읽으며 항상 새로운 스냅샷을 생성합니다. Serializable 격리 수준은 s-lock(gap lock, next key lock)을 걸고 트랜잭션의 끝날때까지 다른 트랜잭션에서 쓰기를 하지 못하게 하여 일관성을 유지하는 방식을 사용하기 때문에 안정성은 높지만 동시성이 떨어집니다. 반면 Repeatable Read는 s-lock을 사용하지 않고 처음 조회를 실행한 시점의 스냅샷을 트랜잭션이 끝날 때까지 사용하기 때문에 다른 트랜잭션에서 쓰기가 가능합니다. 그래서 Serializable 격리 수준보다 동시성이 더 뛰어 납니다. 또한 Repeatable Read는 처음 조회한 쿼리 뿐만 아니라 처음 조회한 시점에서 Undo영역의 모든 테이블의 포인터를 바라보고 있기 때문에 트랜잭션 중간에 다른 테이블을 조회하여도 일관성 읽기가 가능합니다.
-x-lock(O), s-lock(O)
Oracle - SQL Server의 SNAPSHOT과 비슷하게 트랜잭션에서 일관된 읽기를 보장하고 동시에 2개 이상의 트랜잭션에서 동일 Row를 갱신할 경우 선행 트랜잭션은 성공하고 후행 트랜잭션에서 오류가 발생합니다.
- 트랜잭션에 의해 만들어진 쿼리는 트랜잭션 자체에 의해 만들어진 변경 사항을 봅니다.
- Dirty Read, Non-Repeatable Read, Phantom Read가 발생하지 않습니다.
- x-lock(O), s-lock(X)

격리 수준 설정 방법

  • SQL Server ☞ Document - docs.microsoft.com

      -- 세션 수준의 트랜잭션 설정
  > SET TRANSACTION ISOLATION LEVEL
      { READ UNCOMMITTED
      | READ COMMITTED
      | REPEATABLE READ
      | SNAPSHOT
      | SERIALIZABLE
      }
    
  -- 설정 확인
  > dbcc useroptions;
    
  |Set Option             |Value                  |
  |-----------------------|-----------------------|
  |textsize               |-1                     |
  |language               |한국어                  |
  |dateformat             |ymd                    |
  |datefirst              |7                      |
  |lock_timeout           |-1                     |
  |quoted_identifier      |SET                    |
  |ansi_null_dflt_on      |SET                    |
  |ansi_warnings          |SET                    |
  |ansi_padding           |SET                    |
  |ansi_nulls             |SET                    |
  |concat_null_yields_null|SET                    |
  |implicit_transactions  |SET                    |
  |isolation level        |read committed snapshot|
    
  --  READ_COMMITTED_SNAPSHOT,  ALLOW_SNAPSHOT_ISOLATION 확인
  > SELECT NAME, SNAPSHOT_ISOLATION_STATE, SNAPSHOT_ISOLATION_STATE_DESC, IS_READ_COMMITTED_SNAPSHOT_ON 
    FROM SYS.DATABASES
    
  -- READ_COMMITTED_SNAPSHOT 설정
  > ALTER DATABASE [DatabaseName] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
  -- ALLOW_SNAPSHOT_ISOLATION 설정 (Isolation level을 SNAPSHOT)
  > ALTER DATABASE [DatabaseName] SET ALLOW_SNAPSHOT_ISOLATION ON; 
    
  -- READ_COMMITTED_SNAPSHOT 해제
  > ALTER DATABASE [DatabaseName] SET READ_COMMITTED_SNAPSHOT OFF;
  > ALTER DATABASE [DatabaseName] SET ALLOW_SNAPSHOT_ISOLATION OFF;

  • MySQL ☞ SET TRANSACTION … - dev.mysql.com, START TRANSACTION - dev.mysql.com

    SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...
  
transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}
  
level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}
  
access_mode: {
     READ WRITE
   | READ ONLY
}
  
-- 격리수준 설정 예시
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  
-- 읽기 모드 설정 예시
> SET SESSION TRANSACTION READ ONLY;
> SET SESSION TRANSACTION READ WRITE;
  
-- 트랜잭션의 격리 수준 설정 후 트랜잭션 시작해줘야 한다.
> START TRANSACTION;
> SELECT ... UPDATE ... INSERT ... DELETE ...
> COMMIT | ROLLBACK; 
-- 트랜잭션 종료 됨. 
  
SHOW ENGINE INNODB STATUS;
SHOW FULL PROCESSLIST;
select * from information_schema.INNODB_LOCKS; -- lock으로 블로킹 걸렸을때만 알 수 있음
select * from information_schema.INNODB_LOCK_WAITS ilw ;
select * from information_schema.INNODB_TRX it ;
    • SET GLOBAL TRANSACTION …
      1. 설정 이후 생성되는 모든 세션에 적용됩니다.
      2. 기존에 생성된 세션은 영향 받지 않습니다.
    • SET SESSION TRANSACTION …
      1. 설정 이후 현재 세션 내에서 수행되는 모든 트랜잭션에 적용됩니다.
      2. 세션 내 단일 트랜잭션 별 격리 수준(SET TRANSACTION...) 을 지정할 수 있으며 단일 트랜잭션이 종료되면 세션에서 지정한 격리 수준으로 적용됩니다.
    • SET TRANSACTION …
      1. 단일 트랜잭션 별 격리 수준을 지정합니다.
      2. 단일 트랜잭션은 세션 트랜잭션 설정과 다르게 내부에 트랜잭션 설정을 다시 지정할 수 없습니다.
      3. 트랜잭션이 종료되면 다음 트랜잭션은 세션 레벨의 격리 수준이 적용되므로 단일 트랜잭션 격리 수준을 다시 사용하려면 SET TRANSACTION … 명령으로 재설정이 필요합니다.

    • 만약 격리 수준을 완전히 변경하고 싶다면 mysql.conf 또는 mysql.ini 파일에 다음과 같이 추가하고 재시작합니다.

      [mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFF

    • 적용된 격리 수준 확인 방법은 아래와 같습니다.

      -- MySQL 8.x 이상
SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;
    
-- MySQL 5.x 또는 MariaDB 10.x 이하
SELECT @@GLOBAL.tx_isolation, @@GLOBAL.tx_read_only;
SELECT @@SESSION.tx_isolation, @@SESSION.tx_read_only;

  • Oracle ☞ Document - docs.oracle.com

      -- 세션 레벨의 Isolation Level 설정 (READ는 트랜잭션 레벨로만 설정 가능.)
  > ALTER SESSION SET ISOLATION_LEVEL={READ COMMITTED | SERIALIZABLE}
    
  -- 트랜잭션 레벨의 Isolation Level 설정
  > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  > SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 
  > SET TRANSACTION READ ONLY;
    
  > UPDATE TEST_TABLE SET NAME='ABC' WHERE ID=1000;
    
  -- 실행중인 쿼리의 세션 레벨의 ISOLATION_LEVEL
  > SELECT s.sid, s.serial#,
  	CASE BITAND(t.flag, POWER(2, 28))
  	WHEN 0 THEN 'READ COMMITTED'
  	ELSE 'SERIALIZABLE'
  	END AS isolation_level
  	FROM v$transaction t
  	JOIN v$session s ON t.addr = s.taddr;
    	
  |SID |SERIAL#|ISOLATION_LEVEL|
  |----|-------|---------------|
  |2574|11877  |READ COMMITTED |
    
  > COMMIT;

Consistent Read와 Current Read

  • Consistent Read: MVCC에서 포함된 매커니즘으로 트랜잭션 내에서 최초 쿼리 조회 시점을 기준으로 커밋된 데이터만 읽고 쿼리 시작 이후에 커밋된 변경사항은 읽지 않습니다. Oracle과 InnoDB의 경우 단순 조회(SELECT)를 하면 식별번호(Oracle SCN, InnoDB XRX_ID)로 Undo 영역에서 데이터를 읽어 옵니다.

  • Current Read: 식별번호(Oracle SCN, InnoDB XRX_ID)를 따지지 않고 현재 DB의 커밋된 데이터(Current Block)를 읽어 오는 것을 말합니다. Current Read 모드로 읽는 경우는 아래와 같습니다.

    DROP TABLE, ALTER TABLE과 같은 DDL 사용.

    UPDATE, DELETE, INSERT과 같은 DML 사용.

    FOR UPDATE나 MySQL의 FOR SHARE 또는 LOCK IN SHARE MODE를 사용.

    트랜잭션 격리 수준이 READ UNCOMMITED인 경우.

  • MVCC를 적용하는 DBMS에서는 주로 읽기 작업을 Consistent Read로 하고 쓰기 작업을 Current Read(Locking Read)로 하는데 어떤 연산들에 대해서는 DBMS 별로 다르게 동작합니다.

    • Oracle은 INSERT INTO … SELECT, UPDATE … (SELECT …), CREATE TABLE … SELECT문에 포함된 조회 쿼리를 Consistent Read 모드로 읽으며 조회 쿼리에 포함된 테이블에 Locking하지 않습니다.
    • MySQL은 기본 격리 수준인 Repeatable Read일 때 INSERT INTO … SELECT, UPDATE … (SELECT …), CREATE TABLE … SELECT문에 포함된 조회 쿼리의 테이블을 Locking Read로 읽습니다. Locking Read는 Current Read 모드로 읽으면서 테이블에 s-lock을 하는 것을 말합니다. (단, 격리 수준이 Read Committed 또는 Read Uncommitted일 경우에는 Locking 하지 않습니다.)

MVCC(Multi Version Concurrency Control)

MVCC (Multi Version Concurrency Control, 다중 버전 병행 제어)는 한 트랜잭션 내에서 읽기의 일관성을 보장하기 위한 매커니즘으로, 병행 트랜잭션 처리에서 각 트랜잭션이 최초 조회하는 시점에서 스냅샷을 생성하고 이후 동일 트랜잭션 내의 읽기 작업은 저장된 스냅샷에서 읽어 드리는 방식입니다. 그래서 다른 트랜잭션의 작업에 영향을 받지 않고 읽기의 일관성을 보장할 수 있습니다. 또한 읽기 작업에 Locking을 하지 않기 때문에 동시성도 높일 수 있습니다.

  • MySQL과 Oracle의 기본 매커니즘입니다.
  • 기본적으로 Shared Lock을 사용하지 않으므로 Lock 경합이 줄어들어 동시성이 높습니다.
  • 스냅샷 사용은 저장 영역에 데이터가 많은 저장공간이 필요할 수 있으므로 저장공간의 크기 설정 및 관리가 필요합니다.
  • 데이터 버전이 충돌하면 오류가 발생하므로 애플리케이션에서 예외 처리해야 합니다.
  • 참고 ☞ Oracle / PostgreSQL / MySQL MVCC 매커니즘 비교

다른 트랜잭션의 격리 수준(Isolation Level) 조합 테스트

  • T1 (Repeatable Read) T2(Read Committed)라고 가정했을 때 (SQL Server)
T1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- t1 트랜잭션을 repeatable read 설정
T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- t2 트랜잭션을 read committed로 설정
dbcc useroptions; -- 지정된 isolation level을 확인한다.

-- Case 1
T1 select * from test_r1;
T2 select * from test_r1; -- 공유잠금은 다른트랜잭션에서도 가능하므로 조회 가능.(단 T2의 공유잠금은 실행완료되면 풀림)
T2 update test_r1 set name = 'change' where uid = 1000;  -- T1에서 공유잠금 했으므로 T2의 배타적잠금은 블로킹 상태가 됨.
T2 insert into test_r1(uid, name) values(2000, 'new'); -- 신규 행은 삽입 가능.
T1 update test_r1 set name = 'change' where uid = 1000; -- T1에서 잠금을 획득하고 있는 상태이기 때문에 업데이트 가능.

-- Case 2
T1 update test_r1 set name = 'change' where uid = 1000; -- T1에서 배타적잠금을 걸고 
T2에서 select * from test_r1;  -- T2에서 공유잠금을 걸면 블러킹 상태가 됨.

-- Case 3
T2 update test_r1 set name = 'change' where uid = 1000; -- T2에서 배타적잠금을 걸고 
T1에서 select * from test_r1;  -- T1에서 공유잠금을 걸면 블러킹 상태가 됨.
  • T1 (Repeatable Read) T2(Read Uncommitted)라고 가정했을 때 (SQL Server)
T1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
T2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Case 1, Case3 결과 동일
-- Case 4
T1 update test_r1 set name = 'change' where uid = 1000; -- T1에서 배타적잠금을 걸고 
T2에서 select * from test_r1;  -- Read Uncommitted이므로 공유잠금을 하지 않기 때문에 T1에서 변경한 내용 조회 가능
  • T1 (Repeatable Read) T2(Repeatable Read)라고 가정했을 때 (SQL Server)
T1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
T2 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Case2, Case3 결과 동일
-- Case 5
T1 select * from test_r1;
T2 select * from test_r1; -- 공유잠금은 다른트랜잭션에서도 가능하므로 조회 가능.
T2 update test_r1 set name = 'change' where uid = 1000;  -- T1에서 공유잠금 했으므로 T2의 배타적잠금은 블로킹 상태가 됨.
T2 insert into test_r1(uid, name) values(2000, 'new'); -- 신규 행은 삽입 가능.
T1 update test_r1 set name = 'change' where uid = 1000; -- T2에서 공유잠금을 획득하고 있는 상태이기 때문에 블로킹 상태가 됨.

☞ 참고

위키백화 격리수준 - en.wikipedia.org

SQL Server 격리수준 - docs.microsoft.com

SQL Server 트랜잭션 잠금 및 행 버전 관리 - docs.microsoft.com

Oracle 격리수준 11g - docs.oracle.com

Oracle 격리수준 12g - docs.oracle.com

Oracle Consistent Read vs Current Read - wiki.gurubee.net

MySQL 격리수준 - dev.mysql.com

MySQL Consistent Nonlocking Reads - dev.mysql.com

MySQL Locking Read(Current Read) - dev.mysql.com

MySQL 트랜잭션 격리수준 - zzang9ha.tistory.com

DBMS는 어떻게 트랜잭션을 관리할까 - d2.naver.com

동시성 제어 - dataonair.or.kr

동시성 제어 - wiki.gurubee.net

MVCC (Multi Version Concurrency Control)

댓글남기기