danpoong blog

프로시저와 비슷해 보이지만 일반 프로그래밍 언어에서 사용되는 함수와 갗이 복잡한 프로그래밍이 가능하며 return문에 의해 특정 값을 돌려주는 기능이 있음

select문에 포함되어 실행 가능하며 스칼라 함수는 exec로 실행 가능

시스템 함수

sql server 에서 제공하는 함수

사용자정의 스칼라 값 함수

리턴 값이 단일 값인 함수로서 text, ntext, image, cursor, timestamp를 제외한 모든 데이터 형식이 가능

사용자정의 테이블 값 함수

테이블 함수 라고도 하며 리턴 값이 테이블인 함수

인 라인 테이블 함수, 다중 문 테이블 함수가 있음

인 라인 테이블 함수

테이블을 돌려주는 함수이며 뷰와 비슷한 역할을 함

select문이 와서 결과 집합을 돌려줌

다중 문 테이블 함수

begin ~ end 로 정의되며 내부에 일련의 T-SQL을 이용해서 반환될 테이블에 행 값을 insert 하는 형식을 가짐

스키마 바운드 함수

테이블, 뷰 등이 수정되지 못하도록 설정한 함수

예를 들어 함수A가 테이블B, 뷰C 를 참조하고 있다면 이 테이블이나 뷰를 삭제하거나 열 이름을 바꾸지 못하게 함

함수 생성 시 with schemabinding 옵션을 주면 됨

사용자 정의 함수에서는 try~catch 사용이 불가하며 create, alter, drop또한 사용이 불가능함

함수 실행 중 오류가 발생하면 함수의 실행이 멈추고 값을 반환하지 않음

트랜잭션이 발생할 경우 개체에 접근할 수 없는 것을 블로킹이라고 함

만약 트랜잭션을 끝내지 않아 무한대로 기다리는 상황이 생길 수 있어 이것을 막기위해 타임아웃 옵션을 설정할 수 있음

select @@LOCK_TIMEOUT 로 조회 가능하며 -1 일 경우 무한대로 대기함

exec sp_who 프로시저로 블로킹 중인 프로세스 확인이 가능하며 kill프로세스로 해당 프로세스를 중단시킬 수 있음

set lock_timeout 1000 식으로 설정 가능하며 단위는 밀리초 이므로 1000 = 1초 임

타임아웃이 지난다고 트랜잭션이 취소되는 것은 아니며, 잠금이 걸린 데이터에 접근하는 쿼리를 취소하는 것임

두 잠금이 서로 상대가 풀리도록 대기하는 상태를 교착상태라고 함

서로 풀릴때까지 기다리느라 무한대로 멈추어야하지만 sql server에서 이런 상황을 자동으로 검색하여 한쪽을 희생시켜 해결 함 (검색 주기 디폴트 5초)

이 또한 시스템 자원을 소모하므로 성능을 위해 이런 상황을 만들지 않는게 좋음

되도록 트랜잭션 격히 수준을 read committed로 두고 하나의 트랜잭션에 너무 많은 쿼리를 넣지 않아야 함

분산 트랜잭션

서로 다른 서버에서 하나의 트랜잭션으로 데이터를 처리하는것

개념적으로는 하나의 트랜잭션이며 서버가 분리되어있다는 것만 차이 있음

begin tran 대신 begin distributed transaction을 사용하며 커밋은 동일하게 commit tran을 사용

분산 트랜잭션을 사용할떄는 xact_abort 옵션을 on으로 설정해야함

xact_abort를 on으로 설정하면 하나의 트랜잭션이 오류를 일으키면 전체 트랜잭션을 취소하므로 분산 트랜잭션에서는 꼭 ON으로 설정해야함 디폴트는 OFF임

또한 분산 트랜잭션을 사용하기 위해서는 MS DTC가 설치되고 가동되고 있어야 함

자동 커밋 트랜잭션

각 쿼리마다 자동으로 트랜잭션이 붙는 것

보통 사용하는 쿼리는 자동 커밋 트랜잭션이 작동 됨

명시적 트랜잭션

직접 begin, commit 을 사용하는 것

암시적 트랜잭션

타 데이터베이스와 호환을 위해 사용

자동으로 begin tran이 붙지만 commit tran이나 rollback tran 은 직접 써줘야 하는 것

set implicit_transaction on 을 사용하면 암시적 트랜잭션이 시작 됨

잠금

sql server에서 잠금이 중요한 이유는 일관성 떄문

잠금은 동시에 여러 사용자가 하나의 데이터에 접근할 경우 즉 트랜잭션 상황에서 데이터의 격리 수준을 말함

↑약한 격리 수준

read uncommitted (커밋되지 않은 읽기)

동시성은 높지만 일관성이 낮음

read committed (커밋된 읽기) <- sql server 2005 기본값

데이터 변경이 있는 트랜잭션 중에는 읽을 수 없음

update나 insert로 배타적 잠금 (exclusive lock)이 발생되어 dirty read 문제 해결

select에 의한 공유잠금(shared lock)일 경우 데이터 변경이 가능한 unrepeatable read 발생

repeatable read (반복된 읽기)

공유잠금 상태에서 update를 통한 데이터 변경이 불가

하지만 insert를 통한 새로운 데이터 입력은 가능 (phantom read)

snapshot

트랜잭션중인 테이블에 데이터를 입력하면 tempdb에 임시로 저장하고 커밋 후에 원래 테이블에 입력함

serializable (직렬화 가능)

phantom read를 해결

일관성은 높지만 동시성이 낮음

↓강한 격리 수준

*여러 사용자가 동시에 하나의 데이터에 접근하면 일어나는 문제

-dirty read (커밋되지 않은 데이터 읽기)

메모리 (더티 페이지)에 변경된 데이터를 읽는 것

디스크에 저장되지 않고 데이터가 롤백된다면 잘못된 데이터를 읽은것이 됨

-unrepeatable read (반복되지 않은 읽기)

데이터 변경이 있는 트랜잭션 중에는 읽을 수 없음

배타적 잠금 (exclusive lock)이 발생되어 더티리드 문제 해결

select에 의한 공유잠금(shared lock)일 경우에도 데이터 변경이 가능

-phantom read (가상 읽기)

공유잠금 상태에서 insert를 통한 새로운 데이터 입력이 가능

잠금이 걸리는 리소스

행 단위 부터 데이터베이스 단위 까지 여러 잠금 범위가 있음

단위가 작을수록 동시성은 좋지만 관리 할 부분이 많아져 오버헤드가 증가됨

단위가 크다면 반대로 관리 할 잠금 수가 줄어 오버헤드는 줄지만 동시성이 나빠짐

잠금 리소스 종류

RID : 행 식별자는 단일 행을 잠그는 데 사용

KEY : 인덱스 내의 행 잠금은 serializable 격리 수준에서 사용

PAGE : 8KB 크기의 데이터 페이지나 인덱스 페이지

EXTENT : 8개의 페이지가 연속된 것

HORT : 클러스터형 인덱스가 없는 테이블에서 데이터 페이지나 인덱스를 보호하는 잠금

TABLE : 데이터와 인덱스가 포함된 전체 테이블

FILE : 데이터베이스 파일

APPLICATION : 응용프로그램이 지정한 리소스

METADATA

ALLOCATION_UNIT : 할당 단위

DATABASE : 전체 데이터베이스

잠금 모드 종류

서로 같이 작동할 수 없는 것들도 있고, 다른 모드를 수정하는 것도 있음

공유잠금 (S:shared lock) : select같은 데이터 읽기 작업에 사용, 공유잠금 끼리는 호환됨 (동시에 같은 데이터 select 가능)

배타잠금 (X:exclusive lock) : 데이터 수정작업 (insert, update, delete)에 사용, 다른잠금과 호환 안됨, 하나의 리소스에 여러 작업을 못하게 방지

업데이트잠금 (U:update lock) : 업데이트할 수 있는 리소스에 사용, 여러 사용자가 업데이트할 때 발생하는 교착상태 방지

의도잠금 (intent lock) : 잠금 계층 구조를 만드는 데 사용, 의도공유 의도배타 의도배타공유 등이 있다

스키마잠금 ( schema lock) : 테이블 스키마에 종속되는 작업이 실행될 때 사용, 스키마수정, 스키마안정성 잠금이 있음

대량 업데이트 잠금 (bulk updqte lock) : 데이터를 테이블로 대량 복사하는 경우와 TABLOCK힌트가 지정된 경우 사용

키 범위 잠금 : serializable 트랜잭션 격리 수준을 사용할 때 쿼리가 읽는 행 범위를 보호