Transcript 트랜잭션과_잠금.ppt

SQL Server 2000
트랜잭션과 잠금
데브피아 세미나
정원혁 / MCDBA, MCT
http://mssql.ce.ro
http://www.wtime.net
http://www.gtu.co.kr
http://www.inbrein.com
-1-
차례1

트랜잭션
 트랜잭션 기본 개념 (ACID)
 트랜잭션 종류/ 암시트랜잭션 모드
 중첩 트랜잭션/ save tran
-2-
차례2

잠금
 필요성/ 나타나는 문제
 낙관적.비관적 잠금
 잠금의 크기/ 크기와 동시성
 잠금의 길이
 잠금 호환성
 정보 얻기
 blocking
 잠금의 세기
 특수 잠금
-3-
차례3



격리 수준
 잠금 힌트
블록킹과 데드락
분산 트랜잭션과 잠금
 연결된 서버
 set xact_abort
 분산 트랜잭션
 2단계 커밋
-4-
차례4





트리거와 트랜잭션
커서와 트랜잭션
인덱스와 잠금
잠금 수준 escalation
바운드 연결
-5-
트랜잭션?


계좌 이체
무지 큰 테이블에 대한 수정이나 삭제
BEGIN TRAN
COMMIT | ROLLBACK
-6-
트랜잭션?

ACID
 원자성
 더 이상 분류할 수 없는 작업 단위
 All or Nothing
 일관성
 완료된 트랜잭션의 모든 데이터는
일관적이어야
 무결성
-7-
트랜잭션?

ACID
 격리성
 다른 트랜잭션이 수정하기 전 상태의
데이터를 보거나, 두 번째 트랜잭션이
완료된 후의 데이터를 볼 수는 있지만 중간
상태는 볼 수 없다
 Roll forward, Roll back : 순차성
 영속성
 완료되고 나면 영구적으로 시스템에 적용.
수정은 시스템에 오류가 발생한 경우에도
지속.
-8-
트랜잭션의 종류

암시적(implicit) 트랜잭션: 서버가 알아서
UPDATRE bigtable SET… WHERE …
DELETE bigtable WHERE …
=>
BEGIN TRAN
UPDATRE bigtable SET… WHERE …
COMMIT
BEGIN TRAN
DELETE bigtable WHERE …
COMMIT
-9-
트랜잭션의 종류

명시적(explicit) 트랜잭션: 사용자가 지정
UPDATE 입고 SET… WHERE …
UPDATE 재고 SET… WHERE …
=>
BEGIN TRAN
UPDATE 입고 SET… WHERE …
UPDATE 재고 SET… WHERE …
COMMIT
-10-
중첩 트랜잭션

트랜잭션 안에 또 트랜잭션
begin tran
select @@trancount
begin tran
select @@trancount
commit
select @@trancount
commit
select @@trancount
-11-
중첩 트랜잭션
begin tran
begin tran
commit
commit
begin tran
begin tran
rollback
commit
-12-
save tran


begin tran
 save tran AA
 rollback tran AA
commit
-13-
암시적 트랜잭션 모드

Delete bigtable
 해 놓고 보니 where 절 빼먹었다. 아뿔싸!

Begin tran이 없으면 rollback 불가능
암시적 트랜잭션 모드에서는 가능
set implicit_transactions on
dbcc useroptions
신중하게 사용하자 blocking
세션 옵션





-14-
암시적 트랜잭션 모드
-15-
사용할 수 없는 문장











BACKUP LOG
RESTORE LOG
CREATE DATABASE
ALTER DATABASE
DROP DATABASE
RECONFIGURE
UPDATE STATISTICS
LOAD DATABASE
LOAD TRANSACTION
DUMP TRANSACTION
DISK INIT
-16-
암시적 트랜잭션 모드

서버 수준에서 설정 가능
 EM 에서



sp_configure ‘user options’, xxx
reconfigure
select @@options
-17-
세션2
-18-
잠금


필요성: 문 안잠그고 응가하면?
안 걸면 다음과 같은 문제 발생




손실 업데이트(lost update)
커밋되지 않은 종속성(커밋되지 않은 읽기)
(read uncommitted)
일관성 없는 분석(반복하지 않는 읽기) (non repeatable read)
팬텀 읽기 (phantom read)
-19-
잠금 - lost update

손실 업데이트(lost update)
 손실 업데이트는 둘 이상의 트랜잭션이 같은 행을
선택한 다음 원래 선택한 값을 기준으로 행을
업데이트할 때 발생합니다. 이 때 각 트랜잭션은 다른
트랜잭션을 인식하지 못합니다. 마지막 업데이트가
다른 트랜잭션의 업데이트를 겹쳐쓰므로 데이터가
손실됩니다.
 예를 들어, 두 명의 사용자가 같은 문서를 복사한다고
가정합니다. 각 사용자가 각자 복사본을 변경한 다음
변경된 복사본을 저장하면 원본 문서가 겹쳐써집니다.
변경된 복사본을 마지막으로 저장한 사용자는 먼저
사용자가 변경한 내용을 겹쳐씁니다. 이러한 문제를
해결하려면 다른 사용자가 변경 작업을 마칠 때까지
변경할 수 없게 해야 합니다.
-20-
잠금 - read uncommitted


커밋되지 않은 종속성은 다른 트랜잭션이 업데이트 중인
행을 선택할 때 발생합니다. 두 번째 트랜잭션이 읽고
있는 데이터는 아직 커밋되지 않았지만 현재 행을
업데이트 중인 트랜잭션에 의해 변경될 수 있습니다.
예를 들어, 한 사용자가 문서를 변경 중이라고
가정합니다. 변경하는 동안 다른 사용자가 그 시점까지
변경한 내용을 모두 포함하여 문서를 복사한 다음 다른
사용자에게 문서를 배포합니다. 첫 번째 사용자가 그
때까지 변경한 내용이 잘못되었다고 판단하여 편집
내용을 지우고 문서를 저장할 수 있습니다. 배포된
문서에는 틀린 내용이 있으므로 이 내용은 무시해야
합니다. 이러한 문제를 해결하려면 첫 번째 사용자가
변경한 내용이 최종이라고 결정할 때까지 다른 사용자가
변경된 문서를 읽을 수 없게 해야 합니다.
-21-
잠금 - non - repeatable read


일관성 없는 분석은 두 번째 트랜잭션이 같은 행에
액세스할 때마다 다른 데이터를 읽을 때 발생합니다.
일관성 없는 분석은 두 번째 트랜잭션이 읽고 있는
데이터를 다른 트랜잭션이 변경하고 있다는 점에서
커밋되지 않은 종속성과 비슷합니다. 그러나 일관성 없는
분석에서는 두 번째 트랜잭션이 읽은 데이터가 변경한
트랜잭션에 의해 커밋된 것이며 같은 데이터를 읽을
때마다 매번 다른 트랜잭션이 정보를 변경하는 것입니다.
이를 반복하지 않는 읽기라고 합니다.
예를 들어, 한 사용자가 같은 문서를 두 번 읽는데 각 읽기
사이에 다른 사용자가 문서를 다시 작성할 수 있습니다.
한 사용자가 같은 문서를 다시 읽으면 이 문서가 변경되어
있을 것이므로 원래의 읽기는 반복되지 않습니다. 이러한
문제를 해결하려면 한 사용자가 문서를 모두 작성한
후에만 다른 사용자가 문서를 읽게 합니다.
-22-
잠금 - phantom read


팬텀 읽기는 한 트랜잭션이 읽고 있는 행 범위의 한 행에
대해 다른 트랜잭션이 삽입 또는 삭제 작업을 수행할 때
발생합니다. 다른 트랜잭션의 삭제 작업으로 인해
트랜잭션이 첫 번째 행 범위를 읽을 때 읽은 행이 다음에
읽을 때 없어질 수 있습니다. 마찬가지로 다른 트랜잭션의
삽입 결과로 처음 읽을 때 없던 행이 다음에 읽을 때 생길
수도 있습니다.
예를 들어, 문서 작성자가 제출한 문서를 편집자가
변경하는 중 생산 부서에서 문서의 마스터 복사본으로
변경 내용을 통합할 때 작성자가 편집되지 않은 새 자료를
문서에 추가할 될 수 있습니다. 이 문제를 해결하려면
편집자나 생산 부서가 원본 문서 작업을 완료할 때까지
다른 사용자가 새 자료를 문서에 추가할 수 없게 합니다.
-23-
낙관적/ 비관적 동시성



낙관적 동시성 Optimistic Lock
 내가 데이터 처리할 동안 설마 넘들이
건드리겠어?
비관적 동시성 Pessimistic Lock
 내가 데이터 처리할 동안 넘들이 건드릴
가능성이 당연히 있지…
클라이언트 쪽에서 사용
-24-
잠금의 크기









SQL 서버가 알아서 잘
내가 강제로 지정가능 – 잠금 힌트
(별로 권장 안 함)
행 단위 잠금이 기본
행 : RID, KEY
페이지: PAG
테이블: TAB
익스텐트 : EXT
데이터베이스: DB
서버
-25-
잠금 크기와 동시성
-26-
잠금의 길이



공유 잠금
 select 가 끝나자 마자 풀린다
 격리 수준/ 잠금힌트로 조정 가능하다
배타 잠금
 트랜잭션이 끝나야 풀린다
 조정 불가능하다
업데이트 잠금
 트랜잭션 끝까지
-27-
잠금의 호환성
-28-
Pages(참고)






Size: 8K, 96 byte header
Max Row Size: 8060
Max Key Size: 900
Max Number of Columns: 1024
Performance Improvements
 Rows only compacted
when necessary
 Slot array used for binary
search
Torn Page Detection
Page header
Torn bits: 011011000…..
Row A
Row C
Row B
Offset
Slot array
460
200
100
-29-
익스텐트(참고)


균일(Uniform)
Contain 8 pages from a single object
혼합(Mixed)
 Can contain pages from up to 8
objects
Extent
(8 pages = 64K)
T1
8K page
T2
T1
Mixed Extent
T3 T4
-30-
페이지 훔쳐보기(참고)







DBCC TRACEON (3604)
DBCC PAGE
(dbname, file번호, page번호, 옵션)
DBCC TRACEOFF (3604)
예) dbcc page (pubs, 1, 205, 1)
SELECT first FROM SYSINDEXES
WHERE ID = object_id('titles')
SELECT convert(int, 0xcd)
옵션
0: 헤더만
1: 행 단위로
2: 페이지 그대로
3: 행 / 그리고 컬럼 값
-31-
세션3
-32-
잠금 정보 얻기 sp_lock
-33-
잠금 정보 얻기 sp_lock




select @@spid
select db_name( ) , object_name( )
file # , page #, rid #
index id
 0: heap
 1: clustered
 2> : nonclustered
 255: image/ text
-34-
잠금 정보 얻기 sp_lock
-35-
Blocking




앞에서 잠금 걸고 막고 있는 넘
sp_who , sp_who2
 block by
sp_lock
 wait / grant
dbcc inputbuffer( spid )
Real소스\SQL
ERBlk.exe
-36-
세션4
-37-
잠금의 세기

쉽게 생각하면
 공유 잠금
 배타(적) 잠금 (단독 잠금)

어렵게 생각하면
 인텐트(내재) 잠금
 스키마 잠금
 대량 업데이트 잠금
 업데이트 잠금
-38-
특수 잠금 - 인텐트 잠금

잠금 계층 구조를 만드는 데 사용

내재된 공유(IS) 잠금
내재된 단독(IX) 잠금
공유 및 내재된 단독(SIX) 잠금


-39-
특수 잠금 – 스키마


스키마 수정(Sch-M) 잠금은 열을 추가하거나
테이블을 삭제하는 등 테이블 데이터 정의
언어(DDL) 작업이 수행 중일 때 사용됩니다.
스키마 안정성(Sch-S) 잠금은 쿼리를 컴파일할
때 사용됩니다. 스키마 안정성(Sch-S) 잠금은
단독(X) 잠금 등 다른 트랜잭션 잠금을 차단하지
않습니다. 따라서 쿼리가 컴파일되는 동안
테이블에 대한 단독(X) 잠금을 포함하여 다른
트랜잭션을 계속 실행할 수 있습니다. 그러나
테이블에서 DDL 작업은 수행할 수 없습니다.
-40-
특수 잠금 – 대량 업데이트




테이블로 데이터를 대량 복사하고
TABLOCK 힌트가 지정되거나
sp_tableoption을 사용하여 table lock on bulk
load 테이블 옵션이 설정될 때
여러 프로세스가 데이터를 동시에 같은 테이블로
대량 복사할 수는 있지만, 데이터를 대량
복사하지 않는 다른 프로세스가 테이블에
액세스하는 것은 금지
-41-
참고) 대량 업데이트 속도차이
-42-
특수 잠금 – 업데이트 잠금




update 문에서만 발생?
S lock 과 X lock 의 bybrid 잠금
자동적으로 U > X 로 전환
변환 교착을 해결할 방법으로 사용 (잠금 힌트)
-43-
특수 잠금 – 키 범위 잠금



SERIALIZABLE 격리 수준에서
9가지 형식
 rangeS-S / rangeS-U/ rangeX-X ….
특정 범위
-44-
세션5
-45-
격리 수준

SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
-46-
잠금 힌트








readUncommitted (nolock)
readCommitted
repeatableRead
serializable (holdlock)
rowLock
pagLock
tabLock
tabLockX
-47-
잠금 힌트

updLock

readpast
-48-
잠금 힌트 예
select title, au_lname
from titles (TABLOCK) join titleauthor
on titles.title_id = titleauthor.title_id
join authors (PAGLOCK, HOLDLOCK,
INDEX(1))
on authors.au_id = titleauthor.au_id
-49-
Blocking










얼마나 오래 막고 있을까?
set lock_timeout
EM / 현재동작
lock hint: readpast
@@trancount
클라이언트 버그
중첩 트랜잭션 처리 오류 > @@trancount 확인
dbcc inputbuffer
sp_block
kill
-50-
Deadlock











서로 물린 blocking
SQL 서버가 해결해 준다
순환 교착
변환 교착
오류 1205
보통, 클라이언트에서 확인 안 한다
그래서 서버에서 DBA 가 확인해 보아야 한다
dbcc traceon (3605) -- errorlog
dbcc traceon (1204) -- deadlock
dbcc traceon (1200) -- 상세 잠금 정보
dbcc tracestatus(-1)
-51-
순환 교착

서로 다른 개체를 서로 blocking 할 때
-52-
변환 교착

같은 대상에 대해 잠금을 변경하려고 할 때
set transaction isolation level repeatable read
begin tran
select * from titles where title_id = 'bu1032'
waitfor delay '0:00:05'
update titles set price = price * 2
where title_id = 'bu1032'
commit

해결책?
 updlock
 readcomitted
-53-
Deadlock 최소화








일방통행
짧게
폭좁게
set lock_timeout
read uncommitted
set deadlock_priority low
길을 넓힌다
주기적 모니터
-54-
세션6
-55-
연결된 서버

sp_addlinkedserver
-56-
openrowset
SELECT *
FROM OPENROWSET('SQLOLEDB',
'sqlinst';'sa';'',
'SELECT * FROM pubs.dbo.authors
ORDER BY au_lname, au_fname')
-57-
set xact_abort on
begin tran
성공
if
실패
성공
commit

결과는?
-58-
set xact_abort on

if @@error goto on_error:

주로 신경 쓸 오류
 515: not NULL 오류
 544: identity insert
 547: 제약 위반
 550: view WITH CHECK OPTION
-59-
분산 트랜잭션





MSDTC on
linked server 등록 (또는 openrowset)
set xact_abort on
begin distributed tran
 server1에서 작업
 server2에서 작업
commit
-60-
2 Phase commit

준비 단계





트랜잭션 관리자가 커밋 요청을 수신
관련된 모든 리소스 관리자에게 준비 명령
트랜잭션에 대한 로그를 디스크로 기록
각 리소스 관리자가 준비 단계를 완료하면 준비 성공
또는 실패 여부를 트랜잭션 관리자에게 반환
커밋 단계





각 리소스 관리자에게 커밋 명령
각 리소스 관리자가 커밋을 완료
모든 리소스 관리자가 성공적인 커밋을 보고
트랜잭션 관리자가 응용 프로그램에 성공을 알림
준비 실패를 보고한 리소스 관리자가 있으면 트랜잭션
관리자가 각 리소스 관리자에게 롤백 명령을 보낸다
-61-
세션7
-62-
트리거와 트랜잭션
INSERT …
=>
BEGIN TRAN
INSERT…
COMMIT

따라서 트리거 내에서 ROLLBACK 사용가능
-63-
트리거와 트랜잭션
트리거rollback.sql
create trigger trA1ins
on a1 for insert
as
print 'rollback'
rollback
go
begin tran
insert a1 values ('a')
commit
-- 동작 할까?
select * from a1
-64-
중첩 트리거
A
insert
trigger
B
update
trigger
C
rollback
-65-
트리거와 트랜잭션




트랜잭션 안에서 수행
따라서 불필요한 작업들을 트리거 안에 두지
말자
rollback 도 꼭 필요한 경우 아니면 사용말자
update trigger의 이중 부하
-66-
잠금과 인덱스 관계





인덱스 전혀 없을 때
넌클러스터 인덱스만
클러스터도
인덱스 없는 컬럼의 수정
인덱스 안에서의 잠금
sp_indexoption
index n lock.sql
-67-
커서와 잠금






기본적으로 다음 fetch 까지 잠금 유지
디폴트 update 가능 커서
optimistic lock
오류 16934:최적 병행성 검사에 실패했습니다.
이 커서 밖에서 행을 수정했습니다
where primaryKey = …  where current of
SCROLL_LOCKS option
-68-
잠금 수준 escaltion




한 트랜잭션의 lock count > 1250 or
한 인덱스/테이블 스캔 lock count > 765
잠금 관리자가 메모리 검사
전체 메모리의 40% 이상을 잠금에 사용한다면
RID/ KEY/ PAG lock  TAB lock
-69-
syslockinfo

select * from master..syslockinfo
-70-
sysprocesses

open_tran & sleeping
-71-
바운드 연결



둘 이상의 연결이 같은 트랜잭션과 잠금을 공유
sp_getbindtoken | srv_getbindtoken (ODS)
sp_bindsession
-72-
Application lock





잠금 정보 관리 되지 않는 잠금의 검사와 사용
SQL 서버는 이미 알고있는 잠금 항목(rid, key,
page, …)에 대해서만 잠금 처리
예를 들어 procA는 늘 단독으로만 사용되어야
한다면?
sp_getapplock
sp_releaseapplock
-73-
성능 모니터

perfmon
-74-