NOLOCK, 격리수준, 프로파일러로 추적!

MSSQL의 경우,기본적으로 제공하는 격리수준은

READ_COMMITED 입니다. 즉, AUTO COMMIT 라고 생각하시면 되십니다.

 

설정 변경은 가능합니다. 하지만 기본적으로는 저렇게 되고 있습니다.

그렇다보니 UPDATE, INSERT 등을 하면서 테이블이 잠기게 되었을 때, SELECT를 하게 된다면 블락이 걸리게 됩니다.

이럴 때는 DB의 성능이 떨어지고, 데드락이 걸리게 되어 결과가 안나오는 경우가 발생할 수도 있습니다.

이럴 때를 피하기 위해 사용할 수 있는 방법이 WITH (NOLOCK) 입니다.

 

 

 

 

 

목적 : 잠금(lock)이 되어있는 Table의 정보를 가져올 때 사용
잠금(lock)이란 ?  현재 테이블에 어떤 작업이 실행되고 있다고 가정할수 있음
해당된작업(트랜젝선)안에서 데이터는 변경된 데이터를 읽을 수 있다

잠금 무시(Dirty Read)하여 가져오는것인데

이때, 트랜잭션을 명시적으로 선언하고 테이블작업을 할 경우, 
정상적인 종료라면 문제가 없다. 그러나, 트랜잭션을 명시적으로 선언하고
테이블 작업을 할 경우 비 정상적인 종료라면 데이터를 가져오지 못한다.

NOLOCK은 단순 SELECT일때만 할 수 있도록한다

정말 중요한 테이블작업을 할 때 명시적 트랜잭션을 선언하고 작업을 하는데
그때 NOLOCK을 걸라고 한다.

 

 

 

쿼리창에서 테이블에 select을 해본다

select * from [dbo].[AWBuildVersion]
go

 

 

 

-- (트랜잭션 명령어 사용하여 잠금상태 만들기.)

BEGIN TRAN 혹은
BEGIN TRANSACTION 사용

BEGIN TRAN
	UPDATE
	[dbo].[AWBuildVersion]
	SET
		[Database Version] = '14.0.1000.170'
	WHERE
		SystemInformationID = 1

go

 

 

-- 트랜잭션의 명령어중 BEGIN TRAN만 사용하여 실행 한 후

-- 기존 엔진은 놔두고 다른 엔진으로 로그인하여 새 쿼리창을 열고 해당 테이블을

select * from [dbo].[AWBuildVersion]
go

select해본다.
-- 그럼 결과가 나오지 않고 계속 '실행 중'만 표시가 될 것이다.

 

 

-- 새 엔진의 다른 새 쿼리창을 열어

sp_who2 

-- 를 실행하면 유저가 뜬다.

아마 저 sa가 사용중일것으로 예상...........???

사용자를 확인 위처럼 볼 수 있다

 

 

 

-- NOLOCK을 사용하여 다른 사용자가 tran으로 사용중일 때에도 select을 해본다

select * from [dbo].[AWBuildVersion] 
--with(readuncommitted)도 가능
with(nolock)
go

-- 아래와 같이 select의 값이 잘 보인다.

 

 

 

 

-- 기존 엔진으로 돌아와

--1. 변경사항을 커밋하려면 

COMMIT TRAN

을

 

--2. 문제가 생기면 

rollback

을 한다. 

 

 

 

 

-- rollback, endtran을 하게되면 전에 조회가 불가했던 쿼리창에서도 다시 조회가 가능하다.

 

 

 

 

 

 

LOCK걸린 쿼리 KILL하기

 

 

잠금 : 
SELECT 에서 쓰면 공유잠금
INSERT/UPDATE/DELETE 배타적 잠금

exec sp_lock 
exec sp_who2 
SELECT * FROM sys.sysprocesses WHERE blocked > 0

DBCC inputbuffer 에 실행ID를 친다 : 마지막 실행 한 쿼리
KILL에 실행 ID를 치면 해당되는 녀석이 풀릴 수 있다.

 

 

 

 

락걸린 쿼리를 확인한다

exec sp_lock

Mode에 X가 락걸린 것이나 이번에는 그냥 다른걸로 해본다

S는 공유

 

-- who2도 체크가능
exec sp_who2

BlkBy 컬럼에 값이 있으면 락걸린 프로세스의 ID다.

 

 

 

 

--Lock 걸린 spid를 입력하면 현재 돌고 있는 쿼리를 출력해 준다.

DBCC inputbuffer(57)

 

 

 

 

--락걸린 쿼리를 죽인다

KILL 57

/*명령이 완료되었습니다*/

 

---

 

시스템 테이블을 통해서도 확인 가능

SELECT * FROM sys.sysprocesses WHERE blocked > 0

위 쿼리를 돌리면, 락 걸린 세션에 대해서만 정보가 나옵니다

 

 

 

---

---

추가~

 

 

트랜잭션이 종료되지 않아도 데이터 읽기 가능
1. with(nolock)
2. read(uncommited)
-> SELECT을 하게되면 일반적으로는 공유잠금을 SQL Server에 요청하나,
다른쪽(세션)에서 잠금(베타적잠금)을 한 상태에서 
SELECT을 할 경우 공유잠금이 불가하다  -> 그이유는 베타적 잠금은 어떤 잠금하고도
호환 또는 공유하지 않는다. 그래서 SELECT을 해도 데이터를 주지 않음.
위의 두가지 옵션을 가지고 공유 잠금을 하지 않고 SQL에 요청하는것이 nolock or uncommited
이며 이렇게 가져올 수 있지만, 단점으로는 완료되지 않는 데이터를 가져오기 때문에 일관성이 부족하다.

nolock을 더 자주쓰는 이유
동시성에서 with(nolock)이 OLTP(일반적인 database 쿼리문 처리 : 트랜잭션처리)에서 
가장 많이 쓰이는 격리수준이기 때문이다.

 

 

2번째연습

begin tran
	update 
		isotesttb 
	set
		userage = 50
	where 
		idx = 3;
go

rollback

go

select * from isotesttb
--with(readuncommitted)
--with(nolock)
where idx=3

exec sp_lock

dbcc inputbuffer(62)
exec sp_who2 62
kill 62

 

 

 

--이걸 실행하면 이 쿼리창에서는 nolock/readuncommit이 기본 명령어가 된다-> 바로 보인다

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
	*
FROM
	isotesttb
go

-- 커밋

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

 

 

 

MS SQL의 기본 격리 수준은 READ COMMITTED
SELECT는 기본적으로 공유잠금을 요청함
예제의 UPDATE를 할 때, 해당되는 것을 베타적 잠금으로 하는것이고 
SELECT할 때 공유잠금을 요청했기 때문에 
UPDATE가 끝날 때 까지 기다렸다가 완료가 되면 이때, 실행이 된다.

S는 공유 잠금
X는 베타적 잠금

 

 

 

 

예제에서 ROLLBACK을 하면 데이터를 가져옴
데이터를 가져올 때 with(nolock)을 할 경우, 데이터를 dirth Read를 해서 데이터의
일관성을 손상하고, 동시성에서는 부족하다.

이 수준에서도 반복적인 읽기가 불가능 하기때문에 
일관성에 대한 문제가 발생 할 수 있다.

 

 

결론 -> 동시성의 성격에 맞게 알아서 써라!!!!!!!

 

 

 

---

SELECT를 할 때 공유잠금을 설정.

LEVEL REPEATABLE READ

 

 

 

 

 

다른 세션에서 SELECT 할 떄는 상관 없음

다른곳에서 UPDATE나 DELETE를 못하게 한다. (베타적 잠금을 대기상태로 두어 못하게 함)

 

한곳에서 데이터 읽기를 하는 동안, 외부에서 데이터의 변경을 막고, 동일한 곳에서 데이터를
읽어야 하는 상황일때 사용. 

 

-- 격리수준 지정해놓기
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin tran
	select
	*
	from 
		isotesttb
	where 
		idx = 4;

commit

rollback

go



-- 다른세션

begin tran
	update 
		isotesttb 
	set
		userage = 55
	where 
		idx = 4;
commit


select * from isotesttb


exec sp_lock
dbcc inputbuffer(62)
exec sp_who2 62
kill 62

S때문에 X가 대기중임을 볼 수 있다

 

 

 

---

 

LEVEL SERIALIZABLE -- 직렬화

 

 

 

아래의 커밋을 빼고 돌리고 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE	-- 직렬화
BEGIN TRAN
	UPDATE
		isotesttb
	SET
		userage = 1
	where
		idx = 1





	select
		*
	from
		isotesttb
	where
		idx = 1


commit

insert를 시키면 들어간다.

BEGIN TRAN
	INSERT INTO isotesttb
	VALUES
		('홍길동10', 50, 190)

 

하지만 select를 하면 조회가 안된다.

select * from isotesttb

 

 

아마 정확한 조회를 위해 insert같은 추가만 허용하고 select같은 조회는 차단시키는 것 같다.

 

 

 

 

---

 

스냅샷  LEVEL SNAPSHOT

 

스냅샷을 하면 그대로 데이터가 떠잇는걸 가져온다.(사진찍는 것이라고 보면 됨)
BEGIN TRAN을 건 것만 가져가고 그것을 TempDB에 본떠넣는다.
그래서 다른 세션에서 TempDB에 있는 데이터를 가져간다.
바뀐 후의 값은 못가져감.

 

 

첫번째 실행으로 스냅샷 환경을 만들어준다

ALTER DATABASE
	ISODB
SET
	ALLOW_SNAPSHOT_ISOLATION ON;

 

 

두번째 실행

BEGIN TRAN
	UPDATE
		isotesttb
	SET
		userage = 1000
	WHERE
		idx = 2

SELECT
	*
FROM isotesttb 
WHERE idx = 2



commit

 

 

아래 쿼리문을 실행해본다.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
	SELECT
		*
	FROM
		isotesttb
	WHERE
		idx = 2

commit

 

 

 

두 결과의 실행 값이 다른 이유는

첫번째 쿼리문에서 BEGIN TRAN을 건 것만 가져가고 그것을 TempDB에 본떠넣었고 
그래서 다른 세션에서 두번째 실행했을 때, TempDB에 있는 데이터를 가져왔기 때문이다.

commit을 해야 값이 바뀐다.

 

 

 

---

 

의도적으로 잠그거나 의도적으로 공유모드로 할 수 있음

 

 

 

 

-- * 잠금

명시적으로 트랜잭션을 제어해야 한다.
트랜잭션은 자체컴퓨터에서는 문제가 없지만 내가아닌 다른사람이 데이터를 가져오거나,

중첩트랜젝션은 @@TRANCOUNT를 사용하여 한다.

윈도우 관리도구- 성능모니터에서 잠금관련 보는법 
Locks—logical level
Latches—physical level

잠금을 해결하는 방법 : KILL

잠그는 목적은 데이터의일관성
잠그는동안에 데이터를 변경하며 그 동안은 최신데이터가 아님. 잠그는동안 데이터를 가져가면
일관성이 깨짐

의도적으로 잠군경우 SPID로 검색하여 그사람에게 왜 의도적으로 잠궜는지
알아낼 것

-- 잠금힌트!
잠금을 할 때 어떻게 할지에대한 힌트를 줌.
어디까지 잠글거냐ㅡ
아래중에서 뭘 쓸꺼냐'
READCOMMITTED
READCOMMITTEDLOCK
READUNCOMMITTED or NOLOCK
REPEATABLEREAD
SERIALIZABLE or HOLDLOCK
READPAST

--  교착상태
응답없음.. 사용자가 중지할때 까지 응답 없음.....= 데드락 = 교착상태에 빠졌다.

교착상태는 기본적으로 5 초마다 실행됩니다. 

교착 상태 희생자가 선택되고 종료됩니다. (희생자 = 먼저 종료하는쪽 = 희생하는쪽 = 우선순위가 아닌 쪽)

-- 해결방법 -> Latches를 사용하여 주기적으로 모니터링 함

-- 잠금에대한 열쇄를 꼭 체크한다. 

 

 

---

profiler 프로파일러로 추적하기

 

 

 

프로파일러 창을 연다~ 

 

 

 

 

 

 

 

세션로그인(연결) 후 추적 속성에서 TSQL_LOCKS을 선택한다

 

 

 

 

 

이벤트 선택 란을 누르면 아래와 같이 뜬다, 여기서 필요한것들을 선택한다

 

 

 

 

 

스튜디오에 돌아와서

exec sp_tb1_proc
exec sp_tb2_proc

 

기존에 생성한 프로시저를 실행시킨다. 

각각 다른세션에 시켜도 되고, 같이해도됨,?

 

 

 

그러고 추적창을 보면 아래와같이 많이 떠있을 것이다. 잘 진행되고 있는것.

 

 

 

추적을 많이하게되면 부하가 걸려 좋지 않다.