[SQL] 여러 고객과 DB 큐으로 SQL Server를 사용

여러 고객과 DB 큐으로 SQL Server를 사용

큐 역할 테이블, 어떻게 최선의 구성 테이블 / 쿼리가 너무 여러 클라이언트가 동시에 대기열에서 처리 할 수 ​​있음을 감안할 때?

예를 들어, 아래 표는 작업자가 처리해야하는 명령을 나타냅니다. 작업자가 완료되면, 그것은 사실로 처리 된 값을 설정합니다.

| ID | COMMAND | PROCESSED |
|  1 | ...     | true      |
|  2 | ...     | false     |
|  3 | ...     | false     |

클라이언트는과 같이 작업 할 하나 개의 명령을 얻을 수 있습니다

select top 1 COMMAND 
from EXAMPLE_TABLE 
with (UPDLOCK, ROWLOCK) 
where PROCESSED=false;

다수의 근로자가있는 경우, 각 시도는 ID = 2 행을 얻을 수 있습니다. 비관적 잠금을 얻을 것이다 첫 번째, 나머지는 대기합니다. 그리고 그들 중 하나는 3 행, 등을 얻을 것이다

어떤 쿼리 / 구성은 각 작업자 클라이언트가 동시에 그들에 다른 행 각 작업을 얻을 수 것?

편집하다:

몇 가지 답변은에서 프로세스 상태를 기록하는 테이블 자체를 사용하는 방법에 변화를 제안한다. 나는 이것이 하나의 트랜잭션 내에서 가능하지 않을 것이라고 생각했다. (즉, TXN이 커밋 될 때까지 다른 노동자가 표시되지 않습니다 경우 상태를 업데이트하는 점은 무엇입니까?) 아마 제안은 다음과 같습니다

# start transaction
update to 'processing'
# end transaction
# start transaction
process the command
update to 'processed'
# end transaction

이 사람들이 일반적으로이 문제에 접근하는 방식인가? 가능하면 문제가 더는 DB에 의해 처리 될 것을 나에게 보인다.

해결법

1. ==============================

   1.난 당신이 대기열로 테이블을 사용 가서 좋습니다. 제대로 구현 된 큐는 분당 2 백만 인큐 / 디큐 작업 높이로 동시 사용자 및 서비스의 수천을 처리 할 수 ​​있습니다. SQL 서버 2005까지이 솔루션은 복잡했고 단일 트랜잭션에서 혼합 SELECT와 UPDATE를 포함하고 GBN에 의해 ​​링크 된 문서로 잠금 힌트 딱 맞는 조합을 제공합니다. Luckly OUTPUT 절의 출현과 SQL 서버 2005, 훨씬 더 우아한 해결책을 사용할 수 있습니다, 지금 MSDN은 OUTPUT 절을 사용하는 것이 좋습니다 :

   난 당신이 대기열로 테이블을 사용 가서 좋습니다. 제대로 구현 된 큐는 분당 2 백만 인큐 / 디큐 작업 높이로 동시 사용자 및 서비스의 수천을 처리 할 수 ​​있습니다. SQL 서버 2005까지이 솔루션은 복잡했고 단일 트랜잭션에서 혼합 SELECT와 UPDATE를 포함하고 GBN에 의해 ​​링크 된 문서로 잠금 힌트 딱 맞는 조합을 제공합니다. Luckly OUTPUT 절의 출현과 SQL 서버 2005, 훨씬 더 우아한 해결책을 사용할 수 있습니다, 지금 MSDN은 OUTPUT 절을 사용하는 것이 좋습니다 :

   기본적으로 퍼즐의 세 부분은 매우 동시 방식으로 작업이 순서를 바로 얻을 필요가있다 :

   1) 당신은 원자 큐에서해야합니다. 당신은 잠긴 행을 skipp 행을 발견하고 하나의 원자 작업으로 '대기열'로 표시해야하고, OUTPUT 절은 활동하기 시작하는 곳이다 :

   with CTE as (
     SELECT TOP(1) COMMAND, PROCESSED
     FROM TABLE WITH (READPAST)
     WHERE PROCESSED = 0)
   UPDATE CTE
     SET PROCESSED = 1
     OUTPUT INSERTED.*;
   

   2) 처리 된 열에 가장 왼쪽의 클러스터 된 인덱스 키를 사용하여 테이블을 구성해야합니다. ID가 기본 키가 사용 된 경우, 클러스터링 키의 두 번째 열로 이동. ID 열에에 클러스터되지 않은 키를 계속할지 여부를 논쟁은 열려 있지만, 난 강력하게 큐를 통해 어떤 차 클러스터되지 않은 인덱스를 가지고 있지 선호 :

   CREATE CLUSTERED INDEX cdxTable on TABLE(PROCESSED, ID);
   

   3) 당신은 다른 방법에 의해서가 아니라 큐에서이 표를 조회 할 수 없습니다. 픽 작업을하려고 또는 교착 상태에 가능성 리드 극적으로 처리량이 둔화 될 것 대기열로와 저장소로 모두 테이블을 사용하려고합니다.

   검색 요소에 원자 디큐, READPAST 힌트의 조합은 디큐 및 처리 비트에 기초하여 상기 클러스터 된 인덱스의 가장 왼쪽 키 높은 동시 부하 매우 높은 처리량을 보장한다.

2. ==============================

   2.여기에 내 대답 쇼 당신은 대기열로 테이블을 사용하는 방법 ... SQL 서버 프로세스 큐 경쟁 조건

   여기에 내 대답 쇼 당신은 대기열로 테이블을 사용하는 방법 ... SQL 서버 프로세스 큐 경쟁 조건

   당신은 기본적으로 "ROWLOCK, READPAST, UPDLOCK"힌트가 필요합니다

3. ==============================

   3.여러 클라이언트에 대한 귀하의 작업을 직렬화하려면, 당신은 단순히 응용 프로그램 잠금을 사용할 수 있습니다.

   여러 클라이언트에 대한 귀하의 작업을 직렬화하려면, 당신은 단순히 응용 프로그램 잠금을 사용할 수 있습니다.

   BEGIN TRANSACTION
   
   EXEC  sp_getapplock @resource = 'app_token', @lockMode = 'Exclusive'
   
   -- perform operation
   
   EXEC  sp_releaseapplock @resource = 'app_token'
   
   COMMIT TRANSACTION
   

4. ==============================

   4.오히려 명령의 상태를 정의하는 int를 사용할 수 가공에 대한 부울 값을 사용하는 것보다 :

   오히려 명령의 상태를 정의하는 int를 사용할 수 가공에 대한 부울 값을 사용하는 것보다 :

   1 = not processed
   2 = in progress
   3 = complete
   

   각 작업자는 다음 작업을 시작 2 가공, = 1 가공 업데이트에 다음 행을 얻을 것입니다. 가공 전체에서 작업을 3으로 업데이트되면이 방법은 다른 가공 결과의 확장을 허용, 예를 들어보다는 근로자가 '오류와 함께 완료'새로운 상태를 추가 할 수 있습니다 당신을 COMPLET '들을 성공적 완료'는 것을 정의하는 것

5. ==============================

   5.아마도 더 나은 옵션은 다음 버전 / 타임 스탬프 열와 함께 trisSate 처리 열을 사용합니다. 행이 처리에 따라 처리되거나 처리되지 않은 경우에 표시 될 다음 처리 열에있는 세 개의 값을 나타낸다.

   아마도 더 나은 옵션은 다음 버전 / 타임 스탬프 열와 함께 trisSate 처리 열을 사용합니다. 행이 처리에 따라 처리되거나 처리되지 않은 경우에 표시 될 다음 처리 열에있는 세 개의 값을 나타낸다.

   예를 들면

       CREATE TABLE Queue ID INT NOT NULL PRIMARY KEY,
       Command NVARCHAR(100), 
       Processed INT NOT NULL CHECK (Processed in (0,1,2) ), 
       Version timestamp)
   

   당신은 상위 1 처리되지 않은 행, underprocessing에 상태를 설정하고 일이 완료되면 처리에 상태 다시 설정 잡아. 버전 및 기본 키 컬럼에 대한 귀하의 업데이트 상태를하시오. 업데이트가 실패하면 누군가가 이미 있었다.

   당신은 클라이언트 다이가 그것을 처리하는 동안, 그것은 마지막 행에서 모습을 다시 시작할 수 있습니다 다음은 어디에서 시작하는 경우 그래서,뿐만 아니라 클라이언트 식별자를 추가 할 수 있습니다.

6. ==============================

   6.나는 테이블에 잠금 덤비는 멀리 것이다. 그냥 IsProcessing (비트 / 부울) 및 ProcessingStarted (날짜)처럼 두 개의 여분의 열을 만듭니다. 근로자가 충돌하거나 제한 시간 이후에 자신의 행을 업데이트하지 않는 경우에는 데이터를 처리하는 또 다른 작업자 시도 할 수 있습니다.

   나는 테이블에 잠금 덤비는 멀리 것이다. 그냥 IsProcessing (비트 / 부울) 및 ProcessingStarted (날짜)처럼 두 개의 여분의 열을 만듭니다. 근로자가 충돌하거나 제한 시간 이후에 자신의 행을 업데이트하지 않는 경우에는 데이터를 처리하는 또 다른 작업자 시도 할 수 있습니다.

7. ==============================

   7.한 가지 방법은 하나의 업데이트 문을 사용하여 행을 표시하는 것입니다. 당신이 where 절에서 상태를 읽고 설정 절에서 변경하면 행이 고정되기 때문에, 다른 프로세스는 사이에 올 수 있습니다. 예를 들면 :

   한 가지 방법은 하나의 업데이트 문을 사용하여 행을 표시하는 것입니다. 당신이 where 절에서 상태를 읽고 설정 절에서 변경하면 행이 고정되기 때문에, 다른 프로세스는 사이에 올 수 있습니다. 예를 들면 :

   declare @pickup_id int
   set @pickup_id = 1
   
   set rowcount 1
   
   update  YourTable
   set     status = 'picked up'
   ,       @pickup_id = id
   where   status = 'new'
   
   set rowcount 0
   
   return @pickup_id
   

   이 용도는 기껏해야 한 행을 업데이트 할 행 개수. 어떤 행이 발견되지 않은 경우, @pickup_id는 -1이됩니다.

from https://stackoverflow.com/questions/3641703/using-sql-server-as-a-db-queue-with-multiple-clients by cc-by-sa and MIT license