在不锁定表的情况下更新正在使用的表中的数据的最佳方法是什么？

发布于 2024-07-19 21:34:23 字数 634 浏览 4 评论 0原文

我在 SQL Server 2005 数据库中有一个经常使用的表。它有我们现有产品的可用性信息。我们每小时都会从仓库获取更新，在过去的几年里，我们一直在运行一个例程来截断表并更新信息。这只需要几秒钟，而且到目前为止还不是问题。现在有更多的人使用我们的系统来查询此信息，因此我们看到由于阻塞进程而导致大量超时。

...所以...

我们研究了我们的选择，并提出了一个缓解问题的想法。

我们会有两张桌子。表 A（活动）和表 B（非活动）。
我们将创建一个指向活动表（表 A）的视图。
所有需要此表信息的事物（4 个对象）现在都必须通过视图。
每小时例程将截断非活动表，用最新信息更新它，然后更新视图以指向非活动表，使其成为活动表。
该例程将确定哪个表处于活动状态并基本上在它们之间切换视图。

这有什么问题吗？在查询中切换视图会导致问题吗？这可以吗？

感谢您的专业知识。

额外信息

例程是一个 SSIS 包，执行许多步骤并最终截断/更新相关表
阻塞进程是查询此表的另外两个存储过程。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笔芯 2024-07-26 21:34:26

刚刚读到您正在使用 SSIS，

您可以使用以下位置的 TableDiference 组件： http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/0/Default.aspx

< br>
_{（来源：sqlbi.eu)}

通过这种方式，您可以将更改一一应用于表，但当然，这会慢得多，并且根据表大小，将需要服务器上更多的 RAM，但锁定问题将是完全纠正了。

回复收藏 0 原文

风柔一江水 2024-07-26 21:34:26

为什么不使用事务来更新信息而不是使用截断操作。

截断是不记录的，因此不能在事务中完成。

如果您的操作是在事务中完成的，则现有用户不会受到影响。

如何完成此操作取决于表的大小以及数据变化的剧烈程度等因素。如果您提供更多详细信息，也许我可以提供进一步的建议。

回复收藏 0 原文

贱贱哒 2024-07-26 21:34:26

一种可能的解决方案是最大限度地减少更新表所需的时间。

我首先创建一个临时表来从仓库下载数据。

如果您必须在最终表中执行“插入、更新和删除”

让我们假设最终表如下所示：

Table Products:
    ProductId       int
    QuantityOnHand  Int

并且您需要从仓库更新 QuantityOnHand。

首先创建一个临时表，如下所示：

Table Prodcuts_WareHouse
    ProductId       int
    QuantityOnHand  Int

然后创建一个“操作”表，如下所示：

Table Prodcuts_Actions
    ProductId       int
    QuantityOnHand  Int
    Action          Char(1)

更新过程应如下所示：

1.截断表 Prodcuts_WareHouse

2.截断表 Prodcuts_Actions

3.使用仓库中的数据填充 Prodcuts_WareHouse 表

4.用以下内容填充 Prodcuts_Actions 表：

插入：

INSERT INTO Prodcuts_Actions (ProductId, QuantityOnHand,Action)
SELECT     SRC.ProductId, SRC.QuantityOnHand, 'I' AS ACTION
FROM         Prodcuts_WareHouse AS SRC LEFT OUTER JOIN
                      Products AS DEST ON SRC.ProductId = DEST.ProductId
WHERE     (DEST.ProductId IS NULL)

删除

INSERT INTO Prodcuts_Actions (ProductId, QuantityOnHand,Action)
SELECT     DEST.ProductId, DEST.QuantityOnHand, 'D' AS Action
FROM         Prodcuts_WareHouse AS SRC RIGHT OUTER JOIN
                      Products AS DEST ON SRC.ProductId = DEST.ProductId
WHERE     (SRC.ProductId IS NULL)

更新

INSERT INTO Prodcuts_Actions (ProductId, QuantityOnHand,Action)
SELECT     SRC.ProductId, SRC.QuantityOnHand, 'U' AS Action
FROM         Prodcuts_WareHouse AS SRC INNER JOIN
                      Products AS DEST ON SRC.ProductId = DEST.ProductId AND SRC.QuantityOnHand <> DEST.QuantityOnHand

到目前为止，您还没有锁定最终表。

5.在事务中更新最终表：

BEGIN TRANS

DELETE Products FROM Products INNER JOIN
Prodcuts_Actions ON Products.ProductId = Prodcuts_Actions.ProductId
WHERE     (Prodcuts_Actions.Action = 'D')

INSERT INTO Prodcuts (ProductId, QuantityOnHand)
SELECT ProductId, QuantityOnHand FROM Prodcuts_Actions WHERE Action ='I';

UPDATE Products SET QuantityOnHand = SRC.QuantityOnHand 
FROM         Products INNER JOIN
Prodcuts_Actions AS SRC ON Products.ProductId = SRC.ProductId
WHERE     (SRC.Action = 'U')

COMMIT TRAN

通过上述所有过程，您可以将要更新的记录量最小化到所需的最低限度，因此更新时最终表将被锁定。

您甚至可以在最后一步中不使用事务，因此在命令之间表将被释放。

One possible solution would be to minimize the time needed to update the table.

I would first Create a staging table to download the data from the warehouse.

If you have to do "inserts, updates and deletes" in the final table

Lets suppose the finale table looks like this:

Table Products:
    ProductId       int
    QuantityOnHand  Int

And you need to update QuantityOnHand from the warehouse.

First Create a Staging table like:

Table Prodcuts_WareHouse
    ProductId       int
    QuantityOnHand  Int

And then Create an "Actions" Table like this:

Table Prodcuts_Actions
    ProductId       int
    QuantityOnHand  Int
    Action          Char(1)

The update process should then be something like this:

1.Truncate table Prodcuts_WareHouse

2.Truncate table Prodcuts_Actions

3.Fill the Prodcuts_WareHouse table with the data from the warehouse

4.Fill the Prodcuts_Actions table with this:

Inserts:

INSERT INTO Prodcuts_Actions (ProductId, QuantityOnHand,Action)
SELECT     SRC.ProductId, SRC.QuantityOnHand, 'I' AS ACTION
FROM         Prodcuts_WareHouse AS SRC LEFT OUTER JOIN
                      Products AS DEST ON SRC.ProductId = DEST.ProductId
WHERE     (DEST.ProductId IS NULL)

Deletes

INSERT INTO Prodcuts_Actions (ProductId, QuantityOnHand,Action)
SELECT     DEST.ProductId, DEST.QuantityOnHand, 'D' AS Action
FROM         Prodcuts_WareHouse AS SRC RIGHT OUTER JOIN
                      Products AS DEST ON SRC.ProductId = DEST.ProductId
WHERE     (SRC.ProductId IS NULL)

Updates

INSERT INTO Prodcuts_Actions (ProductId, QuantityOnHand,Action)
SELECT     SRC.ProductId, SRC.QuantityOnHand, 'U' AS Action
FROM         Prodcuts_WareHouse AS SRC INNER JOIN
                      Products AS DEST ON SRC.ProductId = DEST.ProductId AND SRC.QuantityOnHand <> DEST.QuantityOnHand

Until now you haven't locked the final table.

5.In a transaction update the final table:

BEGIN TRANS

DELETE Products FROM Products INNER JOIN
Prodcuts_Actions ON Products.ProductId = Prodcuts_Actions.ProductId
WHERE     (Prodcuts_Actions.Action = 'D')

INSERT INTO Prodcuts (ProductId, QuantityOnHand)
SELECT ProductId, QuantityOnHand FROM Prodcuts_Actions WHERE Action ='I';

UPDATE Products SET QuantityOnHand = SRC.QuantityOnHand 
FROM         Products INNER JOIN
Prodcuts_Actions AS SRC ON Products.ProductId = SRC.ProductId
WHERE     (SRC.Action = 'U')

COMMIT TRAN

With all the process above, you minimize the amount of records to be updated to the minimum necessary, and so the time the final table will be locked while updating.

You can even don't use a transaction in the final step, so between command the table will be released.

回复收藏 0 原文