Início / dba / Perguntas / 125630
Accepted
Steffen Mangold
Asked: 2016-01-09 06:04:02 +0800 CST

INSERTs massivos bloqueando SELECTs

  • 772

Eu tenho um problema com uma quantidade enorme de INSERTs que estão bloqueando minhas operações SELECT.

Esquema

Eu tenho uma tabela assim:

CREATE TABLE [InverterData](
    [InverterID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,    
    [ValueA] [decimal](18, 2) NULL,
    [ValueB] [decimal](18, 2) NULL
    CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED 
    (
        [TimeStamp] DESC,
        [InverterID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON)
)

Eu também tenho este pequeno procedimento auxiliar, que me permite inserir ou atualizar (atualizar em caso de conflito) com o comando MERGE:

CREATE PROCEDURE [InsertOrUpdateInverterData]
    @InverterID bigint, @TimeStamp datetime
    , @ValueA decimal(18,2), @ValueB decimal(18,2)
AS
BEGIN
    MERGE [InverterData] AS TARGET
        USING (VALUES (@InverterID, @TimeStamp, @ValueA, @ValueB))
        AS SOURCE ([InverterID], [TimeStamp], [ValueA], [ValueB])
        ON TARGET.[InverterID] = @InverterID AND TARGET.[TimeStamp] = @TimeStamp
    WHEN MATCHED THEN
        UPDATE
        SET [ValueA] = SOURCE.[ValueA], [ValueB] = SOURCE.[ValueB]              
    WHEN NOT MATCHED THEN
        INSERT ([InverterID], [TimeStamp], [ValueA], [ValueB]) 
        VALUES (SOURCE.[InverterID], SOURCE.[TimeStamp], SOURCE.[ValueA], SOURCE.[ValueB]);
END

Uso

Agora executei instâncias de serviço em vários servidores que executam atualizações massivas chamando o [InsertOrUpdateInverterData]procedimento rapidamente.

Existe também um site que faz consultas SELECT na [InverterData]tabela.

Problema

Se eu fizer consultas SELECT na [InverterData]tabela, elas serão processadas em intervalos de tempo diferentes, dependendo do uso de INSERT das minhas instâncias de serviço. Se eu pausar todas as instâncias de serviço, o SELECT é extremamente rápido, se a instância executar uma inserção rápida, os SELECTs ficam muito lentos ou até mesmo um cancelamento de tempo limite.

tentativas

Eu fiz alguns SELECTs na [sys.dm_tran_locks]tabela para encontrar processos de bloqueio, como este

SELECT
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingText,
tl.request_mode

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Este é o resultado:

insira a descrição da imagem aqui

S = Compartilhado. A sessão de espera recebe acesso compartilhado ao recurso.

Pergunta

Por que os SELECTs estão bloqueados pelo [InsertOrUpdateInverterData]procedimento que está usando apenas comandos MERGE?

Tenho que usar algum tipo de transação com modo de isolamento definido dentro de [InsertOrUpdateInverterData]?

Atualização 1 (relacionada à pergunta de @Paul)

Baseie-se no relatório interno do servidor MS-SQL sobre as [InsertOrUpdateInverterData]seguintes estatísticas:

  • Tempo médio de CPU: 0,12ms
  • Média de processos de leitura: 5,76 por/s
  • Média de processos de gravação: 0,4 por/s

Com base nisso, parece que o comando MERGE está ocupado principalmente com operações de leitura que bloquearão a tabela!(?)

Atualização 2 (relacionada à pergunta de @Paul)

A [InverterData]tabela possui as seguintes estatísticas de armazenamento:

  • Espaço para dados: 26.901,86 MB
  • Contagem de linhas: 131.827.749
  • Particionado: verdadeiro
  • Contagem de partições: 62

Aqui está o conjunto de resultados sp_WhoIsActive (quase) completo:

SELECTcomando

  • dd hh:mm:ss.mss: 00 00:01:01.930
  • id_sessão: 73
  • wait_info: (12629ms)LCK_M_S
  • CPU: 198
  • blocking_session_id: 146
  • lê: 99.368
  • escreve: 0
  • estado: suspenso
  • open_tran_count: 0

[InsertOrUpdateInverterData]comando de bloqueio

  • dd hh:mm:ss.mss: 00 00:00:00.330
  • ID da sessão: 146
  • wait_info: NULL
  • CPU: 3.972
  • blocking_session_id: NULL
  • lê: 376,95
  • escreve: 126
  • estado: dormindo
  • open_tran_count: 1
sql-server sql-server-2008

2 respostas

  1. Best Answer

    Primeiro, embora ligeiramente não relacionado à questão principal, sua MERGEdeclaração está potencialmente em risco de erros devido a uma condição de corrida . O problema, em poucas palavras, é que é possível que vários threads simultâneos concluam que a linha de destino não existe, resultando em tentativas de inserção conflitantes. A causa raiz é que não é possível obter um bloqueio compartilhado ou atualizado em uma linha que não existe. A solução é adicionar uma dica:

    MERGE [dbo].[InverterData] WITH (SERIALIZABLE) AS [TARGET]

    A dica de nível de isolamento serializável garante que o intervalo de chaves onde a linha iria está bloqueado. Você tem um índice exclusivo para oferecer suporte ao bloqueio de intervalo, portanto, essa dica não terá um efeito adverso no bloqueio, você simplesmente obterá proteção contra essa possível condição de corrida.

    Questão principal

    Por que são SELECTsbloqueados pelo procedimento [InsertOrUpdateInverterData] que está usando apenas MERGEcomandos?

    Sob o nível de isolamento confirmado de leitura de bloqueio padrão, os bloqueios compartilhados (S) são obtidos durante a leitura de dados e normalmente (embora nem sempre) liberados logo após a conclusão da leitura. Alguns bloqueios compartilhados são mantidos até o final da instrução.

    Uma MERGEinstrução modifica os dados, portanto, adquirirá bloqueios S ou atualização (U) ao localizar os dados a serem alterados, que são convertidos em bloqueios exclusivos (X) antes de realizar a modificação real. Os bloqueios U e X devem ser mantidos até o final da transação.

    Isso é verdade em todos os níveis de isolamento, exceto o isolamento de instantâneo 'otimista' (SI) não - deve ser confundido com versão com leitura confirmada, também conhecida como isolamento de instantâneo com leitura confirmada (RCSI).

    Nada em sua pergunta mostra uma sessão esperando por um bloqueio S sendo bloqueado por uma sessão segurando um bloqueio U. Estas fechaduras são compatíveis . Qualquer bloqueio quase certamente está sendo causado pelo bloqueio em um bloqueio X retido. Isso pode ser um pouco complicado de capturar quando um grande número de bloqueios de curto prazo está sendo obtido, convertido e liberado em um curto intervalo de tempo.

    Vale a pena investigar o open_tran_count: 1no comando InsertOrUpdateInverterData. Embora o comando não tenha sido executado por muito tempo, você deve verificar se não há uma transação contendo (no aplicativo ou no procedimento armazenado de nível superior) que seja desnecessariamente longa. A melhor prática é manter as transações o mais curtas possível. Isso pode não ser nada, mas você definitivamente deve verificar.

    solução potencial

    Como Kin sugeriu em um comentário, você pode procurar habilitar um nível de isolamento de controle de versão de linha (RCSI ou SI) neste banco de dados. O RCSI é o mais usado, pois normalmente não requer tantas alterações no aplicativo. Uma vez ativado, o nível de isolamento de confirmação de leitura padrão usa versões de linha em vez de usar bloqueios S para leituras, portanto, o bloqueio SX é reduzido ou eliminado. Algumas operações (por exemplo, verificações de chave estrangeira) ainda adquirem bloqueios S sob RCSI.

    Esteja ciente, porém, de que as versões de linha consomem espaço tempdb, em termos gerais, proporcional à taxa de atividade de alteração e à duração das transações. Você precisará testar sua implementação completamente sob carga para entender e planejar o impacto do RCSI (ou SI) em seu caso.

    Se você deseja localizar o uso do controle de versão, em vez de habilitá-lo para toda a carga de trabalho, o SI ainda pode ser uma escolha melhor. Ao usar o SI para as transações de leitura, você evitará a disputa entre leitores e escritores, ao custo de os leitores verem a versão da linha antes de qualquer modificação simultânea iniciada (mais corretamente, a operação de leitura no SI sempre verá o estado confirmado de a linha no momento em que a transação do SI foi iniciada). Há pouco ou nenhum benefício em usar SI para as transações de gravação, porque os bloqueios de gravação ainda serão executados e você precisará lidar com quaisquer conflitos de gravação. A menos que seja isso que você quer :)

    Nota: Ao contrário do RCSI (que uma vez ativado se aplica a todas as transações em execução na leitura confirmada), o SI deve ser solicitado explicitamente usando SET TRANSACTION ISOLATION SNAPSHOT;.

    Comportamentos sutis que dependem de leitores bloqueando escritores (inclusive no código de gatilho!) Tornam o teste essencial. Veja minha série de artigos vinculados e os livros on-line para obter detalhes. Se você decidir pelo RCSI, certifique-se de revisar as Modificações de Dados em Isolamento de Instantâneo Confirmado de Leitura em particular.

    Por fim, você deve garantir que sua instância seja corrigida para o SQL Server 2008 Service Pack 4.

    • 12

  2. Humildemente, eu não usaria merge. Eu iria com IF Exists (UPDATE) ELSE (INSERT) - você tem uma chave agrupada com as duas colunas que está usando para identificar as linhas, então é um teste fácil.

    Você mencionou inserções MASSIVE e ainda fez 1 por 1 ... pensou em agrupar os dados em uma tabela de preparação e usar o poder do conjunto de dados SQL POWER OVERWHELMING para fazer mais de 1 atualização/inserção por vez? Como ter uma rotina de teste de conteúdo na tabela de preparação e pegar os 10.000 primeiros de cada vez, em vez de 1 de cada vez...

    Eu faria algo assim na minha atualização

    DECLARE @Set TABLE (StagingKey, ID,DATE)
INSERT INTO @Set
UPDATE Staging 
SET InProgress = 1
OUTPUT StagingKey, Staging.ID, Staging.Date
WHERE InProgress = 0
AND StagingID IN (SELECT TOP (100000) StagingKey FROM Staging WHERE inProgress = 0 ORDER BY StagingKey ASC ) --FIFO

DECLARE @Temp 
INSERT INTO @TEMP 
UPDATE [DEST] SET Value = Staging.Value [whatever]
OUTPUT INSERTED.ID, DATE [row identifiers]
FROM [DEST] 
JOIN [STAGING]
JOIN [@SET]; 
INSERT INTO @TEMP 
INSERT [DEST] 
SELECT
OUTPUT INSERT.ID, DATE [row identifiers] 
FROM [STAGING] 
JOIN [@SET] 
LEFT JOIN [DEST]

UPDATE Staging
SET inProgress = NULL
FROM Staging 
JOIN @set
ON @Set.Key = Staging.Key
JOIN @temp
ON @temp.id = @set.ID
AND @temp.date = @set.Date

    Você provavelmente poderia executar vários trabalhos exibindo os lotes de atualização e precisaria de um trabalho separado executando uma exclusão lenta

    while exists (inProgress is null) 
delete top (100) from staging where inProgress is null

    para limpar a tabela de preparação.

    • 0

relate perguntas