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

你考虑过使用吗 snapshot isolation . 它将允许您为您的SSIS内容开始一个大的胖事务,并仍然从表中读取数据。

这个解决方案似乎比交换桌子要干净得多。

我认为这样做是错误的——更新表必须锁定它,尽管您可以将锁定限制为每页甚至每行。

我会考虑不截断表并重新填充它。这总是会干扰用户阅读它。

如果更新了表而不是替换了表,则可以用另一种方法来控制它——读取用户不应该阻塞表,并且可能能够避开乐观读取。

尝试将WITH(NOLOCK)提示添加到正在读取的SQL视图语句中。即使表格定期更新,您也应该能够让大量用户阅读。

就个人而言,如果您总是要引入停机时间来针对表运行批处理过程,那么我认为您应该在业务/数据访问层管理用户体验。引入一个表管理对象,该对象监视到该表的连接并控制批处理。

当新的批处理数据准备就绪时,管理对象将停止所有新的查询请求(甚至可能是排队?),允许完成现有查询,运行批处理,然后重新打开表进行查询。管理对象可以引发一个事件(batchprocessingEvent),UI层可以解释该事件,让人们知道表当前不可用。

我的0.02美元,

内特

刚读到你正在使用ssis

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

alt text http://www.sqlbi.eu/Portals/0/Articles/Table%20Difference%20Images/DataFlowSimple.png

通过这种方式,您可以将更改逐个应用到表中,但当然,这会慢得多,而且根据表的大小,服务器上需要更多的RAM,但锁定问题将完全得到纠正。

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

truncate未被记录,因此不能在事务中执行。

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

如何做到这一点将取决于表的大小以及数据变化的剧烈程度。如果你能提供更多细节,也许我可以进一步建议。

一个可能的解决方案是最小化更新表所需的时间。

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

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

假设最后一张表如下所示:

Table Products:
    ProductId       int
    QuantityOnHand  Int

您需要从仓库更新现有数量。

首先创建一个临时表,如:

Table Prodcuts_WareHouse
    ProductId       int
    QuantityOnHand  Int

然后创建这样的“操作”表:

Table Prodcuts_Actions
    ProductId       int
    QuantityOnHand  Int
    Action          Char(1)

更新过程应该是这样的:

1.截断表产品切割仓库

2.截断表格ProdCuts动作

3.将仓库中的数据填入ProdCuts仓库表。

4.在ProdCuts动作表中填写以下内容:

插入物:

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

通过以上所有的过程,您可以将要更新的记录数量最小化到所需的最小值,这样在更新时最终表将被锁定的时间也将最小化。

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

如果您可以使用SQL Server企业版,那么我建议您使用SQL Server分区技术。

您可以将当前所需的数据驻留在“活动”分区和“辅助”分区中数据的更新版本中(该分区不可用于查询,而是用于管理数据)。

一旦数据被导入到“辅助”分区中,您可以立即将“活动”分区移出,并将“辅助”分区移入,从而实现零停机和无阻塞。

一旦进行了切换,就可以开始截断不再需要的数据,而不会对新活动数据(以前的辅助分区)的用户产生不利影响。

每次需要执行导入作业时,只需重复/反转该过程。

要了解有关SQL Server分区的更多信息,请参阅:

http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx

或者你可以问我:—)

编辑:

另一方面,为了解决任何阻塞问题,可以使用SQL Server行版本控制技术。

http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx

我们在高使用率的系统上执行此操作,没有任何问题。但是,与所有数据库一样,确保它有帮助的唯一方法是在dev中进行更改,然后对其进行负载测试。不知道您的SSIS包还有其他什么功能,它仍然可能导致阻塞。

如果表不是很大,您可以在应用程序中短时间缓存数据。它可能不会完全消除阻塞,但会减少在发生更新时查询表的机会。

也许对阻塞的过程进行一些分析是有意义的,因为它们似乎是您的景观中已经改变的一部分。只需要一个写得不好的查询就可以创建您看到的块。除了写得不好的查询之外,表可能需要一个或多个覆盖索引来加速这些查询,并让您在不必重新设计已经工作的代码的情况下重新开始工作。

希望这有帮助,

比尔