主页 / dba / 问题 / 261617
Accepted
user960567
Asked: 2020-03-11 07:49:17 +0800 CST

每行具有图像数据库 base64 的表导致死锁/块

  • 772

我们有一张桌子，

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

我们有下面的查询，它导致了太多的死锁/阻塞。

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

请注意，不同的用户同时使用 MasterKey 密钥，但在窥视时间我们看到应用程序变得无响应。当我们看到日志时，我们发现了很多块/死锁。我们还在这张表中看到了 Sch-S 锁。

将 MasterKey 和 DetailKey 添加为主键是否可以解决此问题，或者我们可以做什么？

请注意，有时 JSON 大小很大，因为它包含 base64 格式的图像。

sql-server sql-server-2014

3 个回答

  1. Best Answer

    通常，您应该始终更改此方法：

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

    对此：

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

    忘记你曾经学过第一种方法。这就像去杂货店检查他们是否有鸡蛋，然后回家拿钱包，然后再回到杂货店买鸡蛋。

    此外，是的，聚集索引可以在许多情况下提供帮助。真的MasterKey是一把钥匙（例如它是独一无二的吗）？或者是MasterKey+DetailKey候选键的组合？在后一种情况下，将两列都作为键列的索引（聚集或不聚集）将有助于减少查询在找到具有该值的行后定位特定行必须执行的任何残留搜索MasterKey（这会延长阻塞并可能导致死锁取决于其他因素）。

    实际用例。首先，更改索引：

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

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

    然后将查询更改为：

    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. 将 MasterKey 和 DetailKey 添加为主键是否可以解决此问题，或者我们可以做什么？

    几乎。当前的死锁可能是由于表上没有正确的键而导致过度扫描造成的。此外，您应该使用第一个查询锁定目标行/范围，以确保您拥有执行插入所需的锁定。

    IE避免死锁的方法是锁定更多，更早。只有当两个会话首先获取兼容锁，然后再尝试获取不兼容锁时，才会发生死锁。如果你让他们预先获得不兼容的锁，死锁就会消失。

    例如

    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. 将 JSON 强制放入 TEXT 或 BLOB 列通常会导致阻塞，尤其是较大的图像，因为它们是大对象以及与之相关的所有问题。最常见的是并发、磁盘空间耗尽和事务日志增长。

    根据您对该表的隔离级别，它必须等待它被写入才能读取。如果您可以在更新行时提取先前写入的值，则可以使用 RCSI 或其他有助于读取的隔离级别。否则，请稍微拆分应用程序，以便您可以进行更多并发。这可以通过将有问题的数据移动到不同的模式来完成，使用 NoSQL 引擎处理 JSON 和 img 文件，使用 FILESTREAM 处理 img 文件（尽管还有其他一些考虑），以及任何其他隔离它的方法。

    我让开发人员将 zip 文件创建为 blob 列中的二进制文件，以用于大量冗余 JSON。它们导致阻塞，在某些情况下必须查看隔离级别来修复它。

    我真的很喜欢 Aaaron 的回答，他详细介绍了细节，根据我的经验，我们不得不处理非常大的 JSON 或 BLOB，例如通过隔离级别存储在列中的 img 或 zip 文件，原因有很多，包括无法将密钥更改为大小优化后仍然太大。

    • -1

相关问题