交换 ms-sql 表

发布于 2024-07-14 20:38:21 字数 332 浏览 9 评论 0原文

我想以最好的方式交换到桌子。
我有一个 IpToCountry 表，并且每周根据我导入的外部 CSV 文件创建一个新表。

我发现进行切换的最快方法是执行以下操作：

sp_rename IpToCountry IpToCountryOld
go
sp_rename IpToCountryNew IpToCountry
go

这样做的问题是，在两者之间可能仍会访问该表。
如何在 SQL 中解决这个问题？
考虑使用 sp_getapplock 和 sp_releaseapplock，但我希望尽可能快地从表函数中读取数据。

原文

I want to swap to tables in the best possible manner.
I have an IpToCountry table, and I create a new one on a weekly basis according to an external CSV file which I import.

The fastest way I've found to make the switch was doing the following:

sp_rename IpToCountry IpToCountryOld
go
sp_rename IpToCountryNew IpToCountry
go

The problem with this is that the table might still be accessed in between.
How do I approach this problem in SQL?
In considered using sp_getapplock and sp_releaseapplock, but I want to keep the read from the table function as quick as possible.

分享到QQ

分享到微博

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

发布评论

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

作业与我同在 2024-07-21 20:38:22

假设您无法更新/插入现有表，为什么不使用查看？

例如，您最初可能将数据存储在名为 IpToCountry20090303 的表中，您的视图将如下所示：

CREATE VIEW IpToCountry
AS
SELECT * FROM IpToCountry20090303

当新数据传入时，您可以创建并填充 IpToCountry20090310 em> 表。填充表后，只需更新您的视图：

ALTER VIEW IpToCountry
AS
SELECT * FROM IpToCountry20090310

切换将完全是原子的，不需要任何显式锁定或事务。视图更新后，您可以简单地删除旧表（如果您愿意，也可以保留它）。

Assuming that you're unable to update/insert into the existing table, why don't you wrap all access to the table using a view?

For example, you might initially store your data in a table called IpToCountry20090303, and your view would be something like this:

CREATE VIEW IpToCountry
AS
SELECT * FROM IpToCountry20090303

When the new data comes in, you can create and populate the IpToCountry20090310 table. Once the table is populated just update your view:

ALTER VIEW IpToCountry
AS
SELECT * FROM IpToCountry20090310

The switch will be completely atomic, without requiring any explicit locking or transactions. Once the view has been updated, you can simply drop the old table (or keep it if you prefer).

回复收藏 0 原文

寒冷纷飞旳雪 2024-07-21 20:38:22

我在让分区函数大规模工作时遇到了问题。 CREATE 和 DROP PARTITION 是阻塞操作，您对阻塞几乎没有控制权，如果它无法获得锁定，它将失败，严重级别为 16，并终止您的连接 - 如果不重新建立连接，您将无法捕获并重试该连接连接。但它可能对你来说效果很好。另外，需要 MSS 企业版，不能使用 SE——对于一些较小或更注重成本的商店来说可能太多了。

我还发现视图重定义会在系统表和对象上大规模阻塞（在我的例子中，=事务量+不断插入的数据量），因此这些操作可能会在重新索引和 DTCC 等方面陷入死锁——在一种情况下，特别是 SSMS 中的用户（在所有事情中）尝试浏览对象资源管理器中的视图（有人需要告诉那些人有关 READPAST 的信息）。同样，您的里程可能会有所不同。

相比之下，sp_rename 在规模上对我来说效果很好：它使您可以控制锁定及其范围。要解决交换之前的阻塞问题，请尝试如下所示。从表面上看，这似乎在高容量时具有相同的规模问题......但我在实践中还没有看到它。所以，对我有用......但同样，每个人的需求和经历都是不同的。

DECLARE @dummylock bit 
BEGIN TRANSACTION 
BEGIN TRY
   -- necessary to obtain exclusive lock on the table prior to swapping
   SELECT @dummylock = 1 WHERE EXISTS (SELECT 1 FROM A WITH (TABLOCKX))
   -- may or may not be necessary in your case
   SELECT @dummylock = 1 WHERE EXISTS (SELECT 1 FROM B WITH (TABLOCKX))
   exec sp_rename 'A', 'TEMP'
   exec sp_rename 'B', 'A'
   exec sp_rename 'TEMP', 'B'
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   -- other error handling here if needed
   ROLLBACK TRANSACTION 
END CATCH

I've had problems getting partitioning functions to work at scale. CREATE and DROP PARTITION are blocking operations, and you have little control over the blocking, and if it can't get a lock it will fail with a severity level 16 and kill your connection -- which you can't trap and retry without reestablishing the connection. But it might work just fine for you. Also, MSS Enterprise Edition is required, you can't use SE -- might be too much for some smaller or more cost-concerned shops.

I've also found the view redef to block at high-scale (= transaction volume + sheer volume of constantly-inserted data, in my case) on sys tables and objects, so those operations can deadlock on things like reindexing and DTCCs -- and in one case, specifically with a user in SSMS (of all things) trying to browse views in the Object Explorer (somebody needs to tell those guys about READPAST). Again, your mileage may vary.

In contrast, the sp_rename works well for me at scale: it gives you control over the locking and the scope of it. To solve the blocking issue prior to the swap, try it as shown below. At face value this would seem to have the same scale issue at high volume... but I haven't seen it in practice. So, works for me... but again, everybody's needs and experiences are different.

DECLARE @dummylock bit 
BEGIN TRANSACTION 
BEGIN TRY
   -- necessary to obtain exclusive lock on the table prior to swapping
   SELECT @dummylock = 1 WHERE EXISTS (SELECT 1 FROM A WITH (TABLOCKX))
   -- may or may not be necessary in your case
   SELECT @dummylock = 1 WHERE EXISTS (SELECT 1 FROM B WITH (TABLOCKX))
   exec sp_rename 'A', 'TEMP'
   exec sp_rename 'B', 'A'
   exec sp_rename 'TEMP', 'B'
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   -- other error handling here if needed
   ROLLBACK TRANSACTION 
END CATCH

回复收藏 0 原文