Jet OLEDB:事务提交模式和Jet OLEDB:用户提交同步有什么区别？
尽管 Jet/OLE DB 参数相对有详细记录 我无法理解这两个连接参数之间的区别：
第一个：
Jet OLEDB：事务提交模式 （DBPROP_JETOLEDB_TXNCOMMITMODE）
指示Jet是否写入数据 同步或异步磁盘 当事务提交时。
第二个：
Jet OLEDB：用户提交同步 （DBPROP_JETOLEDB_USERCOMMITSYNC）
表示是否进行了更改 交易中进行的写入被写入 同步或异步模式。
有什么区别？什么时候用哪个？
Althoug both Jet/OLE DB parameters are relativly well documented I fail to understand the difference between these two connection parameters:
The first one:
Jet OLEDB:Transaction Commit Mode
(DBPROP_JETOLEDB_TXNCOMMITMODE)Indicates whether Jet writes data to
disk synchronously or asynchronously
when a transaction is committed.
The second one:
Jet OLEDB:User Commit Sync
(DBPROP_JETOLEDB_USERCOMMITSYNC)Indicates whether changes that were
made in transactions are written in
synchronous or asynchronous mode.
What's the difference? When to use which?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这很长，所以这是简短的答案：
不要设置其中任何一个。这两个选项的默认设置可能是正确的。第一种是事务提交模式，控制Jet的隐式事务，并在显式事务之外应用，并设置为YES（异步）。第二个控制 Jet 在显式事务期间如何与其临时数据库交互，并设置为 NO（同步）。我想不出您想要在这里覆盖默认值的情况。但是，您可能需要显式设置它们，以防您在 Jet 数据库引擎设置已更改为默认设置的环境中运行。
现在，长篇大论的解释是：
我已经查阅了大量与 Jet 相关的资源，看看是否能找出这里的情况。这两个 OLEDB 常量似乎映射到顶级 DAO DBEngine 对象的 SetOptionEnum 的这两个成员（详细信息 此处（适用于没有 Access 帮助文件的用户）：
这些选项用于在运行时针对任何特定情况覆盖 Jet 数据库引擎的默认注册表设置连接，或永久更改注册表中存储的设置。如果您在注册表中查找 HLKM\Software\Microsoft\Jet\XX\，您会发现在您正在使用的 Jet 版本的密钥下有一些密钥，其中两个是：
Jet 3.5 数据库引擎程序员的指南定义了这些：
ImplicitCommitSync：“是”值表示 Microsoft Jet 将等待提交完成。 Yes 以外的值表示 Microsoft Jet 将异步执行提交。
UserCommitSync：当设置值为“是”时，Microwsoft Jet 将等待提交完成。任何其他值都意味着 Microsoft Jet 将异步执行提交。
现在，这只是您已经说过的话的重述。令人沮丧的是，第一个默认值为 NO，而第二个默认值为 YES。如果他们确实控制着同一件事，你会期望它们具有相同的值，否则冲突的值将成为问题。
但实际上关键在于名称，它反映了 Jet 关于如何在事务内部和外部提交数据写入的历史。在 Jet 3.0 之前，Jet 默认在显式事务之外进行同步更新，但从 Jet 3.0 开始，引入了隐式事务，并且默认使用该事务（Jet 3.5 中的注意事项 - 见下文）。因此，这两个选项之一适用于事务外部提交 (dbImplicitCommitSync)，另一个选项适用于事务内部提交 (dbUserCommitSync)。我最终在《Jet Database Engine 程序员指南》（第 607-8 页）中找到了对这些内容的详细解释：
该部分：
还困惑吗？我当然是。
对我来说，关键点似乎是 dbUserCommitSync 似乎控制 Jet 写入用于暂存 EXPLICIT 事务的临时数据库的方式，而 dbImplicitCommitSync 与 Jet 在显式事务之外使用其隐式事务的位置有关。换句话说，dbUserCommitSync 控制 BeginTrans/CommitTrans 循环内引擎的行为，而 dbImplicitCommitSync 控制 Jet 在显式事务之外的异步/同步方面的行为方式。
现在，关于“隐式事务的删除”部分：我的理解是，当您在事务外部循环记录集时，隐式事务适用于更新，但不再适用于事务外部的 SQL UPDATE 语句。按理说，提高逐行更新性能的优化会很好，但实际上对 SQL 批量更新没有太大帮助，因为 SQL 批量更新已经非常快了（相对而言）。
另请注意，事实上，可以通过两种方式执行此操作，这使得 DoCmd.RunSQL 能够进行不完整的更新。也就是说，如果使用 DoCmd.RunSQL 执行，则因 CurrentDB.Execute strSQL、dbFailOnError 失败的 SQL 命令可以运行完成。如果您关闭 DoCmd.SetWarnings，您不会收到错误报告，并且您没有机会回滚到初始状态（或者，如果您被告知错误并决定提交，无论如何）。
所以，我认为发生的事情是，通过 Access UI 执行的 SQL 默认情况下包装在事务中（这就是您获得确认提示的方式），但是如果您关闭提示并且出现错误，您将获得不完整的更新应用。这与 DBEngine 设置无关——这是 Access UI 执行 SQL 的方式问题（并且有一个选项可以关闭/打开它）。
这与 DAO 中的更新形成对比，从 Jet 3.0 开始，DAO 全部包含在隐式事务中，但从 Jet 3.5 开始，只有顺序更新包含在隐式事务中，而批处理 SQL 命令（INSERT/UPDATE/DELETE）则不然。
至少，这是我的阅读。
因此，关于您实际问题中的问题，在设置 OLEDB 连接时，您需要根据您正在做的事情为该连接设置 Jet DBEngine 的选项。在我看来，默认的 Jet DBEngine 设置是正确的，不应更改 - 您希望使用隐式事务进行编辑，在其中遍历记录集并一次更新一行（在显式事务之外） 。另一方面，您可以将整个事务包装在事务中并获得相同的结果，所以实际上，这仅适用于您正在遍历记录集并更新且未使用显式事务的情况，并且默认设置似乎对我来说非常正确。
在我看来，另一个设置 UserCommitSync 也是您绝对不希望保留的设置，因为在我看来，它适用于 Jet 在显式事务期间与其临时数据库交互的方式。在我看来，将其设置为异步非常危险，因为您基本上不知道提交数据时操作的状态。
This is very long, so here's the short answer:
Don't set either of these. The default settings for these two options are likely to be correct. The first, Transaction Commit Mode, controls Jet's implicit transactions, and applies outside of explicit transactions, and is set to YES (asynchronous). The second controls how Jet interacts with its temporary database during an explicit transaction and is set to NO (synchronous). I can't think of a situation where you'd want to override the defaults here. However, you might want to set them explicitly just in case you're running in an environment where the Jet database engine settings have been altered from their defaults.
Now, the long explanation:
I have waded through a lot of Jet-related resources to see if I can find out what the situation here is. The two OLEDB constants seem to map onto these two members of the SetOptionEnum of the top-level DAO DBEngine object (details here for those who don't have the Access help file available):
These options are there for overriding the default registry settings for the Jet database engine at runtime for any particular connection, or for permanently altering the stored settings for it in the registry. If you look in the Registry for HLKM\Software\Microsoft\Jet\X.X\ you'll find that under the key there for the Jet version you're using there are keys, of which two are these:
The Jet 3.5 Database Engine Programmer's Guide defines these:
ImplicitCommitSync: A value of Yes indicates that Microsoft Jet will wait for commits to finish. A value other than Yes means that Microsoft Jet will perform commits asynchronously.
UserCommitSync: When the setting has a value of Yes, Microwsoft Jet will wait for commits to finish. Any other value means that Microsoft Jet will perform commits asynchronously.
Now, this is just a restatement of what you'd already said. The frustrating thing is that the first has a default value of NO while the second defaults to YES. If they really were controlling the same thing, you'd expect them to have the same value, or that conflicting values would be a problem.
But the key actually turns out to be in the name, and it reflects the history of Jet in regard to how data writes are committed within and outside of transactions. Before Jet 3.0, Jet defaulted to synchronous updates outside of explicit transactions, but starting with Jet 3.0, IMPLICIT transactions were introduced, and were used by default (with caveats in Jet 3.5 -- see below). So, one of these two options applies to commits OUTSIDE of transactions (dbImplicitCommitSync) and the other for commits INSIDE of transactions (dbUserCommitSync). I finally located a verbose explanation of these in the Jet Database Engine Programmer's Guide (p. 607-8):
That section:
Confused yet? I certainly am.
The key point to me seems to me to be that dbUserCommitSync seems to control the way Jet writes to the TEMPORARY database it uses for staging EXPLICIT transactions, while dbImplicitCommitSync relates to where Jet uses its implicit transactions OUTSIDE of an explicit transaction. In other words, dbUserCommitSync controls the behavior of the engine while inside a BeginTrans/CommitTrans loop, while dbImplicitCommitSync controls how Jet behaves in regard to asynch/synch outside of explicit transactions.
Now, as to the "Removal of Implicit Transactions" section: my reading is that implicit transactions apply to updates when you're looping through a recordset outside of a transaction, but no longer apply to a SQL UPDATE statement outside a transaction. It stands to reason that an optimization that improves the performance of row-by-row updates would be good and wouldn't actually help so much with a SQL batch update, which is already going to be pretty darned fast (relatively speaking).
Also note that the fact that it is possible to do it both ways is what enables DoCmd.RunSQL to make incomplete updates. That is, a SQL command that would fail with CurrentDB.Execute strSQL, dbFailOnError, can run to completion if executed with DoCmd.RunSQL. If you turn off DoCmd.SetWarnings, you don't get a report of an error, and you don't get the chance to roll back to the initial state (or, if you are informed of the errors and decide to commit, anyway).
So, what I think is going on is that SQL executed through the Access UI is wrapped in a transaction by default (that's how you get a confirmation prompt), but if you turn off the prompts and there's an error, you get the incomplete updates applied. This has nothing to do with the DBEngine settings -- it's a matter of the way the Access UI executes SQL (and there's an option to turn it off/on).
This contrasts to updates in DAO, which were all wrapped in the implicit transactions starting with Jet 3.0, but starting with Jet 3.5, only sequential updates were wrapped in the implicit transactions -- batch SQL commands (INSERT/UPDATE/DELETE) are not.
At least, that's my reading.
So, in regard to the issue in your actual question, in setting up your OLEDB connection, you'd set the options for the Jet DBEngine for that connection according to what you were doing. It seems to me that the default Jet DBEngine settings are correct and shouldn't be altered -- you want to use implicit transactions for edits where you're walking through a recordset and updating one row at a time (outside of an explicit transaction). On the other hand, you can wrap the whole thing in a transaction and get the same result, so really, this only applies to cases where you're walking a recordset and updating and have not used an explicit transaction, and the default setting seems quite correct to me.
The other setting, UserCommitSync, seems to me to be something you'd definitely want to leave alone as well, as it seems to me to apply to the way Jet interacts with its temp database during an explicit transaction. Setting it to asynchronous would seem to me to be quite dangerous as you'd basically not know the state of the operation at the point that you committed the data.
您可能会认为 USERCOMMITSYNC=YES 是同步提交的选项。这就是造成混乱的原因。
我花了很长时间在谷歌上搜索这个主题，因为我发现旧的
vb6应用程序的行为与我在.net oledb/jet4中的行为不同现在我真的应该用指向我阅读的实际页面的链接来支持我要说的内容，但我现在找不到这些页面。
无论如何，我在浏览 MSDN 网站时发现了一个页面，该页面描述了
Jet3中的“设计”错误，该错误转置了USERCOMMITSYNC的功能，意味着值为 NO< /em> 获取同步提交。因此，MS 将默认值设置为NO，并且我们默认获得同步提交。正如大卫·芬顿在上面所描述的那样。我们都已经接受了这种行为。
但是，该文档接着解释了
oledb/Jet4中的行为已更改。基本上微软修复了他们的错误，现在USERCOMMITSYNC=YES的设置就可以实现它所说的功能。但他们改变了默认设置吗？我认为不是，因为现在我的显式事务不使用
oledb/jet4在 .Net 应用程序中同步提交。You'd think that
USERCOMMITSYNC=YESwould be the option to commit synchronously. And that is the cause of the confusion.I spent ages googling on this topic because I found that the behavior I was getting with old
vb6applications was not the same as I get in.net oledb/jet4Now I really should back up what I'm going to say with a link to the actual page(s) I read but I can't find those pages now.
Anyway, I was browsing MSDN website and found a page that described a 'by design' error in
Jet3which transposed the functionality ofUSERCOMMITSYNCmeaning a value of NO gets synchronous commit.Therefore MS set the default to NO and we get synchronous commit by default. Exactly as described above by David Fenton. A behavior we've all come to accept.
But, the document then went on to explain that the behavior in
oledb/Jet4has been changed. Basically MS fixed their bug and now a setting ofUSERCOMMITSYNC=YESdoes what it says.But did they change the default? I think not because now my explicit transactions are NOT committing synchronously in .Net applications using
oledb/jet4.