SNAPSHOT

SQL Server에서의 트랜잭션과 오류 처리

Miriam Antona

Software Engineer

SNAPSHOT

모든 변경은 tempDB에 저장됩니다
SNAPSHOT 트랜잭션 시작 이전에 커밋된 변경과 자신의 변경만 조회
SNAPSHOT 시작 이후 다른 트랜잭션의 변경은 보이지 않음
읽기는 쓰기를 막지 않고, 쓰기는 읽기를 막지 않음
업데이트 충돌이 발생할 수 있음

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - 격리 수준 비교

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes
REPEATABLE READ	no	no	yes
SERIALIZABLE	no	no	no
SNAPSHOT	no	no	no

SNAPSHOT - 예시

Transaction1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transaction2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

차단되지 않습니다!

SNAPSHOT - 예시

Transaction1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transaction2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

차단되지 않습니다!

SNAPSHOT - 요약

장점:

높은 데이터 일관성: 차단 없이 dirty/non-repeatable/phantom read 방지

단점:

tempDB 사용량 증가

사용 시점:

일관성이 필수이고 차단을 원하지 않을 때

READ COMMITTED SNAPSHOT

READ COMMITTED 동작을 변경합니다

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

기본값은 OFF
ON으로 사용하려면:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

ON이면, 각 READ COMMITTED 문은 해당 문 시작 이전에 커밋된 변경만 조회
업데이트 충돌이 발생하지 않음

READ COMMITTED SNAPSHOT - 예시

Transaction1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transaction2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 35000,00        |

READ COMMITTED SNAPSHOT - 예시

Transaction1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transaction2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 30000,00        |

WITH (NOLOCK)

커밋되지 않은 데이터를 읽을 때 사용
READ UNCOMMITTED는 연결 전체, WITH (NOLOCK)은 특정 테이블에만 적용
특정 테이블의 미커밋 데이터만 읽고자 할 때 어떤 격리 수준에서도 사용 가능

WITH (NOLOCK) - 예시

계정 5의 원래 잔액 = $35,000

Transaction1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

Transaction2

...

SELECT current_balance
FROM accounts WITH (NOLOCK) 
WHERE account_id = 5;

| current_balance |
|-----------------|
| 30000,00        |

Vamos praticar!

SQL Server에서의 트랜잭션과 오류 처리