CodeBlack

SELECT 문 사용할 때 with(nolock)를 사용하는 경우가 종종 있다.

with(nolock)을 사용하는 이유는 트랜잭션 격리성 때문인데 해당 내용에 대해 정리 글

Transaction Isolation Level(트랜잭션 격리 수준)이란?

  - A 트랜잭션이 실행되고 있을 때 다른 B 트랜잭션의 접근 권한에 대한 수준을 이야기 합니다.

  - 트랜잭션 격리 수준 단계가 높아질수록 격리 수준이 높아지며 데이터 일관성은 높아지지만 동시성은 저하 됩니다.

Transaction Isolation 관련 문제점

  - Dirty Read : 커밋되지 않은 데이터를 읽어 해당 트랜잭션이 롤백 되면 오류 데이터를 읽을 수 있는 문제점

  - Non-Repeatable Read : 한 트랜잭션이 반복하여 Read 중일 때 다른 트랜잭션이 접근하여 데이터를 변경하여 커밋하여도 커밋된 결과가 반영 되지 않는 문제점

  - Phantom Read : 한 트랜잭션 안에서 일정 범위 레코드를 두번 이상 읽을 때, 첫번째 쿼리에서 없던 레코드가 두번째 쿼리에서 나타나는 문제점

Transaction Isolation Level 단계

  1. Read Uncommitted

        - 트랜잭션 실행 중 다른 트랜잭션이 접근 가능(트랜잭션이 격리되지 않음)

        - A트랜잭션이 데이터를 변경 중 일 때 B트랜잭션이 접근하여 커밋되기전 변경된 데이터를 읽을 수 있음.

        - 만약 커밋되지 않고 A트랜잭션이 롤백되면 B트랜잭션의 결과값은 잘못된 데이터가 됨(Dirty Read 발생)

        - Non-Repeatable Read 발생 가능

        - Phantom Read 발생 가능

  2.  Read Committed

        - 커밋되기 전 바뀐 데이터를 읽을 수 없음.(Dirty Read 발생 X)

        - Non-Repeatable Read 발생 가능

        - Phantom Read 발생 가능

  3.  Repeatable Read

        - 항상 한 트랜잭션의 일관성 있는 데이터 읽기를 보장(Dirty Read, Non-Repeatable Read 발생 X)

        - A트랜잭션 실행 중 B 트랜잭션에서 Insert가 발생 후 동일 조건의 Select문의 A트랜잭션과 B트랜잭션의 결과가 달라짐(Phantom Read 발생)

        - A트랜잭션이 실행 중일 때 다른 트랜잭션이 DB의 내용을 변경해도 서로 영향을 미치지 않는다.

  4.  Serializable

        - 가장 높은 격리 수준

        - 트랜잭션이 완료될 때까지 Select 문장이 사용하는 모든 데이터에 Shared Lock이 걸리므로 다른 트랜잭션이 접근 불가

        - Dirty Read, Non-Repeatable Read, Phantom Read 모두 발생 안함.

위 내용을 아래 표로 정리하였습니다.

* DB별 Default isolation

격리 수준과 with(nolock) 힌트의 관계

    -    With(nolock)힌트는 격리성 제한이 걸린 DataBase의 Read Uncommitted를 허용한다.(격리성 관련 문제점 발생)

    -    격리성 제약조건 있는 DB에 실시간으로 데이터를 확인할 때 주로 사용한다.

    -    데드락이나 교착상태가 발생하지 않는다.

    -     Rollback이 될 수 있는 데이터까지 읽기 때문에 데이터 일관성은 저하된다.

*with(nolock)과 유사한 with(readuncommitted)힌트도 있습니다~!

*본문에 사용된 이미지 출처 : https://lng1982.tistory.com/287

TABLE COLUMN 속성 변경

1. 제약조건이 없는 테이블 컬럼 변경 SQL 쿼리

2. 속성을 변경할 테이블 컬럼에 제약조건이 걸려 있는 경우

1번 쿼리를 실행하면 

Msg 5074, Level 16, State 1, Line 2
개체 'DF_SampleTable_Name'은(는) 열 'Name'에 종속되어 있습니다.
Msg 4922, Level 16, State 9, Line 2
하나 이상의 개체가 이 열에 액세스하므로 ALTER TABLE ALTER COLUMN Name이(가) 실패했습니다

위와 같은 에러메세지가 발생한다.

위 에러 메세지는 속성을 변경할 해당 컬럼에 "Primary key"가 설정되어 있거나 "기본값 또는 바인딩" 또는 "그 외 제약 조건"이 설정되어 있어서 발생하는 경우이다.

위 에러 메세지를 해결하기 위해 해당 컬럼에 적용된 제약조건을 제거하고 컬럼 속성을 변경 또는 삭제하면 된다.

먼제 삭제할 제약 조건의 이름을 확인하기 위해 Table에 걸린 제약 조건 확인 SQL 쿼리를 실행한다.

위 쿼리로 Table에 설정된 Key를 확인할 수 있다.

기본값 또는 바인딩 제약 조건명은 컬럼 변경 SQL문을 실행할 때 발생하는 오류 메세지에서 확인 할 수 있다. 위 오류 메세지에서는 "DF_SampleTable_Name" 이 Name컬럼에 걸린 기본값 또는 바인딩을 설정한 제약 조건의 이름이다.

제약 조건 삭제 SQL 쿼리

위 쿼리로 제약조건을 제거 할 수 있다.

제약 조건을 제거하면 1번에서 소개한 쿼리문을 실행하여 테이블 컬럼 속성을 변경할 수 있다.

* 단 컬럼 내 데이터가 변경할 데이터 타입으로 형식이 아니면 컬럼 데이터 타입 속성 변경이 실패한다.

Database 살펴 볼 때 내부 테이블들의 모든 정보를 알고 싶은 경우가 있다.

각 테이블을 조회하는 노가다 성 작업으로 해결할 수 도 있지만 테이블의 수가 많을 경우는 많은 시간이 소비된다.

다행히 한번에 모든 테이블 정보를 볼 수 있는 sql 쿼리문이 있다.

위 쿼리문을 동작하면 Database 내 모든 테이블 컬럼 정보를 확인할 수 있다.

*Microsoft SQL Server Management Studio에서 실행.

동적쿼리

- 코드 실행 시점에 SQL 쿼리문이 동적으로 구성되고 실행되는 쿼리

- Stored Procedure를 생성할 때 필요에 따라 동적 쿼리로 작성

- 동적 쿼리 실행 SQL 문자열에 값이 바뀌는 변수를 넣어 쿼리문을 작성하게 되면 SP가 새로 캐싱되어 재사용성을 떨어트림

정적쿼리

- 변수에 문자열을 대입하여 쿼리문을 작성하는 것이 아닌 일반적으로 작성된 SQL 쿼리

- 정적 쿼리로 작성된 Stored Procedure는 새로 캐싱되지 않아 재사용성을 떨어트리지 않는다.

* 위 내용을 요약하자면 작성한 쿼리가 어떠한 상황에서도 변경되지 않는 쿼리가 정적쿼리가 될 것이고 입력값이나 특정 상황에 따라 쿼리문이 변경될 수 있는 쿼리가 동적쿼리이다.

동적쿼리의 실행

- 동적쿼리는 EXEC()함수를 사용하거나 SP_EXECUTESQL을 사용하여 실행한다.

Stored Procedure(저장 프로시저) < 동적쿼리로 작성된 SP 예제가 있으니 참고

MS SQL Stored Procedure

저장 프로시저란?

- SQL Query문들을 하나의 함수처럼 실행하기 위한 Query의 집합

장점 

1. 하나의 요청으로 여러 SQL 쿼리를 실행하여 네트워크 부하 감소
2. 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 함으로 처리 시간 감소
3. 최초 사용한 SP는 캐싱이 되어 재사용이 용이
4. 사용자에게 테이블 접근 권한을 대신 프로지서 접근 권한을 주어 DataBase 보안성 강화
5. 모듈식 프로그래밍 가능

위와 같이 SSMS(2014) 에서 클릭하면 아래와 같은 파일이 생긴다

각 매개 변수칸에 입력 값을 넣어주어 실행하면 SP가 만들어 진다.

예제 SP를 실행하기 위한 table 셋팅

위 쿼리를 실행하면 테이블을 만들고 만들어진 테이블 내용을 확인할 수 있다.

동적쿼리와 sp_executesql을 이용한 입력 변수와 출력변수가 있는 SP 예제

- @input의 입력 값의 유무에 따라 WHERE절을 @Sql String에 추가하여 Sql문이 새로 캐싱 되는 SP

[MS-SQL SP_EXECUTESQL]

@stmt

- 유니코드 문자열이나 SQL쿼리문을 일괄 처리

- @stmt 유니코드 상수 또는 문자열만 가능

- N'문자열'은 가능하지만 '문자열'은 불가능

@params

- @stmt에 포함된 몯느 매개변수의 정의를 포함하는 유니코드 문자열

- @stmt와 동일하게 유니코드 문자열이나 상수만 가능

- 출력 변수일 경우 [OUT|OUTPUT] 명시

@param1...n

- 매개변수 문자열에 정의 된 첫번째 매개변수의 값

- @stmt에 제공되는 모든 매개변수에 대해 제공되는 매개변수 값이 있어야 한다

*참고문서 https://docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017