1) Transaction 격리 수준 (isolation Level)
A. isolation level 이란 : 해당 Transaction이 실행될 때 다른 Transaction이 접근할 수 있는 범위 수준
B. isolation level 수준에 따른 분류
i. Read Uncommitted
가) Transaction에서 아직 commit이나 Rollback 되지 않은 데이터도 조회가 가능하다.
나) Lock이 없이 조회가 가능하니 데이터의 정확도보다 퍼포먼스가 우선인 OLTP 서비스에 적합하다.
다) 격리를 시키지 않고 조회가 가능하기 때문에 Commit 되지 않은 데이터가 조회될 수 있고 해당 상환을 Dirty Read 라고 한다.
|
Session01 |
Session02 |
|
DROP TABLE IF EXISTS TBL_TEST4 GO CREATE TABLE TBL_TEST4 ( A INT , B INT ); INSERT TBL_TEST4 VALUES (1,1); GO
BEGIN TRAN UPDATE TBL_TEST4 SET B=2 GO |
|
|
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM TBL_TEST4
 |
|
ROLLBACK TRAN |
|
|
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM TBL_TEST4
 |
ii. Read Committed
가) Transaction에서 commit이 완료되어 Resource 데이터만 조회 가능하다.
나) 다른 Transaction에서 조회 후 공유 Lock 상태에서 격리된 Transaction이 데이터를 변경하고 commit이 완료되어 다른 Transaction에서 데이터를 다시 조회 했을 시 다른 데이터가 조회되어 한 Transaction 내에서 순서에 따라 다른 데이터를 가져와 적합성이 깨질 수 있다.
|
Session01 |
Session02 |
|
DROP TABLE IF EXISTS TBL_TEST5 GO CREATE TABLE TBL_TEST5 ( A INT , B INT ); INSERT TBL_TEST5 VALUES (1,1); GO
|
|
|
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DROP TABLE IF EXISTS TBL_TEST6 GO CREATE TABLE TBL_TEST6 ( A INT , B INT );
GO
BEGIN TRAN DECLARE @B INT
SELECT @B=B FROM TBL_TEST5 WHERE A=1
INSERT TBL_TEST6 VALUES (1,@B); GO |
|
BEGIN TRAN UPDATE TBL_TEST5 SET B=2 COMMIT TRAN |
|
|
|
SELECT @B=B FROM TBL_TEST5 WHERE A=1 INSERT TBL_TEST6 VALUES (1,@B); COMMIT TRAN GO
SELECT * FROM TBL_TEST5 SELECT * FROM TBL_TEST6
 |
iii. Repeatable Read
가) Transaction에서 소유한 Resource의 데이터를 다른 Transaction에서 변경할 수 없도록 Lock을 소유한다.
나) Row Lock Level의 Lock 소유하고 변경만 막기 때문에 데이터가 생기기도 하는 Phantom Read 현상이 발생되기도 한다.
|
Session01 |
Session02 |
|
DROP TABLE IF EXISTS TBL_TEST7 GO CREATE TABLE TBL_TEST7 ( A INT , B INT ); INSERT TBL_TEST7 VALUES (1,1); GO |
|
|
|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DROP TABLE IF EXISTS TBL_TEST8 GO CREATE TABLE TBL_TEST8 ( A INT , B INT );
GO
BEGIN TRAN DECLARE @A INT
SELECT @A=COUNT(*) FROM TBL_TEST7 INSERT TBL_TEST8 VALUES (1,@A); GO |
|
INSERT TBL_TEST7 VALUES (2,2); GO |
|
|
|
DECLARE @B INT
SELECT @B=COUNT(*) FROM TBL_TEST7 INSERT TBL_TEST8 VALUES (2,@B);
COMMIT TRAN
SELECT * FROM TBL_TEST8
 |
2) Lock 제한 설정
A. Lock Timeout 설정
i. Lock에 대한 제한 설정을 설정하고 Lock 충돌을 방지한다.
ii. isolation level과 Lock Timeout을 적절히 혼합하여 데이터 적합성과 퍼포먼스 사이에 밸런스를 조절한다.
iii. 다음과 같은 쿼리로 설정 및 확인한다. (기본값은 -1로 무제한이다.)
|
SET LOCK_TIMEOUT 10000 SELECT @@LOCK_TIMEOUT |
B. Transaction 우선 순위 결정
i. 현재 세션이 다른 세션과 교착상태에 있는 경우 현재 세션이 계속 실행되도록 하는 상대적인 중요도 설정
ii. SQL Server는 교착 상태가 발생되면 Rollback 하는데 비용이 적게 드는 쪽을 설정하여 Rollback 함
iii. 우선 순위 결정으로 Rollback 진행될 세션을 결정할 수 있다.
|
DECLARE @deadlock_var NCHAR(3); SET @deadlock_var = N'LOW';
SET DEADLOCK_PRIORITY @deadlock_var; |
3) 출처
A. https://jwprogramming.tistory.com/12
C. https://docs.microsoft.com/ko-kr/sql/t-sql/functions/lock-timeout-transact-sql?view=sql-server-ver15
F. https://blog.naver.com/jevida/140207526376
'Database > SQL Server' 카테고리의 다른 글
| 통계와 동기, 비동기 처리 특징 (0) | 2021.04.29 |
|---|---|
| 형 변환과 Callate (0) | 2021.04.29 |
| 확장 이벤트를 통한 Dead Lock 캡처 (0) | 2021.04.22 |
| Lock 과 DeadLock (0) | 2021.04.22 |
| Flyway 이용한 형상관리 (0) | 2021.04.16 |
최근댓글