행복하고 소박하게 살 수 있는 길

SELECT * FROM v$resource_limit;

오라클에서 현재 세션 수 확인

SQL Server Management Studio(SSMS)의 여러 버전 중 정확히 버전인지는 기억이 나지 않는다. 2005는 되는 걸로 기억하고... 개체 탐색기를 통해서 SP를 찾아보려하는데 숫자가 너무 많아서 아예 에러메시지를 뱉으며 표시가 안되는 경우가 있었다. 우선 개체탐색기 정보 탭에서는 탐색이 가능해서 그걸로 확인했다.

이래저래 귀찮으니 그냥 시스템SP의 도움을 받아 쿼리로 직접 SP의 내용을 까보자.

sp_helptext @objname='찾고자하는SP명'

이 방법이 훨씬 편하다. SP 명만 정확히 넣으면 스크롤하면서 SP명을 쳐다볼 필요도 없다.

ORACLE에서 함수 내용 확인하는 법.

SELECT text from user_source where name = upper('function_name') order by line

-- JOIN UPDATE
UPDATE ORDERS
SET OrderDate = getdate()
FROM ORDERS A INNER JOIN [ORDER DETAILS] B ON A.ORDERID = B.ORDERID
WHERE B.DISCOUNT > 0.1

-- JOIN DELETE
DELETE TABLE_MAIN
FROM TABLE_MAIN A
INNER JOIN TABLE_JOIN B ON A.SEQNUM = B.SEQNUM AND B.PROGBN = 'Y'

1) NOLOCK (= READUNCOMMITTED)

MS-SQL 서버의 경우 데이터의 일관성을 보장하기 위해 SELECT 작업을 할떄도 공유잠금(S)을 설정합니다.

이러한 경우 SELECT 하는 동안에는 동일한 대상(행,페이지, 테이블 등)에 다른 spid에서 배타적 잠금(X)을 설정할 수 없습니다.

하지만, 이러한 특성 때문에 동시사용자가 많은 환경에서 차단(Blocking) 현상이 발생할 가능성이 있습니다.

즉,

1) 배타적 잠금이 걸린 대상에 대해서는 공유잠금(S)을 설정할 수 없기 때문에 차단이 발생하는 경우와,

2) 공유잠금이 걸린 대상에 배타적 잠금을 설정하는 경우가 모두 차단현상이 발생하는 문제가 발생합니다.

이를 해결하기 위해 SELECT 쿼리의 FROM Clause 다음의 각 테이블에 NOLOCK 옵션을 사용하게 되면 SELECT 쿼리를 할 때 공유잠금을 설정하지 않고 데이터를 읽게 됩니다. 이를 통해, 위의 두 가지 경우 차단현상이 발생하지 않고 작업을 수행할 수 있습니다.

2) ROWLOCK

SQL Server는 기본적으로 행 단위 잠금을 사용합니다. 다만, 내부적인 기준에 따라 행 단위 잠금보다 더 상위단위(페이지, 테이블) 대상에 대해서 잠금을 설정해야 한다면 페이지잠금, 테이블잠금을 자동적으로 승격하여 사용하게 됩니다. 이러한 경우, 특별하게 사용자가 이를 통제하여 행단위잠금을 강제하기 위해서 사용하는 옵션입니다. 이 옵션은 채번과 같은 매우 고유한 업무에 대해서만 제한적으로 사용되어야 합니다.

3)UPDLOCK

UPDLOCK은 SELECT하는 대상에 대해 곧바로 배타적잠금을 설정할 예정인 경우, SELECT 하는 대상에 다른 세션에서 배타적 잠금을 설정하지 못하도록 통제하기 위해 사용합니다.

즉,

1) spid 51 : SELECT * FROM 테이블

2) spid 52 : UPDATE 테이블 SET ...

3) spid 51 : UPDATE 테이블 SET ...

이러한 경우 51번의 업데이트 작업은 52번의 업데이트 작업이 완료될 때까지 차단되고 대기해야 합니다.

이때,

1) spid 51 : SELECT * FROM 테이블 WITH (UPDLOCK)

2) spid 52 : UPDATE 테이블 SET ...

3) spid 51 : UPDATE 테이블 SET ...

를 사용하면, 52번 업데이트 작업이 51번에서 설정된 UPDLOCK으로 인해 차단되고, 3번 UPDATE 작업은 계속하여 진행할 수 있게 됩니다.

이러한 잠금과 관련된 옵션은 데이터 일관성과 병행성이라는 두 마리 토끼를 잡기 위해 노력하면서, 데드락과 같은 문제가 발생하지 않도록 하기 위해, 각 업무환경에 따라 매우 주의하여 사용되어져야 합니다.

쿼리분석기의 단축키

조금 길긴 하지만.. 알아두면 편한것들은 파란색으로 처리하였습니다.. ^^*

SELECT * FROM DBO.SYSOBJECTS
WHERE xTYPE = 'U'
AND [NAME] LIKE '%검색어%' -- 테이블명을 LIKE 조건으로 검색
ORDER BY [NAME]

xTYPE의 종류

AF : 집계함수

C : CHECK 제약조건

D : DEFAULT 제약조건 혹은 독립실행형

F : FOREIGN 제약조건

PK : Primay Key 제약조건

P : Stored Procedure

FN : SQL 스칼라함수

S : 시스템 기본 테이블

U : 테이블(사용자정의)

UQ : UNIQUE 제약조건

V : 뷰

SELECT * 
FROM INFORMATION_SCHEMA.TABLES  AS TBL
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS CLM ON TBL.TABLE_NAME = CLM.TABLE_NAME 
WHERE COLUMN_NAME LIKE '%검색어%' AND TABLE_TYPE = 'BASE TABLE' 
-- 컬럼명으로 검색하여 테이블 정보 출력

SELECT DISTINCT B.NAME FROM SYSCOLUMNS A, SYSOBJECTS B
WHERE A.ID = B.ID
AND B.XTYPE = 'U' AND A.NAME LIKE '%컬럼명%' -- 해당 컬럼명이 포함된 테이블 이름을 반환

SELECT DISTINCT O.NAME FROM DBO.SYSOBJECTS O
INNER JOIN DBO.SYSCOMMENTS C ON O.ID = C.ID
WHERE O.XTYPE = 'P' AND C.TEXT LIKE '%KEYWORD%'; -- SP 내에서 내용 검색

1. 주석 작성 규칙
1. 상단 주석 작성 방법
1. 작성자, 작성일자, 내용, 리턴값을 기입합니다.
2. 모든 SP는 int 타입의 값을 리턴합니다.
   만약, 정의하지 않는다면 암묵적으로 0이 반환되는데... 이 값을 SP의 실행 결과 또는 에러 번호로 활용하는 것이 좋습니다.
3. description 항목은 SP가 CREATE될 때, DDL트리거에 의해 확장 속성으로 자동 추가됩니다.
   
   
   /**

   author : doeyull.kim

   e-mail : purumae@gmail.com

   created date : 2009-03-31

   description : 기사카테고리를추가합니다.

   return value :

   0 = 에러가없습니다.

   1 = 트랜잭션을Commit할수없는상태입니다. 트랜잭션을Rollback합니다.

   100 = 시스템에러가발생하였습니다. dbo.ErrorLogs 테이블을조회하세요.
   101 = 상위기사카테고리가존재하지않습니다.
   

   **/
   

   
   
2. 파라미터 주석 작성 방법
1. 파라미터 오른쪽에 --// 로 시작하는 한 줄 주석을 작성합니다.
2. 작성된 주석은 SP가 CREATE될 때, DDL트리거에 의해 확장 속성으로 자동 추가됩니다.
   
   
         @intParentCategoryID int = NULL --// 상위기사카테고리일련번호.

       , @nvcCategoryName nvarchar(100) --// 기사카테고리이름.

       , @intCategoryID int = NULL OUTPUT --// 기사카테고리일련번호.

   
   
3. 로직 flow를 설명하기 위한 주석 작성 방법
1. sp_helpmodule을 통해 출력되는 문서에는 Flow블럭이 있는데, 이 곳에 출력하기 위한 주석 작성 규칙입니다.
2. /**_ 로 시작해서 */로 끝 맺습니다.
3. /**_ 바로 뒤에 #을 사용하여 번호를 매기게 됩니다 (필수).
   번호를 들여쓸때는, 들여쓰는 depth만큼 #을 반복합니다.
   
   

   /**_# Rollback and return if inside an uncommittable transaction.*/

   /**_# 회원정보를쿼리합니다.*/

   /**_## 회원정보가존재하지않으면4010105를리턴합니다.*/

   /**_# 일반회원계정이아니면, 4010152를리턴합니다.*/

   /**_## 14세미만내국인회원가입신청이후보호자동의가완료되지않은상태이면4010112를리턴합니다.*/

   /**_## 외국인회원가입신청이후여권확인이완료되지않은상태이면4010120를리턴합니다.*/

   /**_## 사용자추가가제한된회원이면, 4010122를리턴합니다.*/

   /**_# 회원이가진사용자ID 개수를쿼리하여, 현재3개이상이라면4010111을리턴합니다.*/

   /**_# 제재된회원이면, 4010153을리턴합니다.*/

   /**_# User를추가합니다.*/

   /**_## [Users] 테이블에INSERT합니다.*/

   /**_### 이미존재하는User ID이면4010106을리턴합니다.*/

   /**_## [UserInformations] 테이블에INSERT합니다.*/

   /**_### 이미존재하는닉네임이면4010110을리턴합니다.*/

   /**_### 이미등록된이메일이면4010121을리턴합니다.*/

   /**_# 가입약관동의를기록합니다.*/

   /**_# 메일수신에동의한경우메일링리스트에추가합니다.*/

   /**_## [MailingLists] 테이블에INSERT합니다.*/

   /**_## [MailingListLogs] 테이블에INSERT합니다.*/

   
   
   
   소스 중간 중간에 위와 같이 주석을 작성했다면, sp_helpmodule에 의해 아래와 같이 출력됩니다.
   
   

   1. Rollback and return if inside an uncommittable transaction.

   2. 회원정보를쿼리합니다.

       1. 회원정보가존재하지않으면4010105를리턴합니다.

   3. 일반회원계정이아니면, 4010152를리턴합니다.

       1. 14세미만내국인회원가입신청이후보호자동의가완료되지않은상태이면4010112를리턴합니다.

       2. 외국인회원가입신청이후여권확인이완료되지않은상태이면4010120를리턴합니다.

       3. 사용자추가가제한된회원이면, 4010122를리턴합니다.

   4. 회원이가진사용자ID 개수를쿼리하여, 현재3개이상이라면4010111을리턴합니다.

   5. 제재된회원이면, 4010153을리턴합니다.

   6. User를추가합니다.

       1. [Users] 테이블에INSERT합니다.

           1. 이미존재하는User ID이면4010106을리턴합니다.

       2. [UserInformations] 테이블에INSERT합니다.

           1. 이미존재하는닉네임이면4010110을리턴합니다.

           2. 이미등록된이메일이면4010121을리턴합니다.

   7. 가입약관동의를기록합니다.

   8. 메일수신에동의한경우메일링리스트에추가합니다.

       1. [MailingLists] 테이블에INSERT합니다.

       2. [MailingListLogs] 테이블에INSERT합니다.

   
   
2. EXECUTE AS 'SPExecutor'
1. SP가 실행될 때의 보안 컨텍스트를 SPExecutor로 강제 지정합니다.
2. Sequel Safe를 설치하면, SPExecutor라는 Login 및 User가 추가됩니다. (db_owner 역할입니다.)
3. Application Server가 데이터베이스에 연결할 때 사용하는 Login의 권한은 매우 제한적인데, Sequel Safe에서는 모든 SP에 대한 Execute 권한 뿐입니다. 문제는 SP에서 동적 Query를 사용하거나 DDL문을 사용할 때 발생하는데, 이런 제약을 피하기 위해 db_owner 역할에 속하는 SPExecutor 사용자를 "가장"하도록 합니다.
   
   
   WITH EXECUTE AS 'SPExecutor'
   
   
   
3. 실행 환경 설정 (SET 옵션)
1. SET NOCOUNT ON
   affected row의 수를 클라이언트에 전달하지 않는 옵션입니다.
   성능에 큰 영향이 있으므로, 반드시 설정하셔야 합니다.
   
   

   SET NOCOUNT ON;

   
   
2. SET XACT_ABORT ON
   SP 실행 중 run time 에러가 발생하면, 트랜잭션을 롤백하는 옵션입니다.
   에러 핸들링 정책과 밀접한 관계가 있으며, Sequel Safe이 제시하는 에러 핸들링 방법을 사용하려면 이 옵션도 필수 입니다.
   
   
   SET XACT_ABORT ON;
   
   
4. 항상 선언하는 변수 @intReturnValue
1. SP의 리턴 값을 저장하기 위한 변수로, 모든 SP에서 맨 처음 선언하는 변수입니다.
   
   
   DECLARE @intReturnValue int;
   
   
   
5. 변수 명명 규칙
1. 헝거리안 표기법을 사용합니다.
   
   

   데이터 타입	접두사	예
   char	chr	@chrSSN
   varchar	vch	@vchTitle
   nchar	nch	@nchName
   nvarchar	nvc	@nvcSubject
   datetime	dtm	@dtmIssuedDate
   smalldatetime	sdt	@sdtStartDate
   tinyint	iny	@inyBankCode
   smallint	ins	@insArticleCount
   int	int	@intCustomerSN
   bigint	inb	@inbCyberCashAmount
   numeric / decimal	dec	@decProfit
   money	mny	@mnyRealCashAmount
   binary	bin	@binPath
   varbinary	vbi	@vbiPassword
   bit	bit	@bitCompletedFlag
   timestamp	tsp	@tspRowVersion
   uniqueidentifier	guid	@guidSubscriptionKey
   xml	xml	@xmlPart
   table	tbl	@tblNumbers
   date	dat	@datLogDate
   datetime2	dt2	@dt2BeginDate
   datetimeoffset	dto	@dtoBasisDate
   time	tim	@timLoginTime
   geography	geg	@gegLocation
   geometry	gem	@gemLocation
   hierarchyid	hid	@hidBBSID

   
   
6. Orphaned Transaction 처리를 위한 구문
1. orphaned transaction 이란... 트랜잭션이 commit도 rollback도 되지 못한 상태로 있게 되는 매우 비 정상적인 상황입니다.
   XACT_ABORT 옵션이 켜져 있어도, 207이나 208에러와 같이 자동 롤백이 되지 않는 종류의 에러가 발생하면 orphaned transaction이 생길 수 있는데... 이런 상황이 되면 블럭킹으로 시스템 장애가 올 수 있기 때문에 이에 대비한 루틴이 모든 SP의 상단에 필요합니다.
2. XACT_STATE() 함수가 반환하는 값이 -1인 경우
   ☞ 트랜잭션이 열려있으나 commit 불가능한 상태임을 의미합니다.
   
   
   /**_# Rollback and return if inside an uncommittable transaction.*/

   IF XACT_STATE() = -1

   BEGIN

       SET @intReturnValue = 1;

       GOTO ErrorHandler;

   END

   
   
7. TRY ~ CATCH ~ 블럭
1. 로직을 수행하기 위한 구문은 TRY 블럭에 작성합니다.
2. TRY 블럭에서 run time 에러가 발생하면 CATCH 블럭의 구문이 실행됩니다. 그런데 이 에러를 잘 활용하면 우리는 그 중에서 예상 가능한 Logical한 에러를 골라 SP의 리턴값을 매핑할 수 있습니다.
   
   아래 예에서 Categories 테이블에 INSERT할 때 예상 가능한 에러는, 부모 카테고리 ID가 존재하지 않는 경우를 생각해 볼 수 있습니다.
   
   권고하는데, TRY 블럭에서 IF EXISTS()문으로 부모 카테고리 ID의 존재 여부를 확인하지 마십시오.
   그 대신... 샘플에서처럼 FOREIGN KEY를 설정해 두고, SP에서는 바로 INSERT 하십시오.
   
   부모 카테고리 ID가 존재한다면 에러 없이 INSERT가 수행될 것이고, 그렇지 않다면 547번 에러가 발생하여 CATCH 블럭이 실행될 것입니다.
   
   우리는 CATCH 블럭을 작성할 때, 위와 같이 예상 가능한 에러에 대해 핸들링 할 수 있습니다.
   
   즉, 에러번호가 547이고 위반한 Constraint의 이름이 FK_Categories_Categories 문자열을 포함한다면 SP가 101번을 리턴하도록 하는 것이죠.
   
   
   BEGIN TRY

       /**_# [Categories] 테이블에INSERT합니다.*/
       INSERT dbo.Categories (topCategoryID, parentCategoryID, categoryName)

       SELECT NULL, @intParentCategoryID, @nvcCategoryName, @intCategoryLevel

       FROM dbo.Categories WITH (NOLOCK)

       WHERE categoryLevel = @intCategoryLevel;

    

       SET @intCategoryID = SCOPE_IDENTITY();

       COMMIT TRANSACTION;

   END TRY

   BEGIN CATCH

       IF ERROR_NUMBER() = 547 AND ERROR_MESSAGE() LIKE N'%FK_Categories_Categories%'

           SET @intReturnValue = 101;

    

       GOTO ErrorHandler;

   END CATCH;




8. ErrorHandler 레이블
1. 보통은 CATCH 블럭에 의해 ErrorHandler 레이블로 이동할 것이지만, TRY 블럭에서 개발자가 직접 ErrorHandler 레이블로 GOTO 할 수 있습니다.
2. ErrorHandler 레이블은 @intReturnValue에 값이 정의 된 경우.. 그 값을 리턴하고 정상 종료하게 합니다.
   하지만, 예상하지 못한 시스템 에러가 발생할 때는 @intReturnValue의 값이 0이거나 NULL인 상황이 만들어 지는데... 이 경우 dbo.ErrorLogs 테이블에 에러 내용을 INSERT하고 에러를 Rethrow하게 됩니다.
3. 아래 소스에 등장하는 dbo.P_AddErrorLog는 지난 포스트에서 말씀드린 공통 모듈 중 하나입니다.
   앞 선 포스트의 내용대로 따로 오셨다면, 이미 테스트 DB에 존재하고 있습니다.
   
   

   ErrorHandler:

   IF XACT_STATE() <> 0

       ROLLBACK TRANSACTION;

    

   IF @intReturnValue IS NULL OR @intReturnValue = 0

       EXEC @intReturnValue = dbo.P_AddErrorLog;

    

   RETURN @intReturnValue;
   

   
   
   
9. 기타
1. INSERT 절에서 INTO를 생략합니다.
2. DELETE 바로 다음에 나오는 FROM을 생략합니다.

[출처] http://purumae.tistory.com/61