SQL 开始事务但不提交

发布于 2024-12-01 01:51:23 字数 361 浏览 0 评论 0原文

如果我开始一个事务,但从未调用 COMMIT。数据会发生什么情况? 我有一个较小的数据库(比如一百万个左右的 SKU)..我将其导出为 1024 的小偶数块..(第三方约束限制了我的文件大小)。
我必须标记已导出的记录..

例如。更新产品集导出 = 1,其中 sku = '1234';

现在每隔一段时间我都会遇到一个导致第三方文件写入工具崩溃的问题。 但这发生在给定记录的文件创建之前。

所以我在想是否在更新记录之前调用开始事务,并仅在确认文件已构建后才提交。

这可能会导致一些没有其孪生的开始交易。

所以两个问题..有更好的方法吗? (除了摆脱有缺陷的第三方) 或者,属于从未提交的事务一部分的记录会发生什么情况?

If I begin a transaction, but never get to call COMMIT. What happens to the data?
I have a smallish database (say a million or so SKU's).. I am exporting it in small even numbered chunks of 1024.. (third party constraints limit my file size).
I have to flag the records that have been exported..

eg. Update products set exported = 1 where sku = '1234';

Now every once and a while I have a problem that crashes the third party file writing tool.
But this happens before the file for the given record was created.

So I was thinking if I call begin transaction before I update the records, and commit only after I've confirmed the file was built.

This may result in a few begin transactions that don't have their twin.

So two questions.. Is there a better way? (apart from getting rid of the buggy third party)
Or what happens to records that were part of a transaction that was never committed?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

放赐 2024-12-08 01:51:23

您的事务将保持打开状态,直到连接完全关闭(而不仅仅是返回到连接池)。这很糟糕

要在没有显式事务的情况下进行更新并管理1024行块,请执行类似的操作

UPDATE TOP (1024)
   Products
SET
   exported = 1
OUTPUT
   INSERTED.*
WHERE
   exported = 0;

您可以修改它以使用具有“正在处理”“已导出”等的状态列,以便您知道何时读取但未导出内容

Your transactions stay open with the locks until the connection is fully closed (not just returning to the connection pool). This is bad

To do an UPDATE without an explicit transaction and manage 1024-row chunks, do something like this

UPDATE TOP (1024)
   Products
SET
   exported = 1
OUTPUT
   INSERTED.*
WHERE
   exported = 0;

You can modify this to use a status column that has "Processing" "Exported" etc so you know when stuff was read but not exported

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文