Início / dba / Perguntas / 20399
Accepted
ErikE
Asked: 2012-07-05 14:09:38 +0800 CST

Tabela de fila FIFO para vários trabalhadores no SQL Server

  • 772

Eu estava tentando responder à seguinte pergunta do stackoverflow:

Depois de postar uma resposta um tanto ingênua, pensei em colocar meu dinheiro onde estava minha boca e realmente testar o cenário que estava sugerindo, para ter certeza de que não estava mandando o OP em uma caça ao ganso selvagem. Bem, acabou sendo muito mais difícil do que eu pensava (não é surpresa para ninguém, tenho certeza).

Aqui está o que eu tentei e pensei:

  • Primeiro tentei um TOP 1 UPDATE com um ORDER BY dentro de uma tabela derivada, usando ROWLOCK, READPAST. Isso gerou impasses e também processou itens fora de ordem. Deve ser o mais próximo possível do FIFO, exceto erros que exijam a tentativa de processar a mesma linha mais de uma vez.

  • Em seguida, tentei selecionar o próximo QueueID desejado em uma variável, usando várias combinações de READPAST, UPDLOCK, HOLDLOCKe ROWLOCKpara preservar exclusivamente a linha para atualização por essa sessão. Todas as variações que experimentei sofriam dos mesmos problemas de antes, bem como, para certas combinações com READPAST, reclamando:

    Você só pode especificar o bloqueio READPAST nos níveis de isolamento READ COMMITTED ou REPEATABLE READ.

    Isso foi confuso porque foi READ COMMITTED. Já passei por isso antes e é frustrante.

  • Desde que comecei a escrever esta pergunta, Remus Rusani postou uma nova resposta para a pergunta. Eu li seu artigo vinculado e vejo que ele está usando leituras destrutivas, já que ele disse em sua resposta que "não é realisticamente possível manter os bloqueios durante as chamadas da web". Depois de ler o que seu artigo diz sobre pontos de acesso e páginas que exigem bloqueio para fazer qualquer atualização ou exclusão, temo que, mesmo que eu fosse capaz de descobrir os bloqueios corretos para fazer o que estou procurando, não seria escalável e poderia não lidar com simultaneidade massiva.

Agora eu não tenho certeza para onde ir. É verdade que manter bloqueios enquanto a linha é processada não pode ser alcançado (mesmo que não suporte tps altos ou simultaneidade massiva)? o que estou perdendo?

Na esperança de que pessoas mais inteligentes do que eu e pessoas mais experientes do que eu possam ajudar, abaixo está o script de teste que eu estava usando. Ele voltou para o método TOP 1 UPDATE, mas deixei o outro método, comentado, caso você queira explorar isso também.

Cole cada um deles em uma sessão separada, execute a sessão 1 e, em seguida, rapidamente todas as outras. Em cerca de 50 segundos, o teste terminará. Veja as mensagens de cada sessão para ver o trabalho que ela fez (ou como falhou). A primeira sessão mostrará um conjunto de linhas com um instantâneo obtido uma vez por segundo, detalhando os bloqueios presentes e os itens da fila sendo processados. Às vezes funciona e outras vezes não funciona de jeito nenhum.

Sessão 1

/* Session 1: Setup and control - Run this session first, then immediately run all other sessions */
IF Object_ID('dbo.Queue', 'U') IS NULL
   CREATE TABLE dbo.Queue (
      QueueID int identity(1,1) NOT NULL,
      StatusID int NOT NULL,
      QueuedDate datetime CONSTRAINT DF_Queue_QueuedDate DEFAULT (GetDate()),
      CONSTRAINT PK_Queue PRIMARY KEY CLUSTERED (QueuedDate, QueueID)
   );

IF Object_ID('dbo.QueueHistory', 'U') IS NULL
   CREATE TABLE dbo.QueueHistory (
      HistoryDate datetime NOT NULL,
      QueueID int NOT NULL
   );

IF Object_ID('dbo.LockHistory', 'U') IS NULL
   CREATE TABLE dbo.LockHistory (
      HistoryDate datetime NOT NULL,
      ResourceType varchar(100),
      RequestMode varchar(100),
      RequestStatus varchar(100),
      ResourceDescription varchar(200),
      ResourceAssociatedEntityID varchar(200)
   );

IF Object_ID('dbo.StartTime', 'U') IS NULL
   CREATE TABLE dbo.StartTime (
      StartTime datetime NOT NULL
   );

SET NOCOUNT ON;

IF (SELECT Count(*) FROM dbo.Queue) < 10000 BEGIN
   TRUNCATE TABLE dbo.Queue;

   WITH A (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
   B (N) AS (SELECT 1 FROM A Z, A I, A P),
   C (N) AS (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) FROM B O, B W)
   INSERT dbo.Queue (StatusID, QueuedDate)
   SELECT 1, DateAdd(millisecond, C.N * 3, GetDate() - '00:05:00')
   FROM C
   WHERE C.N <= 10000;
END;

TRUNCATE TABLE dbo.StartTime;
INSERT dbo.StartTime SELECT GetDate() + '00:00:15'; -- or however long it takes you to go run the other sessions
GO
TRUNCATE TABLE dbo.QueueHistory;
SET NOCOUNT ON;

DECLARE
   @Time varchar(8),
   @Now datetime;
SELECT @Time = Convert(varchar(8), StartTime, 114)
FROM dbo.StartTime;
WAITFOR TIME @Time;

DECLARE @i int,
@QueueID int;
SET @i = 1;
WHILE @i <= 33 BEGIN
   SET @Now  = GetDate();
   INSERT dbo.QueueHistory
   SELECT
      @Now,
      QueueID
   FROM
      dbo.Queue Q WITH (NOLOCK)
   WHERE
      Q.StatusID <> 1;

   INSERT dbo.LockHistory
   SELECT
      @Now,
      L.resource_type,
      L.request_mode,
      L.request_status,
      L.resource_description,
      L.resource_associated_entity_id
   FROM
      sys.dm_tran_current_transaction T
      INNER JOIN sys.dm_tran_locks L
         ON L.request_owner_id = T.transaction_id;
   WAITFOR DELAY '00:00:01';
   SET @i = @i + 1;
END;

WITH Cols AS (
   SELECT *, Row_Number() OVER (PARTITION BY HistoryDate ORDER BY QueueID) Col
   FROM dbo.QueueHistory
), P AS (
   SELECT *
   FROM
      Cols
      PIVOT (Max(QueueID) FOR Col IN ([1], [2], [3], [4], [5], [6], [7], [8])) P
)
SELECT L.*, P.[1], P.[2], P.[3], P.[4], P.[5], P.[6], P.[7], P.[8]
FROM
   dbo.LockHistory L
   FULL JOIN P
      ON L.HistoryDate = P.HistoryDate

/* Clean up afterward
DROP TABLE dbo.StartTime;
DROP TABLE dbo.LockHistory;
DROP TABLE dbo.QueueHistory;
DROP TABLE dbo.Queue;
*/

Sessão 2

/* Session 2: Simulate an application instance holding a row locked for a long period, and eventually abandoning it. */
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE
   @QueueID int,
   @Time varchar(8);
SELECT @Time = Convert(varchar(8), StartTime + '0:00:01', 114)
FROM dbo.StartTime;
WAITFOR TIME @Time;
BEGIN TRAN;

--SET @QueueID = (
--   SELECT TOP 1 QueueID
--   FROM dbo.Queue WITH (READPAST, UPDLOCK)
--   WHERE StatusID = 1 -- ready
--   ORDER BY QueuedDate, QueueID
--);

--UPDATE dbo.Queue
--SET StatusID = 2 -- in process
----OUTPUT Inserted.*
--WHERE QueueID = @QueueID;

SET @QueueID = NULL;
UPDATE Q
SET Q.StatusID = 1, @QueueID = Q.QueueID
FROM (
   SELECT TOP 1 *
   FROM dbo.Queue WITH (ROWLOCK, READPAST)
   WHERE StatusID = 1
   ORDER BY QueuedDate, QueueID
) Q

PRINT @QueueID;

WAITFOR DELAY '00:00:20'; -- Release it partway through the test

ROLLBACK TRAN; -- Simulate client disconnecting

Sessão 3

/* Session 3: Run a near-continuous series of "failed" queue processing. */
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET XACT_ABORT ON;
SET NOCOUNT ON;
DECLARE
   @QueueID int,
   @EndDate datetime,
   @NextDate datetime,
   @Time varchar(8);

SELECT
   @EndDate = StartTime + '0:00:33',
   @Time = Convert(varchar(8), StartTime, 114)
FROM dbo.StartTime;

WAITFOR TIME @Time;

WHILE GetDate() < @EndDate BEGIN
   BEGIN TRAN;

   --SET @QueueID = (
   --   SELECT TOP 1 QueueID
   --   FROM dbo.Queue WITH (READPAST, UPDLOCK)
   --   WHERE StatusID = 1 -- ready
   --   ORDER BY QueuedDate, QueueID
   --);

   --UPDATE dbo.Queue
   --SET StatusID = 2 -- in process
   ----OUTPUT Inserted.*
   --WHERE QueueID = @QueueID;

   SET @QueueID = NULL;
   UPDATE Q
   SET Q.StatusID = 1, @QueueID = Q.QueueID
   FROM (
      SELECT TOP 1 *
      FROM dbo.Queue WITH (ROWLOCK, READPAST)
      WHERE StatusID = 1
      ORDER BY QueuedDate, QueueID
   ) Q

   PRINT @QueueID;

   SET @NextDate = GetDate() + '00:00:00.015';
   WHILE GetDate() < @NextDate SET NOCOUNT ON;
   ROLLBACK TRAN;
END

Sessão 4 e superior -- quantas quiser

/* Session 4: "Process" the queue normally, one every second for 30 seconds. */
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET XACT_ABORT ON;
SET NOCOUNT ON;

DECLARE @Time varchar(8);
SELECT @Time = Convert(varchar(8), StartTime, 114)
FROM dbo.StartTime;
WAITFOR TIME @Time;

DECLARE @i int,
@QueueID int;
SET @i = 1;
WHILE @i <= 30 BEGIN
   BEGIN TRAN;

   --SET @QueueID = (
   --   SELECT TOP 1 QueueID
   --   FROM dbo.Queue WITH (READPAST, UPDLOCK)
   --   WHERE StatusID = 1 -- ready
   --   ORDER BY QueuedDate, QueueID
   --);

   --UPDATE dbo.Queue
   --SET StatusID = 2 -- in process
   --WHERE QueueID = @QueueID;

   SET @QueueID = NULL;
   UPDATE Q
   SET Q.StatusID = 1, @QueueID = Q.QueueID
   FROM (
      SELECT TOP 1 *
      FROM dbo.Queue WITH (ROWLOCK, READPAST)
      WHERE StatusID = 1
      ORDER BY QueuedDate, QueueID
   ) Q

   PRINT @QueueID;
   WAITFOR DELAY '00:00:01'
   SET @i = @i + 1;
   DELETE dbo.Queue
   WHERE QueueID = @QueueID;   
   COMMIT TRAN;
END
sql-server locking

2 respostas

  1. Best Answer

    Você precisa de exatamente 3 dicas de bloqueio

    • READPAST
    • UPDLOCK
    • ROWLOCK

    Eu respondi isso anteriormente no SO: https://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition/940001#940001

    Como Remus diz, usar o service broker é melhor , mas essas dicas funcionam

    Seu erro sobre o nível de isolamento geralmente significa que a replicação ou NOLOCK está envolvida.

    • 12

  2. O servidor SQL funciona muito bem para armazenar dados relacionais. Quanto a uma fila de trabalho, não é tão bom. Veja este artigo que foi escrito para MySQL, mas também pode ser aplicado aqui. https://blog.engineyard.com/2011/5-subtle-ways-youre-using-mysql-as-a-queue-and-why-itll-bite-you

    • 1

relate perguntas