MS SQL 에서 SELECT TOP 1 사용에 관하여.

Question

안녕하세요.간단한것 같지만, 저에게는 너무 어려운 질문 하나만 드리겠습니다.MS SQL 환경 입니다.쿼리의 주 목적은 이러 합니다.한 테이블에서 업데이트된 날짜 중 제일 오래된 한 건을 뽑아,값을 수정 하고 PK값을 리턴 하면 됩니다.테이블 tbHISTORY 는INT_ID (INT 값 , pk )STR_STATUS (VARCHAR(1)). T 와 R 플러그 값 입니다. R &#61; 대기. T &#61; 진행)DT_UPDATE(DATE TIME) 칼럼으로 이루어져 있습니다.STR_STATUS 이 R 값 인 것중에서.DT_UPDATE 칼럼이 제일 오래된 1건 을 가져와서 STATUS 값을 T 값으로 변경만 하면 됩니다.그리고 마지막에 ID 값을 SELECT 해주면 됩니다.간단하게.DECLARE &#64;INT_ID INTSET &#64;INT_ID &#61; (SELECT TOP 1 INT_IDFROM tblHISTORYWHERE STR_STATUS &#61; 'R'ORDER Y DT_UPDATE DESC)UPDATE tblHISTORYSET STR_STATUS &#61; 'T'WHERE 1&#61;1AND INT_ID &#61; &#64;INT_IDSELECT &#64;INT_ID이렇게 프로시저 작성하면 될것 처럼 보입니다.하지만 이 프로시저를 동시에 여러프로그램에서 실행 하게 되면, 모두 동일한 INT_ID 를 받게 됩니다.이를 해결하고자, 테이블에 락을 걸어 주려고 하니 이 테이블이 다른곳에서도중요한 일을 하고 있어서 락을 걸면 전체 성능에 큰 영향을 끼쳐 락을 걸면 안되더군요.그래서 한방 쿼리로 하기로 했습니다.셀렉트 하고, 업데이트 하는 과정을 한방에 업데이트 하고 끝 내기로 하고 싶은데,..마지막에 SELECT &#64;INT_ID 를 할 방법을 모르겠습니다.UPDATE tblHISTORYSET STR_STATUS &#61; 'T'WHERE 1&#61;1AND INT_ID &#61; (SELECT TOP 1 INT_IDFROM tblHISTORYWHERE STR_STATUS &#61; 'R'ORDER Y DT_UPDATE DESC)위 쿼리에서 저 TOP 1 된 INT_ID 값을 어떻게 알 수 있나요 ?그리고 위와 같은 상황이라면 더 나은 다른 방법이 있을까요 ?

kenu,전독시 · Answer

SELECT TOP 1 INT_ID FROM tblHISTORY WHERE STR_STATUS = 'R' ORDER Y DT_UPDATE DESC이렇게 하면 볼 수 있지 않나요?

르매 · Answer

궁금하신 부분만 먼저 풀어 드리면.. OUTPUT절을 사용해서 수정된 행의 INT_ID를 가져올 수 있습니다. (SQL Server가 설치되어 있지 않아서 실행해보진 못했습니다.)DECLARE &#64;MODIFIED_ID TABLE ( INT_ID INT NOT NULL ); UPDATE tblHISTORY SET STR_STATUS &#61; 'T' OUTPUT INSERTED.INT_ID INTO &#64;MODIFIED_ID WHERE 1&#61;1 AND INT_ID &#61; ( SELECT TOP 1 INT_ID FROM tblHISTORY WHERE STR_STATUS &#61; 'R' ORDER Y DT_UPDATE DESC ) SELECT INT_ID FROM &#64;MODIFIED_ID;OUTPUT ... INTO ... 를 사용할 때는 변경 전/후의 데이터를 테이블 변수에만 담을 수 있기 때문에, 위와 같이 다시 테이블 변수에서 INT_ID를 SELECT하는 과정이 필요합니다.하지만, 위와 같이 UPDATE 쿼리 한 번을 쓴다고 해서 동시성 문제가 해결되지는 않습니다. UPDATE문이 tblHISTORY 테이블을 잠그기 전에.. 서브쿼리가 실행되어 TOP 1인 INT_ID를 가져오는데 이때 기본값인 Shared Lock이 걸립니다. 그럼 UPDATE 문이 동시에 실행되었을 때 역시나 동일한 INT_ID를 가져오게 되는 거죠.결국 처음 사용했던 TOP 1의 값을 변수에 담는 것과 별반 차이가 없어요.근본적으로 이런 설계나 방법을 사용하지 않는 것이 제일 좋으나.. 거기까지 작업하기 어렵다면.. 결국 잠금을 사용해야 합니다.어차피 SELECT한 1개의 행을 UPDATE가 끝날 때까지만 잠그는 동작이기 때문에 인덱스 구성만 잘 해 놓았다면 잠금으로 인한 성능 저하는 크지 않을 겁니다.필요한 인덱스 : STR_STATUS와 DT_UPDATE 컬럼의 순서로 복합 인덱스잠금 힌트 : WITH (UPDLOCK)위와 같은 방법을 사용한 아래 쿼리가 적당한 선택지로 보입니다.DECLARE &#64;INT_ID INT; BEGIN TRANSACTION; SELECT TOP 1 &#64;INT_ID &#61; INT_ID FROM tblHISTORY WITH (UPDLOCK) WHERE STR_STATUS &#61; 'R' ORDER BY DT_UPDATE DESC; UPDATE tblHISTORY SET STR_STATUS &#61; 'T' WHERE 1&#61;1 AND INT_ID &#61; &#64;INT_ID; COMMIT;

MS SQL 에서 SELECT TOP 1 사용에 관하여.

2개의 답변

기술 목록