Início / dba / Perguntas / 261617
Accepted
user960567
Asked: 2020-03-11 07:49:17 +0800 CST

Tabela com banco de dados de imagem64 por linha causa deadlocks/bloqueio

  • 772

Temos uma mesa,

CREATE TABLE [dbo].[MyTable](
    [MasterKey] [uniqueidentifier] NOT NULL,
    [DetailKey] [varchar](100) NULL,
    [JSON] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_MyTable_details] ON [dbo].[MyTable]
(
    [MasterKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

E temos a consulta abaixo que está causando muitos deadlocks/blocks.

IF EXISTS(SELECT 1 from  [MyTable](nolock) where  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey)
BEGIN

 UPDATE [MyTable]
           SET [JSON] = @JSON
    WHERE  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey
END
ELSE
BEGIN
INSERT INTO [MyTable]
           ([MasterKey]
           ,[DetailKey]
           ,[JSON])
     VALUES
           (@MasterKey
           ,@DetailKey
           ,@JSON)
END

Observe que um usuário diferente usa a tecla MasterKey ao mesmo tempo, mas no momento da espiada, vemos que o aplicativo não responde. Quando vemos os logs encontramos muitos bloqueios/deadlocks. Também vemos bloqueios Sch-S nesta tabela.

A adição de MasterKey e DetailKey como chave primária pode corrigir esse problema ou o que podemos fazer?

Observe que às vezes o tamanho do JSON é enorme porque inclui imagens como base64.

sql-server sql-server-2014

3 respostas

  1. Best Answer

    Em geral, você deve sempre alterar essa abordagem:

    IF EXISTS ( /* scan the table with a where clause */ )
    UPDATE ( /* scan the table AGAIN with a where clause */ )
ELSE
    INSERT

    Para isso:

    UPDATE ( /* scan the table JUST ONCE with a where clause */ )
IF @@ROWCOUNT = 0
  INSERT

    Esqueça que você já aprendeu a primeira abordagem. É como ir ao supermercado para verificar se eles têm algum ovo, depois voltar para casa para pegar sua carteira e voltar ao supermercado novamente para comprar seus ovos.

    Além disso, sim, um índice clusterizado pode ajudar em muitos cenários. É MasterKeyrealmente uma chave (por exemplo, é única)? Ou é a combinação de MasterKey+ DetailKeya chave candidata? No último caso, um índice (agrupado ou não) com ambas as colunas como colunas-chave ajudará a reduzir qualquer pesquisa residual que a consulta tenha que fazer para localizar uma linha específica depois de encontrar as linhas com esse MasterKeyvalor (o que prolonga o bloqueio e pode levar a impasses dependendo de outros fatores).

    Caso de uso real. Primeiro, altere os índices:

    DROP INDEX [ix_MyTable_details] ON [dbo].[MyTable];

CREATE UNIQUE CLUSTERED INDEX cix_MyTable_details
  ON dbo.MyTable(MasterKey, DetailKey);

    Em seguida, altere a consulta para:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

UPDATE dbo.[MyTable]
  SET [JSON] = @JSON
  WHERE MasterKey = @MasterKey AND DetailKey = @DetailKey;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.[MyTable]
       ([MasterKey]
       ,[DetailKey]
       ,[JSON])
  VALUES
       (@MasterKey
       ,@DetailKey
       ,@JSON);
END

COMMIT TRANSACTION;
    • 7

  2. A adição de MasterKey e DetailKey como chave primária pode corrigir esse problema ou o que podemos fazer?

    Quase. Os deadlocks atuais provavelmente estão sendo causados ​​por varredura excessiva devido à falta de uma chave adequada na mesa. Além disso, você deve bloquear a linha/intervalo de destino com a primeira consulta para garantir que você tenha os bloqueios necessários para executar a inserção.

    Ou seja, a maneira de evitar impasses é bloquear mais e mais cedo. Os deadlocks ocorrem apenas quando duas sessões primeiro adquirem bloqueios compatíveis e, posteriormente, tentam adquirir bloqueios incompatíveis. Se você fizer com que eles adquiram os bloqueios incompatíveis de antemão, o impasse desaparece.

    POR EXEMPLO

    begin transaction
IF EXISTS(SELECT 1 from  [MyTable](updlock,holdlock) where  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey)
BEGIN

 UPDATE [MyTable]
           SET [JSON] = @JSON
    WHERE  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey
END
ELSE
BEGIN
INSERT INTO [MyTable]
           ([MasterKey]
           ,[DetailKey]
           ,[JSON])
     VALUES
           (@MasterKey
           ,@DetailKey
           ,@JSON)
END
commit transaction
    • 1

  3. Forçar JSON em uma coluna TEXT ou BLOB muitas vezes pode causar bloqueio, especialmente as imagens maiores simplesmente por serem objetos grandes e todos os problemas associados a isso. Os mais comuns são simultaneidade, espaço em disco esgotado e crescimento do log de transações.

    Dependendo do seu nível de isolamento nessa tabela, ela precisa esperar que ela seja escrita antes de poder ser lida. Se você puder extrair o valor escrito anterior enquanto a linha é atualizada, você pode usar RCSI ou um nível de isolamento diferente que ajudará nas leituras. Caso contrário, divida o aplicativo um pouco para que você possa fazer mais simultaneidade. Isso pode ser feito movendo os dados incorretos para um esquema diferente, usando um mecanismo NoSQL para seus arquivos JSON e img, usando FILESTREAM para arquivos img (embora isso tenha algumas outras considerações) e qualquer outra coisa para isolar isso.

    Eu tive desenvolvedores criando arquivos zip como um arquivo binário em uma coluna blob para JSONs redundantes massivos. Eles causaram bloqueio e, em vários casos, os níveis de isolamento tiveram que ser analisados ​​para corrigi-lo.

    Eu realmente gosto da resposta de Aaaron e ele passou pelos detalhes, pela minha experiência, tivemos que lidar com JSON ou BLOBs muito grandes, como arquivos img ou zip armazenados em uma coluna através de níveis de isolamento por vários motivos, incluindo as chaves que não podem ser alteradas para os tamanhos eram ainda muito grande quando otimizado.

    • -1

relate perguntas