DBMS 2

DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!

Concurrency Control

DBMS 2

Informix 가이드

Informix 프로그래머 가이드

Concurrency Control

작성자

admin

작성일

2021-02-19 14:03

조회

779

Concurrency Control

Isolation - Read Concurrency Control

ANSI SQL - 92 Transaction Isolation (SET TRANSACTION)

Read Uncommitted
Read Committed
Repeatable Read
Serializable Read

- ANSI 표준
- access mode 지원
- 트랜잭션당 한번만
- 트랜잭션이 끝날 때까지 유효
- 트랜잭션에서만 사용

Informix Isolation (SET ISOLATION)

Dirty Read
Committed Read
Cursor Stability
Repeatable Read

- non-ANSI
- 트랜잭션에서 전환가능
- 트랜잭션이 끝나거나 다른 SET ISOLATION 구문을 실행할 때까지 유효
- 로깅모드 데이터베이스에서 언제든 사용 가능

Access Mode

SET TRANSACTION READ WRITE ;
SET TRANSACTION READ ONLY ;

ANSI Levels	SET TRANSACTION	SET ISOLATION
Read Uncommitted	READ UNCOMMITTED	DIRTY READ
Read Committed	READ COMMITTED	COMMITTED READ
N/A	N/A	CURSOR READ
Repeatable Read	REPEATABLE READ	REPEATABLE READ
Serializable	SERIALIZABLE	REPEATABLE READ

Read Uncommitted / Dirty Read

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SET ISOLATION TO DIRTY READ ;

Read Committed / Committed Read

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
SET ISOLATION TO COMMITTED READ ;

Cursor Stability

SET ISOLATION TO CURSOR STABILITY ;

Serializable / Repeatable

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
SET ISOLATION TO REPEATABLE READ ;

그림 1: Read Concurrency Control

Lock - Update Concurrency Control

Lock의 적용 단위

Database 레벨
Table 레벨
Page 레벨
Row 레벨
Key 레벨

그림 2: Update Concurrency Control

Lock의 종류

Shared Lock
Exclusive Lock
Update Lock

Lock 모드 설정

SET LOCK MODE TO NOT WAIT;
SET LOCK MODE TO WAIT 20;
SET LOCK MODE TO WAIT;

Retain Update Lock

SET ISOLATION TO DIRTY READ RETAIN UPDATE LOCKS;
SET ISOLATION TO COMMITTED READ RETAIN UPDATE LOCKS;
SET ISOLATION TO CURSOR STABILITY RETAIN UPDATE LOCKS;

그림 3: Retain Update Lock

Key value locking

인덱스가 있는 데이터를 update,delete,insert 할 때 인덱스 Key에 locking 하는 기법

그림 4: Key value locking

« ESQL/C Programming

Query Optimization »

목록보기